Bottom Line: Find out why I've stopped recommending Excel Tables (in some scenarios) due to the confusion they cause among users with varying Excel skills.
Skill Level: Beginner
Watch the Video
Download the Excel File
You can access the same Excel file I use in the video here:
Excel Tables
So, one of my favorite features of Excel is also one of my worst enemies. It's a feature that all Excel users can benefit from, but unfortunately, it's gotten me into a lot of trouble over the years. It's cost me hours of extra work, leading to frustration and confusion with my bosses and coworkers.
The feature I'm referring to is Excel Tables, and in most cases, I just don't recommend it anymore.
Don't get me wrong. I love Excel Tabes for many reasons.
The Benefits of Excel Tables
If you're familiar with Excel tables, you know their many benefits. They can take a range of data and turn it into a nicely formatted table with banded rows, automatic filters, and the ability to expand automatically when new rows or columns are added.
What's not to love here? Well, in reality, tables aren't that popular. Not many users use tables, and I believe it's for one main reason: they require you to learn a new formula language.
The Challenge of Structured References
For example, if we write a simple AVERAGE formula on a regular range, it looks like this:
=AVERAGE(G4:G18)
This is a regular range reference we're all familiar with. However, if we write that same formula against data in a table, it looks like this:
=AVERAGE(Table1[Amount])
This is called structured referencing. It introduces a whole new world for Excel users that can cause a lot of confusion. While this formula language might seem straightforward, it can be quite challenging when you get into it. It's like learning a new language.
In the video above, I explain how it reminds me of the show, The Amazing Race. Contestants on that show are dropped into a city around the world and have to ask for help from local citizens to complete challenges. The problems is that they don't always speak the local language, which can be frustrating.
Excel Tables are like this for a lot of users. They find it difficult to use an Excel file that contains a different formula language from the one they normally “speak”.
Real-World Example
Let me share a real-world example of how tables have caused trouble for me. When I worked in the FP&A department of a company several years ago, we were in charge of the annual budgeting process. This involved creating and populating budget templates for departments worldwide. We had multiple sheets in these templates with tables, and we had to deal with 928 departments and about 800 people involved in the process.
Most of these users had varying levels of Excel skills. We had a few advanced users, several intermediate users, and many beginners. Because structured references and Excel tables are more of an intermediate to advanced skill, we couldn't expect all managers to understand them. This led to many phone calls and emails saying, “Your file's broken,” or “I don't understand these formulas.”
Workarounds and Solutions
One potential solution I thought could save me was a setting in Excel. If you go into File > Options > Formulas, there's a setting to “Use table names in formulas.” Turning this off makes it so new formulas do not automatically use structured references.
However, this setting is an application-level setting. This means it stays with the user’s computer and doesn't travel with the workbook. This can create “bilingual workbooks” where different users see different types of references, leading to confusion.
Tips for Using Excel Tables
I want to be clear: I'm not saying don't use tables. I love tables, but I don't always recommend them. It really depends on the scenario. Here are some tips to help you decide when to use tables:
- Use tables when you know your users: If they're familiar with tables or if you think it's a good opportunity to teach them about tables, then use them.
- Avoid tables when sending files to vendors or customers: If you're not sure who you're sharing the file with or who they might share it with, avoid tables.
- Avoid tables with a large user base that has varying skills: As in my budgeting example, if you have users with different Excel skills, it's best to avoid tables.
Possible Solutions for the Future
I've thought of several potential solutions that Microsoft could implement to improve the experience with table formulas.
Ultimately, we want to remove the scenario where we have bilingual workbooks and allow users to author or edit formulas in their preferred style.
Tables have been around since Excel 2007, and they are an integral part of the data analysis process. It would be great if more users could use them. Until then, it's probably best to avoid tables unless you know your users will be able to use them.
Share Your Thoughts
I'm curious to know what you think. Leave a comment below and let us know your experience with Excel Tables and what changes you'd like to see to make them more popular.
If you're new to Excel Tables or would like a refresher, check out my Excel Tables Tutorial Video.
Thanks for reading, and have a great day!
no no. A broad user base is an opportunity to preach tables.
When it comes to formulas there really is no difference. Type “=AVERAGE(”
Go to G4 Hold down Shift and arrow down hit Enter or “)” enter.
Exactly the same as usual. 9 out of 10 users don’t even look at the formula (because they have foolishly left the Advanced setting for after pressing Enter to Move Down instead of turning it off so they can still see the formula they wrote.
How to make them more popular? Some kind of shadow effect in the next column to warn users not to type in it unless they want to create a full column.
Maybe something similar at the bottom, this time to invite more data.
Do not like them. For exactly the reason you specified. Haven’t taken the time to learn them. LOL
I’m creating workbooks for a large accounting department and I’ll keep using tables. They make it so much easier for me to maintain workbooks when there are people with varying skill levels. Granted, most of my users are never looking at the formulas and they just expect everything to work.
The feature that causes me more trouble is pivot tables because they’ve learned how to create a pivot table, but not how it works, where it comes from, how to manipulate it, and most importantly, that it doesn’t refresh automatically.
I’m very much waiting for the PIVOTBY and GROUPBY functions to make it to our Excel versions.
I don’t like the structured formulas either. But the tables have some advantages. If I don’t want a structured formula, I make the formula outside the table area and then change the cell values by hand. Quick and dirty 🙂
if you make the formula with more than one cell selected first, then it doesn’t use structured refs
Jon, my comment is a question. Have you brought your proposed solutions to the attention of the Microsoft Excel team?
In general, I agree with your points. I usually have to provide an explanation of how to insert rows correctly, because a common mistake is people appending to the end of a table instead of inserting rows or extending the table using the drag handle. But, including the Total row helps avoid that problem because if the Total row is visible, a person will tend to look for a way to insert Table rows rather than just typing below the last row of the table.
The main reason I use a Table in a template is because of formulas auto filling when inserting new rows. However, my preference is still to avoid using Tables unless they are really needed.
I love Excel tables, and use them as often as I can because of their efficiency, versatility, tidiness and yes, their structured referencing. I don’t mind the Structured Refs – but it seems that items 2 & 3 in your suggested solutions would fix most, if not all, of the problems you described. Not a fan of their default formatting, but that’s an easy fix. Don’t STOP recommending Tables … just add your caveats.
Hello.
First of all, thank you for your candid presentation. I have a similar experience with tables. Tables are, as you said, for users who have about the same knowledge of Excel. I don’t prefer them, although I do use them from time to time.
In addition, it is a different way of thinking, which is not close to many.
Greetings. Joze
I think what I really don’t like about Table is how it thinks it’s smart and will automatically apply the formula to the whole column when perhaps THAT particular cell was meant to have that formula and the rest of the column is not. And sometimes you aren’t aware that the whole column has changed the formula until later when the boss is checking and asking you ‘these are all wrong’ and then you saw that the formula has been updated inadvertently….
It is convenient that you enter a formula and every other cell is automatically updated but I wish there is a button right there for you to enable and disable the automatic update, rather than needing to go back and do the Ctrl Z to undo their auto update which may or may not work every time
If you have more than one formula in a table column, it will ask you if you want to update the whole column.
I also use tables regularly, especially when building template sheets where I need the formulas to automatically update when they expand. I just make sure I have good instructions on them.
Thanks for another interesting read, Jon. Having to learn yet another set of commands is one of the main reasons I generally avoid tables. As a retiree, I only share tables with others in the family, but even at this level, there is a range of skills, so your remarks are apposite here too.
Keep well!
I love tables and use them all the time. It’s almost annoying when I come across a situation where they are a problem. I use a lot of pivots and charts as well so tables really help. As for your ideas on options, I would absolutely go with number 3 followed by 2 as a backup option.
I love tables! They are essentially a database. There are downsides of course, but if you treat them like a database (i.e., standardize and normalize your data) and add some helper columns along the way, you can do quite a lot with them and don’t have to copy formulas or formatting. Microsoft has made improvements in the last few years that really help too.
I often use a table to store large sets of records then use formulas to summarize in a dashboard or summary page. With a little VBA code you can add buttons that add rows or filter the table.
I also create a “Config” sheet that contains drop-down lists for data validation. Or you can refer to a column in another table and it is dynamic. Create a list of customers with contact info on one sheet, then use the customer name as the data validation for a column in a transaction table on another sheet. Now drop-down lists are recognized by Excel and you can type a few characters to find records!
careful Dennis, don’t let the database police hear you say things link that!
having a separate config sheet is always a Good Idea (with each of those lists being a table as well)
I have some tables where the Validation list is “the last entry for this field from this person/vehicle”, so that comes up as the only suggestion (as it rarely changes)
Hi Jon, I know a trick if you write formulas and you DO NOT want the structured reference: just select more than 1 cells and write the formula, then press Enter or Ctrl enter (my favorite key combination, filling the selection with the formula, NOT changing the format). Selecting more than 1 cell while writing the formula always gives you – and all other users of your sheet – the A1 notation, without changing any settings. If you just need the formula in one cel, delete the formula in the other cell.
When I give Excel trainings, the Ctrl Enter is my “power trick”, unfortunately it doesn’t work in tables because I then don’t get the structured language although i personally prefere the structured language! I am so used to first select the cells, then write the formula for the active cell and then press Ctrl Enter… in tables I don’t do it, I don’t need it because filling one cell in an empty column duplicates the formula (in Structured Reference Style) to the whole column.
The only thing that makes writing Structured References a little bit difficult is that you can’t use F4 for absolute/mixed references (that really sucks). I use a lot of queries tho, that automatically create tables, so that’s a given, just have to get used to it…