Ever seen a formula like =SUMIFS(Sheet1!B2:B3923, Sheet1!C2:C3923, A1, Sheet1!D2:D3923, A2) and wondered what it is really doing?!?
If so, you are not alone.
Formulas written with cell references tend to look complicated and clunky. What if we could write formulas in plain English?
That is what Structural References do. When using structural references in formulas, your focus will be on your data, not on which cell ranges the data takes up.
For example, you can write formulas like these:
- SUM(mySales[no. of customers]) to find how many customers we had.
- SUMIFS(mySales[no. of customers], mySales[product], “FastCar”) to find how many customers bought “FastCar”
Learn how to use Structural References and why they can change you life
Introduction to Structural References
[Watch this on our YouTube channel]
Download Example File
Click here to download example workbook and play with Structural References to learn more.
Learn more about Tables & Structural Refs
- Introduction to Tables in Excel
- Using Tables with VLOOKUP formulas
- How to preserve table column references
- Customizing table formatting
Do you use tables & structural references ?
I use tables all the time. They help me stay focused on analysis & visualization instead of cell addresses.
What about you? Do you use tables & structural references? Please share your experiences, tips & ideas using comments.
Bonus Video: Here is a video I did with Bill Jelen (MrExcel) about this topic.
Personal update: We are at beautiful Woodbury Inn on Blue Ridge Pkwy and loving our stay. See this video.
29 Responses to “Introduction to Structural References”
I agree that structured reference formulas are much easier to read, and also easier to write once you understand the notation.
One issue that can be annoying, is there is NO notation to create an absolute reference to a column. In a formula with cell references you can anchor column A with the $ symbol.
For example, "$A1" anchors column A so the reference to column A will not change when you copy or drag the formula across the columns.
You can NOT use the $ symbol in front of a column name with structured references. When you drag the formulas across columns, the column references will also move to the right. This would be problematic for your SUMIFS formula if you wanted to anchor the Region and Product columns, but make the Net Sales column un-anchored so you could pickup the Profit/Loss column by dragging the formula to the right.
Absolute Reference Solution
However, there is a workaround for this. Duplicating the column name as if it were a range of multiple columns will anchor it. For example, change the formula in cell K6 to the following:
=SUMIFS(sales[Net Sales],sales[[Region]:[Region]],$K4,sales[[Product]:[Product]],$K5)
Now you can drag the formula to the right to get the sum for Product/Loss in cell L6. Note that you have to drag the formula with the mouse. Copy/paste does not work.
I have an article and video on my website that gives more details and a sample workbook.
http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/
Thanks for the tip Jon. An easier option would be to copy paste the formulas instead of dragging. This will fix the references to column names while changing cell references as expected.
Copy/paste does not work if you want to make some of the column references absolute and keep others relative.
For example, in you sample workbook, let's say you want to copy cell K6 to the right to return the sum for the Profit/Loss column (column H). If you were using a formula with normal cell references, you could anchor the references to the product and region columns by adding a $ sign in front of the column letter ($D, $E). You would leave the reference to the Net Sales column (column G) relative. Then copy/paste to the right and you would get the SUMIF results for Profit/Loss.
This same principle does NOT work with structured reference formulas. If you copy/paste your formula in cell K6 to the right, it does not return reference the Profit/Loss column. If you drag the formula to the right, it references the Profit/Loss column, but also moves the references to Region & Product one column to the right. This returns incorrect results because the formula is now using the wrong columns for the criteria ranges in the SUMIF formula.
The solution I mentioned is a workaround to make structured reference columns absolute in a formula. I doubt Microsoft intended it to work this way, but it does work.
You will more likely encounter this issue if you have a table that has 12 months of column headers (Jan - Dec as column headers across the top), and you want to be able to drag/copy your SUMIF formulas across with your criteria columns anchored (absolute) and your month column relative.
In theory, you probably don't want to have a table with months in the column headers. It is not the proper data structure to use with pivot tables or data models. But the structure of months as column headers does occur and can be useful in certain situations.
I believe absolute/relative referencing is one of the limitations of structured reference tables. It's probably why they put the word "structured" in the name. 🙂 They are a bit more confined than cell references. However, there is a workaround, and you just have to know the limitations when working with them. I agree with all your points on how useful they are and make formulas easy to read.
Very good tip, as usual! I want to add something:
When you copy those Structural References to the right,
- if you drag them, they will be relative!
- if you copy (ctrl+c) and paste(ctr+v) they will be absolute!
😉
CERTAINLY THEY MAKE LIFE EASY THANKS CHANDOO
Normalized databases are nothing but a series of named table ranges.
Use whtever useful for u. no matter it is absolute or structured???????? Take a cup of coffee and enjy lyfe...
I am attempting to upgrade my formula writing to include using Tables and Structured References. I wrote a SUMIF for an Excel Table and everything worked great. Then I had a thought "Why not use the Table Headers (therefore just point and click) for parameter inputs for the SUMIF!" What a brilliant thought but to no avail. The SUMIF took the input but apparently can not understand the input. This was a very quick way to enter formula parameters. Any idea WHY it would not work. Formula examples below. First line is the typed formula (the answer is correct), the second is MY BRILLANT method. "SelectedRegion" is a range name.
347367 =SUMIF(sales[Region],SelectedRegion,sales[Net Sales])
0=SUMIF(sales[[#Headers],[Region]],SelectedRegion,sales[[#Headers],[Net Sales]])
Hi Michael,
If I understand you correctly, you want to create the formula by clicking on the column headers instead of typing it? If so, this is definitely possible.
When you are entering the formula and want to click the table to enter "sales[Region]" in the formula, hover your mouse over the TOP PART of the column header cell until the mouse cursor turns into a down arrow. You have to hover the cursor pretty close to the top part of the header cell. Once the cursor turns into a down arrow, left-click it once. This will input the "table[column name]" notation into your formula.
See the following screenshot for an example.
http://www.excelcampus.com/wp-content/uploads/2013/07/Select-Column-Data-in-Table-Screenshot.png
If you left-click the column header twice when the down arrow appears, the [#Header] notation will also appear. But this is NOT what you want for sum if formula. You only want to reference the data cells, excluding the header.
Let me know if this is clear.
I <3 structured references
[...] Introduction to Structural References [...]
[...] Learn: Introduction to Tables, Introduction to structural references. [...]
[…] Related: Introduction to Tables & Structural References. […]
[…] am sure you all are aware of a feature called as Excel Tables OR Structured References in Excel. Excel Tables is (in my opinion) the best way to store your raw data and put Formulas in the […]
Just wanted to say THANK YOU times a million for this video!! I have been trying to figure out structured references for a homework assignment for hours and nothing helped until I watched this! SO HELPFUL and you saved my ass on my next exam!!
[…] Table1[#Headers] guff above means – then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with […]
[…] what that Table1[#Headers] guff above means – then I strongly suggest you check out Chandoo’s Introduction to Structural References or give GOOGLE a […]
[…] 2007, and if you’re not familiar with them then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with MrExcel. A large part of their appeal is that they spookily […]
[…] Structural References & Tables […]
Well I found a work around to the lack of an absolute symbol for Structured References that I found easier than duplicating the column names.
Just make the formula an array entered formula (Cntr+Shift+Enter) then copy across. Your structured references will stay absolute and your normal cell references stay relative. You then have to just step through each formula and "un-Array" it by clicking the edit line (the curly brackets disappear) and enter. This just worked well for me for about 30 formulas.
Thanks Chandoo for the post as I thought I was going nuts when I struck this problem. I just thought the more logical default for structured references would have been for them to be absolute not relative but your article confirmed this was not the case.
Cheers
John Hackwood
Thank you for this post. I now love structured references, and I now use them in most of my engineering calculations.
Tables for financial models or pro-formas are transposed, with each year's data in a seperate column. Is there a way to use structured references with row headers instead of column headers?
Hello Chandoo,
You seem to know a lot about tables. These are a great tool for formatting, for typing and auditing formulas. However for some reason, I have one file (5Mo on xlsb format) that takes several minutes to close. The file is quick to open, to save and calculate, the only problem I find is when closing it. If I delete all the tables (by changing them to normal ranges), this do not happen. Have you encountered this behavior?
Thanks in advance.
K<L<M
[…] more references on using Tables in Excel formulas, try this helpful article/video from Chandoo, or this helpful explanation from Excel […]
Hey Chandoo,
Thanks so much! First time posting. Very informative and helpful.
Regards,
Seth
I'm having difficulty using countifs with a table to get the number of non-blank cells in a column (field) of the table.
=COUNTIFS(TableName[FieldName1],"Equip"&"*"TableName[FieldName2],$B$158,TableName[FieldName3],I184,TableName[FieldName4],""&" ")
where ,""&" " is intended to count the number of non-blank values in FieldName4. Filtering the actual table with these criteria shows there is one item with a value of 0 (so not blank) and the result should be 1, but I get 0. Any thoughts/suggestions?
Found the issue. The field name being used as a condition had spaces in it that were difficult to see. About drove me nuts trying all different iterations of determining what was/was not counted.
If i am working of data more than 10k records and more than 100 col. if i make a table of that then i will do analysis on that so will it work smoothly or will make complication while analysis i mean (not responding) if it do so how i can resolve it.
@Shoaib
It really depends on exactly what are you trying to achieve from your data?
Firstly in the table copy paste any formulas as values for cells where the values won't change
Minimise the use of Volatile functions
Have a read about Volatile functions here: https://www.sumproduct.com/thought/volatile-functions-talk-dirty-to-me
How to use the same formula using sumif