This article introduces Excel, the official number cruncher of Office. The purpose of Excel is to track, analyze, and tabulate numbers. Use the program to project profits and losses, formulate a budget, or analyze Elvis sightings in North America. Doing the setup work takes time, but after you enter the numbers and tell Excel how to tabulate them, you’re on Easy Street. Excel does the math for you. All you have to do is kick off your shoes, sit back, and see how the numbers stack up. This article explains what a workbook and a worksheet are, and how rows and columns on a worksheet determine where cell addresses are. You also discover tips and tricks for entering data quickly in a worksheet, how to format data, and how to construct data-validation rules to make sure that data is entered accurately.

Getting Acquainted with Excel

If you have spent any time in an Office program, much of the Excel screen will look familiar to you. The buttons on the Formatting toolbar Bold, the Align button, and the Indent buttons, for example work the same in Excel as they do in Word. The Font and the Font Size drop-down lists work the same as well. Any command in Excel that has to do with formatting text and numbers works the same in Excel and Word. The commands for opening new files, closing files, and creating files are also the same. An Excel file is called a workbook. Each workbook comprises one or more worksheets. A worksheet, also known as a spreadsheet, is a table where you enter data and data labels. At the bottom of the worksheet are tabs for visiting the other worksheets in the workbook. The beauty of Excel is that the program does all the calculations and recalculations for you after you enter the data. If you were to change the number in cell B7, Excel would instantly recalculate the total amount of rainfall in Sonoma County in cell F7. People who struggled in math class will be glad to know that you don’t have to worry about the math because Excel does it for you. All you have to do is make sure that the data and the formulas are entered correctly.

Rows, columns, and cell addresses

Not that anyone except an Enron accountant needs all of them, but an Excel worksheet has 256 columns and 65,536 rows. The rows are numbered, and columns are labeled A to Z, then AA to AZ, then BA to BZ, and so on. The important thing to remember is that each cell has an address whose name comes from a column letter and a row number. The first cell in row 1 is A1, the second cell is B1, and so on. You need to enter cell addresses in formulas to tell Excel which numbers to compute.

Workbooks and worksheets

When you open a new Excel file, you open a workbook, a file with three worksheets in it. The worksheets are called Sheet1, Sheet2, and Sheet3 (you can change their names and add more worksheets). To get from worksheet to worksheet, click tabs along the bottom of the Excel screen. Why three worksheets? Because you might need more than one worksheet for a single project. Think of a workbook as a stack of worksheets. Besides calculating the numbers in cells across the rows or down the columns of a worksheet, you can make calculations throughout a workbook by using numbers from different worksheets in a calculation.

Entering Data in a Worksheet

Entering data in a worksheet is an irksome activity. Fortunately, Excel offers a few shortcuts to take the sting out of it. These pages explain how to enter data in a worksheet, the different types of data, and how to enter text labels, numbers, dates, and times.

The basics of entering data

What you can enter in a worksheet cell falls in four categories:

 -  Text

 -  A value (numeric, date, or time)

 -  A logical value (True or False)

 -  A formula that returns a value, logical value, or text Still, no matter what type of data you enter, the basic steps are the same:

1. Click the cell where you want to enter the data or text label. A square appears around the cell to tell you that the cell you clicked is now the active cell. Glance at the left side of the Formula bar if you’re not sure which cell you’re about to enter data in. The Formula bar lists the cell address.

2. Type the data in the cell. If you find typing in the Formula bar easier, click and start typing there. As soon as you type the first character, you see the Cancel button (an X), the Enter button (a check mark), and the Edit Formula button (an equal sign) on the Formula bar.

3. Press the Enter key to enter the number or label. Besides pressing the Enter key, you can also press an arrow key or click the Enter button (the check mark) on the Formula bar. If you change your mind about entering data, click the Cancel button or press Esc to delete what you entered and start over.

Entering text labels

Sometimes a text entry is too long to fit in a cell. How Excel accommodates text entries that are too wide depends on whether there is data in the cell to the right of the one you entered the text in:

 -  If the cell to the right is empty, Excel lets the text spill into the next cell.

 -  If the cell contains data, the entry gets cut off. Nevertheless, the text you entered is in the cell. Nothing gets lost when it can’t be displayed on-screen. You just can’t see the text or numbers except by glancing at the Formula bar, where the contents of the cell can be seen in their entirety. To solve the problem of text that doesn’t fit in a cell, widen the column, shorten the text entry, re-orient the text, or wrap the contents of the cell. Wrapping means to run the text down to the next line, much the way the text in this paragraph runs to the next line when it reaches the right margin. Excel makes rows taller to accommodate wrapped text in a cell. To wrap text in cells, select the cells, choose Format -> Cells, select the Alignment tab in the Format Cells dialog box, and select the Wrap Text check box.

Entering numeric values

When a number is too large to fit in a cell, Excel displays pound signs (###) instead of a number or displays the number in scientific notation. You can always glance at the Formula bar, however, to see the complete number. And you can always widen the column to make the number display in its entirety. To enter a fraction in a cell, leave a blank space between the whole number and the fraction. For example, to enter 53⁄8, type the 5, press the spacebar, and type 3/8. Here’s a little trick for entering numbers with decimals quickly. To spare yourself the trouble of pressing the period key (.), you can tell Excel to enter the period automatically. Instead of entering 12.45, for example, you can simply enter 1245. Excel enters the period for you: 12.45. To perform this trick, choose Tools -> Options, select the Edit tab in the Options dialog box, choose Fixed Decimal, and, in the Places text box, enter the number of decimal places you want for numbers. Turn off this option when you want to go back to entering numbers the normal way.

Entering date and time values

Dates and times can be used in calculations, but entering a date or time value in a cell can be problematic because these values must be entered in such a way that Excel can recognize them as dates or times, not text. Entering date values Enter a date value in one of the following ways: m/d/yy 7/31/04 m-d-yy 7-31-04 d-mmm-yy 31-Jul-04 After you enter a date this way, Excel converts it to the format it prefers for dates: 7/31/2004. If you don’t enter the year, Excel assumes that the date you entered is in the current year. (You can enter fractions as well as dates in cells with the forward slash. However, to make sure that Excel recognizes the entry as a fraction, place a 0 in front of the fraction, if necessary. Excel interprets 3/8 as March 8, but if you enter 0 3/8, Excel understands you’re entering a fraction.) When it comes to entering two-digit years in dates, the digits 30 through 99 belong to the 20th century (1930–1999), but the digits 00 through 29 belong to the 21st century (2000–2029). For example, 7/31/04 refers to July 31, 2004, not July 31, 1904. To enter a date in 1929 or earlier, enter four digits instead of two to describe the year: 7-31-1929. To enter a date in 2030 or later, enter four digits instead of two: 7-31-2030. Dates entered in these formats are treated as text entries, not date values, and can’t be used in calculations:

 -  July 31, 2004

 -  31 July 2004 To enter a date directly in a formula, enclose the date in quotation marks (and make sure that the cell where the formula is entered has been given the Number format, not the Date format). For example, the formula =today()- ”1/1/2003” calculates the number of days that have elapsed since January 1, 2003.

Entering time values

Enter a time value in one of the following ways: h:mm AM/PM 3:31 AM h:mm:ss AM/PM 3:31:45 PM Hours, minutes, and seconds must be separated by colons (:). Unless you enter AM or PM with the time, Excel assumes that you’re operating on military time. For example, 3:30 is considered 3:30 in the morning, not 3:30 in the afternoon. Don’t enter periods after the letters am or pm (that is, not a.m., p.m.).

Combining date and time values

You can combine dates and time values by entering the date, a blank space, and the time:

 -  7/31/04 3:31 am

 -  7-31-04 3:31:45 pm Here are shortcuts for entering the current time or current date in a cell: Press Ctrl+; (semicolon) to enter the current date; press Ctrl+Shift+; (semicolon) to enter the current time. Not that you need to know it especially, but Excel converts dates and times to serial values for the purpose of being able to use dates and times in calculations. For example, July 31, 2004 is the number 38199. July 31, 2004 at noon is 38199.5. These serial values represent the number of whole days since January 1, 1904. The portion of the serial value to the right of the decimal point is the time, as a portion of a full day.

Entering Lists and Serial Data with the AutoFill Command

Data that falls in the “serial” category month names, days of the week, and consecutive numbers and dates, for example can be entered quickly with the AutoFill command. Believe it or not, Excel recognizes certain kinds of serial data and will enter it for you as part of the AutoFill feature. Instead of laboriously entering this data one piece at a time, you can enter it all at one time by dragging the mouse. Follow these steps to “autofill” cells:

1. Click the cell that is to be first in the series. For example, if you intend to list the days of the week in consecutive cells, click where the first day is to go.

2. Enter the first number, date, or list item in the series.

3. Move to the adjacent cell and enter the second number, date, or list item in the series. If you want to enter the same number or piece of text in adjacent cells, it isn’t necessary to take this step, but Excel needs the first and second items in the case of serial dates and numbers so that it can tell how much to increase or decrease the given amount or time period in each cell. For example, entering 5 and 10 tells Excel to increase the number by 5 each time, so that the next serial entry is 15.

4. Select the cells or cells you just entered data in. To select a single cell, click it; to select two, drag over the cells.

5. Click the AutoFill handle and start dragging in the direction in which you want the data series to appear on your worksheet. The AutoFill handle is the little black square in the lower-right corner of the cell. Finding it can be difficult. Carefully move the mouse pointer over the lower-right corner of the cell and, when you see the mouse pointer change into a black cross, click and start dragging. The AutoFill Options button appears after you enter the serial data. Click it and choose an option if you want to copy cells or fill the cells without carrying along their formats. To enter the same number or text in several empty cells, drag over the cells to select them or select each cell by holding down the Ctrl key as you click. Then type a number or some text and press Ctrl+Enter.