Introduction to Structural References

Posted on June 26th, 2013 in Learn Excel - 18 comments

Ever seen a formula like =SUMIFS(Sheet1!B2:B3923, Sheet1!C2:C3923, A1, Sheet1!D2:D3923, A2) and wondered what it is really doing?!?

If so, you are not alone.

Formulas written with cell references tend to look complicated and clunky. What if we could write formulas in plain English?

That is what Structural References do. When using structural references in formulas, your focus will be on your data, not on which cell ranges the data takes up.

For example, you can write formulas like these:

  • SUM(mySales[no. of customers]) to find how many customers we had.
  • SUMIFS(mySales[no. of customers], mySales[product], “FastCar”) to find how many customers bought “FastCar”

Learn how to use Structural References and why they can change you life

Introduction to Structural References

[Watch this on our YouTube channel]

Download Example File

Click here to download example workbook and play with Structural References to learn more.

Learn more about Tables & Structural Refs

Do you use tables & structural references ?

I use tables all the time. They help me stay focused on analysis & visualization instead of cell addresses.

What about you? Do you use tables & structural references? Please share your experiences, tips & ideas using comments.

Bonus Video: Here is a video I did with Bill Jelen (MrExcel) about this topic.

Personal update: We are at beautiful Woodbury Inn on Blue Ridge Pkwy and loving our stay. See this video.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

18 Responses to “Introduction to Structural References”

  1. Jon Acampora says:

    I agree that structured reference formulas are much easier to read, and also easier to write once you understand the notation.

    One issue that can be annoying, is there is NO notation to create an absolute reference to a column. In a formula with cell references you can anchor column A with the $ symbol.

    For example, “$A1″ anchors column A so the reference to column A will not change when you copy or drag the formula across the columns.

    You can NOT use the $ symbol in front of a column name with structured references. When you drag the formulas across columns, the column references will also move to the right. This would be problematic for your SUMIFS formula if you wanted to anchor the Region and Product columns, but make the Net Sales column un-anchored so you could pickup the Profit/Loss column by dragging the formula to the right.

    Absolute Reference Solution

    However, there is a workaround for this. Duplicating the column name as if it were a range of multiple columns will anchor it. For example, change the formula in cell K6 to the following:

    =SUMIFS(sales[Net Sales],sales[[Region]:[Region]],$K4,sales[[Product]:[Product]],$K5)

    Now you can drag the formula to the right to get the sum for Product/Loss in cell L6. Note that you have to drag the formula with the mouse. Copy/paste does not work.

    I have an article and video on my website that gives more details and a sample workbook.

    http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/

    • Chandoo says:

      Thanks for the tip Jon. An easier option would be to copy paste the formulas instead of dragging. This will fix the references to column names while changing cell references as expected.

      • Jon Acampora says:

        Copy/paste does not work if you want to make some of the column references absolute and keep others relative.

        For example, in you sample workbook, let’s say you want to copy cell K6 to the right to return the sum for the Profit/Loss column (column H). If you were using a formula with normal cell references, you could anchor the references to the product and region columns by adding a $ sign in front of the column letter ($D, $E). You would leave the reference to the Net Sales column (column G) relative. Then copy/paste to the right and you would get the SUMIF results for Profit/Loss.

        This same principle does NOT work with structured reference formulas. If you copy/paste your formula in cell K6 to the right, it does not return reference the Profit/Loss column. If you drag the formula to the right, it references the Profit/Loss column, but also moves the references to Region & Product one column to the right. This returns incorrect results because the formula is now using the wrong columns for the criteria ranges in the SUMIF formula.

        The solution I mentioned is a workaround to make structured reference columns absolute in a formula. I doubt Microsoft intended it to work this way, but it does work.

        You will more likely encounter this issue if you have a table that has 12 months of column headers (Jan – Dec as column headers across the top), and you want to be able to drag/copy your SUMIF formulas across with your criteria columns anchored (absolute) and your month column relative.

        In theory, you probably don’t want to have a table with months in the column headers. It is not the proper data structure to use with pivot tables or data models. But the structure of months as column headers does occur and can be useful in certain situations.

        I believe absolute/relative referencing is one of the limitations of structured reference tables. It’s probably why they put the word “structured” in the name. :) They are a bit more confined than cell references. However, there is a workaround, and you just have to know the limitations when working with them. I agree with all your points on how useful they are and make formulas easy to read.

  2. rolo says:

    Very good tip, as usual! I want to add something:

    When you copy those Structural References to the right,

    - if you drag them, they will be relative!
    - if you copy (ctrl+c) and paste(ctr+v) they will be absolute!

    ;-)

  3. zurman says:

    CERTAINLY THEY MAKE LIFE EASY THANKS CHANDOO

  4. Kevin says:

    Normalized databases are nothing but a series of named table ranges.

  5. Use whtever useful for u. no matter it is absolute or structured???????? Take a cup of coffee and enjy lyfe…

  6. Michael Mlinar says:

    I am attempting to upgrade my formula writing to include using Tables and Structured References. I wrote a SUMIF for an Excel Table and everything worked great. Then I had a thought “Why not use the Table Headers (therefore just point and click) for parameter inputs for the SUMIF!” What a brilliant thought but to no avail. The SUMIF took the input but apparently can not understand the input. This was a very quick way to enter formula parameters. Any idea WHY it would not work. Formula examples below. First line is the typed formula (the answer is correct), the second is MY BRILLANT method. “SelectedRegion” is a range name.

    347367 =SUMIF(sales[Region],SelectedRegion,sales[Net Sales])
    0=SUMIF(sales[[#Headers],[Region]],SelectedRegion,sales[[#Headers],[Net Sales]])

    • Jon Acampora says:

      Hi Michael,

      If I understand you correctly, you want to create the formula by clicking on the column headers instead of typing it? If so, this is definitely possible.

      When you are entering the formula and want to click the table to enter “sales[Region]” in the formula, hover your mouse over the TOP PART of the column header cell until the mouse cursor turns into a down arrow. You have to hover the cursor pretty close to the top part of the header cell. Once the cursor turns into a down arrow, left-click it once. This will input the “table[column name]” notation into your formula.

      See the following screenshot for an example.

      http://www.excelcampus.com/wp-content/uploads/2013/07/Select-Column-Data-in-Table-Screenshot.png

      If you left-click the column header twice when the down arrow appears, the [#Header] notation will also appear. But this is NOT what you want for sum if formula. You only want to reference the data cells, excluding the header.

      Let me know if this is clear.

  7. dan l says:

    I <3 structured references

  8. [...] Learn: Introduction to Tables, Introduction to structural references. [...]

  9. […] Related: Introduction to Tables & Structural References. […]

  10. […] am sure you all are aware of a feature called as Excel Tables OR Structured References in Excel. Excel Tables is (in my opinion) the best way to store your raw data and put Formulas in the […]

  11. Molly says:

    Just wanted to say THANK YOU times a million for this video!! I have been trying to figure out structured references for a homework assignment for hours and nothing helped until I watched this! SO HELPFUL and you saved my ass on my next exam!!

  12. […] Table1[#Headers] guff above means – then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with […]

  13. […] what that Table1[#Headers] guff above means – then I strongly suggest you check out Chandoo’s Introduction to Structural References or give GOOGLE a […]

  14. […] 2007, and if you’re not familiar with them then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with MrExcel. A large part of their appeal is that they spookily […]

Leave a Reply