In this lesson, you will learn how to enter different types of data in an Excel worksheet.
To create a worksheet that does something, you must enter data into the cells that make up the worksheet. There are many types of data that you can enter, including:
You can enter any combination of letters and numbers as text. Text is automatically left-aligned in a cell. To enter text into a cell:
Column headings and row headings are sometimes referred to as titles. The column headings appear across the top of the worksheet beneath the title. The row headings are on the left side of the worksheet, usually in Column A. Column headings describe what the numbers represent in a column. You can enter column headings to specify time periods such as years, months, days, dates, and so on. Row headings describe what the numbers represent in a row. You can enter row headings to identify income and expense items in a budget, subject titles, and other categories.
To enter column headings into a worksheet:
To enter row headings into a worksheet:
Figure 4.1
Data that you enter also appears on the formula bar as you type it.
Sometimes your column and row headings may spill over into the adjacent cells. This
means the column is too narrow for the text to fit. Don't worry. You can choose the
Format Column AutoFit Selection command to widen the column and accommodate the long
entries.
Panic Button: Bail out! To cancel an entry before you are done, click on the Cancel button (the button with the X on it), or press Esc.
Timesaver Tip: Numbers as Text You may want to enter a number as text (for example, a ZIP code). Precede your entry with a single quotation mark (`), as in `46220. The single quotation mark is an alignment prefix that tells Excel to treat the following characters as text and left-align them in the cell.
Valid numbers can include the numeric characters 09 and any of these special characters: + ( ) , $ % . . Numbers are automatically right-aligned. You can include commas, decimal points, dollar signs, percentage signs, and parentheses in the values that you enter. Although you can include punctuation, you may not want to. For example, rather than type a column of dollar amounts including the dollar signs, commas, and decimal points, you can type numbers such as 700 and 81295, and then format the column with currency formatting. Excel would then change your entries to $700.00 and 81,295, respectively. See Lesson 16 for more information.
To enter a number:
Panic Button: ####### If you enter a number, and it appears in the cell as all number signs (#######) or scientific notation (for example, 7.78E+06), don't worry--the number is okay. The cell is not wide enough to display the number. For a quick fix, select the cell, and choose Format Column AutoFit Selection. For more information, see Lesson 19. If the numbers signs or scientific notation still display in the cell, choose Format Cells, and choose the Number category.
You can enter dates and times in a variety of formats. When you enter a date using a format shown in Table 4.1, Excel converts the date into a number which represents the number of days since January 1, 1900. Although you won't see this number (Excel displays it as a normal date), the number is used whenever a calculation involves a date. This feature is called AutoFormat.
Format | Example |
MM/DD/YY | 4/8/58 or 04/08/58 |
MMMYY | Jan92 |
DDMMMYY | 28Oct91 |
DDMMM | 6Sep |
HH:MM | 16:50 |
HH:MM:SS | 8:22:59 |
HH:MM AM/PM | 7:45 PM |
HH:MM:SS AM/PM | 11:45:16 AM |
MM/DD/YY HH:MM | 11/8/80 4:20 |
HH:MM MM/DD/YY | 4:20 11/18/80 |
Timmesaver Tip: To Hyphen or to Slash You can use hyphens () or slashes (/) when typing dates. Capitalization is not important. For example, 21 FEB becomes 21Feb and FEB 21 also becomes 21Feb.
Panic Button: ####### If you enter a long date, and it appears in the cell as all number signs (#######), don't worry--the date is okay. The cell is not wide enough to display the date. For a quick fix, select the cell, and choose Format Column AutoFit Selection. For more information, see Lesson 19.
Panic Button: Day or Night? Unless you type AM or PM, Excel assumes that you are using a 24-hour military clock. Therefore, 8:20 is assumed to be AM, not PM. If you type 8:20 PM, Excel displays the military time equivalent: 08:20 in the formula bar.
Excel offers several features for helping you copy entries into several cells at the same time. For example, you might want to avoid typing the same data over and over.
These features are explained in greater detail in the following sections.
You can copy an existing entry into any surrounding cells, by performing the following steps:
An easier way to fill is to drag the fill handle in the lower right corner of the selected cell to highlight the cells into which you want to copy the entry (see Figure 4.2). When you release the mouse button, the contents of the original cell are copied to the selected cells.
Timesaver Tip: Copying Across Worksheets You can copy the contents of cells from one worksheet to one or more worksheets in the workbook. To copy to other worksheets, first select the worksheet you want to copy from and the worksheets you want to copy to (see Lesson 7). To do so, click the sheet tabs while holding down the Shift key. Then, select the cells you want to copy. Open the Edit menu, select Fill, and select Across Worksheets. Select All (to copy the cells' contents and formatting), Contents, or Formats, and select OK.
Figure 4.2
Drag the fill handle to copy the contents and formatting into neighboring cells.
Unlike Fill, which merely copies an entry to one or more cells, AutoFill copies intelligently. For example, if you want to enter the days of the week (Monday through Sunday), you type the first entry (Monday), and AutoFill inserts the other entries for you. Try it:
Figure 4.3
Drag the fill handle over the cells you want to fill.
Excel has the days of the week stored as an AutoFill entry. You can store your own
series as AutoFill entries. Here's how you do it.
Figure 4.4
Excel lets you create your own AutoFill series.
Now that you have your own AutoFill entry, you can type any item in the list and
use AutoFill to insert the remaining entries.
Timesaver Tip: Transforming Existing Entries to AutoFill If you have already typed the entries you want to use for your AutoFill entries, select the entries before you choose Options from the Tools menu. Click on the Custom Lists tab, and select the Import button. Excel lifts the selected entries from your worksheet and sticks them in the List Entries text box.
Although AutoFill is good for a brief series of entries, you may encounter situations in which you need more control or need to fill lots of cells with incremental entries. In such situations, you should use the series feature. Excel recognizes four types of series, shown in Table 4.2.
Series | Initial Entry | Resulting Series |
Linear | 1,2 | 3,4,5 |
100,99 | 98,97,96 | |
1,3 | 5,7,9 | |
Growth | 10, 20 | 40, 80, 160 |
10, 50 | 250, 1250, 6250 | |
Date | Mon | Tue, Wed, Thur |
Feb | Mar, Apr, May | |
Qtr1 | Qtr2, Qtr3, Qtr4 | |
1992 | 1993, 1994, 1995 | |
Autofill | Team 1 | Team 2, Team 3, Team 4 |
Qtr 4 | Qtr 1, Qtr 2, Qtr 3 | |
1st Quarter | 2nd Quarter, 3rd Quarter, 4th Quarter |
Figure 4.5
The Series dialog box.
When you type the first few letters of an entry, AutoComplete intelligently completes the entry for you, based on the entries you've already made in that column. AutoComplete works with data entered in columns only, not rows. For example, if you want to enter a column of countries, (England, Spain, Italy), you type all the entries once, and the next time you want to type one of these entries, AutoComplete inserts it for you. Try it:
Windows 95: AutoComplete and PickLists The AutoComplete and PickList features are new in Excel for Windows 95 and were not available in Excel 5. In this lesson, you learned how to enter different types of data and how to automate data entry. In the next lesson, you will learn how to edit entries.
© Copyright, Macmillan Computer Publishing. All rights reserved.