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.
Structured References
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:
=SUMIF(Table1[Group],"Biscuit Sweet",Table1[Stock])
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:
=SUMIF(Table1[Group],$B$4,Table1[Stock])
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:
=SUMIF(Table1[Stock],$B$4,Table1[Value])
But that’s not what we want either. We need the first argument to be absolute and the last argument relative.
Recap
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:
=SUMIF(Table1[[Group]:[Group]],$B$4,Table1[Stock])
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:
=SUMIF(Table1[[Group]:[Group]],$B$4,Table1[Value])
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:
=VLOOKUP("SKU12",Table1[[Code]:[Price]],4,FALSE)
You can make them relative by removing the double square brackets and repeating the Table name like so:
=VLOOKUP("SKU12",Table1[Code]:Table1[Price],4,FALSE)
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:
=Table1[@Value]*(1+H$6)
Before I can drag it across to cell I8 I need to make the @Value reference absolute like so:
=Table1[@[Value]:[Value]]*(1+H$6)
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.
Please Share
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.
Alexander Balleh says
Is there a way to refer to a cell in a seperate table using lookup criteria from the initial table? I got errors with power pivot since my data contains too many duplicates to create relationships. Essentially I want to compare Bob’s budget each month to his forecast in a seperate table, while avoid using a vlookup to ease reading the formula. If combinations of syntax or structured references can look up say an employee number in a range of rows of the other data table, that would be great
Catalin Bombea says
Hi Alexander,
That’s what Power Pivot does. Load your lookup table to power pivot and add a relationship between data table and lookup table, that’s all you need.
Or, you can load to data model both the data table and the lookup table, and you can merge them into a single table.
Catalin
Brad Edgar says
Mynda,
This is great. There is always so much to learn with Excel. Favorite part by far is how to actually use absolute referencing structured references.
Honestly because of the flexibility of Excel Tables to expand without having to use offset formulas in name ranges, why wouldn’t you want to use them all of the time.
Thanks
Brad
Mynda Treacy says
I agree completely, Brad 🙂 I’m happy to put up with the few limitations of Tables to have the structured reference functionality.
Mynda
Adi says
Dear Phil & Mynda,
Thanks for an excellent tutorial and a superb add-in. I will personally thank Jon on his website.
You have a knack of coming out with something new / arcane everytime you post. So glad to receive your tips.
Thank you very much!!!
Adi 🙂
Mynda Treacy says
Thanks, Adi 🙂 That’s wonderful to know.
Mynda
jef says
Very good tip. Easier option. Thanks for sharing.
Philip Treacy says
Thanks Jef.
Rajesh says
Awesome trick. Thank you very much.
Mynda Treacy says
Thanks, Rajesh 🙂
Col Delane says
Hi Mynda
Another good tutorial from you about some tricky Excel functionality. However, whilst structured referencing has some advantages, I find them a pain in the proverbial :-(, because:
(1) the “ease of deciphering” the formula is outweighed by the time-consuming fiddling around that is required to make the references absolute or relative as required, and
(2) what appears to be the inability to have relative referencing when copying and pasting a structured reference. Copying and pasting of formulae and utilising the absolute/relative referencing that comes with that is one of the absolute fundamentals (pardon the pun) in the use of Excel. One should not have to drag across columns / down rows (or use fill handle?) to have structured references remain relative! What if your table has hundreds or thousands of rows/columns – dragging or using the fill handle should NOT be the only option.
If I’ve missed something about using structured referencing with Excel tables that overcomes the latter problem when copying, I’d be very happy to be enlightened.
Regards
Col
Mynda Treacy says
Hi Col,
I agree that they are fiddly and should be more user friendly, although Jon’s free addin goes a long way to fixing their limitations.
While the fill handle requirement is a pain I find if I drag it across the columns where required, I can then double click the bottom right corner of the cell containing my new formula to fill it down the column, so it’s a bit more painful than a regular formula but still worth the while.
For me the benefits of the dynamic ranges that are built into Excel Tables makes them worth using and putting up with their absolute reference hassles.
I hope that the absolute/relative limitations might be fixed in a future Excel release, however I don’t have any inside info on that, it’s just my wish!
Mynda
Babak Damadi says
Is there a downloadable Excel Workbook to follow along?
Mynda Treacy says
Hi Babak,
I’ve added a link to the post (towards the top) where you can download the workbook 🙂
Mynda
Jon Acampora says
Thank you Mynda! This is a great tutorial. I did not know about the relative references to multiple columns. That’s a great trick that I will have to incorporate into the add-in.
Have a good one! 🙂
Mynda Treacy says
Cheers, Jon 🙂
I hope everyone loves your add-in as much as I do.