Referencing in Excel refers to the process of linking one cell to another. Generally referencing is used to create formulae that can perform a variety of functions to the data within one cell or a series of cells. When linking cells, the use of an equals sign (=) will tell Excel that you are using a formula to carry out a calculation or function.
How linked cells behave
It is worth noting a few key features when referencing another cell using a formula:
- When referencing a cell in a formula, the formula will only copy the cell’s contents and will not apply any formatting from the referenced cell to the target cell
- If a source cell (for example, B3 above) is copied, the linked cell (D3) remains linked to the original (B3)
- However, if the source cell is dragged (or cut and then pasted) to a new location on the spreadsheet, the link does move with it and D3 will continue to display ‘Azalea’
- If the contents of the source cell are changed, any cells that refer to the source cell will use the new value
Manipulating cells with references
Cell references can be part of cell formulae or functions. So, cells containing numbers can have calculations performed on them, and those containing text can be edited. Several cells can be included in such formulae – so for example two cells can be added, or concatenated to produce combined text:
Cells that refer to more than one source cell behave similarly to cells that refer to a single cell – when the cell is copied the references move too to maintain their position relative to the copied cell. So copying D3 down to D4 in the above example will link the cell to B4 and C4, and produce the result ‘TinaToledo’ in cell D4. This is true whether the formula is a simple reference or mathematical operation, or uses an Excel function.
You can apply functions and formulae to multiple cells. The most common of these is the =SUM() function. This will add together the contents of a series of cells specified in your formula. This can be done in two ways, with each returning the same results. You can either use a comma to separate the individual cells you wish to add up, or alternatively use a colon to indicate the range being used:
These ranges perform in exactly the same way as individual references – copying a formula containing the range results in the reference changing.
All of the referencing we have discussed up to this point has been regarding ‘relative references’. The references are known as relative because they refer to cells that are not ‘fixed’ and relate to the relative position of a cell. You are, however, able to fix the contents of cells so that when you copy them the value will always refer to the cell or series of cells you have fixed them to.
To do this all you need to do in insert ‘$’ signs in the cell reference in front of the column reference (so B becomes $B) and before the row reference (3 becomes $3). So in the example below, cell F3 refers to B3 and so contains the result ‘Jacques Anquetil’.
Check out more Excel top tips on AAT Comment to help you get to grips with all that Excel can offer. If you’re stuck on something specific, let us know by commenting and we’ll see if we can cover it in another blog post.
To delve deeper into this topic, log into your Filtered account and take a look at ‘Fixed References’.
Paolo Lenotti is the Head of Marketing & PR at Filtered.com.