Paste Special

Paste Special is another time saving trick that may be going underused.

You are probably already familiar with the cut, copy and paste functions in a lot of applications.

  • Copy allows you to make a copy of selected or highlighted data to be pasted somewhere else
  • Cut allows you to remove selected or highlighted data and then paste it somewhere else
  • When you cut or copy, something is added to your clipboard, pasting allows you to take something from your clipboard and place it where you want.


So, what is different about Paste Special?

Paste Special gives you more options and control over what you paste from your clipboard, and what you do with it.

Different applications can offer you different options when pasting.

In the cases below I will be using Paste Special in Excel, and I will not be covering all options, but hopefully you will see enough to feel comfortable with trying them yourself.

The options I use most often are highlighted below.

Paste Special Options

To demonstrate examples, I will use the table in the section below, and different versions of it:


Paste Special Formula

Column B of the file gives the line number of the table.

1 is entered in the first line (obvious enough).

The second line adds 1 to the line above it and we want to copy this formula to all the lines below it, so we get 2;3;4;5;6;7;8;etc.

Paste Special Formula Start

By simply copying and pasting the formula, Excel will copy the formula but also the formatting of the cell being copied.

In our case, we are copying cell B3 which has no border, so when we copy it down it overwrites any borders we had set.

Paste Special example just pasting


However, if we copy B3 and then highlight the relevant rows and go to  Paste Special and select “Formulas”, Excel will copy and paste the formula only – it will not paste any formatting or borders from the original cell.

Paste Special Formulas example


Paste Special Values

To demonstrate Paste Special Values, I have added a formula to column E.

This just multiplies the value in column D by 0.1 (or 10%).

We are going to copy column E to column F


Paste Special Values Example just pasting


When we copy column E and paste to F the formula is changed to multiply the values in column E by 0.1, which as you may have noticed is not what we want.

Paste special Values Example

If we only wanted to paste the value, then use Paste Special and select “Values”.  Excel takes the value (the result of the formula) and pastes it in column F.

Paste Special Values



Paste Special Comments

Another way to use Paste Special would be to copy comments on a cell to others.

I have added a comment to cell A2 and want to copy this comment to all other cells in that column – pasting the comment would obviously be faster than creating the comment for each person.

Paste Special Comments example

If we copy cell A2, which includes the comment, and paste it to all others in the column we would get:

Paste Special Comments just pasting

 Excel has pasted the value, formatting, borders and comments of A2, but we only want to paste the comments.

Copying A2 and then selecting “Comments” from the Paste Special list would give us this:

Paste Special comments done the right way

Only the comments have been copied and pasted.  The values, formats, borders, etc were not pasted too.


 Paste Special Multiply

Paste Special also allows you to perform certain operation.  One that I use quite often is the multiply option.

Column E contains the same value as D, but I want to multiply this by the 10% rate in G1.

Paste special  multiply example

 To do this, I copy the 10% in G1 and then highlight the numbers in column E and select “Multiply” from the Paste Special options.

This then multiplies the 10% that we copied by each of the values we applied the Paste Special operation to.

You’re probably wondering why would you ever need to use this?  I use this option on a weekly basis to convert a batch of numbers to negative numbers.  There is more on this at the bottom of this post.

Paste Special Multiply

Paste Special Transpose

Transpose allows you to convert a horizontal list to a vertical list, or vice versa.

Below is a horizontal list of all our names.

0041 - Paste Special Transpose

By copying the above list and then in A3, I select “Transpose” from the Paste Special options.

My horizontal list is transposed to a vertical list.

Paste Special Transpose


Converting a Batch Of Numbers To Negatives

An example of where a batch of numbers may need to be converted to negatives would be in the case of a downloaded bank statement for example.

Assuming that you want all the credit numbers to be negatives, it is possible to simply type a dash (-) before the value.

But if you have a large number of numbers to change, this can be boring and time consuming, plus there is a chance that you may miss numbers to be changed.

example of converting numbers to negatives

Personally, when I have to complete this task I:

  1. Filter for credits
  2. Copy “-1” which is typed somewhere to the side
  3. Highlight the filtered results and Go To visible cells
  4. Paste Special multiply – which will multiply the credits by -1, giving a negative number.

Note : The following may seem like a lot of steps and a long way to complete the task, but it is quick to do when you get used to it.

Step By Step…

  • Filter column B for “credit”
  • In cell D1 (or anywhere to the side) type in “-1”

second step in converting numbers to negative

  • Copy the -1
  • Highlight the row of numbers in column C
  • Open the Go To dialog box by pressing Ctrl and G together

Go To dialog box

  • Press the “Special” button
  • Select “Visible cells only”
  • Press “OK”

converting batch of numbers to negatives example

  • Now Right-click on one of the highlighted numbers and from the Paste Special menu, select “Multiply”
  • Clear your filter and admire your handiwork.

Final screen shot of example converting numbers to

Remember when you are using the “Multiply” operation of Paste Special, you are also selecting to paste “All” from the top section of the Paste Special unless you change it from the default “All”.

So, why did we go through all those “Go To” steps?  If you didn’t, the Paste Special may apply to all numbers in the highlighted range, even if the filter has them hidden.

By using Go To “Visible cells only”, you are only selecting what the filter has not hidden and won’t affect any hidden values.


[grwebform url=”” css=”on”/]
Click Here to Leave a Comment Below 0 comments

Get every new post delivered to your Inbox

Join other followers:

%d bloggers like this: