Link to Cells: Absolute Vs Relative links, 3D Formulas and Paste Live links to your data

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Whilst linking to a cell is relatively straightforward (just type = and point to the cell you want to link to), understanding the different types of links you can create, knowing quick ways of creating links to a range of cells without doing each individually, and learning how to link to cell references across multiple sheets at once are all examples of some useful techniques for linking cells which will be covered in this topic.

Absolute and relative links involve ways of linking to cells that can lock the reference to the row or column (or both) so that the formula can be dragged down/across whilst maintaining its integrity. 3D formulas are a way to link to a cell/range reference (e.g. cell C5) across multiple sheets at once, so is useful for when you have inputs stored across different worksheets (all in identical format). Pasting Live links to data is a way of using the Paste Special Options to paste links (rather than typing them yourself), either as a formula or as a live snapshot of the cells which updates when the underlying data changes.

When you link to a cell in Excel by typing = and pointing your cursor to a cell, by default it creates a relative reference. For example going into cell B1, typing = and pointing to cell A1 will show the formula as =A1. This is a relative reference because it changes when you drag down the formula – for example if you drag the formula in cell B1 down one row (by hovering over the bottom right-hand corner of the cell extending it down to cell B2) then the formula in cell B2 will be =A2, linking to the cell in Column A in the same row, i.e. the formula is relative to the current cell.

By contrast if you link to cell A1 from cell B1 but press F4 before hitting enter then the formula changes to =$A$1. The dollar signs inserted within the formula indicate that it is an absolute reference. If you dragged this formula down to cell B2 it will remain as =$A$1 so it will still always link to the value in cell A1 regardless of where the formula is. Even if you dragged the formula across to cell C1 it will still reference A1.

By pressing F4 more than once it will toggle between having two dollar signs to one dollar sign either side of the formula to no dollar signs (it will toggle between $A$1, A$1,$A1,A1). Having a dollar sign only before the letter indicates that only the column is absolute (the row is still relative) whereas having only a dollar sign before the number indicates that only the row is absolute.

If in cell B1 the formula is =$A1 (where the column is absolute), the formula will change to =$A2 if dragged down to cell B2 but will remain as $A1 if dragged across to cell C1. Similarly, if the formula in cell B1 is instead =A$1 then the formula will remain as A$1 if dragged down to cell B2 but will change to =B$1 if dragged across to cell C1.

Making use of absolute and relative references is very useful when linking to cells. In the Topic Example Workbook, in cells B6:B20 we want to multiply each corresponding number in cells A6:A20 by the constant multiplier in cell B3. Without making use of absolute references, our formula in cell B6 would be =A6*B3 but then when we drag it down to cell B7 the formula becomes =A6*B4 which is wrong – it returns 0 because it is referencing the wrong multiplier cell. We either then can’t make use of Excel’s auto-fill functionality (so have to type each formula in separately) or a much easier alternative is to reference cell B3 as an absolute reference – when we link to cell B3 we press F4 before pressing enter and the formula in cell B6 becomes =A6*$B$3 (ensure that the cursor is over the reference to B3 when pressing F4). Now when we drag this formula down to cell B7 it changes to =A7*$B$3, i.e. it references the current row but maintains the reference to cell B3 as required.

An alternative to this would be to name the cell B3 as a named range which are by default always absolute references when referring to a single cell. For example if we named the cell ‘multiplier’ then our formula in cell B6 would be =A6*multiplier, and when dragged down to cell B7 would read =A7*multiplier – both referring to cell B3 as the one which has the multiplier value.

When the cell being linked to is in another workbook it is advisable to refer to it using a named range rather than an absolute or relative reference. The reason for this is that within a workbook, any references to a cell (absolute or relative) are updated if columns or rows are inserted/deleted next to it or the cell is moved (by dragging the cell to another location), whereas a link to a cell in an external workbook is not automatically updated if the workbook being linked from is closed.

3D formulas are a way of linking to a cell reference across multiple worksheets. For example if you have 3 worksheets in identical format called Sheet1, Sheet2 and Sheet3 and you want to consolidate the data in these sheets by summing the cell range A5:A9 across all of them then this is possible by means of a 3D formula which would read =SUM (Sheet1:Sheet3!A5:A9). This sums the values in cells A5:A9 in Sheet 1, adds it to the sum of A5:A9 in Sheet 2 and A5:A9 in Sheet 3.

To create a 3D formula, start linking to the cell(s) on the first sheet you want to reference e.g. start typing =SUM( and then highlight the cells on Sheet 1 (say A5:A9), and the formula will read =SUM(Sheet1!A5:A9, but then before closing the brackets and finishing the formula hold down the Shift Key and then select other tabs that you want to reference in the formula. For example if you hold down shift and select the tab Sheet2, the formula will now read =SUM(‘Sheet1:Sheet2’!A5:A9 indicating that it will sum the range A5:A9 in both Sheet 1 and Sheet 2. Note that reference to the sheets is from the first sheet to the last sheet selected so if the sheets are in the order of Sheet1,Sheet2 and Sheet 3 then when holding down shift if you select Sheet 3 the formula will read =SUM(‘Sheet1:Sheet3’!A5:A9) which will take the sum of that range in all 3 sheets.

Whilst this is undoubtedly a very useful way of consolidating data stored across multiple sheets there are 3 very important things to bear in mind with this:

• The order of the worksheets is crucial to the formula working as expected. In the example above, if we have a 3D formula =SUM(‘Sheet1:Sheet3’!A5:A9) and then we insert or move a worksheet to be in-between Sheet 1 and Sheet 2 (or in-between Sheet 2 and Sheet 3) then this worksheet will also be included in the formula result. This is great if it is what is intended – it’s an easy way to add an additional set of inputs to be included in the consolidation formula – but 3D formulas have the risk of including wrong data if sheets are inadvertently moved around or inserted/deleted. To prevent this it is recommended that you apply Workbook protection when using 3D formulas which prevents people from moving or inserting/deleting worksheets.
• The structure of each sheet referenced in the 3D formula must be identical (unless worksheet scoped named ranges are used) – i.e. the cell range being referenced must contain the same set of data in each sheet. To protect the structure and prevent rows/columns being inserted (or cell references being moved) it is advisable to apply worksheet protection to each sheet being referenced
• When multiple sheets are selected (by holding down Shift or Ctrl and selecting tabs) any changes made in one of the sheets will be reflected in all of them. For example if Sheet1, Sheet2 and Sheet3 are all highlighted then typing a number into cell A10 of Sheet1 will also insert that number into cell A10 of Sheet 2 and Sheet3. Again this is a very useful feature when that is what is intended – it’s an easy way to make changes across multiple sheets at once – but just be mindful of ensuring that multiple sheets are not selected when this is not what is required.

It is also possible to combine data across multiple worksheets using Excel’s Data Consolidation Tool. For a demonstration of how this works, see the YouTube video link in the ‘Links and Other Resources’ section below.

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.

In the Topic Example Workbook, fill in the yellow cells in each of the worksheets:

Absolute and relative references
Link to cell C2 as:

• A relative reference in cell H4 and drag down/across to cell J7
• An Absolute Row reference in cell H9 and drag down/across to cell J12
• An Absolute Column Reference in cell H15 and drag down/across to J18, and
• As an absolute row and column reference in cell H21 and drag down/across to cell J24

A demonstration is shown in the video clip below

3D Formulas

• In cell E3 write a normal formula that adds together the value in cell B5 from Sheet1 and Sheet 2.
• In cell E5 write a 3D formula that also sums cell B5 in Sheet 1 and Sheet 2.
• In cell E7 write a 3D formula that sums the range B5:B9 across all worksheets from Sheet 1 to Sheet 3 (inclusive).
• Change the order of the sheets so that Sheet 4 comes in-between Sheet 2 and Sheet 3 and the total displayed in this 3D formula to reflect the numbers in Sheet 4.

A demonstration is shown in the video clip below

Paste Special:

• In cells B2:B39 there is a multiplier value which each corresponding cell in Column A will be multiplied by via a paste special operation. Highlight cells A2:A39, copy the cells, select B2:B39 go to Paste Special and select the ‘ Multiply ‘ operation.
• Copy cells D1:G1 (the Column Headings) and Paste Special -> Transpose into cells I2:I5

A demonstration is shown in the video clip below

Paste Links:

• Copy the range B5:C12 in Sheet1 and paste a link to these cells in the ‘Paste Links’ worksheet (in cell B5).
• Copy the same range in Sheet 1 and Paste as a Live picture into the ‘Paste Links’ worksheet.

A demonstration is shown in the video clip below

Resist the temptation to view the answers file below until you have tried this!

The completed Excel Workbook should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

YouTube Video – Consolidating Data from Multiple Worksheets By Position