This tutorial covers how to make table references absolute. It will cover both table column and cell references.
Download the Excel workbook to follow along.
Table Column References Change
By default, table references are relative, so they change. This is a surprise to many users as a table reference looks absolute.
In the following example, the AVERAGE function has been used in cell F3 to average the scores for Maths. It looks like an absolute reference as it explicitly reads Grades table and the Maths column – Grades[Maths].
However, when this formula is filled to the right for English and Art. The formula changes.
In this example, this is good. We want this behaviour.
Make a Table Column Reference Absolute
Let’s look at an example where this behaviour is not desirable.
We have used the following XLOOKUP function in cell F2 to return the Name and Department for each ID from the table on the left, named staff. It does not work when copied into column G.
In this example, we need to make the staff[ID] column absolute, so that when it is copied into column G to the right, it does not change.
The staff[Name] column however, can be left relative, so that it moves to the Department column when the formula is copied.
To do this, we change the staff[ID] reference to staff[[ID]:[ID]]. The complete XLOOKUP formula now looks like this.
So, the column header is repeated either side of the colon and an extra set of square brackets is added to enclose this range.
The following image shows the formula in column G with the table column reference unchanged.
Make a Table Cell Reference Absolute
Now, let’s look at how we would make a single table cell reference absolute.
In this example, the table on the right that contains the XLOOKUP is formatted as a table. Because of this, the previous cell reference of $E2 has been changed to [@ID].
But we need to make this absolute, or it will move to [@Name] when we copy the formula to the right.
To do this, we follow the same approach as with the previous example, but ensure that the @ symbol is inside the first square bracket.
So, the [@ID] reference is changed to [@[ID]:[ID]].
Although we did not enter it into the formula, you can see the table name is added to the reference.
And nice and simple, that is how you can make a table column reference or a table cell reference absolute.