Leveraging Copy, Cut, Paste, and Paste Special

Problem

You'd like to take advantage of standard Windows copy-and-paste functionality but aren't familiar with the caveats for doing so within Excel.

Solution

Read the following discussion.

Discussion

Under Excel's Edit menu , you'll find the usual Cut, Copy, and Paste menu items. For the most part, these work just as they do in any other Windows program. To move the contents of a cell from one place to another, use the Cut and Paste operations. To copy a cell into other cells, use the Copy and Paste operations. There are a few things to be aware of when performing these operations in Excel:

• When you cut or copy and then paste, all of the cell's attributes are copied, along with the data or formula that it contains. This means things like font, alignment, borders, and patterns will be copied as well.
• When you cut and paste data to a new location, any formulas referring to its original cell location are automatically updated to refer to its new location. Copying a cell to another location has no effect on formulas that refer to the original cell.
• When you copy a cell containing a formula, relative cell references in the formula will be adjusted by the relative distance of the pasted cell from the copied cell.
• When you cut a formula and paste it to a new location, the formula will remain unchanged; i.e., it should still refer to the same cells and give the same results as before the cut-and-paste operation.

Sometimes you may want to cut or copy and then paste all of the format settings and data from one cell to another. In cases when you don't, you can use the Paste Special option, which is also under the Edit menu. When you select the Paste Special option, a dialog box like that shown in Figure 1-19 appears.

Figure 1-19. Paste Special dialog box

Here you can select exactly what it is you want to copy and paste. For example, if you want to copy and paste only cell format settings, you can select the Formats option. Similarly, if you want to copy and paste just a formula and not the cell's formatting, then you can select the Formulas option, and so on.

The Operation options allow you to specify some basic mathematical operations to perform on the data being copied and the contents of the destination cell. For example, say you copy a number from one cell and Paste Special it to a cell that already contains another number. If you select the Add option, the sum of the two cell values will be entered in the destination cell.

The "Skip blanks" option excludes empty cells from the selected range of cells being copied. The Transpose option is similar to a matrix transpose operation. For example, if you copy a column of numbers and paste it with the Transpose option, it will be converted to a row of numbers.

Here are some handy keyboard shortcuts for the basic cut-and-paste operations:

 

Cut

Ctrl-X

 

Copy

Ctrl-C

 

Paste

Ctrl-V

There is no shortcut for the Paste Special operation; however, you can right-click anywhere in your spreadsheet to bring up a pop-up menu containing the Paste Special option. I find this quicker than accessing the main menu bar.

Cut, Copy, Paste, and other options (e.g., Format Cells) are also shown on the right-click pop-up menu.