Excel Tables use a new type of formula notation called structured references. Instead of referencing individual cell addresses, formulas in Tables reference the column names. These new formulas are called structured reference formulas.
The structured references take some time to learn and get used to. If you don't like the Table formulas then you can turn them off.
How to Turn Off Table Formulas in Excel Options
This can be done in the Excel Options Window.
Here are the instructions to turn Structured References (Table Formulas) Off:
- Click File > Options in Excel.
- Click the Formulas option on the left side menu.
- In the Working with Formulas section, uncheck the box that says “Use table names in formulas”.
- Press OK.
When you are editing or creating a formula and select a cell or range inside a Table, the regular cell address (A1) referencing will be used instead of the structured references.
How to Toggle Table Formulas On/Off with VBA
We can also use a VBA macro to toggle the table formulas setting by changing the GenerateTableRefs property of the Application object.
Here is the VBA code to Turn Off table formulas:
Application.GenerateTableRefs = xlGenerateTableRefA1
Here is the VBA code to Turn On table formulas:
Application.GenerateTableRefs = xlGenerateTableRefStruct
This is an application level setting. I explain more about that below.
What Happens When Table Formulas are Turned On/Off?
There are a few important things to note about this setting.
- Any existing formulas that contain structured reference will NOT be changed. This only applies to new formulas or formulas that you edit.
- This an application level setting. That means it applies to all workbooks that you work on. The setting does not travel with your workbooks. If you send your file to a co-worker and they have the setting turned on, then they will see structured references in their formulas. They also have to turn the “use table names in formulas” setting off on their computer.
I really like structured references and I personally think they make formulas easier to read and write. However, I also think they are the biggest barrier to using Tables. A lot of users see these formulas for the first time, don't like them, and don't use Tables because of it. This is unfortunate because Tables are an extremely useful and powerful tool in Excel that can save us a lot of time.
I have a full video on a Beginner's Guide to Excel Tables that explains all of the great benefits of using Tables.
I learned this tip from my good friend Zack Barrasse at ExcelTables.com. Zack literally wrote the book on Tables. Check it out (Amazon link) if you want to learn more about this awesome feature of Excel.
I also have a free 3-part video series on the Lookup Formulas like VLOOKUP and INDEX/MATCH. Using structured references with the lookup formulas can really save us a lot of time and help prevent errors.
Please leave a comment below with any questions. Thanks!