Paste special allows you to copy any cell or range of cells, and paste specific characteristics from those cells, it is also one of the most important features to learn when working with regularly within Excel.
Contents:
What is Paste Special?
In Excel, when you copy a cell or a range of cells, you are copying everything within those cells to your computer's "clipboard" or a short-term storage area, where all information, values, formatting, comments, everything is held. When you paste, you are transferring the held information held in the clipboard to a new location, in essence duplicating what was copied exactly as it was.
Paste special is different than standard paste, as it gives you control over what specifically is pasted. Instead of a blanket paste where everything is copied over, you can specify what exactly is brought over, be it formulas and no formatting, formatting and no values, or any combination of the options that we'll go over in the section below.
Mastering this technique will save you hours of fiddling with formatting, writing repetitive formulas, and doing simple arithmetic. Once you've set up the way a specific cell should look like and the proper way a formula should behave when copied, you can replicate these across your entire workbook with a few keystrokes.
How to Use Paste Special
Every time you wish to paste something, there must be something copied into the clipboard first.
To copy a cell or a range of cells, highlight an area or just one cell, and press "ctrl + C". (You can also right click and hit copy or use the copy button on the home tab but this is the slower method)
This will leave an area highlighted with a broken green line that rotates around the selected area. These are sometimes referred to as the marching ants.
Once something is stored in the clipboard, you can then move on to pasting it. There are two main ways to use paste special, and they are functionally the same but just require different inputs.
The fastest and most optimal way is with keyboard shortcuts. Instead of pressing "ctrl + V", to access the paste special menu, you'll need to press "alt + E + S". From here, one more key stroke can be used to select which paste special you actually wish to use. For instance, to paste as values, a quick string of "alt + E + S + V" will paste as values and takes a fraction of a second to type out.
It may be hard to use these shortcuts at first but the more you use them, the more you'll commit to muscle memory. You'll be pressing these keys without a second's thought.
Most of the shortcuts are also keyed in a way which makes it easy to remember what they do. "alt + E + S + V" for instance will paste as Values. "alt + E + S + F" will paste only Formulas, and so on.
The other method is using the mouse to either right click, then select paste special, or selecting the "Paste" option located on the Home ribbon tab, and accessing the menu that way.
This is the right click menu, the icons seen above the "Paste Special" option and to the right, signify different paste options detailed in the below section.
The "paste" button on the Home ribbon tab operates in much the same way. Press the button, and select your paste special type. The "Paste special" button at the bottom will bring up the full menu.
Every Type of Paste Special - Fully Explained
Paste Special - All
Paste all functions the same as the normal paste. All cell contents and formatting will be pasted over from the copied cells.
Paste Special - Formulas
Paste formulas will take any formulas from the copied range, and paste the same formulas, in the new section. The formula in the cells "= 2 + 2", for example, will be copied over.
This also goes for absolute and relative cell references in a formula. The formula "= A2" pasted down a row, will be "= A3", but "= $A$2" will stay "= $A$2".
Paste Special - Values
Paste values will only bring over what is actually displayed in each cell. Only the formula's final calculated value will be pasted, and will be a hard-coded value. No formatting is carried over.
Paste Special - Formats
Paste formats will paste all cell formatting but will leave any values or formulas held in the copied cells. Conditional formatting, text color, background color, and borders, are all brought over.
Paste Special - Comments and Notes
Paste comments and notes will paste any comments or notes in the copied cell range. No formatting, values, or formulas are carried over.
Paste Special - Validation
Paste validation will paste data validation rules and forms from the copied cells. Data validation restricts what information and values can actually be entered into a cell. This is commonly used in forms and files that are shared between people.
Paste Special - All using Source theme
Paste all using source theme works similar to paste format, but instead takes any sheet where you've changed the worksheet theme (under Page Layout > Themes), and copies that over to the new cell range. This will affect any pivot table themes, heading styles, and standard graph styles across your workbook. This would be especially useful if you have a standardized theme that must be used companywide.
Paste Special - All except borders
Paste all except borders brings over everything the standard paste brings over, minus any borders. Very useful when dealing with borders that you don't want to change but need to move data and formulas around.
Paste Special - Column widths
Paste column widths takes the widths of any selected column or columns and changes the widths of the receiving cells to match.
Paste Special - Formulas and number formats
Paste formulas and number formats will copy over all formulas and the resulting number's formatting. A combination of two of the above options. Most useful when using custom formatted numbers and formulas in conjunction with one another.
Paste Special - Values and number formats
Paste values and number formats will convert any formula to a hardcoded value and will also preserve the original number formatting. This is useful when you need to convert formulas to numbers without changing their formatting.
Paste Special - All merging conditional formats
Paste all merging conditional formats will paste cell contents as well as the conditional formatting options from any copied cells.
Paste Special - Operations
The paste special - operations option allows you to perform a mathematical operation on selected cells.
If you would like a more in-depth tutorial, check one out here. But basically, with the "operation" paste, you can simply select a cell value of whatever you want to add/subtract/multiply/divide with, and paste over the data that you want to change.
Paste Special - Skip Blanks
This is an option that can be used alongside any of the other paste special options. The skip blank option will not overwrite any cells in the paste range with any copied blank cells.
For instance, if you have two partial sets of data, you can select the entire table, and paste it over another set of partial data, and the blank cells copied over won't overwrite the cells in the paste range.
Paste Special - Transpose
Like skip blanks, transpose can be added in conjunction with any other paste special option or operation. Transpose will flip the direction of a copied range of cells. A vertical list pasted with transpose, will paste as a horizontal list. Horizontal ranges will paste as a vertical list in a single column.
Paste Special - Paste Link
Paste special will create cell references to the copied range. If you copy cell A2 and paste links somewhere else in the workbook, it will paste as "= A2".
Examples
1. Using Paste Special to Move Formulas Around
Paste special can be used to save you from retyping formulas that you've created somewhere in your workbook, but wish to re-use elsewhere.
A lot of times, depending on the case, you won't want to take any formatting when copying and pasting formulas, so in this case, we would use paste special - formulas, to only bring over the formulas and leave any formatting alone.
Whenever you are copying and pasting formulas, make sure that you understand absolute and relative cell references (formulas with a $ symbol in them) as this will greatly affect how a formula performs when being pasted.
= EOMONTH(B3,-1)+1Let's say for this example, I've written the above formula to calculate the first date of the month in which a product was shipped. Let's also assume that the yellow rows are a flag of something important, and we must preserve the formatting. A normal copy and paste will make all of the rows yellow, which we do not want.
VS
Instead, if we use the "Formulas" option in paste special, we can only pull over the formulas to all of the rows, leaving the formatting alone.
2. Using Paste Special to Convert Formulas to Values
Often times you will want to "value out" a section of a workbook or an entire workbook. Either for sharing or to make sure that no changes to one part of a workbook can affect another.
A great way to do this is to copy an entire range that contains formulas, and paste as values. This will value out the entire selected range.
On the left is the formula results, and the right is showing what is in each cell (using ctrl + `). If we want to get rid of the formulas but keep the values that the formulas are returning, copy the entire range, and paste as values. This will value out all of the formulas and give you hardcoded values to send out.
3. Using Paste Special to Copying Formatting From Any Excel File
Like the example above, paste special is also great to copy formatting from one area of a spreadsheet to another.
If you have a format that you need to replicate somewhere else, instead of completely redoing your formatting, simply select a range that contains the proper formatting, and paste formatting to move your formatting anywhere.
In this example, you can see a formatted table, and an unformatted table. By selecting the already formatted one and pasting over the unformatted one, you can replicate everything exactly as is, saving a ton of time.
Here, the table on the left is formatted correctly, and we want to bring over the same formatting to the updated table on the right.
In order to do this, first select the range with the formatting that we wish to copy, and then paste over the unformatted range.
