Paste Special is an extremely powerful tool allowing you to paste only certain aspects of your copied data such as the value without the underlying formula. It also provides the user with a means of consolidating data, creating links and transposing data tables.
- Copy the cells as you normally would
- Select the destination cell
- Click the arrow below the Paste button in the Clipboard group of the Home tab
- Select Paste Special
- Select the desired option and click Ok
A menu providing different paste options appears
In the top half of the dialogue box are various aspects of the data that you may paste. For example, the value only or validation criteria only
Excel pastes the data according to your demands stripping out any irrelevant aspects
In the second half of the Paste Special dialogue box are some consolidation options so that you can add/subtract/divide or multiply your copied value with the value of the cell you are pasting into
Skip Blanks ensures that any blank cells in the range that you have copied are not included within the paste
Transpose is incredible. It switches your table of data from rows to columns or vice versa. Check the transpose checkbox and select any other paste options you want to transpose your data.
Paste Link creates a link between the copied cell and the pasted cell. So if you copied cell D4 on Sheet1 and then clicked Paste Link button on cell F10 of Sheet2, the 2 cells are linked. If the content of cell D4 on Sheet1 changed, the content of cell F10 on Sheet2 is automatically updated.