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.
Enter your email address below to download the sample workbook.
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.
Excel Slicers – Everything You Need to Know
Chris
Eye roll at people who love structured references.
Show us how to do a fairly simple accumulation sumif based on multiple columns.
If I see that done, maybe then I’ll start giving them some love.
Mynda Treacy
I agree, referencing multiple columns isn’t the most intuitive, but like anything, once you get the hang of it it’s not that difficult. That said, don’t forget you can submit ideas for improvements to Excel on UserVoice.
nkowk
Excellent. You are the best of all I have seen ! Thank you very much
Mynda Treacy
Thank you! Glad you found it helpful 🙂
Shaoyuan Chen
Really wonderful excel data informations. Even only 20 pages but for me are really
more than that. Thank you very much !!
Mynda Treacy
Glad you found it useful, Shaoyuan 🙂
Donal
Great article. I use Absolute structured references all of the time, but I still learned something.
I have a reoccuring issue… I export excel data from my eCommerce components. These components are in active development, and from time to time the structure of the excel export will change, e.g. new columns added or simply ordering changed.
For example, previously, my excel export had the following column order…
Date Submitted, club, clubid, feedback, comment, email, ChildsName.
I pasted this into my “Data” table
I had another table which analysed the raw data, and the following formula counted the number of people who gave feedback of 3 (column in Analysis table has heading = “3”…
=COUNTIFS(Data[[feedback]:[feedback]],Analysis[[#Headers],[3]],Data[[clubid]:[clubid]],Analysis[[ClubID]:[ClubID]])
After a recent excel update, the excel export has a different order
Date Submitted, club, feedback, comment, email, ChildsName, clubid
When I paste this into my “Data” table, the formulas in my Analysis table get changed to…
=COUNTIFS(Data[[comment]:[comment]],Analysis[[#Headers],[3]],Data[[feedback]:[feedback]],Analysis[[ClubID]:[ClubID]])
This is incredibly infuriating, as my reports keep breaking, as the developers work their magic on the eCommerce components. Is there a way to write my analysis formula, so that absolute structured references will always refer to the correct column, while remaining absolute?
Thanks for any help you can provide.
Catalin Bombea
There are a few ways out of this situation. Are you using excel 2010 or higher? You can try Power Query in this case, to import the new data into your Data sheet.
Another way out is to use vba to import data by columns, this way your Data headers will always remain the same, and you will import the corresponding columns from the source file into the correct column.
Changing data headers in the destination file, to match the position of the source columns, will allow you to paste only data, not headers. By default, when you rename a column in a table, excel will automatically update that name in all formulas from that workbook, and this is a very good behavior, but in your case, pasting data including headers is not a good option. Rearranging columns in a defined table is easy, just drag the header cell in the position you want.
Catalin
Alexander Balleh
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
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
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
I agree completely, Brad 🙂 I’m happy to put up with the few limitations of Tables to have the structured reference functionality.
Mynda
Adi
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
Thanks, Adi 🙂 That’s wonderful to know.
Mynda
jef
Very good tip. Easier option. Thanks for sharing.
Philip Treacy
Thanks Jef.
Rajesh
Awesome trick. Thank you very much.
Mynda Treacy
Thanks, Rajesh 🙂
Col Delane
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
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
Is there a downloadable Excel Workbook to follow along?
Mynda Treacy
Hi Babak,
I’ve added a link to the post (towards the top) where you can download the workbook 🙂
Mynda
Jon Acampora
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
Cheers, Jon 🙂
I hope everyone loves your add-in as much as I do.