One of the things I like best about Excel Tables is ALSO one of the things I like the least.
I'm talking about table formulas.
Video Tutorial
Watch on YouTube & Subscribe to our Channel
Downloads
For example, when I create a simple formula in the Excel Table below, multiplying cell C2 by cell D2, Excel writes those as [@Qty] and [@Price] when I select them. Using these column header names—or structured references—instead of cell designations can cause the formulas to look longer or different from what many people are used to.
While some people find structured references easier to read, to others they make the formula appear more convoluted. This is especially true if the formulas are long or the column headers are not well labeled. This use of structured references might cause some confusion for your boss, coworkers, or other users who are trying to understand your formulas.
Turning Off Structured References
You can actually turn off the use of structured references if it's helpful for you. To do so,
- Go to File.
- Click Options.
- Select Formulas.
- Uncheck the box labeled Use table names in formulas.
Now, when we type a formula in the Excel Table, it will use cell names instead of column header references.
Just know that turning off the table names is part of the Excel settings for your computer. It's an application-level setting. That means you'll need to change the settings for each computer you use.
If you turn the setting off on your computer and send a file to another user, the setting will NOT be turned off on their computer.
How do you feel about structured references? Do the header names make the formulas more or less confusing to read and write for you? Leave a comment and let me know your thoughts.
Hi
Thank you for sharing your knowledge 🙂
Since I learned table formulas, I can’t do without it even in long formulas.
Have a nice day
I’m closer to the hate position :).
I HATE structured references for the exact reasons you listed. Thank you, thank you, thank you for showing me how to turn off this annoyance!
I love structured references. As a programmer, I’m a believer in using meaningful names to make code more readable. And if your column names are not meaningful, why not?
That said, the formulas can be rather long if you have long column names or table names, so I sometimes abbreviate my names.
One annoying feature of structured references is that they refer to columns relatively, not absolutely. That’s usually the opposite of what I want. Really, we should be able to choose.
I agree with your thoughts on Structured references, confused the heck out of me the first time i saw them!
I don’t like the table references, so I followed your instructions but my formulas are still showing the table references even though I closed and re-opened Excel?
Hi Peter,
The setting will only turn off the structured references when new formulas are created. It does not change the references in existing formulas. Sorry if I wasn’t clear on that.
Great Tip Jon, the video was short and sweet!
Hi Jon
So from your post I gather it’s one or the other. We can’t have some worksheets using table header names and some not?
Some of my tables use short names and other’s not so short…
Kind greetings from a chilly and dark South Africa
Hi Simon,
You can use both A1 and structured referencing in the same sheet or workbook. If you have the setting turned off, then you will have to manually type the structured references. If you have it turned on, then you will have to manually type the A1 referencing.
Toggling the setting on/off does not change existing formulas. So you can turn it on and write formulas with structured refs, then turn it off and write formulas with A1 refs.
This is totally fine, but can lead to a lot of mixed referencing styles throughout the workbook.
I hope that helps. Thanks again and have a nice day! 🙂
Former hater, now a lover. What helped? 1) My habit of being thoughtful about naming data fields 2) I’ve learned the trick of leaving vowels out to shorten words but preserve recognition. It limits the need for work wrap and shortens formulas 3) intellisense is your helper. If you remember your table names(start them all with Tbl), then arrow and tab you way through the formula. When you write formulas this way, reading them back for editing or debugging becomes natural quite quickly. 4) Finally, it is a good framework from which to understand the new array based formulas/thinking.
I love it! Thanks for sharing your tips Brent!
As others have said, fine if the column headers are short and sweet, but what happens when you want to make a formula absolute?
My brain seems to be wired towards the hate side for that reason and the length of the formula reason and I see A2 more clearly in my head than @current_year etc!
I’m with Debbie in thanking you for showing us how to turn it off!!
Hi Lee,
Great question! You can make column references absolute. It’s not straightforward, but here’s a post that covers it.
https://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/
However, you have to use A1 notation if you want to make row references absolute.
Great demonstration.
I hate them.
Understand, I love formulas like =Qty*Price. But that’s not what Excel produces.
Fine, I’m OK with the idea of needing special ways to signal Excel what’s what… except that Excel could do just fine with any word/phrase in a header being a special word/phrase that cannot be used otherwise, no use of it as a Named Range for example. Still, I could live with it.
But what kills it, unequivocably for me, is that Excel cannot seem to get its fool head around the idea that I might desire a header with multiple lines, which I can only achieve using “Alt-ENTER”, but that in no circumstance on God’s green Earth do I EVER want freaking formulas that drop a line due to Excel stupidly displaying the formula using the “Alt-ENTER” to signal a new line. INSIDE a single formula reference item.
Morons.
And so I don’t use them. Period. Not even with the fact of them existing helping routine maintenace of a Table by seeing if someone’s begun overwriting them when pasting information in, seeing it instantly due to not seeing the funky looking thing, oh, sorry, the “structured formula” existing.
HATE them.
Nice tip!
I’m on the love side. As you say, having well named tables and columns helps immensely, but it’s worth the effort for two big reasons (among many). First, now that we can have dynamic/spillable formulas, it helps greatly having tables that are “smart” enough to know how much data they contain and avoid using whole column references in places that might exact a stiff penalty in recalc time. Second, it means never having to turn the page or hunt something down to confirm what data lives where. I’m fond of asking “does anyone remember what was in column H of Sheet3 because I know I don’t and neither will anyone else when they look at this SUM calculation.” Or something like “I know we want the customer’s zip code, but I can’t remember which of the 81 columns of data has that information.” Referring to content by name means never having to worry about such things.
Agree! I worked on the data dictionary team for a big company. We always had some programmers who got all grumpy when they couldn’t name their own variables. They didn’t want to understand that other coders had to use the same variables in their code and they had to mean the same thing. Some programmers get territorial about “their” code.
Hi Jon, I don’t hate anything really but in this instance I’m easy with the conventional formula procedure using individual cells but knowing about the alternatives is handy.
I love them but there are just some places in my apps I need to use absolute references and that’s okay. I have no problem with both but leaning a little heavier towards Structured References.
Thanks for this, Jon. I teach a beginning Excel class, and most students take a while to get comfortable with deciphering basic cell references. They like the table functionality, but their eyes cross when looking at the structured references (good to know what to call them), especially, as you noted, they’re used in a formula longer than a few cells.
I’ve experienced the same issues, Lallie. And it’s even worse when users discover them on their own when using a workbook that someone else created.
It’s great to hear that you are teaching an Excel class and at least trying to teach others about Tables. I believe Microsoft needs to make some changes to Tables before they are adopted by the masses.
The programmer part of me loves the idea of structured references because they provide context to a formula. I typically use formulas in reports that I am providing to clients where my table headings need to be clear to them. I do not have the option of making the headers shorter. This leads to formulas that look confusing. I am sticking with column headers.
I totally agree with your stance. I opinion extends to the whole table functionality. Love some features and hate some of the restrictions.
Microsoft has to improve table features.
Hi:
I sort of love the structured table references for the easier understanding of the formulas.
I hate them, because it is not easy to copy a formula with structured references from one table to another (even if the other table has the same column
headings). The simple copying (CTRL/C / CTRL/V or similar) copies the formula but keeps the structured references to the original table.
I know that I can copy the formula text by editing the formula, copying the text from the formula cell, escape from the editing and then paste the formula into a cell in the new table. In this case the formula will refer to columns of the new table, even if they are positioned in different orders.
I have made a sort of solution with a VBA macro that I invoke with CTRL/SHIFT/C. The macro body is
Sub CopyFormulaToClipboard()
Dim obj as New DataObject
Obj.SetText ActiveCell.Formula
obj.PutInClipboard
end sub
After CTRL/SHIFT/C on a cell with structured table references, I can simple CTRL/V to put exact the same fomula text in another table.
Unfortunately this only works in English Excel, because the Excel internal representation of list delimiter (or parameter delimiter in function calls in the Formula) is a comma. In my Danish Excel the delimiter shall be typed as semicolon. To solve this (partly) the middle line is actually
obj.SetText Application.WorksheetFunction.Substitute(ActiveCell.Formula, “,”, Application.International(xlListSeparator))
This works in all practical cases I have used, but it would fail if the formula somehow contained a Comma in a textstring 🙁
Table formulas are a disaster, once you get multiple tabs/tables and goofy names. Then when you transition your workbook, it will be beyond saving.
Table formulas= 1 user and non-changing data structure. If you think you really need this, start using Access.
Cell References= Many users and changing data structure. Just stay with this as it’s superior.