Paste Special

The Paste Special dialog box is extremely useful.
The most useful feature is probably the ability to copy the values of cells without copying the formulas or formatting.
If you want to quickly access this dialog box using a shortcut key, press (Ctrl + Alt + V) followed by the letter in parentheses.
You can also use the shortcut key (Alt + E, S).

Paste Options

All - (A). Pastes all aspects of the cell, values, formulas, formats, formatting etc. This is the same as just using the Paste command.
Formulas - (F). Pastes only the formulas, any relative references are adjusted automatically.
Values - (V). Pastes only the static values and text or the displayed values resulting from formulas.
Formats - (T). Pastes only the format attached to the copied cells.
Comments - (C). Pastes only the comments attached to the copied range of cells.
Validation - (N). Pastes only the Data Validation rules attached to the copied range of cells.
All Using Source Theme - (L). (Added in 2007). Pastes all cell contents in the document theme formatting that is applied to the copied data.
All Except Borders - (X). Pastes only the data, the borders or formatting are not changed.
Column Widths - (W). Pastes only the column widths from the copied range of cells.
Formulas and Number Formats - (R). Pastes only the formulas and their number formats. This is useful when copying values to cells that are already formatted.
Values and Number Formats - (U). Pastes only the values and their number formats. This does not copy the formulas.
All merging conditional formats - (Added in 2010). Merges conditional formatting.

Operations

These four operations allow you to combined the contents of the copy area and the paste area.
The data is not overwritten but is combined to produce new values.
None - This is the default and no mathematical operations are performed on the current selection.
Add - (D). Adds the value (or values) that have been copied to the current selection.
Subtract - (S). Subtracts the value (or values) that have been copied from the current selection.
Multiply - (M). Multiplies the current selection by the value (or values) that have been copied.
Divide - (I). Divides the current selection by the value (or values) that have been copied.
Skip Blanks - (B). This will ignore any blank cells that have been included in the copied range. If you are copying data onto existing data then any blank cells in the copy range will not be pasted. This prevents you from pasting in a blank value over a cell that already contains something. This is useful as it allows you to overwrite selected cells without overwriting all the cells.
Transpose - (E). If you transpose cells that contain formulas then the formulas are automatically adjusted.
Transposing your rows and columns can be easily done. Copy the cell range you wish to transpose and before pasting choose (Edit > Paste Special) and select the Transpose check box. The paste area must be outside the copy area.
If you want to transpose cells containing formulas, then the formulas and cell references and adjusted. If you don't want the formulas adjusted then make sure your formulas are absolute references.
Paste Link - (L). Links the pasted data from the copied cell or range to the new cell or range.

Copying Between Cells, Worksheets and Workbooks

Depending on what is on the clipboard and where it was copied from will influence what dialog box is displayed.
When you copy data between cells, worksheets and workbooks in the same session of Excel the Paste Special dialog box will always be the dialog box displayed on the left.
If the data has been copied from a different Excel session or from a different application altogether and you display the Paste Special dialog box then the dialog box on the right will be displayed.

Important

You cannot use the Paste Special after you have cut data, only when you have copied it.
To leave the formulas unaffected when you transpose a block of cells make sure the cell references are absolute.
The Skip Blanks option will prevent you pasting any blank cells on top of existing cells.
If you do not use Paste Special when pasting values created by formulas in a different worksheet you will get a #REF! error message.