Essential Editing Techniques
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:
SEE ALSO
For information about tracking and approving edits in a multiuser environment, see "Accepting or Rejecting Revisions."
- How to select cells and ranges
- How to clear cells and delete cells
- How to copy data from one cell to another
- How to use the new Clipboard toolbar
- How to move cells by dragging
- How to add new rows and columns to the worksheet
- How to undo and repeat commands
Selecting Cells and Ranges
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.
SEE ALSO
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:
Selecting a Range Using the Mouse
The following steps show you how to select a range of cells using the mouse:
- Position the cell pointer over the first cell you want to select.
- Hold down the mouse button, and then drag the mouse over the remaining cells in the selection. Release the mouse button.
- If you want to select additional, noncontiguous cell ranges— ranges of cells that don't touch—hold down the Ctrl key, and then repeat steps 1 and 2 until all the ranges have been selected. When you're finished, release the Ctrl key. Figure 16-1 shows a multiple-range selection.
NOTE
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.
Selecting a Range Using the Keyboard
The following steps show you how to select a range of cells using the keyboard:
- Use the arrow keys to move to the first cell you want to select.
- Hold down the Shift key, and then press the appropriate arrow key to select the remaining cells in the range. Release the Shift key.
- To select additional, noncontiguous cell ranges, press Shift+F8. The Add indicator appears on the status bar, indicating that you can add a range to the selection. Repeat steps 1 and 2 to add the range.
Figure 16-1. To select noncontiguous ranges, hold down the Ctrl key.
Clearing Cells and Deleting Cells
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.
TIP
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:
- Place the cell pointer in the cell, row, or column you want to delete from the worksheet. If you want to delete a range of cells, select the range.
- Choose Delete from the Edit menu. The dialog box shown in Figure 16-2 appears.
- Click the option button that corresponds to the way you want remaining cells moved after the deletion.
- Click OK to delete the selected cells and move other cells to fill the gap.
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.
Undoing Commands
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 MouseIf 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."
NOTE
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.
Redoing Commands
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.)
Repeating Commands
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.
CAUTION
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:
- Execute a command you plan to repeat. (The best commands are those that require several steps to complete.) For example, place a border around a cell, select the cell, choose Cells from the Format menu, click the Border tab, click the Outline box, and then click OK.
- Select the next cell you want to modify with the same command.
- Choose Repeat Format Cells from the Edit menu.
TIP
Think About Repetitive ActionsThe 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.)
Using Cut and Paste to Move Data
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.
NOTE
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:
- Select the group of cells you want to move.
- Choose Cut from the Edit menu. (You can also click the Cut button on the toolbar or press the Ctrl+X key combination.)
- Click the cell to which you want to move the data. (If you're moving a group of cells, highlight the cell in the upper left corner of the area you're copying to.)
- From the Edit menu, choose Paste. (Or click the Paste button on the toolbar or press the Ctrl+V key combination.)
Using the New Office Clipboard Toolbar
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.
Using Copy and Paste to Duplicate Data
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:
- Select the group of cells you want to copy.
- Choose Copy from the Edit menu. (You can also click the Copy button on the toolbar or press Ctrl+C.)
- Click the cell into which you want to copy the data. (If you're duplicating a group of cells, highlight the cell in the upper left corner of the area you're copying to.)
- Choose Paste from the Edit menu. (Or click the Paste button on the toolbar or press Ctrl+V.)
Moving Cells by Using Drag and Drop
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.)
WARNING
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.
