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.


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 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.


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 - 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.


 


Additional Shortcut Menu

There is another way to access some of these options and that is using a shortcut menu.
This shortcut menu can be displayed by holding down either the Shift, Ctrl or Alt key when you drag a cell using the right mouse button.
Make sure than when you select a cell (or range of cells) you do not click on the Fill Handle in the bottom right hand corner.


 

Move Here - Moves the source cells to the selected destination.
Copy Here - Copies the source cells to the selected destination. This is the same as the All option button on the (Edit > Paste Special) dialog box.
Copy Here as Values Only - Copies the values contained in the source cells to the selected destination cells but does not copy formulas. This is the same as the Values option on the (Edit > Paste Special) dialog box.
Copy Here as Formats Only - Copies the formats of the source cells to the destination cells, without affecting the contents. This is the same as the Formats option on the (Edit > Paste Special) dialog box.
Link Here - Creates linking formulas at the destination that refer to the source cells. This is the same as the Paste Link button on the (Edit > Paste Special) dialog box.
Create Hyperlink Here - Creates a web style link to the source cells in the selected destination.
Shift Down and Copy - Copies all aspects of the cell down to a cell below or to the right.
Shift Right and Copy - Copies all aspects of the cell down to a cell below or to the right.
Shift Down and Move - Moves the cell down to a cell below or to the right.
Shift Right and Move - Moves the cell down to a cell below or to the right.
Cancel - Closes the shortcut menu.


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.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext