Excel online training course

excel lesson 15, Paste Special

Using Paste Special

Paste Special is a function Excel gives us to copy information and paste only certain elements from the original in a new location.
You can for example copy a formula and with "Paste Special" only paste the values of this in a new cell.

Choose a cell you wish to copy and click on the "Copy" button in the standard toolbar.
Choose a target cell.
Then click "Edit" and "Paste Special" in the menubar.
This will open the "Paste Special" window.
Here we can choose what we want to paste in our target cell.
Everything, formulas, values, format, etc..

For example:
When we have a value of 3000 in cell A3 and then copy and paste it with "Paste Special" to cell A4 which has a value of 2000 in it.
Then we can choose to make the values of these cells automaticly divide, sum, deduct or anything you want.
To do this we select our cell A3 with our formula and click on "Copy".
Then we select the cell A4 with our number and click on "Edit" and "Paste Special".
We choose "Values" in the "Paste" section of the window.
And in the "Operation" section we choose to "Add".
We click on "Ok" and the result in cell A4 will be 5000.

Pasting linked formulas

We can link formulas between different workbooks.
If the value of the formula changes in the first workbook, then the value in the linked formula will automaticly be adjusted.
To do this we select the formula in the first workbook which we would like to copy and we click on "Copy".
Then we select the second workbook, select a cell, and click on "Paste Special".
In the window we click on the "Paste Link" button on the bottom.

The cell adress reference is actually the only thing that is copied.
If we study the formula in our workbook we can see the following, first the workbook (book1.xls) then the worksheet (Jan) and then the cell reference $B$7.

When we use linked formulas its not possible to change the location of the different workbooks on your harddisk, neither change their name, otherwise Excel cant find the formula anymore.

When we change the data in the original workbook and then we will open the linked workbook, Excel will tell us that the original has been changed and/if we want to update the linked workbook.
You can choose between "Update" and "Don't Update".
Usually the answer will be Update, otherwise this linked workbook doesnt have alot function.