When you reference a singular cell or cell range in Excel (e.g. =sum(A1) or =sum(A1:B1)), you call upon the contents of that cell or cell range. By default, all cell references in Excel are relative. This means that when pasted into another cell, the formula will change the cell references relative to the location of the new cell.
For example, the below formula sums the contents of C5:D5.
When this formula is copied down into cells E6 and E7, the cell range being summed changes from C5:D5 to C6:D6 and C7:D7.
If you are creating a one-off formula or creating a simple table such as the one above, this methodology is perfectly acceptable. However, there are a number of scenarios where partially or fully fixing cell range references can be extremely useful.
How to fix cell references in Excel
The methodology for fixing cell references in Excel is easy. Simply use the $ sign to define the fixed element. For example:
- A1 – fully relative (i.e. both the column and row references will change when the formula is copied)
- $A1 – column A is fixed, row number is relative (i.e. only the row reference will change when the formula is copied)
- A$1 – column is relative, row 1 is fixed (i.e. only the column reference will change when the formula is copied)
- $A$1 – absolute reference; both column A and row 1 are fixed (i.e. neither row nor column references will change when the formula is copied)
The same rules apply when looking at cell ranges. One or both of the cell range inputs can be fixed. A good example would be the creation of a rolling sum.
Here, $C$5:C5 is used. This means that $C$5 is an absolute reference (i.e. fully fixed; both column and row) and C5 is relative. When copied, the $C$5 remains fixed and the C5 will vary depending on where it is pasted. For example:
When copied into cell D8, the second C5 reference becomes C8, creating a rolling sum formula (2 + 4 + 3 + 4 = 13).
There are a number of instances where using absolute referencing in Excel (e.g. $A$1, or $A$1:$B$10) can be extremely useful. One of the most common examples would be when using VLOOKUP where the table array is often absolute to ensure the same source is applied across copied lookup formulas.
Using F4 to change cell referencing in Excel
A useful tip is that you can use the F4 key when editing formula to flick through the various cell referencing options. To activate this functionality, you simply need to click on the cell reference within the formula bar, and hit the F4 key as many times as required.