[ad_1]
The Paste Special button has many fabulous properties available to the Excel user. In this article I would like to move past the basic features and examine some of the lesser used options. You will find the Paste Special command in the Clipboard group of the Home ribbon, or on the right-click menu. Copy a cell and then use one of these methods to open the Paste Special dialog box. In the newer versions of Excel the right-click menu has some shortcuts available, but I find the dialog box the simplest and most straightforward way to use Paste Special.
Operation
The default option in this section of the dialog box is none, but you will also see the four basic mathematical operations. You can use Paste Special to perform the same basic operation on a selection of existing values.
1. Create a set of simple numbers in A1:A10
2. Enter ‘2’ in C1
3. Select C1 and copy the cell
4. Select A1:A10
5. Go to the Paste Special dialog box and choose multiply
6. OK
The numbers in A1:A10 should all be double their previous value. If you want a value in a cell instead of a formula, this is a great way to perform a basic operation.
TIP: This technique is a fabulous method of changing numbers that are being stored as text into real numbers. You may not intend to store a number as text, but often this happens when you ‘data dump’ (bring data in from another source).
Multiply numbers stored as text by 1 or add 0 to them to change numbers stored as text into numbers stored as numbers. The values will look the same, but Excel will see them very differently.
Skip Blanks
Let’s say you have figures in A1:A10. You have calculations in B1 and B10 and you want to paste their values into A1 and A10. Rather than doing that in two steps you can:
1. copy cells B1:B10
2. Click in A1
3. Go to the Paste Special dialog box
4. Choose values and Skip blanks
5. OK
The values that were in B1 and B10 should now be in A1 and A10, but all the other cells in the range remain unchanged.
Paste Link
If you ever need to have the same value in more than one cell in your workbook, you can use a link to ensure that the linked cell always has the same value as the cell it is linked to.
1. Type your name in A1
2. Copy A1
3. Click in C1 and go to the Paste Special dialog box
4. Click on the rectangle in the bottom left corner that says Paste Link
Your name should now appear in C1
Go back to A1 and change your name to something else. As soon as you accept the new value C1 should change to match it.
Remember that knowledge is power. You may not need these techniques every day, but knowing they exist will make you more powerful and less frustrated when you run into a situation that would benefit from their use.
For more information on some of the basic uses of Paste Special, have a look at my article An Introduction to Paste Special.
[ad_2]
