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.
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.