How to Turn OFF Structured References in Excel Table Formulas - Excel Campus
10

How to Turn OFF Structured References in Excel Table Formulas

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.

table-formula-references-can-be-turned-off-example

This can be done in the Excel Options Window.

Here are the instructions to turn Structured References (Table Formulas) Off:

  1. Click File > Options in Excel.
  2. Click the Formulas option on the left side menu.
  3. In the Working with Formulas section, uncheck the box that says “Use table names in formulas”.
  4. Press OK.

turn-excel-table-formulas-structured-references-off

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.

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.

excel-tables-video-thumb

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.

Excel Tables eBook - Zack Barresse - Kevin Jones

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.

lookup-formulas-video-thumbnail-480x287

Please leave a comment below with any questions.  Thanks!

Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 10 comments
Pablo Silva - November 9, 2017

Thank you very much!! This was exactly what I was looking on the internet!

Reply
leon - August 23, 2017

Hi Jon
Table formulas always have a problem being at the bottom of the column. When i refresh my data from the SQL DB the formulas do not refresh accordibly to keep the table content of rows or gets wiped with the new number of rows
Putting it top of column it does not refresh to the ful
l row content

Reply
Sudhakar - August 23, 2017

I will not stop thanking you for your knowledge improving posts. this article and others like it is just a great work.

Thanks

Reply
Geard - August 23, 2017

Thanks Jon.
Does this mean that the formula will still get adjusted automatically when new data is added to the table as it does with the structured references?

Reply
Robert (Bob) Keene - August 23, 2017

Thank you so very much for making all the EXCEL clues readily available to us. They are truely outstanding resources…!!!

Reply
Uche Uche - August 23, 2017

I will not stop thanking you for your knowledge improving posts. this article and others like it is just a great work.

Thanks.

Reply
Sarmad - August 23, 2017

Jon Acampora (Good Day)
I want to ask some different.

After installing FUZZY LOOKUP EXE. & RUN but Fuzzy lookup not run in my system and other system, so what can i do?

What is the shortcut key of text or background color selection in excel I know Alt HH and other but i want to know short cut or create shortcut for text / background color choosing…

Hope u advise us.

Kind Regards
Sarmad

Reply
Col Delane - August 23, 2017

Hi Jon
I’m trying to get used to structured references, but it’s a bit of a love-hate relationship as there are a few significant disadvantages of Tables that really annoy me:
1. you can’t use a formula to generate the column heading (which then becomes the Table field name) – it must be a string (even entered numbers get converted to a string!)
2. formulas that reference a Table (at any level) become a bit lengthy & unwieldy because of the additional qualifying references (e.g. Table1[MyFieldName])
3. Whilst you can enter a structured reference to define the “Applies to” range in Conditional Formatting rules, that definition gets automatically converted to individual cell addresses (e.g. $A$1:$B$5)

Regards
Col

Reply
Mike - January 11, 2017

Thank you!!!! These table references were driving me nuts!!!

Reply

Leave a Reply: