Refer to this part of the book regularly when you need to remember how data is moved from cell to cell in Excel. In this section, you'll learn the following editing techniques:
For information about tracking and approving edits in a multiuser environment, see "Accepting or Rejecting Revisions."
Several Excel commands work with individual cells or groups of cells called ranges. Selecting a cell means making it the active cell; as a result, its name appears in the Name box to the left of the formula bar. To select an individual cell or a range of cells, you can use either the mouse or the keyboard.
If you have used previous versions of Excel, you'll notice that cool blue has replaced black as the default selection color in Excel 2000 when a range is selected.
To make cell ranges easier to work with, you can assign a name to a cell range, and then use the name in place of the cell reference. See "Using Range Names in Functions."
Excel requires a particular notation when you type out cell ranges. For example, A1:E1 represents a single row of five cells along the top edge of the worksheet, and E5:E8 represents a single column of four cells oriented vertically in the worksheet. Each cell range starts with a beginning cell name, followed by a colon and the ending cell name. You'll use cell ranges in many of the formulas and functions you create in Excel worksheets. In this illustration, a rectangular block of 45 cells (nine rows by five columns) named A1:E9 has been selected:
The following steps show you how to select a range of cells using the mouse:
In Figure 16-1 two ranges of cells are selected (A5:A9 and C5:C9). Only one of the cells, however, is the active cell—C5—denoted by the Name box and the bar around the cell. Most commands will affect all the selected cells, including the active cell. Entering new information and a few special commands will affect only the active cell and won't change anything in the other selected, or highlighted, cells.
The following steps show you how to select a range of cells using the keyboard:
Figure 16-1. To select noncontiguous ranges, hold down the Ctrl key.
Now that you know how to select ranges of cells, you can put your new skill to work and build a worksheet that is of value to you. For instance, if you want to clear the contents from a group of cells, select the cells and press the Delete key. Excel removes the content but keeps the cell formatting so that you can enter new values in the same format. (For example, if you clear cells formatted for dollar values, the next time you place a number in one of these cells it will be formatted for dollars.) To see the complete range of clear options, choose Clear from the Edit menu, and Excel will display a submenu that contains commands for clearing the formatting, the contents, the comments, or all three items together.
You can also clear the contents of a cell by right-clicking the cell and then choosing Clear Contents from the pop-up menu.
If you'd rather delete a single cell from the worksheet, moving the rows below it up or shifting columns over to the left, choose the Delete command rather than the Clear command. In many applications, the terms delete and clear have the same meaning, but in Excel there is a distinct difference between the two commands. Clearing a cell is like using an eraser to remove the contents or the format from a cell, but deleting a cell is like cutting it out with a tiny pocket knife and then moving the remaining cells up or over to fill the gap.
The following steps show you how to use the Delete command to delete cell ranges, entire rows, or entire columns from a worksheet:
For example, in Figure 16-2 where B3 is the selected cell, to delete cell B3 and move cells over to fill the gap, click Shift Cells Left. To delete cell B3 and move cells up to fill the gap, click Shift Cells Up. You can also click Entire Row or Entire Column to remove all the selected rows or columns. In Figure 16-2 you could, for example, remove row 3 or column B.
If you make a mistake when deleting a range of cells or executing another Excel command, you can undo your mistake by immediately choosing Undo from the Edit menu. For example, if you deleted a range of cells in error, choosing Undo will return the cells to the worksheet as if you had never deleted them.
Figure 16-2. The Delete command lets you remove a cell, row, or column from the worksheet and fill the gap with adjacent cells.
Selecting Rows and Columns Using the Mouse
If you want to quickly select part or all of your worksheet, you can click one of several hot spots on your screen.
To select an entire column with a single mouse click, click the column letter at the top of the column. To select an entire row, click the row number on the left edge of the row. You can also select multiple columns or rows by selecting a row or column head and dragging across the heads of the rows or columns you want to select. Best of all, if you need to select the entire worksheet, you can click the Select All box in the upper left corner of the worksheet. Here you see a worksheet that has two columns selected.
You can also click the Undo button on the Standard toolbar, or press Ctrl+Z, to undo a command.
The Undo button on the Standard toolbar also has multiple levels of undo (like Microsoft Word). This lets you "go back in time" to fix editing mistakes you made 3, 4, or 10 commands back. This is an extremely useful feature, because now and then you'll probably think better of a modification you made that took several steps to accomplish. By clicking the small arrow attached to the Undo button, you can scroll through a list of the edits you have made and pick the one you want to reverse. (See Figure 16-3.) Excel will then undo each command, from your most recent action back to and including the one you just picked.
Figure 16-3. Using the Undo button, you can undo one or more editing mistakes in the past.
Undo has a few limitations. For example, you can't undo the actions of adding a new worksheet to your workbook or deleting an existing worksheet. You also can't undo the actions of saving revisions to a file or customizing the Excel interface. If Undo isn't available for a particular command, the Undo command on the Edit menu will be dimmed and will read "Can't Undo."
Excel is smart about tracking your actions during a given work session. It doesn't create an Undo command when you use the scroll bars, press keyboard navigation keys, run online Help, or look for cell data using the Go To or Find commands. However, your ability to undo ends when you close your workbook or exit from Excel.
What happens if you decide to, well, undo an Undo command? For example, what do you do if you delete a range of cells, restore them with Undo, and then, on reflection, decide to remove them after all? One option is to select the cells again and choose Delete. But Excel makes it even easier to be fickle. After you use the Undo command, Excel changes its name to Redo on the Edit menu so that you can run your last command again. This gives you the opportunity to switch back and forth between two different editing commands to see which result you like best. Excel also adds the commands you have reversed to the Redo button on the Standard toolbar, letting you redo several commands at once. (This makes the Redo button the functional opposite of the Undo button, allowing you to restore and remove edits you have made.)
Below the Undo command on the Edit menu is the Repeat command, which allows you to repeat the command you just executed but at a different place in the worksheet. Here's how it works: If you just used the Cells command on the Format menu to place a border around cell B3, Excel will display a Repeat Format Cells command on the Edit menu. This allows you to add the same border to a new cell by simply highlighting the new cell and clicking Repeat on the Edit menu. You can make this even faster by pressing F4 or the shortcut key combination Ctrl+Y.
The Redo and Repeat commands are different animals (albeit from the same savanna), so be sure not to confuse them.
Follow these steps to use the Repeat command:
Think About Repetitive Actions
The Repeat command is a speed feature, designed to help you work faster in Excel. But most people forget to use it because they don't anticipate repetitive actions. Think about how you work, and you might discover several clever uses for the Repeat command. (We use Repeat most often for formatting labels and changing number formats.)
At times you'll want to move cell entries from one place to another on your worksheet. To accomplish this task, you can use the Cut and the Paste commands on the Edit menu. When you cut a range of cells using the Cut command, Excel places a dotted-line marquee around the cells to indicate which cells will be moved, and then it places the cell contents (including comments and formatting) into a temporary storage location known as the Windows Clipboard. When you select a new location for the data and choose Paste, the cells and their formatting are pasted from the Clipboard into their new location and the original cells are replaced. (If you're pasting a range of cells, they're inserted in a block.) To cancel the move after the marquee appears, press the Escape key.
If you cut and paste more than one cell, they must be in a block. Excel doesn't allow you to move noncontiguous blocks of cells. Also, in contrast to other Windows applications, Excel only lets you paste once after you cut. To paste multiple times, use the Copy command.
Figure 16-4 shows a group of cells after the Cut command was chosen (notice the marquee), while Figure 16-5 shows the same worksheet after the Paste command was chosen. Note that when you use the Paste command, you can also copy over cells containing data that you don't want to delete, so be cautious when moving information. As alternatives to using the Edit menu's Cut and Paste commands, you can use the Cut and Paste buttons on the Standard toolbar or the standard Windows key combinations Ctrl+X and Ctrl+V.
Figure 16-4. The Cut command marks the selected cells with a marquee.
Figure 16-5. The Paste command copies data from the Clipboard into the active cell.
The following steps show you how to move a range of cells using the Cut and Paste commands:
Once you have cut or copied two pieces of data into the Clipboard, Excel 2000 displays the Office Clipboard toolbar, a special editing tool that retains the last 12 items placed into the Clipboard by any Windows application. Figure 16-6 shows the Clipboard toolbar in action, which at that moment contained nine data items from my work session (five Excel worksheet ranges, two Word document selections, and two Microsoft Paint images).
Figure 16-6. The Clipboard toolbar stores up to 12 cut or copy operations for future use.
The Office Clipboard is a first-in, first-out stack that places the most recent cut or copy operation in the first open slot in the Office Clipboard. The toolbar can hold a total of 12 cut or copy operations—after that point, the toolbar begins discarding the oldest items in the Clipboard. However, we've noted one exception to this rule: the Office Clipboard won't let cut or copy operations in other applications push Excel data out of the Office Clipboard stack. This feature is apparently designed to preserve the Clipboard integrity of the application that is currently being used. (In other words, if you jump out of Excel to edit an e-mail message, you won't come back to discover that the Clipboard toolbar contains only Microsoft Outlook data.)
You can paste any one of the Office Clipboard items into your worksheet. Just select the cell in which you want to place the data, and click the Clipboard toolbar icon corresponding to the data you want to paste. To paste all of the items in the Office Clipboard, click the Paste All button. To empty out the Office Clipboard and start with a clean slate, click the Clear Clipboard button.
When you're finished using the Office Clipboard toolbar, click the Close button on the toolbar.
If you just want to duplicate a range of cells in the worksheet, not move them, you can use the Copy command on the Edit menu. This command places a copy of the cells you have selected into the Clipboard, and you can transfer these cells any number of times to your worksheet using the Paste command. The Copy command indicates the cells you're duplicating with the dotted-line marquee so that you can see what you're copying as you do it. As when you use the Cut command, you're limited to copying contiguous (touching) blocks of cells when you use the Copy command. If you don't use the Office Clipboard toolbar, only the most recent cut or copy operation is pasted.
To speed up your copy operations, you can use the Copy button on the Standard toolbar or the Ctrl+C key combination.
The following steps show you how to copy a range of cells using the Copy and Paste commands:
The fastest way to move a group of worksheet cells is by dragging. By using the drag-and-drop technique, you can edit a worksheet in an efficient and visibly uncomplicated way—by dragging a group of cells from one location to another. To enable drag-and-drop editing, you need to select cells (usually with the mouse), release the mouse button, and then move the cell pointer toward an outside edge of the selected cells until the cell pointer changes into the arrow pointer. When the pointer changes shape, you can hold down the left mouse button and drag the selection to a new location. As you move the cells, Excel displays both an outline of the range and the current range address, so that you can align the cells properly in your worksheet. (See Figure 16-7.)
If you happen to drop cells onto existing data when you drag them, Excel will warn you that you're about to replace the contents of your copy destination. Click OK if you want to replace the old cells, or click Cancel if you want to choose a new place for the data.
To copy cells by dragging, hold down the Ctrl key while you drag the selected cells. When you drag with the Ctrl key down, a plus (+) sign is added to the arrow pointer to let you know you're copying data.
Figure 16-7. The quickest way to move cells in the worksheet is by using the drag-and-drop mouse technique.
Now and then you'll want to add new rows or columns to your worksheet to create space. You might decide to add cells because your existing data is too crowded, or perhaps you're creating a report that has changed in scope and requires a new layout to communicate effectively. You add new rows and columns to your worksheet by using the Rows and Columns commands on the Insert menu. When you add rows or columns to your worksheet, the existing data shifts down to accommodate new rows or shifts to the right to allow for new columns.
The following steps show you how to add a row to your worksheet:
Inserting Individual Cells
Excel lets you add individual cells to your worksheet's rows or columns by choosing Cells from the Insert menu. Before you use the Cells command, you should select the worksheet cell below or to the right of the new cell you want. For example, if you want to add a new cell to column B between cells B3 and B4, highlight cell B4 before choosing the Cells command. When you choose Cells, the Insert dialog box appears.
Use the Insert dialog box to tell Excel to shift the cells to the right or down. If you're adding a cell to a column, click Shift Cells Down. If you're adding a cell to a row, click Shift Cells Right. You can also insert entire rows and columns by using the Insert Rows and Insert Columns commands.
The following steps show you how to add a column to your worksheet: