If you ended up in this article, it's a big probability that you had referred a column or cell in your Excel table and press F4 and finally heard the annoying chime. Fortunately, you haven't given up and made manual changes on the references. In this guide, we’re going to show you How to use absolute references with Excel Tables.
Excel Table references (Structured references)
As you may have known already, an Excel Table can be referred by a special syntax called structured references. This reference type allows you to see the column name and table name if necessary while assuming the row numbers are relative.
Here is our sample table with the name of "Team":
[@Attack] // refers the relative row in the column named "Attack" [Defense] // refers the entire column named "Defense" Team[Speed] // refers the entire column named "Speed" Team[[HP]:[Speed]] // refers all columns between columns named "HP" and " Speed"
Tip: You can ignore table name while referring a single column or a cell in a single column.
Using absolute references with Excel Tables
So, how can you use absolute references with Excel Tables? If you copy the reference samples above, you can see that Excel changes the column reference as well.
Except for the multi-column reference:
=SUM(Team[[HP]:[Speed]])
Thus, the trick is to use the multi-column reference to point a single column. All you need to do is to use the column name twice. For example:
SUM(Team[[Attack]:[Attack]]) // refers the column named "Attack" and will not change by copying
If you want to lock the columns while keeping rows relative, add the "@" operator after first bracket:
Team[@[HP]:[HP]]
