After you create a workbook, you can begin entering data. The simplest way to enter data is to click a cell and type a value, which is a method that works very well when you're entering a few pieces of data, but it is less than ideal when you're entering long sequences or series of values. For example, Craig Dewar, the VP of Marketing for Consolidated Messenger, might want to create a worksheet listing the monthly program savings that large customers can enjoy if they sign exclusive delivery contracts with Consolidated Messenger. To record those numbers, he would need to create a worksheet with the following layout.
Entering the sequence January, February, March, and so on repeatedly can be handled by copying and pasting the first occurrence of the sequence, but there's an easier way to do it: use AutoFill. With AutoFill, you enter the first element in a recognized series, grab the fill handle at the lower-right corner of the cell, and drag the fill handle until the series extends far enough to accommodate your data. A similar tool, FillSeries, enables you to enter two values in a series and use the fill handle to extend the series in your worksheet. For example, if you want to create a series starting at 2 and increasing by 2, you can put 2 in the first cell and 4 in the second cell, select both cells, and then use the fill handle to extend the series to your desired end value.
You do have some control over how Excel 2007 extends the values in a series when you drag the fill handle. For example, if you drag the fill handle up (or to the left), Excel 2007 extends the series to include previous values. If you type January in a cell and then drag that cell's fill handle up (or to the left), Excel 2007 places December in the first cell, November in the second cell, and so on.
Another way to control how Excel 2007 extends a data series is by holding down the key while you drag the fill handle. For example, if you select a cell that contains the value January and then drag the fill handle down, Excel 2007 extends the series by placing February in the next cell, March in the cell after that, and so on. If you hold down the key, however, Excel 2007 repeats the value January in each cell you add to the series.
Be sure to experiment with how the fill handle extends your series and how pressing the key changes that behavior. Using the fill handle can save you a lot of time entering data.
Other data entry techniques you'll use in this section are AutoComplete, which detects when a value you're entering is similar to previously entered values; Pick from Drop-down List, which enables you to choose a value from existing values in a column; and , which enables you to enter a value in multiple cells simultaneously.
The following table summarizes these data entry techniques.
If an AutoComplete suggestion doesn't appear as you begin typing a cell value, the option might be turned off. To turn on AutoComplete, click the Microsoft Office Button and click Excel Options. In the Excel Options dialog box, click the Advanced category. In the Editing Options section of the dialog box, select the Enable AutoComplete for cell values check box and click OK.
Another handy feature in the current version of Excel 2007 is the Auto Fill Options button that appears next to data you add to a worksheet using AutoFill.
Clicking the Auto Fill Options button displays a list of actions Excel 2007 can take regarding the cells affected by your fill operation. The options in the list are summarized in the following table.
In this exercise, you will enter a data series by dragging the fill handle, enter data by accepting AutoComplete values, enter cell data by using Pick From Drop-down List, and control how Excel 2007 formats an extended data series by setting the program's Auto Fill Options.
USE the Series workbook from the My Documents\Microsoft Press\Excel SBS\Data and Data Tables folder.
BE SURE TO start Excel 2007 before beginning this exercise.
OPEN the Series workbook.
CLOSE the Series workbook.