Learn to use Microsoft Excel
Paste Link
One of the great things about spreadsheets is that you can use them with only a little knowledge, yet by spending a little time learning a bit more you can do so much more. One of my favourite functions in Excel is the Past Link – this allows you to copy a spreadsheet cell, or a range of cells (the little boxes in the spreadsheet that can contain a number, formula or text) and then place this anywhere within the spreadsheet.
Microsoft Excel allows you to have different pages (called worksheets) and you can paste link information from one worksheet to another, infact you can do this between different named spreadsheets. I personally don’t use this feature as I dislike it when someone sends me a spreadsheet without the linked spreadsheet attached to the e-mail. That of course is a personal preference and many people use linked references over a number of different spreadsheets for very good reasons.
So what is the great thing about a paste link, well it allows you to show or reference the contents of one part of your spreadsheet to another area of it, for example it could be a list of names, a summary or a total. The great thing is that when the original information changes then the linked cells are also updated. When you start to learn how to use spreadsheets this may not seem like a very useful idea; just wait until you use your spreadsheet at work or in your business, it is simply amazing how a spreadsheet grows arms and legs (so to speak) and it you soon can’t see all of the spreadsheet on the computer screen at once. The Paste Link function can save your life by allowing you to copy the content of a cell, or part of your spreadsheet to another area, possibly into a summary page that is nicely formatted for printing for example.
To paste link a cell or range of cells all you need to do is copy the cells (rows and/or columns) then paste special and then select the Paste Link button on the menu provided.
In most applications there is more than one way to do something and I usually try to use the quickest methods for my day-to-day use of spreadsheets. So I will give one way for my worked example below, just remember there are often 3-4 ways to do the same thing.
Copy Paste
To use the copy paste function select the cells and copy the information into the Windows clipboard. I do this using a number of different methods, the easiest is by right-clicking your mouse and selection Copy, or you can use the drop down edit menu and selecting Copy (or Ctrl + c with your keyboard) – see I did say there is more than one way to do it!
After you have successfully selected and used the Copy you will see the selected range of cells highlighted (like moving ants, well I remember reading this somewhere it does not look like ants to me!) and this will let you see that you have correctly selected the cells you wanted.
Now navigate through your spreadsheet to the worksheet or location where you want this information to appear and then paste it using the special Paste Link command.
To do this click on the cell where you want to paste link and then right click on the mouse and select Paste special, this will pop up another menu option on your screen.
This brings up a menu with several different options. You want to select the Paste Link button on the bottom left of the menu.
You now have a cell with a reference link to the original cells in your spreadsheet. If the values or contents of this original spreadsheet cell(s) change so will you linked cells.
If you carefully select the cell with the reference link you will be able to see the reference used to make this cell link.
If you look at the formula bar it shows the link as
=Sales!$E$2
The “Sales” is the reference to the worksheet, while the $E$2 is the cell reference on in the “Sales” worksheet, this is separated by the “!” character. If you are wondering what the $ is used for it means you are being pretty observant, well done. In Microsoft Excel the use of the “$” character in a cell reference makes it an absolute reference, in other words you could move the cell to another location and it would always link to cell E2 in the “Sales” worksheet, pretty clever.
You can use fixed or absolute references in many spreadsheet formulas to make sure you are referencing the correct cells, and this is very useful when dragging formula down a column or across a spreadsheet and you want part of the reference to stay the same. For example you wish to reference a cell that contains a value for sales tax, by giving this cell and absolute reference in a spreadsheet formula you can copy the formula down a column so that it calculates the sales tax for a range of sales.
So there you go, your first Paste Link, plus a little more thrown in for free.
You can download a simple Microsoft Excel spreadsheet along with this tutorial in Microsoft Word that demonstrates the Paste Special - Paste Link use - this is in a zip format called pastelink.zip (93.2KB)
You can use these under a Creative Commons 2.5 License