How to use the Excel Paste Special tool to paste values, formulas, formats and more
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
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
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.
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
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
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%,
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
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
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
If we look at the PASTE SPECIAL dialogue box again, you can do similar type of things but where you want to maybe
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
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
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
To see the true power of this feature,
And just to prove that it works,
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
Another way to do this is
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.
2014 World Cup Spreadsheet With the draw for the Brazil 2014 Soccer World Cup due on the 6th of December, we are ready and waiting with our 2014 World Cup Spreadsheet. As with the other prediction spreadsheets we have created … Continue reading
Excel round to nearest 1000 Most people are aware of how to use the ROUND functions to round numbers to various decimal levels e.g. 5.5678 to either 5.568 or 5.57 or 5.6 or 6 depending on what you specify the … Continue reading
We have used Audit Excel/Miracle Solutions for all our Excel training requirements for the past 18 months, the assessment is a good tool to determine that the trainee is allocated to the correct training.– KRONES Lifecycle Service Center Africa