Getting Out Your Scissors and Glue in Excel 2003

You can use the old standby Cut, Copy, and Paste commands to move or copy information in an Excel 2003 worksheet. These commands use the Clipboard as a kind of electronic halfway house where the information you cut or copy remains until you decide to paste it somewhere. Because of this Clipboard arrangement, you can use these commands to move or copy information to any other worksheet open in Excel or even to other programs running in Windows (such as a Word document).

To move a cell selection with Cut and Paste, follow these steps:

1. Select the cells you want to move.

2. Click the Cut button on the Standard toolbar (the button with the scissors icon).

Or, if you prefer, you can choose Cut from the cell shortcut menu or Edit --> Cut from the menu bar.

You can cut out all of this button-and-menu stuff and just press Ctrl+X. Whenever you choose the Cut command in Excel, the program surrounds the cell selection with a marquee (a dotted line that travels around the cells' outline) and displays the following message on the status bar:

Select destination and press ENTER or choose Paste

3. Move the cell pointer to, or select, the cell in the upper-left corner of the new range to which you want the information moved.

4. Press Enter to complete the move operation.

Or, if you're feeling really ambitious, click the Paste button on the Standard toolbar, or choose Paste from the cell shortcut menu, or choose Edit --> Paste from the menu bar, or press Ctrl+V. (Do you think that there are enough pasting alternatives in Excel?)

Notice that when you indicate the destination range, you don't have to select a range of blank cells that matches the shape and size of the cell selection you're moving. Excel only needs to know the location of the cell in the upper-left corner of the destination range to figure out where to put the rest of the cells.

Copying a cell selection with the Copy and Paste commands follows an identical procedure to the one you use with the Cut and Paste commands. After selecting the range to copy, you have even more choices about how to get the information into the Clipboard. Instead of clicking the Copy button on the Standard toolbar or choosing Copy from the cell shortcut menu or Copy from the Edit menu, you can press Ctrl+C.

Keeping pace with the Paste Options

Right after you click the Paste button on the Standard toolbar or choose the Edit --> Paste command from the menu bar to paste cell entries that you copy (not cut) to the Clipboard, Excel displays a Paste Options button with its own drop-down button at the end of the pasted range. You can use the options available when you click the drop-down button to modify the paste operation in the following ways:

• Keep Source Formatting: When you select this option, Excel copies the formatting from the original cells and pastes this into the destination cells (along with the copied entries).

• Match Destination Formatting: When you select this option, Excel formats the copied entries according to the formatting assigned to the destination cell range.

• Values Only: When you select this option, Excel copies only the calculated results from any formulas in the source range into the destination range. This means that the destination range will consist of entirely of labels and values regardless of how many formulas exist in the source range.

• Values and Number Formatting: When you select this option, Excel copies the calculated results of any formulas along with the number formatting assigned to both the values and formulas in the source cell range to the destination range. This means that labels copied from the source range take on the formatting of the destination range, while the values retain the number format given to them in the source range.

• Values and Source Formatting: When you select this option, Excel copies the calculated results of any formulas along with all the formatting assigned to the labels, values, and formulas in the source cell range to the destination range. This means that all the labels and values in the destination range appear formatted just like the source range even though all the original formulas are lost and only the calculated values are retained.

• Keep Source Column Widths: When you select this option, Excel makes the width of the columns in the destination range the same as those in the source range when it copies their cell entries.

• Formatting Only: When you select this option, Excel copies only the formatting (and not the entries) from the source cell range to the destination range.

• Link Cells: When you select his option, Excel creates linking formulas in the destination range so that any changes that you make to the entries in cells in the source range are immediately brought forward and reflected in the corresponding cells of the destination range.

So what's so special about Paste Special?

Normally, unless you fool around with the Paste Options, Excel copies all the information in the range of cells you selected: formatting, as well the formulas, text, and other values you enter. If you want, use the Paste Special command to specify that only the entries be copied (without the formatting) or that just the formatting be copied (without the entries). You can also use this command to have Excel copy only values in a cell selection, which means that Excel copies all text entries and values entered in a cell selection but does not include formulas or formatting (just like selecting the Values Only Paste option described in the previous section). When you paste values, Excel discards all formulas in the cell selection and retains only the calculated values — these values appear in the new cell range just as though you had entered them manually.

To paste particular parts of a cell selection while discarding others, choose Edit --> Paste Special from the menu bar. When you choose Paste Special over the regular Paste command, Excel displays the Paste Special dialog box. Here you can specify which parts of the current cell selection to use by selecting the appropriate Paste Special radio button or check box as follows:

• All to paste all the stuff in the cell selection (formulas, formatting, you name it).

• Formulas to paste all the text, numbers, and formulas in the current cell selection without their formatting.

• Values to convert formulas in the current cell selection to their calculated values.

• Formats to paste only the formatting from the current cell selection, leaving the cell entries in the dust.

• Comments to paste only the notes that you attach to their cells (kinda like electronic self-stick notes).

• Validation to paste only the data validation rules into the cell range that you set up with the Data --> Validation command (which enables you to set what value or range of values is allowed in a particular cell or cell range).

• All Except Borders to paste all the stuff in the cell selection without copying any borders you use there.

• Column Widths to apply the column widths of the cells copied to the Clipboard to the columns where the cells are pasted.

• Formulas and Number Formats to include the number formats assigned to the pasted values and formulas.

• Values and Number Formats to convert formulas to their calculated values and include the number formats you assign to all the pasted values.

• None to have Excel perform no operation between the data entries you cut or copy to the Clipboard and the data entries in the cell range where you paste.

• Add to add the data you cut or copy to the Clipboard and the data entries in the cell range where you paste.

• Subtract to subtract the data you cut or copy to the Clipboard from the data entries in the cell range where you paste.

• Multiply to multiply the data you cut or copy to the Clipboard by the data entries in the cell range where you paste.

• Divide to divide the data you cut or copy to the Clipboard by the data entries in the cell range where you paste.

• Skip Blanks check box when you want Excel to paste everywhere except for any empty cells in the incoming range. In other words, a blank cell cannot overwrite your current cell entries.

• Transpose check box when you want Excel to change the orientation of the pasted entries. For example, if the original cells' entries run down the rows of a single column of the worksheet, the transposed pasted entries will run across the columns of a single row.

• Paste Link button when you're copying cell entries and you want to establish a link between copies you're pasting and the original entries. That way, changes to the original cells automatically update in the pasted copies.

You can alternatively select the Formulas, Values, No Borders, Transpose, and Paste Link paste options directly from the pop-up menu attached to the Paste button on the Standard toolbar without having to open the Paste Special dialog box. Note: The No Borders option (from the Paste button of the Standard toolbar) is the same as the All Except Borders option from the Paste Special dialog box. Simply click the drop-down button attached to the Paste button and select the desired option from its pop-up menu. You can also open the Paste Special dialog box from this pop-up menu by selecting the Paste Special item at the very bottom of the menu.