Which structured reference option returns only the data part of the table?

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.

Which structured reference option returns only the data part of the table?

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:

  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.

Which structured reference option returns only the data part of the table?

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.

Which structured reference option returns only the data part of the table?

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.

Which structured reference option returns only the data part of the table?

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.

Which structured reference option returns only the data part of the table?

Please leave a comment below with any questions.  Thanks!

Previous How to Filter for Duplicates with Conditional Formatting

Next Filter Multiple Columns – Free Video Training Series – Part 3 of 3

You may also like

Which structured reference option returns only the data part of the table?

How to Prevent Excel from Freezing or Taking A Long Time when Deleting Rows

Which structured reference option returns only the data part of the table?

Quick Tips and Shortcuts for Renaming Excel Tables

Which structured reference option returns only the data part of the table?

How to Split Text in Cells with Flash Fill in Excel

Which structured reference option returns only the data part of the table?

How to Fix an Excel Table That’s Slow to Scroll or Respond

  • Mike says:

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

    • Jon Acampora says:

      Thank you Mike! I’m happy to hear it helped. 🙂

  • Col Delane says:

    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

  • Sarmad says:

    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

  • Uche Uche says:

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

    Thanks.

  • Robert (Bob) Keene says:

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

  • Geard says:

    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?

  • Sudhakar says:

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

    Thanks

  • leon says:

    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

  • Pablo Silva says:

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

  • RabbitPie says:

    Hi folks
    I had table references but once I saved and closed the spreadsheet they changed automatically to cell references. My problem is how to change them back to table references? (my tables are data connections and change in size so having absolute references is not ideal).
    Thanks

    • Tom says:

      Hey RabbitPie,
      I’m running into this same anomaly right now, did you ever figure this one out?

  • Marc says:

    Hi Jon,

    I have a table where certain columns are linked to tables in a prior month tab. For example, my October table (named “TblOct”) compares the current month data to the September table (named “TblSep”) in a separate tab and computes the variance. When I progress to the next month, I make a copy of the Oct tab and rename it Nov. I then change the references to TblSep that carried over from the tab I copied to TblOct and refresh the formulas to compute the variance. However, when I close and re-open the file, the references change back from the new TblOct back to TblSep. Why would Excel revert to the prior table reference? I appreciate any guidance you can provide. Thanks in advance.

    • 2A protects 1A says:

      Yeah, I’m not digging this as you can’t copy to other sheets. The bugs need working out.

  • Siva says:

    Yes indeed it takes few mins of writing formulas this way to fall in love with structured references. Thank you for the videos and articles.. they are very easy to follow and pack a punch also… I fell in love with structured references but now i am heartbroken 😉 as its not working.

    Thing is the structured reference is good until i close the workbook. Once i close and reopen it gets switched to range reference. I made sure that in options | formulas | “Use Table Names for formulas” is checked.

    Could you please suggest what am i missing here.

    Thank you in advance.

  • Rajendra says:

    Hello, What happens if I convert structure table with formulas in it to Normal range and change it back to structure table, does it change to Cell reference, is there a option to change all structure formulas to cell address including the old ones. Thanks in advance.

    Why my formula to the left eg column “A” has serial No formula A3 as A2+1 but doesn’t extend if I add the Row to structure table! I used alternative as =Row()-1 which works fine.

    What are structured references in Excel?

    Excel offers a way to refer to data that is contained in a table by using the table name and column headers. The combination of using table name and column headers is called a structured reference. Structured reference makes it easy to add formulas to an Excel table.

    What is a structured reference form?

    Clinical & Academic Structured Reference. This form should be used to provide a clinical and/or academic reference for medical specialty training or an academic post. The candidate to whom this structured reference refers has applied for such a post and has given your name as a referee.

    What is an unqualified structured reference?

    Qualified and unqualified structured reference For an unqualified reference, there is no need to indicate the table name. Figure 10. Example of unqualified reference. When we enter a formula outside the table, we use a qualified reference through these steps: In cell C9, enter the first part of the formula =sum(

    What are 3 D references in Excel?

    A reference that refers to the same cell or range on multiple sheets is called a 3-D reference.