Home // Video Tutorials // 3 - 6 (Intermediate) // 4 // Excel Paste Special tool

Excel Paste Special tool

How to use the Excel Paste Special tool to paste values, formulas, formats and more

Excel Paste Special tool

The Excel PASTE SPECIAL tool feature gives you the power to decide how much information will be copied and pasted.

This means you can decide for example, to only copy the formats of a cell, or maybe you only want the formula or maybe only the numbers. It also has a number of other useful features.

In this segment you will learn how find and activate the feature, and how to use the paste options to save yourself time in your daily work.

The best way to explain the PASTE SPECIAL command is through an example; here we have a very small spreadsheet, a number of divisions and some information relating to Sales, Cost of Sales, Gross Profit and GP margin. Normally what people do is

  • they highlight it
  • they copy it,
  • and then they’ll paste it
  • and what you get is an exact copy of the cells you copied,
  • if you undo this

What happens if you want to copy this section, however you don’t want all this colours and borders that have been included? That is where the PASTE SPECIAL command comes in to play. Again you

  • highlight the section
  • and you say Copy,
  • you click here
  • you go Edit
  • PASTE SPECIAL
  • and this block with pop up and it gives you a number of options,

At the moment as you can see it’s clicked on All, which will give you an exact replica of these cells. However lets say we don’t want the colours, we don’t want the borders, and if fact we want the formulas to disappear and to give us just a number.

  • if you click on Values
  • and you push Ok,
  • what you get is the raw information in number format,
  • So you’ll see that this 750 was a formula there, here it is just a number,
  • there are no borders
  • and no colouring

Another example of the benefit of the PASTE SPECIAL feature would be for example here, here in this cell we have a comment which we’ve typed saying “Sales are based on products sold AND services rendered” however we would like that comment to be included in all these items.

This is a small spreadsheet so you could just type it out, but when it gets longer it can become very tedious. What you can do is

  • click on the cell,
  • say Edit,
  • Copy,
  • highlight the other cells,
  • say Edit
  • PASTE SPECIAL
  • and you’ll see if you look at through the section, there is one that says you will just paste just the Comments, you if you click on that
  • you’ll say Ok,

What you’ll see now is that each cell has got the exact same comment in it, but it’s a lot easier than typing each one individually.

Let’s look at the PASTE SPECIAL dialogue box again, and just understand all the options available in this section

  • All, has been explained where everything related to the cell is copied,
  • if you click on Formulas only the formulas will be copied,
  • if you clicked on Values, any formula that exists will be changed and only the value will be pasted,
  • if you click on Formats only the format will be pasted.
  • if you look at the Comments only the comments in the copied cells will be pasted,
  • Validation will only paste the data validation sitting behind the cell,
  • if you want you can copy a section of cells, and paste them so that everything except the borders is pasted.
  • you can make sure the Column Widths are the same
  • or you can use these options where the Formula and the Number Formats are pasted,
  • or the Values and Number Formats are pasted.

And this will enable you to paste only the information that you want to paste.

The PASTE SPECIAL feature has another section which provides a number of useful tools. And again the best way to show what these tools do is to use an example.

Let’s say you have the Sales numbers here and you’ll like them all to increase by 10%,

  • if you click in the cells
  • you’ll see that is a number,
  • this is a bit of a formula,
  • that’s a number again,

And the only real way to do it is to go through each one and up it by 10%, maybe there’s another way. What you can do is

  • in a blank cell somewhere, because were doing 10%, you type 1.1
  • you put it in
  • and what you do is you Copy that cell
  • you highlight the section that you want it to multiply by,
  • you say Edit,
  • PASTE SPECIAL
  • and in this operation what you’re going to say is Multiply, and what you’re basically saying is take this cell and multiply it into these cells,
  • when you click Ok,
  • you’ll see the numbers all change, they’re all 10% up
  • and the formatting it adopts is the same as this one, so you may need to change the formatting,
  • if you look in here that was previously 300 now its 330,
  • this one was a formula and so what excel does is it types in the multiply by 1.1 so you can still see what is happening there,
  • Similarly the same happens with all of these.

A very good use for this is where you want to change positive numbers into negative number. So here we have Cost of Sales, at the moment it has been input as a positive number but maybe you prefer to see it as a negative number to clearly show its an expense. What you can do is

  • you come to a blank cell
  • you type in -1
  • you Copy that cell
  • you highlight the ones you want it to change
  • you go PASTE SPECIAL
  • and again in this case we want to multiply it, so you click Multiply,
  • say Ok
  • and immediately all of these will be changed to negative,

Now just remember this formula will now be incorrect because it is subtracting that from there, and because we’ve made it a negative, it is now incorrect. So

  • you need to go and change it to a positive,
  • you Copy it,
  • and again because you don’t want to mess up the various borders,
  • you highlight the cells you want to copy to
  • you say PASTE SPECIAL
  • and you only want the formula to be pasted not the format, so you say Formulas,
  • click Ok
  • and its immediately done.

If we look at the PASTE SPECIAL dialogue box again, you can do similar type of things but where you want to maybe

  • add a number consistently to cells,
  • or subtract,
  • we’ve shown you multiply
  • Or you can even divide.

The next feature I want to look at is the Skip blanks feature. So for example let’s say this was our spreadsheet but someone has come up and said no-no I think

  • this one should be 500
  • that should be 350
  • this should be 300
  • and that should be – 250

Obviously because the spreadsheet is so small you could just replace them, but if it gets big enough you could have a problem. What you can do is

  • you can highlight the section,
  • you Copy it
  • you go to this section
  • you say Edit,
  • PASTE SPECIAL
  • and you click Skip Blanks,
  • and what its going to do is its going to paste this section over here,
  • but wherever there’s a blank it will leave the number that’s in there already,
  • so just have a look at the 165 and the 55
  • when you say Ok,
  • you’ll see that what has happen is the 500 and the 300 have come through as they should have,
  • But these two numbers have stayed the same.

Another useful feature of the PASTE SPECIAL command is the ability to transpose information. So lets say that you decide that this row here you’d rather have it as a column, what you can do is

  • you say Edit,
  • Copy ,
  • you click in the cell where you want the column to come,
  • you say Edit,
  • PASTE SPECIAL
  • you’ll see here there’s a Transpose button,
  • when you click Transpose
  • and you say Ok,
  • what it does is it copies that row, but puts it down as a column.

To see the true power of this feature,

  • we can highlight that whole section,
  • say Edit
  • Copy,
  • click here,
  • Edit,
  • PASTE SPECIAL,
  • and say Transpose
  • and when we say ok,
  • This section will be swiveled around so that all the divisions are now sitting in the rows,
  • and the descriptions are now sitting in the columns.

And just to prove that it works,

  • you can highlight this section again
  • say Edit
  • Copy
  • come over here
  • Edit
  • PASTE SPECIAL
  • Transpose
  • and you say Ok,

And what you’ll see is that it is exactly the same as the original,

Paste Link button gives you another way of linking cells, again the best way to explain this is with an example. Normally when you link cells, for example

  • we could say that, is equal to area,
  • enter
  • and then if you wanted to you could copy it down and across.

Another way to do this is

  • you can highlight the section you want it to refer to,
  • you Copy it
  • you click where it must be linked
  • and you say Edit,
  • PASTE SPECIAL
  • Paste Link,
  • What you’ll see is if you click here
  • all these cells now refer directly to the original cell

And it’s a nice quick way to link cells without having to copy, paste and then drag it across.

By understanding all the options and features of the PASTE SPECIAL feature you will find that you save yourself significant time during the day which should make your working life easier.