Comments on: Structured reference in Excel tables

One of the most useful features of Excel tables are structured references. When you have just stumbled upon a special syntax for referencing tables, it may look boring and confusing, but after experimenting a bit you will surely see how useful and cool this feature is. Continue reading →

Comments page 2. Total comments: 80

  1. Superb explanation...

  2. Thank you for the explanation. I am thinking how we can use the column name dynamically.
    For example I have a table which have monthly purchases(January, February, etc. like month names are headers) . I don't have total column, but instead I have a cell below the table (not in the table reference) and I need to find the current month purchase total.

    If current month is January, I need to update the total of January column. My table name is tblPurchase.

    I can find the current month column name using MATCH formula like below

    =CONCAT("tblPurchase[", TEXT(TODAY(), "mmmm"), "]")

    But how can I use this column name as a reference? Assumed that A5 is the result cell of the above formula.

    =SUBTOTAL(9, A5)

    It returns 0 only. Can anyone help me

    1. Hello!
      Create a named range (for example, "March").
      You can find the sum of the values from this named range using the formula

      =SUM(INDIRECT(TEXT("03/01/2021","mmmm")))

      You can learn more about named ranges in Excel in this article on our blog.
      If this is not what you wanted, please describe the problem in more detail.

  3. One additional nuanced scenario is if the column name is a number:

    For example, when making a relative structured reference to multiple columns within the current row.

    The above example works fine:

    =SUM(Sales[@Jan]:Sales[@Feb])

    ...unless the column names are a number

    Substituting
    Jan = 2021.01
    Feb = 2021.02

    =SUM(Sales[@2021.01]:Sales[@2021.02]) *** This will not work!

    An additional set of brackets around the column name is required:

    =SUM(Sales[@[2021.01]]:Sales[@[2021.02]]) *** This works!

    1. Hi Marty,

      Thank you for your comment.

      More precisely, that additional set of brackets is required because of the dot. If the columns names were integers, extra brackets wouldn't be needed.

      The general rule is this: if the column names contain spaces, punctuation marks or special characters, an additional set of brackets around the column name should be added.

      Again, thank you for this observation! We've added this information to the article.

  4. Thank you for the topic is great.
    I would like to ask if there is a way to solve the below scenario:

    1. Range of Data let's say B5:F12
    2. the first row is the header (col1, col2, ..., col5)
    3. I have a lot of formulas referencing different columns of this range.
    exp1: =SUM(F6:F12)
    exp2: =SUM(E6:E12)+SUM(F6:F12)

    3. I converted the range into a table named table1

    the question:
    is there a way to change all the references in all the old formulas to structured references of table1?
    exp1(will change to): =SUM(Table1[col5])
    exp2(will change to): =SUM(Table1[col4])+SUM(Table1[col5])

    Appreciate you response
    Firas

  5. Is there a way to convert a formula containing 'old-style' cell references to structured references. I have a set of formulas that reference a range I have converted into a table. Now I have to manually adjust $A3 to [@[Client ID]] in the formulas. It would be great if this could be done across a range of columns automatically similar to Insert > Name > Apply when using Named Ranges. Can it be done?

    1. I have this same question, but I want to just show cell references because it is very difficult for me to read the names and figure it out as a formula but I would like to keep it as a table.

  6. Good morning.

    I have a table and I want to get values (Similar to vlookup) in cells outside the table and row of the table.

    Taking the lookup value and Table Column Name to retrieve from cells content (Not constant values in a formula).

    Please, could you help me?

    Thank you

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  7. Joining 2 columns in a table. A number value in column A joined to a Date value in column B to form a text value in column C using formula C=A&" - "&month(B)

    Tables converts this formula to
    =[@Number]&" - "&MONTH([@[DATE]])

    Sample;
    65 Nov 26, 2020 65 - 11
    66 Dec 07,2020 66 - 12

    Is there a way to format the month portion only in column C so it displays as Nov and Dec...

    Desired;

    65 Nov 26, 2020 65 - Nov
    66 Dec 07, 2020 66 - Dec

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =[@Number]&” – “&TEXT([@[DATE]],"mmmm")

      You can read more about the TEXT function in the above linked article.

  8. Hi,

    I am having a heck of a time with doing and AND function with structured references. The question is [Project Type] of "Mixed Use" and [Funding Type] of "Loan" and every time I set it up, it says invalid. I don't know what else to type. Please help. Thanks

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please specify what you were trying to find, what formula you used and what problem or error occurred.

  9. Totally impressed with all the great stuff that comes out of Ablebits. This is no exception. The problem is, and I hate to say this, I simply don't have the time or inclination to relearn a whole new way of writing and executing this new way of creating formulas and all the rules and exceptions involved. I am an old timer that learned spreadsheets on SuperCalc, 123 and Lotus then finally Microsoft came up with what I consider to be the last word in what I need for my fairly simple calcuations having to do with my stock day trading.

    Sadly, a few years back, I decided to create a sophisticated Workbook whereby I created a primary worksheet comprised of 365 pivot tables for each day of the year. Each table has all my stock positions broke down into specific lots and and from that, I keep running totals of all kinds of statistical data I created with standard formulas. The workbook has all kinds of worksheets crammed with statistical data that I reference and use in the primary worksheet.

    So, one day, I fiddled with one of the options for naming thinking that the use of the header names instead of standard cell references would make things easier. From that day on, Structured references began creeping into my formulas as I added things. I had no idea it would grow to be such a problem. It especially became prevalent when I imported CSV files and created new pivot tables in other worksheets. I also have a lot of worksheets in this workbook with long sophisticated names that I have to pull data from. Column Header names were also long but by making them that way, I knew precisely where to go to extract data. I learned to read my formulas fluently even with these setbacks and move around quickly and precisely over a period of time. Now, I am constantly having to physically remove structured names as they keep popping up like burnt popcorn and rewrite the formulas all over again. I finally found out how to get back into the proper location to turn off table names in formulas, but I have not found any way to force excel to convert all the structured names back to the good old Column, Row nomenclature and I either cannot figure out the proper way to phrase the proper question, perhaps there is no way to easily fix this but I really would like to purge all the structured references without having to start over again after all these years.

    Sorry for the long winded explanation but I am hoping one of you fine people can help me with suggestions on how to proceed, or steer me to somewhere on the web to a place that can.

    Thank you for your kind consideration.

    1. "I finally found out how to get back into the proper location to turn off table names in formulas"

      Thank you, good sir. I have been googling for a long time trying to figure out how to show cell references, not names you gave me what to search for.

      For anyone looking on a Mac, it is different than on Windows, as is so often the case.
      Preferences>Tables & Filters>Uncheck "Use table names in formulas."

      You then have to update a formula, and they will all change to show coordinates. Closing and reopening the workbook will not do the trick.

    2. Hello!
      Unfortunately, without seeing your data it hard to give you advice.
      If I understand your task correctly, you can convert your tables to regular data ranges as described here. This will automatically change the formulas in your workbook.
      If I misunderstood you, please explain your problem in more detail.

  10. I am working on a doc with multiple sheets and I am referencing different tables in different sheets.

    I reference a table on another sheet and everything displays how I want but when I reorganize the referenced table by another metric (sort by price vs sort by model etc) it changes the values in the other table.

    Is there a way to reference a specific cell in a table despite how the table is organized?

  11. Just wanted to say WOW. This is so thorough, clear and well written. Kudos and thank you to the author. I'm saving this for reference for certain!

  12. 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]")

  13. first thank you for this fantastic tutorial
    one question:
    how about dynamic range like $A$2:A$2 when we drag it to right and it came to $A$2:B$2 and ...
    how can we have this in structurede reference?

    thanks alot

    1. Hi Hosna,

      I am not sure this can be done with structured references, at least I do not know a way. You can just continue using $A$2:A$2, it will work for table's data as well.

  14. What version of Excel supports this function? If I save a spreadsheet that uses structured references to the .xls format, will those be stripped from it?

  15. Is there a work around for something like $B$2, while working in tables? I understand mostly the absolute reference structure working in tables, but i am curious on how to re-create $B$2 using the table syntax.

  16. These things kinda vex me.
    I used to have all my SUMIFs and SUMPRODUCTS working fine.
    Now...sheesh, its extremely difficult to try to find structured table references to sum based on multiple criteria.

  17. Matt, thank you for your help. I was able to make it work. I did need to include the table name, but that may be my version of Excel

    Formula, (in a column titled 'Total'), that worked for me:
    =SUM(Table1[[#Headers],[Amount]]:[@Amount])

    Greatly appreciate the help.
    Sergio

  18. Hi,
    If the name of the column i want to use is in a specific cell, how is the correct notacion of this reference.
    Example
    In cell B1 = Amount_20
    Instead of writing the formula "=ProfitAndLossStandard__3[@[Amount_20]]" I tried to use "=ProfitAndLossStandard__3[@[B1]]" but did not work.
    How do I make this reference correctly?

    1. =INDIRECT("ProfitAndLossStandard__3["&B1&"]"

  19. awesome tutorial! I'm currently diving into courses of excel provided by pwc which lacks the explanation of this. Nice job!

  20. I'm having an issue. I'm using Table structured references in my linear regression sheets. However, after I have everything setup like I want it and I save and re-open the file, the structured reference formulas turn into regular formulas referencing the rows/columns of the current data in the sheet like a regular formula. I need my structured references to stay intact so they will grow and shrink as I add and remove data to the tables.
    for example:
    =MEDIAN((Table14[CostPerUnit]))
    after saving and re-opening turns into:
    =MEDIAN('Sheet1'!$K$16:$K$69)

    Normal behavior? Anyway to avoid it or am I missing something? Any help is appreciated!! Thank you!

    1. I have the same problem. Any solution?

  21. I see your examples on structured references in columns. How can I use this in rows? I want a cumulative total column next to a column of values. I can do this with =SUM($B$3:B6) in column C but can I do this with structured references? My table has a column called DESCRIPTION and one called AMOUNT. I would like a cumulative column with a running total.

    Thanks for the help
    Sergio

    1. Hi Sergio, similar to my answer above you can use the Header to 'fix' the top. Try:
      =SUM([[#Headers],[Column B Name]]:[@[Column B Name]])

  22. Hi
    I'm trying to calulate the sequential duplicate number in a list of Id Numbers in a table. There are different numbers of duplicates for each Id number. Because the table is downloaded as an excel template from Dynamics I need to use the structured reference (using cell references doesn't calculate in the downloaded spreadsheet as the formula doesn't copy down without structured references). Normally I would just use =countif(d$2:d2,d2), but this doesn't automatically calculate when downloaded from Dynamics. When I create my version of this in a structured reference formula it doesn't work. I've tried =COUNTIF([Form '#]:[@[Form '#]],[@[Form '# ]]) where Form # is the field in the table but it proviudes me with the number of duplicate records in every row. I've tried a few alternate versions but they don't work either. Thanks in advance, Shane

    1. Shane, I was looking something else up and saw your comment. Sorry I didn't see it earlier. If you are still wondering the problem is that the first [Form '#] of your "range" is already the entire column and hence why all results are total number of that duplicates. Try [[#Headers],[Form '#]] instead to force it to use the header cell of that column for your range. So the new formula would be:
      =COUNTIF([[#Headers],[Form '#]]:[@[Form '#]],[@[Form '# ]])

      1. Hi Matt, I was having the same exact issue as Shane with the table reference for a COUNTIF formula. I tried your table reference fix and I didn't get it entered right, the first time. I tried it again, a second time and sure enough, it worked. I've got to say, YOU ARE THE MAN!!! No one out there on the internet explains, or shows how to do a mixed reference in a table reference version like that. Thanks for showing how it's done.

  23. I've discovered structured references and want to do more with them. Seems like they only work as intended (auto filling columns) on the same worksheet with the table / query. Is there a trick to doing this from a different worksheet?

    1. Hi Dave,
      Structured references can also be used from a different worksheet. When making a formula, you just need to select cells in the original table using the mouse. In this tutorial, you can find some examples of formula with structured references outside a table.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)