MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))

You can use the formula bar to edit the contents of a selected cell, or you can perform your editing "on location" in the cell. Office Excel 2007 also includes a few special features you can apply to tasks such as entering date sequences, which once used to involve editing each cell but are now semiautomatic, if you know where to find the "trigger."

Editing in Cells or in the Formula Bar

While typing or editing the contents of a cell, you can use Cut, Copy, Paste, and Clear to manipulate cell entries. Often, retyping a value or formula is easier, but using commands is convenient when you're working with long, complex formulas or with labels. These commands work just as they do in a word-processing program such as Word when you're working in a cell or in the formula bar. For example, you can copy all or part of a formula from one cell to another. For example, suppose cell A10 contains the formula =IF(NPV(.15,A1:A9)>0,A11,A12) and you want to type =NPV(.15,A1:A9) in cell B10.

To do so, select cell A10, and in the formula bar, select the characters you want to copy-in this case, NPV(.15,A1:A9). Then press Ctrl+C, or click the Copy button (located in the Clipboard group on the Home tab). Finally, select cell B10, type = to begin a formula, and press Ctrl+V (or click the Paste button).

When you type or edit formulas containing references, Excel gives you visual aids called range finders to help you audit, as shown in Figure 8-19, where we obviously have a problem with our SUM formula.

Figure 8-19: Double-click a cell containing a formula to edit it and to display range finders.

For more information about auditing, see "Auditing and Documenting Worksheets" on page 241.

Editing Options

The Advanced category in the Excel Options dialog box (click the Microsoft Office Button, Excel Options) contains an assortment of options that control editing-related workspace settings, as shown in Figure 8-20. These options include the following:

• After Pressing Enter, Move Selection This locks in the entry and makes the cell below active. To change the direction of the selection after you press Enter, use the Direction drop-down list. When you clear this check box, pressing Enter locks in the entry and leaves the same cell active.

• Automatically Insert A Decimal Point Ordinarily you type numbers and decimal points manually. To have Excel enter decimal points for you, select this option, and then select the number of decimal places you want. For example, when you type 12345 with two decimal places specified, Excel enters 123.45 in the cell. When you apply this option, Fixed Decimal appears in the status bar. This option applies only to entries you make after you select it, without altering existing data. It also applies only when you do not type a decimal point. If you type a number including a decimal point, the option has no effect.

• Enable Fill Handle And Cell Drag-And-Drop This is required for the direct manipulation of cells using the mouse. See "Moving and Copying with the Mouse" on page 192. Leaving the Alert Before Overwriting Cells option selected is always a good idea.

• Allow Editing Directly In Cells This is required for in-cell editing. See "Editing in Cells or in the Formula Bar" on page207.

• Extend Data Range Formats And Formulas This lets Excel apply formatting to new cells entered in a list or table, based on the formats of existing cells. See "Extending Existing Formatting" on page 219.

• Enable Automatic Percent Entry This helps you type values in cells with the Percentage format. When you select this check box, all entries less than 1 are multiplied by 100. When you clear this check box, all entries-including those greater than 1-are multiplied by 100. For example, in a cell to which you have already applied the Percentage format, typing either .9 or 90 produces the same result-90%-in the cell. If you clear the Enable Automatic Percent Entry check box, typing 90 results in the displayed value 9000% (as long as you have applied the Percentage format to the cell).

  Note

  A quick way to apply the Percentage format to a clean cell is to type a number as a percentage. For example, type 1% in a cell, and the cell then displays subsequent numbers in the same Percentage format.

• Enable AutoComplete For Cell Values This lets Excel suggest cell entries by comparing existing values it finds in the same column as you type. See "Letting Excel Help with Typing Chores" on page 230.

• Zoom On Roll With IntelliMouse Ordinarily, if your mouse has a wheel, rotating it causes the worksheet to scroll (or zoom while pressing Ctrl). Select this check box to switch the behavior of the wheel so that the worksheet zooms when you rotate the wheel (or scrolls while you press Ctrl).

• Alert The User When A Potentially Time-Consuming Operation Occurs If an editing operation will affect a large number of cells, this option controls whether you will be notified and lets you specify the number of cells it takes to trigger the notification.

• Use System Separators Ordinarily, Excel defaults to the designated numeric separators for decimals and thousands (periods and commas, respectively) specified by your Windows system settings. If you want to specify alternative separators, you can do so here.

• Show Paste Options Buttons/Show Insert Options Buttons This shows the smart tag menu after pasting or inserting. Ordinarily when you perform a paste or insert operation, a smart tag action menu appears, offering various context-specific actions you can then perform. Clear these options to turn off these features.

• Cut, Copy, And Sort Inserted Objects With Their Parent Cells This is required to "attach" graphic objects to cells. See "Tools to Help You Position Objects on the Worksheet" on page 391.

Figure 8-20: Click the Microsoft Office Button, Excel Options, Advanced to display editing-related workspace settings.

The Automatically Insert A Decimal Point option in the Advanced category of the Excel Options dialog box is handy when you need to type long lists of numeric values. (It's equivalent to the floating-decimal feature available on most ten-key calculators.) For example, if you're performing a lengthy data-entry task such as typing multiple dollar values on a worksheet, select the Automatically Insert a Decimal Point option, and click 2 in the Places list. Then just type numbers, and press Enter, saving you an extra keystroke for the decimal point in each entry. If you're entering 1,000 values, typing 295 instead of 2.95 eliminates 25 percent of the keystrokes you would otherwise have to perform. However, you need to be careful to either type trailing zeros or add decimal points to some numbers. For example, you would usually type 5 to enter a 5.00 value, but with two fixed decimal places turned on, the same entry becomes 0.05, making it necessary for you to type either 500 or 5. to correctly place the decimal point.

Filling and Creating Data Series

As described earlier in this chapter, the fill handle has many talents to make it simple to enter data in worksheets. Uses of the fill handle include quickly and easily filling cells and creating data series using the incredibly useful AutoFill feature.

Take a look at Figure 8-21. If you select cell B2 in this worksheet and drag the fill handle down to cell B5, Excel copies the contents of cell B2 to cells B3 through B5. However, if you click the smart tag action menu that appears after you drag, you can select a different AutoFill action, as shown in Figure 8-22.

Figure 8-21: Copy the contents of a cell to adjacent cells by dragging the fill handle.

Figure 8-22: Create a simple series by dragging the fill handle and then clicking Fill Series on the smart tag action menu.

Inside Out-Create Decreasing Series

Generally, when you create a series, you drag the fill handle down or to the right, and the values increase accordingly. You can also create a series of decreasing values, however, by dragging the fill handle either up or to the left. Select the starting values in cells at the bottom or to the right of the range you want to fill, and then drag the fill handle back toward the beginning of the range.

Notice that the AutoFill Options smart tag looks a little different from the standard smart tag, a visual clue that you can perform special tasks with the selected cells.

If you click Fill Series on the smart tag action menu, Excel creates the simple series 21, 22, and 23 instead of copying the contents of cell C2. If, instead of selecting a single cell, you select the range C1:C2 in Figure 8-22 and drag the fill handle down to cell C5, you create a series that is based on the interval between the two selected values, resulting in the series 30, 40, and 50 in cells C3:C5. If you click Copy Cells on the AutoFill Options menu, instead of extending the series, Excel copies the cells, repeating the pattern of selected cells as necessary to fill the range. Instead of filling C3:C5 with the values 30, 40, and 50, choosing Copy Cells will enter the values 10, 20, and 10 in C3:C5.

If you select a text value and drag the fill handle, Excel copies the text to the cells where you drag. If, however, the selection contains both text and numeric values, the AutoFill feature takes over and extends the numeric component while copying the text component. You can also extend dates in this way, using a number of date formats, including Qtr 1, Qtr 2, and so on. If you type text that describes dates, even without numbers (such as months or days of the week), Excel treats the text as a series.

Inside Out-Fill Series Limited to 255 Characters

Excel lets you type up to 32, 767 characters in a cell. However, if you want to extend a series using AutoFill, the selected source cells cannot contain more than 255 characters. If you try to extend a series from an entry of 256 characters or more, Excel copies the cells rather than extending the series. This is not really a bug but is a side effect of the Excel column-width limitation of 255 characters. Besides, a 256-character entry is not going to be readable on the screen anyway. If you really need to create series out of humongous cell entries like this, perhaps a little worksheet redesign is in order. Otherwise, you'll have to do it manually.

Figure 8-23 shows some examples of simple data series created by selecting single cells containing values and dragging the fill handle. We typed the values in column A, and we extended the values to the right of column A using the fill handle. Figure 8-24 shows examples of creating data series using two selected values that, in effect, specify the interval to be used in creating the data series. We typed the values in columns A and B and extended the values to the right of column B using the fill handle. These two figures also show how AutoFill can create a series even when you mix text and numeric values in cells. Also note that we extended the values and series in Figure 8-24 by selecting the entire range of starting values in cells A3:B12 before dragging the fill handle to extend them, showing how Excel can extend multiple series at once. (We applied the bold formatting after filling, to make it easier to differentiate the starting values.)

Figure 8-23: Create simple data series by selecting a single value and dragging the fill handle.

Figure 8-24: Specify data series intervals by selecting a range of values and dragging the fill handle.

Inside Out-How AutoFill Handles Dates and Times

AutoFill ordinarily increments recognizable date and time values when you drag the fill handle, even if you initially select only one cell. For example, if you select a cell that contains Qtr 1 or 1/1/2008 and drag the fill handle, AutoFill extends the series as Qtr 2, Qtr 3, or 1/2/2008, 1/3/2008, and so on. If you click the smart tag action menu after you drag, you'll see that special options become available if the original selection contains dates or the names of days or months:

An interesting feature of this menu is Fill Weekdays, which not only increments a day or date series but also skips weekend days. Depending on the original selection, different options might be available on the smart tag action menu.

Extending with AutoFill

Sometimes you can double-click the fill handle to extend a series from a selected range. AutoFill determines the size of the range by matching an adjacent range. For example, in Figure 8-25, we filled column A with a series of values. Then we filled column B by selecting the range B1:B2 and double-clicking the fill handle. The newly created series stops at cell B5 to match the adjacent cells in column A. When the selected cells contain something other than a series, such as simple text entries, double-clicking the fill handle copies the selected cells down to match the length of the adjacent range.

Figure 8-25: We extended a series into B3-B5 by selecting B1-B2 and double-clicking the fill handle.

Dragging the Fill Handle with the Right Mouse Button

When you use the right mouse button to fill a range or extend a series, a shortcut menu appears when you release the button, as shown in Figure 8-26. This menu differs somewhat from the AutoFill Options smart tag menu and lets you specify what you want to happen in advance, as opposed to the smart tag menu's ability to change the action after the fact.

Figure 8-26: If you right-click and drag the fill handle, this shortcut menu appears when you release the mouse button.

The box that appears on the screen adjacent to the pointer indicates what the last number of this sequence would be if we dragged the fill handle like usual (with the left mouse button)-in this case, 160. The Linear Trend command creates a simple linear series similar to that which you can create by dragging the fill handle with the left mouse button. Growth Trend creates a simple nonlinear growth series, using the selected cells to extrapolate points along an exponential growth curve. In Figure 8-27, rows 4 through 6 in column A contain a series created using Linear Trend, and the same rows in column C contain a series created using Growth Trend, using the same starting values.

Figure 8-27: We created a linear trend series in column A and a growth trend series in column C.

Using the Series Command

When you click Series on the shortcut menu that appears when you press the right mouse button and drag the fill handle, the Series dialog box appears, letting you create custom incremental series. Alternatively, you can click the Series command on the Fill menu (located in the Editing group on the Home tab) to display the Series dialog box shown in Figure 8-28.

Figure 8-28: Use the Series dialog box for more control when creating a series.

Use the Series dialog box to specify an interval with which to increment the series (step value) and a maximum value for the series (stop value). Using this method has a couple of advantages over direct mouse manipulation techniques. First, you do not need to select a range to fill, and second, you can specify increments (step values) without first selecting cells containing example incremented values. You can select example values if you want, but it is not necessary.

The Rows option tells Excel to use the first value in each row to fill the cells to the right. The Columns option tells Excel to use the first value in each column to fill the cells below. For example, if you select a range of cells in advance that is taller than it is wide, Excel automatically selects the Columns option when you open the Series dialog box. Excel uses the Type options in conjunction with the start values in selected cells and the value in the Step Value box to create your series. If you select example cells first, Step Value reflects the increment between the selected cells.

The Linear option adds the value specified in the Step Value box to the selected values in your worksheet to extend the series. The Growth option multiplies the last value in the selection by the step value and extrapolates the rest of the values to create the series. If you select the Date option, you can specify the type of date series from the options in the Date Unit area. The AutoFill option works like using the fill handle to drag a series, extending the series using the interval between the selected values; it determines the type of data and attempts to "divine" your intention. Selecting the Trend check box extrapolates an exponential series, but it works only if you select more than one value before displaying the Series dialog box.

For more about typing dates, see "Entering a Series of Dates" on page 523.

Using the Fill Menu Commands

Use the Down, Right, Up, and Left commands on the Fill menu shown in Figure 8-29 to copy selected cells to an adjacent range of cells. Before clicking these commands, select the range you want to fill, including the cell or cells containing the formulas, values, and formats you want to use to fill the selected range. (Comments are not included when you use these Fill commands.)

Suppose cell A1 contains the value 10. In Figure 8-29, we selected the range A1:K2 and then clicked Fill, Right to copy the value 10 across row 1. With the range still selected, we can click Fill, Down to finish filling the selected range with the original value.

Figure 8-29: Use the Fill menu for quick access to common fill actions.

The Across Worksheets command on the Fill menu copies cells from one worksheet to other worksheets in the same workbook. For more information about using the Across Worksheets command, see "Filling a Group" on page 241.

Distributing Long Entries Using the Justify Command Clicking Fill, Justify doesn't do what you might think it does. It splits a cell entry and distributes it into two or more adjacent rows. Unlike other Fill commands, Justify modifies the contents of the original cell.

For information about the other justify feature-that is, justifying text in a single cell-see "Justifying Text in Cells" on page 319.

For example, in the worksheet on the left in Figure 8-30, cell A1 contains a long text entry. To divide this text into cell-sized parts, select cell A1, and click Home, Fill, Justify. The result appears on the right in Figure 8-30.

Figure 8-30: Clicking Justify distributes the long label in cell A1 to cells A1-A5.

When you click Justify, Excel displays a message warning you that this command uses as many cells below the selection as necessary to distribute the contents. Excel overwrites any cells that are in the way in the following manner:

• If you select a multirow range, Justify redistributes the text in all selected cells. For example, you can widen column A in Figure 8-30, select the filled range A1:A5, and click Justify again to redistribute the contents using the new column width.

• If you select a multicolumn range, Justify redistributes only the entries in the leftmost column of the range but uses the total width of the range you select as its guideline for determining the length of the justified text. The cells in adjacent columns are not affected, although the justified text will appear truncated if the adjacent column's cells are not empty.

Creating Custom Lists

If you find yourself repeatedly entering a particular sequence in your worksheets, such as a list of names or products, you can use the Excel Custom Lists feature to make entering that sequence as easy as dragging the mouse. After you've created the sequence, you can enter it in any range of cells by typing any item from the sequence in a cell and then dragging the fill handle. For example, Figure 8-31 shows the single name we entered in cell A1 and the custom list we entered in cells A2:A9 by dragging the fill handle.

Figure 8-31: You can create custom lists that you can type by dragging the fill handle.

To create a custom list, follow these steps:

1. Click the Microsoft Office Button, and then click Excel Options.

2. Select the Personalize category. In the Top Options For Working With Excel area, click the Edit Custom Lists button.

3. With NEW LIST selected in the Custom Lists box, type the items you want to include in your list in the List Entries box. Be sure to type the items in the order you want them to appear.

4. Click Add to add the list to the Custom Lists box.

5. Click OK to return to the worksheet.

Importing Custom Lists

You can also create a custom list by importing the entries in an existing cell range. To import the entries shown in Figure 8-31, we first selected the range containing the list we wanted before opening the Excel Options dialog box. We then selected the Personalize category and clicked Edit Custom Lists. The address of the selected range appears in the Import List From Cells text box at the bottom of the dialog box. Then we clicked the Import button to add the selected entries as a new list. (You can also select the list after opening the dialog box when the cursor is in the Import List From Cells box.)

Extending Existing Formatting

The automatic-formatting feature lets you add new columns of data to a previously constructed table without having to apply formatting to the new cells. For example, if you want to add another column to the existing table in Figure 8-32, select cell E2, type the column heading, and then continue entering numbers in cells E3-E6.

Figure 8-32: The automatic format extension adds data to an existing table without reformatting.

Excel correctly surmises that you want the new entries to use the same formatting as the adjacent cells in column D. You can turn this feature off by clicking the Microsoft Office Button, clicking Excel Options, and then in the Advanced category, clearing the Extend Data Range Formats And Formulas check box.

Tables are a new feature in Excel 2007 with special qualities (or, if you prefer, a "reimagining" of the old Lists feature). If you are working in a table, additional options control the extension of formatting and formulas. Click the Microsoft Office Button, click Excel Options, and then select the Proofing category. Click the AutoCorrect Options button to display the AutoCorrect dialog box shown in Figure 8-33. The tab labeled AutoFormat As You Type contains the pertinent options.

Figure 8-33: The AutoCorrect dialog box controls automatic format and formula extension when working in tables.

As you can see in Figure 8-33, the options on the AutoFormat As You Type tab control whether Excel automatically creates hyperlinks whenever you type recognizable Internet and network paths; the automatic-formatting behavior shown in Figure 8-32; and whether formulas are automatically extended in tables, in a similar fashion as the automatic-formatting feature.

For more about AutoCorrect, see "Fixing Errors As You Type" on page 227. For more about tables, see Chapter 21, "Managing Information in Tables."