Excel Tables are one of my favourite features. They make working in Excel much more efficient in so many ways, but they have some quirky (read cool) features, one of which is the Structured References they use in formulas.
And if you’ve ever tried to write a formula that references an Excel Table then you might have been scratching your head wondering how you make the references absolute or relative.
Tip: the F4 key toggles through the absolute cell reference settings with regular cell references (as shown below), however it doesn't work with the Structured References you get when you reference an Excel Table.
First of all let’s recap; Structured References are made up of two parts:
Table name [column name]
We can use them in a SUMIF formula to return the total Stock for the ‘Biscuit Sweet’ Group in this table (called Table1):
Which can be written:
And in English it reads:
IF the Group column in Table 1 contains ‘Biscuit Sweet’, SUM the Stock column in Table 1.
That’s all straight forward, but what if we wanted to make a nice summary at the top of the table linked to a data validation list in cell B4 like this:
We’ll change the formula slightly so that the result updates based on the group chosen in the data validation list cell B4:
Copying and Pasting Formulas with Structured References
Now let’s say we want to insert a SUMIF formula in cell F4 for the Total Value:
You might try to copy and paste the formula from cell D4 to F4, but unfortunately you’ll get exactly the same formula 🙁
Dragging with the Fill Handle
Dragging the fill handle is getting closer. When you drag the formula across columns with the fill handle the structured references are treated as Relative. For example; when the formula in D4 is dragged to F4 it becomes:
But that’s not what we want either. We need the first argument to be absolute and the last argument relative.
Before we move on let’s recap:
- Copying and Pasting treats references as absolute
- Dragging across columns treats references as relative
How to Make Structured References Absolute
So, here is the secret sauce for absolute Structured References…. Drum roll:
You need to repeat the column name and wrap it an extra set of square brackets like this:
Now when you drag the fill handle on cell D4 over to cell F4 the reference to the Group column remains the same and the last argument updates because it is still relative:
Absolute the Reference to more than one Column
When you reference a range of columns Excel automatically sets them as absolute, for example in a VLOOKUP formula:
You can make them relative by removing the double square brackets and repeating the Table name like so:
Absolute References to Table Rows
When you reference a single cell in a table the column name is prefixed by the @ symbol, as you can see in cell G8 below:
Let’s say we want to do some analysis to see the effect if we increase stock by 10% or 12%. We can quickly set up some formulas in columns H and I like this:
See the formula in cell H8 is:
Before I can drag it across to cell I8 I need to make the @Value reference absolute like so:
F4 to Absolute Structured References
Now, if you’re like me you’ll be wishing the F4 key worked with Structured References too.
The good news is my friend Jon Acampora of Excel Campus has a free add-in that does just that.
You can download the Absolute Reference Add-in here. Be sure to say thanks to Jon for this excellent tool.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.