Entering and Revising Data





Entering and Revising Data

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.

Tip

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.

Method

Action

AutoFill

Enter the first value in a recognized series and use the fill handle to extend the series.

FillSeries

Enter the first two values in a series and use the fill handle to extend the series.

AutoComplete

Type the first few letters in a cell, and if a similar value exists in the same column, Excel 2007 suggests the existing value.

Pick from Drop-down List

Right-click a cell, and from the shortcut menu that appears, choose Pick From Drop-down List. A list of existing values in the cell's column appears. Click the value you want to enter into the cell.

Select a range of cells to contain the same data, type the data in the active cell, and press .


Troubleshooting

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.

Option

Action

Copy Cells

Copies the contents of the selected cells to the cells indicated by the Fill operation.

Fill Series

Fills the cells indicated by the Fill operation with the next items in the series.

Fill Formatting Only

Copies the format of the selected cell to the cells indicated by the Fill operation, but does not place any values in the target cells.

Fill Without Formatting

Fills the cells indicated by the Fill operation with the next items in the series, but ignores any formatting applied to the source cells.

Fill Days, Weekdays, etc.

Changes according to the series you extend. For example, if you extend the cells Wed, Thu, and Fri, Excel 2007 presents two options, Fill Days and Fill Weekdays, and enables you to select which one you intended. If you do not use a recognized sequence, the option does not appear.


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.


1.
On the Monthly worksheet, select cell B3 and then drag the fill handle down until it covers cells B3:B7.

Excel 2007 repeats the value Fabrikam in cells B4:B7.

2.
Select cell C3, hold down the key, and drag the fill handle down until it covers cells C3:C7.

Excel 2007 repeats the value January in cells C4:C7.

3.
Select cell B8 and type the letter F.

Excel 2007 displays the characters abrikam in reverse video.

4.
Press to accept the value Fabrikam for the cell.

5.
In cell C8, type February.

6.
Right-click cell D8 and then click Pick From Drop-down List.

A list of values in column D appears below cell D8.

7.
From the list that appeared, click 2Day.

The value 2Day appears in cell D8.

8.
In cell E8, type 11802.14.

The value $11,802.14 appears in cell E8.

9.
Select cell B2 and then drag the fill handle so that it covers cells C2:E2.

Excel 2007 replaces the values in cells C2:E2 with the value Customer.

10.
Click the Auto Fill Options button and then click Fill Formatting Only.

Excel 2007 restores the original values in cells C2:E2 but applies the formatting of cell B2 to those cells.

CLOSE the Series workbook.




 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows