Chapter 8: Worksheet Editing Techniques

Cut and paste. Insert and delete. Undo and redo. It all seems elementary, but as always in Microsoft Office Excel 2007, many other features are hiding beneath the obvious approaches to the simplest tasks; in fact, after reading this chapter, you'll find solutions to problems you probably never even considered. We'll cover all the essential editing techniques, including editing multiple worksheets, checking spelling, selectively pasting entries, creating data series, and outlining and auditing worksheets.

Copying, Cutting, and Pasting

When you copy an item, Office Excel 2007 saves it in memory, using a temporary storage area called the Clipboard. You capture the contents as well as the formatting and any attached comments or objects.

For more information about comments, see "Auditing and Documenting Worksheets" on page 241. For more information about objects, see Chapter 10, "Creating Spiffy Graphics."

When you copy or cut cells, a marquee appears around the cell. (We used to refer to this scrolling dotted line as marching ants.) This marquee indicates the area copied or cut. You can even paste copied or cut cells onto other worksheets or workbooks without losing the marquee.

The Cut and Copy buttons on the Home tab are useful, but you should know the keyboard shortcuts for the quintessential editing commands listed in Table 8-1. You can click the equivalent buttons on the Ribbon, but really, if you never learn another keyboard shortcut, learn these.

Table 8-1: Essential Keyboard Shortcuts
Open table as spreadsheet

Press	To
Ctrl+C	Copy
Ctrl+X	Cut
Ctrl+V	Paste
Ctrl+Z	Undo
Ctrl+Y	Redo

Copying and Pasting

After you copy, you can paste more than once. As long as the marquee is visible, you can continue to paste the information from the copied cells. You can copy this information to other worksheets or workbooks without losing your copy area marquee. The marquee persists until you press Esc or perform any other editing action. The area you select for copying must be a single rectangular block of cells. If you try to copy nonadjacent ranges, you'll get an error message.

Collecting Multiple Items on the Clipboard

Using the Collect And Copy feature, you can copy (or cut) up to 24 separate items and then paste them where you want them-one at a time or all at once. You do this by displaying the Clipboard task pane shown in Figure 8-1 by clicking the Dialog Box Launcher next to the word Clipboard on the Home tab on the Ribbon.

Figure 8-1: The Clipboard task pane stores multiple items that you copy or cut.

Ordinarily when copying, you can work with only one item at a time. If you copy several items in a row, only the last item you copied is stored in the Clipboard. However, if you first display the Clipboard task pane and then copy or cut several items in succession, each item is stored in the task pane, as shown in Figure 8-1.

You can change the regular collect-and-copy behavior so Excel collects items every time you copy or cut, regardless of whether the Clipboard task pane is present. To do so, click the Options button at the bottom of the Clipboard task pane, as shown in Figure 8-1, and click Collect Without Showing Office Clipboard or Show Office Clipboard Automatically, depending on whether you want the task pane to appear. The latter option activates an additional option, Show Office Clipboard When Ctrl+C Pressed Twice, which is one of the "automatic" methods.

Each time you copy or cut an item, a short representation of the item appears in the Clipboard task pane. Figure 8-1 shows five items in the Clipboard task pane. You can paste any or all of the items wherever you choose. To paste an item from the Clipboard task pane, select the location where you want the item to go, and then click the item in the task pane. To empty the Clipboard task pane for a new collection, click the Clear All button.

Hunting and Gathering

You can use the Clipboard task pane to quickly assemble a list. Although the Collect And Copy feature is useful for editing, it can also be a great tool for gathering information. Copy items such as names or addresses from various locations in the order you want them to appear. Then click the Paste All button in the Clipboard task pane to paste all the items you have collected, in the order collected, into a single column.

Pasting Multiples

After you copy, press Ctrl+V to paste whatever you copied. It's a no-brainer. However, did you know that if you select a range of cells before pasting, Excel fills every cell in that range when you paste? Figure 8-2 illustrates this.

In Figure 8-2, we did the following:

• Copied cell A1 and then selected the range C1:C12 and pasted, resulting in Excel repeating the copied cell in each cell in the selected range

• Copied Cells A1:A4 and then selected the range E1:E12 and pasted, resulting in Excel repeating the copied range within the range

• Copied cells A1:A4 and then selected cell G1 and pasted, resulting in an exact duplicate of the copied range

Figure 8-2: Before you paste, select more cells than you copied to create multiple copies of your information.

Note 

If you select a paste range that contains more cells than the copied range, Excel repeats the copied cells until it fills the destination. However, if you select a paste range that is smaller than the copied range, Excel pastes the entire copied range anyway.

Using the Paste Options Smart Tag

Notice in Figure 8-2 that we clicked the Paste Options smart tag action menu that appears near the lower-left corner of the pasted range. This smart tag appears whenever and wherever you paste, offering action options applicable after pasting-a sort of "Smart Paste Special." The best part is that you can try each action in turn. Keep selecting paste options until you like what you see, and then press Enter. The following describes each item on the Paste Options smart tag action menu:

• Use Destination Theme Changes the formatting of the pasted data to match the theme of the destination. (Themes control the overall look of your documents and include specifications for colors, fonts, and objects.) This is the default action.

• Match Destination Formatting Copies formatted data into a differently formatted table without having to redo the formatting.

• Keep Source Formatting Retains the copied formatting.

• Values And Number Formatting Pastes values without losing number formats.

• Keep Source Column Widths Retains column widths. This option is like clicking Keep Source Formatting with the added action of "pasting" the column width.

• Formatting Only Leaves the contents of the cells alone and transfers the formatting. This works in the same way as the Format Painter button, located in the Clipboard group on the Home tab.

• Link Cells Instead of pasting the contents of the cut or copied cells, pastes a reference to the source cells, ignoring the source formatting.

For more about themes, see "Using Themes and Cell Styles" on page 275.

Cutting and Pasting

When you cut rather than copy cells, subsequent pasting places one copy in the selected destination, removes the copied cells from the Clipboard, removes the copied data from its original location, and removes the marquee. When you perform a cut-and-paste operation, the following rules apply:

• Excel clears both the contents and the formats of the cut range and transfers them to the cells in the paste range. Excel adjusts any formulas outside the cut area that refer to that cell.

• The area you select for cutting must be a single rectangular block of cells. If you try to select nonadjacent ranges, you'll get an error message.

• Regardless of the size of the range you select before pasting, Excel pastes only the exact size and shape of the cut area. The upper-left corner of the selected paste area becomes the upper-left corner of the moved cells.

• Excel overwrites the contents and formats of any existing cells in the range where you paste. If you don't want to lose existing cell entries, be sure your worksheet has enough blank cells below and to the right of the cell you select as the upper-left corner of the paste area to hold the entire cut area.

• You cannot use Paste Special after cutting. Furthermore, no smart tag menu appears when you paste after cutting.

Pasting Selectively Using Paste Special

Paste Special is quite possibly the most useful (and most used) power-editing feature. You can use this feature in many ways, but probably the most popular is copying the value in a cell without copying the formatting or the underlying formula. After you copy a cell or cells, click Paste on the Home tab, and click Paste Special to display the Paste Special dialog box shown in Figure 8-3. (You must copy to use Paste Special. When you cut, Paste Special is unavailable.) The most popular Paste Special actions are directly accessible as commands on the Paste menu on the Home tab, as shown on the left in Figure 8-3.

Figure 8-3: Paste Special is probably the most popular power-editing feature, and its most often used options are available as commands on the Paste menu.

Note 

The Paste menu is actually a button with a downward-pointing arrow below it; clicking the button is equivalent to clicking the Paste command. To display the menu shown on the left in Figure 8-3, click the arrow.

Note 

You can also open the Paste Special dialog box by right-clicking the cell where you want to paste and then clicking Paste Special.

Here's what the Paste Special options do:

• All Predictably, pastes all aspects of the selected cell, which is the same as clicking the Paste command.

• Formulas Transfers only the formulas from the cells in the copy range to the cells in the paste range, adjusting relative references. This option is also available as a command on the Paste menu.

• Values Pastes static text, numeric values, or only the displayed values resulting from formulas. This option is also available as the Paste Values command on the Paste menu.

• Formats Transfers only the formats in the copy range to the paste range.

  Note 

  You can quickly copy and paste formats from a single cell or from a range of cells using the Format Painter button, next to the Paste menu on the Home tab.

• Comments Transfers only comments attached to selected cells.

• Validation Pastes only the data validation settings you have applied to the selected cells.

• All Using Source Theme Transfers only the copied data and applies the theme that has been applied to the destination cells.

  For more information about themes, see "Using Themes and Cell Styles" on page 275.

• All Except Borders Transfers data without disturbing the border formats you spent so much time applying. This option is also available as the No Borders command on the Paste menu.

• Column Widths Transfers only column widths, which is handy when trying to make a worksheet look consistent for presentation.

• Formulas And Number Formats Transfers only formulas and number formats, which is helpful when copying formulas to previously formatted areas. Usually, you'll want the same number formats applied to formulas you copy, wherever they happen to go.

• Values And Number Formats Transfers only the resulting values (but not the formulas) and number formats.

Because the All option pastes the formulas, values, formats, and cell comments from the copy range into the paste range, it has the same effect as clicking Paste, probably making you wonder why Excel offers this option in the Paste Special dialog box. That brings us to our next topic-the Operation options.

Pasting Using Math Operators

You use the options in the Operation area of the Paste Special dialog box to mathematically combine the contents of the copied cells with the contents of the cells in the paste area. When you select any option other than None, Excel does not overwrite the destination cell or range with the copied data. Instead, it uses the specified operator to combine the copy and paste ranges.

For example, say you want to get a quick total of the Northern and Eastern regions in Figure 8-4. First you copy the Northern Region figures to column G, and then you copy the Eastern Region numbers in column D, select cell H9, and click Paste Special. You then select the Values and Add options in the Paste Special dialog box, and after clicking OK, you get the result shown at the bottom of Figure 8-4.

Figure 8-4: We used the Values option of Paste Special to add the totals from column D to those in column G.

On the CD You'll find the Humongous08. xlsm file in the Sample Files section of the companion CD.

The other options in the Operation area of the Paste Special dialog box combine the contents of the copy and paste ranges using the appropriate operators. Just remember that the Subtract option subtracts the copy range from the paste range, and the Divide option divides the contents of the paste range by the contents of the copy range. Also note that if the copy range contains text entries and you use Paste Special with an Operation option (other than None), nothing happens.

Select the Values option when you use any Operation option. As long as the entries in the copy range are numbers, you can use All, but if the copy range contains formulas, you'll get "interesting" results. As a rule, avoid using the Operation options if the paste range contains formulas.

Note 

Excel assigns the value 0 to blank spaces in the copy and paste ranges, regardless of which Operation option you select.

Pasting Links

The Paste Link button in the Paste Special dialog box, shown in Figure 8-4, is a handy way to create references to cells or ranges. Although the Paste Special dialog box offers more options, it is more convenient to use the Paste Link command on the Paste menu on the Home tab. When you click Paste Link, Excel enters an absolute reference to the copied cell in the new location. For example, if you copy cell A3, then select cell B5, click the Paste menu, then click Paste Link, Excel enters the formula =$A$3 in cell B5.

If you copy a range of cells, Paste Link enters a similar formula for each cell in the copied range to the same-sized range in the new location.

For more information about absolute references, see "Understanding Relative, Absolute, and Mixed References" on page 429.

Skipping Blank Cells

The Paste Special dialog box contains a Skip Blanks check box that you select when you want Excel to ignore any blank cells in the copy range. Generally, if your copy range contains blank cells, Excel pastes those blank cells over the corresponding cells in the paste area. As a result, empty cells in the copy range overwrite the contents, formats, and comments in corresponding cells of the paste area. When you select Skip Blanks, however, the corresponding cells in the paste area are unaffected.

Transposing Entries

One of the often-overlooked but extremely useful Paste Special features is the Transpose check box, which helps you reorient the contents of the copied range when you paste-that is, data in rows is pasted in columns, and data in columns is pasted in rows. (This option is also available as a command on the Paste menu.) For example, in Figure 8-5, we copied the two columns of data shown in cells D2:E6, and then we selected cell G2 and clicked Transpose on the Paste menu on the Home tab. This works both ways. If we subsequently select the range just pasted and click Transpose again, the data is pasted in its original orientation.

Figure 8-5: We copied cells D2-E6, selected cell G2, and then clicked Home, Paste, Transpose to redistribute the rows of data into columns.

Note 

If you transpose cells containing formulas, Excel transposes the formulas and adjusts cell references. If you want the transposed formulas to continue to correctly refer to non-transposed cells, make sure the references in the formulas are absolute before you copy them. For more information about absolute cell references, see "Using Cell References in Formulas" on page 428.

Inside Out-Using Paste Values with Arrays

As with any other formula, you can convert the results of an array formula to a series of constant values by copying the entire array range and-without changing your selection-clicking Home, Paste, Paste Values. When you do so, Excel overwrites the array formulas with their resulting constant values. Because the range now contains constant values rather than formulas, Excel no longer treats the selection as an array. For more information about arrays, see "Using Arrays" on page 468.

Pasting Hyperlinks

The Paste As Hyperlink command on the Paste menu on the Home tab has a specific purpose: to paste a hyperlink that refers to the copied data in the location you specify. When you create a hyperlink, it's as if Excel draws an invisible box, which acts like a button when you click it, and places it over the selected cell.

Hyperlinks in Excel are similar to Web links that, when clicked, launch a Web page. You can add hyperlinks to locations on the Web in your workbooks-a handy way to make related information readily available. You can use hyperlinks to perform similar tasks among your Excel worksheets, such as to provide an easy way to access other worksheets or workbooks that contain additional information. You can even create hyperlinks to other Microsoft Office documents, such as a report created in Microsoft Office Word or a Microsoft Office PowerPoint presentation.

Within Excel, you create a hyperlink by copying a named cell or range, navigating to the location where you want the hyperlink (on the same worksheet, on a different worksheet, or in a different workbook), and then clicking Home, Paste, Paste As Hyperlink. To create a hyperlink in and among Excel worksheets and workbooks, you must first assign a name to the range to which you want to hyperlink. (The easiest method is to select the cell or range and type a name in the name box at the left end of the formula bar.) Note that hyperlinks differ from Excel links, which are actually formulas.

For more information, see "Pasting Links" on page 189. For information about defining names, see "Naming Cells and Ranges" on page 441. For more information about hyperlinks, see Chapter 25, "Collaborating Using the Internet."

When you rest your pointer on a hyperlink, a tip appears showing you the name and location of the document to which the hyperlink is connected, as shown in Figure 8-6. To use a hyperlink, click it and hold the mouse button down until the pointer changes to a hand, and then release the mouse button to open the linked document. (This is an improvement in functionality for Excel 2007; in previous versions, it was the opposite-you clicked to activate the link and held the mouse to select the cell, which made it difficult to select or edit hyperlinked cells.)

Figure 8-6: We created hyperlinks to supporting workbooks at the bottom of this worksheet.

To edit or delete a hyperlink, right-click it, and then click Edit Hyperlink or Remove Hyperlink.

On the CD You'll find the Pacific Sales. xlsm, Pacific Sales Staff. xlsx, and Pacific Strategy. xlsx files in the Sample Files section of the companion CD.

Moving and Copying with the Mouse

Sometimes referred to as direct cell manipulation, this feature lets you quickly move a cell or range to a new location. It's that simple. When you select a cell or range, move the pointer over the edge of the selection until the arrow appears, and then click the border and drag the selection to wherever you like. As you drag, an outline of the selected range appears, which you can use to help position the range correctly.

To copy a selection rather than move it, hold down the Ctrl key while dragging. The pointer then appears with a small plus sign next to it, as shown in Figure 8-7, which indicates you are copying rather than moving the selection.

Figure 8-7: Before you finish dragging, press Ctrl to copy the selection. A plus sign and destination reference appear next to the pointer.

Note 

If direct cell manipulation doesn't seem to be working, click the Microsoft Office Button, click the Excel Options button, and in the Advanced category, make sure the Enable Fill Handle And Cell Drag-And-Drop check box is selected.

You can also use direct cell manipulation to insert copied or cut cells in a new location, moving existing cells out of the way in the process. For example, on the left in Figure 8-8, we selected cells A6:E6 and then dragged the selection while holding down the Shift key. A gray I-beam indicates where Excel will insert the selected cells when you release the mouse button. The I-beam appears whenever the pointer rests on a horizontal or vertical cell border. In this case, the I-beam indicates the horizontal border between rows 8 and 9, but we could just as easily insert the cells vertically (which would produce unwanted results). You'll see the I-beam insertion point flip between horizontal and vertical as you move the pointer around the worksheet. To insert the cells, release the mouse button while still pressing the Shift key. When you release the mouse button, the selected cells move to the new location, as shown on the right in Figure 8-8.

Figure 8-8: The gray I-beam indicates where Excel will insert selected cells.

For information about using the keyboard for this task, see "Inserting Copied or Cut Cells" on page 197.

If you press Ctrl+Shift while dragging, the selected cells are both copied and inserted instead of moved. Again, a small plus sign appears next to the pointer, and Excel inserts a copy of the selected cells in the new location, leaving the original selected cells intact. You can also use these techniques to select entire columns or rows and then move or copy them to new locations.