Turn OFF Structured References in Excel Table Formulas

Structured references are a type of formula notation in excel. This means that instead of referencing to an individual cell address it uses a table reference. This type of formulas is called structured reference formulas. What if you want to turn this off because you want to copy paste your formula to a new sheet without referencing the values to your existing table and use only individual cell address.

Here are the steps to do this:

1. In Excel, Click File > Options > Formulas
2. In the “working with Formulas” section, uncheck “Use table names in formulas”
3. Press OK

excel_options
Existing formulas will not change your formula referencing after you disable this but new formulas created will use the individual cell address.

Excel_cell_reference_01

One thought on “Turn OFF Structured References in Excel Table Formulas

  1. Hi I am having trouble getting rid of my structured references, I have updated my options under formulas to make sure use table names in formulas is un-ticked but they are still showing up even when I make a new formula from my pivot table. Help please!! I am use Getpciot data functions for pviot table references and I want to use those use without the table headings. Please see below for example what is currently happening
    =GETPIVOTDATA(“[Measures].[Sum of Amount]”,Pivot!$A$3,”[Table1].[Date (Month)]”,”[Table1].[Date (Month)].&[May]”,”[Table1].[Account]”,”[Table1].[Account].&[A4 Sketch]”,”[Table1].[Catergory]”,”[Table1].[Catergory].&[Sales]”,”[Table1].[Date (Year)]”,”[Table1].[Date (Year)].&[2020]”)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.