Rather than using the equals sign and linking to cells, an easier way to create a link to a set of data is to use copy and paste options, making use of paste special. To get to the paste special options (screen shot below), select and copy some data (using Ctrl + C) and then click on Home -> Paste -> Paste Special (or use the [Keyboard shortcut] Alt,H,V,S).
From here you can paste particular attributes of the data you have copied – the most common is to paste values (or values and number formats) as opposed to a normal copy and paste of cells with formulas which would copy the formulas instead. However, you can also copy across attributes such as Formats (which includes number formats, font and conditional formats) and the width of the columns.
The options under the ‘operation’ section will allow you for example to take a set of numbers in one column and multiply/divide/add/subtract them by corresponding numbers in another column (as opposed to creating a new column to perform this operation). For example, to change all the numbers in Column A in the ‘Paste Special’ worksheet in the Topic Example Workbook such that they are multiplied by the corresponding multiplier in Column B, we can highlight Column A, copy it (Ctrl + C), then highlight Column B, go to Paste Special, select the ‘Multiply’ option and click OK. The numbers in Column B will now be the values in Column A times the multipliers that were previously in Column B. One possible application of this is that you can force Excel to convert values to a number by multiplying them all by 1.
The transpose option is also particularly useful – it allows you to switch the data from rows into columns or vice versa. In the Topic Example Workbook, we have a list of column headings in cells D1:G1, and we want to get a list of these column headings listed in rows in cells I2:I5. To do this we select the range D1:G1, copy it, select cell I2, and then go to the paste special options and select the ‘Transpose’ tick-box.
The paste special option of most interest for this topic however, is the ‘Paste Link’ button at the bottom. Clicking this will not actually paste the values or the formulas contained in the cells being copied, but will create a link to those cells instead, in the same way as typing = and pointing to the cell(s). For example, selecting cell A2, copying it and then selecting say cell B5 and clicking Paste special -> Paste Link will put the formula =A2 into cell B5. When you want to replicate an entire block of data on another sheet this is in many ways better than pasting values across because it’s live (so any changes to the underlying data being copied is captured) and is much faster and more accurate than creating those links yourself manually.
However, this creates only relative references to your data, those formulas may be able to be manipulated and will be formatted according to the cell formats in the current sheet rather than in the way the data is originally formatted. For those reasons, it is often better to paste a link to the data in the form of a linked picture which creates a picture image showing the data exactly as it is shown on the source sheet/area where it is being copied from. To create this select the range you wish to replicate, copy it, go to where you want to display it then click on the drop-down under the Paste Options and select ‘Linked Picture’ under ‘Other Paste Options’. (Or use the keyboard sequence Alt, H, V, I).
This creates a live picture (in the same way as the Camera tool in previous version of Excel) – it is live because if the data shown in the picture changes then the picture also updates to reflect this. Because it is a picture, it also retains the original formatting of the source and is easy to move around. It is advisable to then format the data being copied in the way that you ultimately want it presented when it is copied and pasted as a live picture.
Copying and pasting as a linked picture/object is also the best way to link data or graphs in Excel into other Microsoft Office applications such as Word and PowerPoint. To do this, select the data (or the graph) in Excel, then go to the other application, click on Paste Special and in the dialog box shown below, select ‘Paste Link’ and then ‘Microsoft Excel Worksheet Object’. This too is a live representation of your data/graph – if it changes in Excel then when the other document (e.g. Word document, Powerpoint presentation) is opened it will attempt to update the data by means of an external links, working in the same way as Excel linking to other workbooks.
