Copy and paste are two of the most frequently performed actions in Excel, but when you paste, you don't necessarily want to paste everything. Sometimes you need a Paste Special!
How can I Paste Special?
Once you have copied some cells, then instead of just using the standard paste (Ctrl V or the paste button), you can select Paste Special as follows.
| Mouse 🖱 | Keyboard ⌨ |
| Use the ribbon: (1) Home (2) Paste and (3) Paste Special (as shown in the screenshot above). |
Use one of the key combinations
|
Either way, you are then presented with the paste special dialogue box which offers lots of options, which we will now review.
In each case, select the option(s) you want and click OK or press enter on your keyboard to complete the special paste.
What Paste Special options do I have?
The Paste Special dialogue box is organised into three areas marked above, to answer these three questions:
- What do you want to paste?
- What operation should be carried out, if any?
- What special actions should be performed, if any?
Within each area, you can only select one option. Combinations of options across areas are sometimes allowed, sometimes not, depending on what you have selected. Unavailable options are automatically greyed out by Excel.
So let’s look at these three areas in turn.
- What do you want to paste?
The default is “All”, which is the same as standard paste if no other options are selected. This means everything is pasted including formulas, font and background formats, comments and notes.
The options I use the most here are:
- Formulas - pastes formulas only and nothing else, so formats validations and borders remain unchanged in the pasted-to cells. Example use: copy a formula from a cell which is formatted bold to other cells which are not bold and which should remain so.
- Values - pastes values only and nothing else. This option is so useful, it has its own blog!
- Formats – pastes formats only and nothing else. Example use: copy the number formatting from a cell which is correctly formatted (such as a data cell) to other cells, some of which contain other formulas (such as SUM formulas).
- Validation – pastes validation rules only and nothing else. These are the rules you can set up using data, data validation e.g., to restrict inputs to only positive numbers (see my video on data validation for more details). Example use: copy the validation from some input cells requiring positive numbers and which also contain data to other input cells also requiring positive numbers but where the copied data would not be relevant.
- All except borders - pastes everything (including formulas, font and background formats) but not borders. Example use: copy a SUM formula from one column to other columns where the column at the end has a border which you want to keep.
- Column widths - pastes only the column widths and nothing else. Example use: you have multiple sheets which should have the same look and feel and want to ensure that the column widths are identical across the sheets.
- What operation should be carried out, if any?
The default is “none”, which is usually what you want. But you can choose to add, subtract, multiply or divide. These actions are typically performed on inputs only, not on formulas. Example uses here include:
- Add or Subtract – Example use: In a sales planning, increase the planned number of units to be sold in each period by 100: Enter 100 in a cell and copy it, select destination cells and paste special, add.
- Multiply or Divide – Example use: Convert data in Euros to thousands of Euros: Enter 1000 in a cell and copy it, select destination cells and paste special, divide.
These can also be used to anonymise confidential data for training or demonstration purposes.
- What special actions should be performed, if any?
These are the paste special specials and there are three on offer, the first two of which can be combined.
- Skip blanks – pastes only those copied cells with content and ignores any which are blank. Example use: in one workbook you have formulas to calculate actual sales values by brand (and no sub-totals or totals) and you want to paste these as values into an input area in a planning model but this has formulas for sub-totals and totals which you don't want to overwrite. Use the combination: paste special, values and skip blanks.
- Transpose – pastes copied content and switches it from horizontal to vertical or vice versa. Example use: you have monthly values arranged horizontally and want to paste them into an area where the months are arranged vertically, like this:
- Paste link – pastes a link to the copied cell(s). If you use this for one cell, the link is fixed for both column and row. Example use: You copy a key input cell (say the selected scenario or the master check result) and do a paste special, paste link to this cell in the main output sheet. The result is the same as the normal method of starting in the main output sheet and entering a formula = source cell. But paste link may be more efficient If you are already on the source sheet.
how2excel tips
1. Use the keyboard to quickly select options
In the Paste Special dialog box, all options have one letter underlined e.g. V in Values. Simply type this letter to activate the option. Finally press Enter on the keyboard to carry out your Paste Special.
2. Paste Special repeatedly
As with standard paste, you can paste special repeatedly, if necessary. This is possible as long as the copied data is still in memory, which is indicated by this message in the status bar at the bottom: of Excel.
Expert tip: Alternatively view the clipboard to manage all recently copied items and paste any of them.
That’s it! I hope you liked this special blog on paste special which I wrote specially for you!
