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

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



One Reply to “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]”)


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 )

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.

%d bloggers like this: