This Excel training tutorial will teach you that copying information from one place and pasting it into another is very easy to do. You simply highlight the cells you wish to copy, press the Ctrl and C keys simultaneously before going to the area where you wish to paste them and pressing Enter. Well… no. You are likely to encounter this error:
While this example says #NAME?, it may also display #REF? There will be a yellow box appearing next to it that tells you what the problem is. The issue is that formulas, formats, values and other data within Excel cells are not copied properly when pasting in this manner. While you waste time reformatting and recalculating data to fix this, Excel has a feature called Paste Special. It allows you to choose what information you wish to include or exclude during the pasting process. It can be found in your Excel toolbar once you click on the arrow beneath Paste on the Clipboard box. Below we describe all the advanced Excel functions you will find there. This is what Paste Special will look like when you have clicked on it:
All
Selecting this option will copy the cells exactly as they are; including the formatting, formulas, values and any other information within.
Formulas
Clicking on this option will paste all the information within the selected cells, except for their formatting. So you will have the values, text, formulas and numbers copied over.
Values
If you click on this option, it will calculate values from the copied formula into the new cell selection.
Formats
This option will only copy the formatting within the chosen cells. It will not paste any other data such as numbers, text or formulas.
Comments
Choosing this option will only copy the notes that have been attached to the cells, without pasting any other information.
Validation
If you use the Data Validation Command and have set up specific rules for validating data within certain cells, then this option will paste only those rules.
All using Source Theme
By clicking on this option, all the information within the copied cells will be pasted over; including the cell styles applicable to them.
All except borders
This option is used when all the data within the selected cells is required, without the borders surrounding them.
Column widths
Selecting this option will paste the original column widths of the cells into the new area, along with the all the information within them.
Formulas and number formats
This is used when the pasted formulas and values must include the number formats originally assigned to them.
Values and number formats
This option will convert formulas into calculated values, as well as paste the number formatting that was assigned to the copied cells.
None
Clicking this option will ensure that no operation is performed by Excel when you copy cells and paste them elsewhere.
Add
If you click this option, Excel will automatically add the values that you have copied to those in the cells where you paste.
Subtract
This option will command Excel to subtract the values that you copy from those in the cells where you have selected to paste them.
Multiply
This should be clicked if you wish Excel to multiply the copied values with the ones already in the cells that you wish to paste them to.
Divide
Excel will divide the values that have been copied automatically with the values in the cells where you need to paste.
Skip blanks
If there are empty cells within the section you wish to copy, then this option will only paste the cells that have data in them.
Transpose
This advanced Excel option will swop horizontal entries with vertical ones. If you have headings listed horizontally, then they will be listed vertically once they have been transposed by Excel (or vice versa). The information within them will be automatically updated to reflect accurately.
Paste link
Use this option if you need to create a link between the original entries that have been copied and then pasted elsewhere. Once established, any changes you make to the original cells will also show in the pasted cells.
OK
Once you have selected the functions you wish to perform, then clicking this button will implement the changes requested.
Cancel
If you click on this button, then it will stop the entire paste action altogether.
We wrote this article at the request of one of our Excel Training Course student suggestion, he told us that Paste Special was a function that has really saved him a bunch of time on his Excel work. We recommend to anyone in South Florida looking to deepen their Excel skills, to take a look at our live excel courses.
Check out this Excel video on Paste Special from youtube:
