Absolute Structured References in Excel Table Formulas

May 8, 2013 | by Jon Acampora |  55 comments

This post will explain a trick for creating absolute structured references in Excel Table formulas.

Structured Reference Tables are great for creating clean, easy to read formulas.  But creating absolute references to the columns (aka anchoring the columns) in the formula is a bit tricky.

Quick Guide

Duplicate the column references as if referring to multiple columns.  Absolute references to:

  • One column in the same or other table: table1[[column1]:[column1]]
  • One cell in the same row as the formula: table1[@[column1]:[column1]]
  • Table names must be used even if the reference and formula cell are in the same table.
  • You must drag these formulas across columns to maintain the absolute reference (copy & paste does not work).

I have developed an add-in allows you to use the F4 key on the keyboard to create absolute/relative references.  You can download the Absolute Reference Add-in here.

Video Tutorial

_

Overview of Tables

In Excel 2007, Microsoft introduced Structured Reference Tables (aka Tables).  These tables have a lot of great features that make it easier to work with and analyze data sets.  Tables include a new syntax for referring to table columns in formulas.

Excel Cell Reference vs Table Reference

Instead of using cell addresses with column letters and row numbers, Tables allow you to reference cells or ranges with the table and column name.  The major benefit of this is that formulas are much easier to type and read when they refer to descriptive attributes of the table (table and column names).

If you are not familiar with the Tables feature yet, checkout this video:

Excel Tables Tutorial: Beginners Guide for Windows & Mac

Excel Tables Video Page

The video is an in-depth tutorial on how to create and use tables.  I explain 10 awesome features that will save you lots of time when working with your data.

_

Problem with Absolute References in Tables

However, there is no direct way to create an absolute reference for a table reference in a formula.  By default, all table references are absolute and have the following behavior when dragged or copied:

  • Formula dragged across columns: Column references change by referring to the next column to the right.
  • Formula copy/pasted across: Column references remain static; do not change when copy/pasted.

When your formula needs to contain a combination of absolute and relative references, there is no way to drag or copy the formula across and keep the references correct.  Dragging the formula across will make all the references change, and copy/pasting will make all the references stay the same.

Example

I’m going to use the following SUMIF formulas as an example.  You can download the example workbook below to follow along.

  • Cell Reference (cell G5): =SUMIF($E$12:$E$23,$E5,G$12:G$23)
  • Structured Table Reference (cell G6): =SUMIF(t_Data[Color],[@Color],t_Data[Q1 Units])

These formulas reference the exact same cells in the worksheet.  The structured reference formula contains the table and column names instead of the cell references.  This table style was introduced in Excel 2007, and carries through to Excel 2010 and 2013.  In my opinion the formula is much easier to read because you know exactly what you are summing.  In this case, we are summing all rows that contain the Color in the t_Data table that match the color in the same row [@Color] as the formula for [Q1 Units].

The goal is to drag/copy this formula to the right so we can see the results for Q2, Q3, and Q4.  And we don’t want to waste time retyping the formula.

In the cell reference formula we are able to anchor column E in the first argument by adding a $ sign in front of the E ($E$12:$E$23).  So when you copy or drag the formula across the columns, the reference will remain anchored to column E.  This is an absolute reference.  If we perform this same copy/drag operation with the structure reference formula, the column references will change for that same argument.  The “t_Data[Color]” reference will change to “t_Data[Region]” which is one column to the right.  This is considered a relative reference and not what we want for this formula.

The Solution

To create an absolute reference with structured references you need to add an additional and duplicate column reference.

t_Data[[Color]:[Color]]

Duplicating the column reference will anchor the reference when dragging across columns.  Here are the full formulas with relative and absolute references.

  • Relative: =SUMIF(t_Data[Color],[@Color],t_Data[Q1 Units])
  • Absolute: =SUMIF(t_Data[ [Color]:[Color] ],t_Summary[@ [Color]:[Color] ],t_Data[Q1 Units])

In the absolute formula you’ll notice that I anchored the first two arguments.  The [@Color] column needs be anchored as well.

To anchor a row reference you need to put the @ symbol before the duplicate column reference and wrap it all in brackets.  The table name is also required, even when the reference is in the same table as the formula.  So the row reference looks like:

t_Summary[@[Color]:[Color]]

This is an anchored reference to one cell in the same row as the formula.

Absolute Reference Add-in

The Absolute Reference Add-in helps make this process a lot faster by allowing you to use the F4 key on the keyboard to toggle between absolute/relative references in table formulas.

Absolute References Add-in F4 Key

It is very simple and easy to use, but packed with automated features so you can update your formulas in under 3 seconds!

Absolute Reference Add-in Userform Screenshot

 

Checkout the download page for more details.

Download

Absolute References in Structured Reference Tables.xlsx (14.5 KiB)

Posted In:  Tables Tips
55  comments
55 Comments
  • Thanks a lot Jon. Best reply found across all the forums and blogs. I was using INDIRECT for the absolute referencing, but was somehow not feeling comfortable. Your file explains everything. Simple and great stuff!

    Thanks,
    Sriram

  • Thanks Sriram! You’re right about the INDIRECT function. INDIRECT is a volatile function, which means that it has to recalculate every time a change is made anywhere in the model. This can make your model very SLOW.

    Most functions are non-volatile, and will only recalculate when there is a change in their dependent cells. For example, the formula = SUM(A1:A5) would only calculate if there was a change to cells A1:A5 (as long as those cells contained values and not formulas with dependencies).

    A volatile function like =INDIRECT(“A1″) will recalculate every time a change is made to any cell in the model. The calculation speed of your model can really slow down if you have hundreds or thousands of cells that contain a volatile function like INDIRECT. This is also true of the OFFSET function.

    I’m working on an additional solution that will make it faster to create the absolute references in structured formulas.

    Thanks again,
    Jon

    • Hi Jon. Great tutorial. Re your comment The calculation speed of your model can really slow down if you have hundreds or thousands of cells that contain a volatile function like INDIRECT. it’s worth pointing out that just having ONE volatile function in your spreadsheet can cause hundreds or thousands of cells to recalculate. That’s because any cells downstream of a Volatile function get calculated whenever that volatile function gets recalculated – even if that volatile cell didn’t change.

      • Thanks Jeff! That’s a great point! Have you written any posts on volatile functions, or recommend any good ones? I think that calculation dependency is important to know and probably often overlooked when designing a model.

      • Hi Jon.I mentioned some of this at http://chandoo.org/wp/2013/09/29/i-said-your-spreadsheet-is-really-fat-not-real-phat/ under the heading Handle sweaty Dynamite and Volatile Functions with extreme care…

        I’m going to turn that section into a post of its own at Chandoo.org because it is real important stuff, and is so often overlooked.

        Hey, your blog is awesome. I only just discovered it. I’m also going to do a blog at Chandoo on other blogs worth watching, and I’ll give this one honorable mention.

        • That’s a great article that you wrote. So many useful tips for making your workbooks more efficient. I’ll be looking forward to your volatile functions post, and it’s title. :)

          Well I’m glad you discovered the site, and thanks for the mention!

  • Thanks a lot for taking the effort to do this. i just built a model with cell referencing that becomes very difficult to explain or troubleshoot but your explanation on structured referencing has just solved my issues. thanks a bunch.

  • Hi Temitope,

    I’m glad this helped you. There is definitely a learning curve with structured references. They don’t behave exactly like cell references, and this issue of absolute references is a good example. But, I have found them to be much easier to work with when writing formulas in large models with multiple worksheets. I have found that you typically have to train the users of your model if they are not familiar with structured references.

    What other areas of structured references would you like to learn about?

    Thanks again,
    Jon

  • Sylvain Champagne July 12, 2013 at 2:26 pm

    Thanks a lot for sharing this!
    Structured references can be an extremly powerful tool when combined with external data queries and this was the last piece of a puzzle for me.

    I am curious to know if you ever contacted Microsoft about this and ,if yes, what their answer was.

    Thanks again.

    • Hi Sylvain,

      I’m glad this helped you and I agree about the power of structured references. I did not contact Microsoft about it, but I believe I originally found this tip on a Microsoft community forum. There were a lot of tips on there, and this solution was buried in the mix of comments. I decided to write a much more detailed article because I’ve benefited greatly from it and thought others would too. It has saved me a lot of time. I was previously doing a lot of find and replace to change the references.

      It would be great if they fixed it in future versions. I doubt any patch can be made for versions 2007, 2010, and 2013 where the problem still exists. Seeing that many people still use Excel 2003, I think this will be an outstanding issue for a long time in the future.

      Thanks,
      Jon

  • Thanks a lot Jon. I found the video really clear, understandable, practical and useful. The very thing I needed to explain a) why I was going wrong and b) what to do about it. In the past I have just resorted to using traditional absolute cell references which seemed a shame given how readable the structured table references make things … albeit, a tad longer than the cell references.

    Cheers, thanks for your help.

    Trevor

    • Thanks Trevor. I’m glad you found it useful. I agree that structured tables make formulas much more readable. I have started to use them more, and find that they also force some level of organization in your model because you are more likely to accurately describe your data when naming tables and column headers.

  • Wow, I have spent countless hours re-writing formulas as a result of not knowing this trick. I was so happy when the dynamic tables were introduced in 2010, but hated that the column references would not stay put. Thank you so much, this will be a HUGE time saver!

    • Thank you Renee! I’m really glad this helped you. I had the same issue and spent a lot of time doing find and replace on formulas. It was not an efficient method.

  • Awesome! I am going to start using structured tables and see how much faster my Excel files go once I get rid of some unnecessary formulas.

    Thank You!

    • Thanks Brad! I would be interested to know if your files calculate faster with tables. Please share your findings with us.

      One thing that is definitely faster with structured tables is writing formulas. Especially when you are referencing a table on a different sheet. You can simply type the table name in the formula followed by an open bracket character “[” and a list of all the column names will appear in the formula bar. This is much faster than having to navigate to another sheet and selecting the column or range you want to reference.

      Thanks again,
      Jon

  • Thank you very much Jon for your very usefull tip !

    Until now, I mixed tables and classics references. I will now use your method to have nice formulas ;)

    Miki

  • I’m very acquainted with using excel’s formulas. Tables seem to be a specific use for structured data, something like access. It’s very useful to use tables in excel, but it seems to be like learning another “excel”.

    • Hi Jose,

      I agree that tables can be like learning another “Excel”, or least a whole new chapter in the book. I believe the benefits are well worth taking the time to learn tables though. Tables are a huge time saver when it comes to common tasks like formatting, filtering, sorting, and analyzing your data. If you haven’t seen it already, checkout my new video on tables. Excel Tables Tutorial Video – Beginners Guide for Windows & Mac

      And let me know if you have any questions about tables.

      Thanks!
      Jon

      • Thank Jon by your answer. Really, I’m using excel as a database even using traditional formulas and filtering, but the table is a more useful way to use excel in that way (like a database) and I’m making efforts to learn. Thank you a lot for taking the effort to help people like me (Cheers from Brazil).

      • What an excellent tutorial you made! I recently held a training session on tables, and am sharing your link, as YOU are a far better instructor than I! :)

  • Very helpful post Jon. I have a follow-up question, along the line of best practices: I often find I want to use one or more sheets as data sources, and one sheet as a summary or rollup. That way I can download data in the structure native to the source application (often very verbose, with many columns I don’t care about) and use a summary table in a separate sheet to winnow it down to just the columns I care about.

    This works great so long as my summary sheet on deals only with one source table. If you suppose for a moment that on my summary sheet I wish to create a 2nd summarized table, below an existing first one, with the source data on an third sheet, the problem I’m having is that Excel seems to expect that my reference to tblSource2 is always row-aligned with the data in tblSource2nd. So I have to position the beginning of my 2nd summary table on the same row as the beginning of my source table. Which means putting it to one side of the first summary table.

    In other words, if I start my 2nd summary table on row 10 of the summary sheet with, say, tblSource2[1stColumnICareAbout], the resolution picks the 10th row of the source table. Is there any way you’ve discovered to likewise anchor the start of this referencing table with an absolute reference to the start of the *referenced* table?

    Don’t know if it makes a difference, but I’m using Mac Excel 2011.

    thanks – Doug

    • Hi Doug,

      Jon and I were *just* talking about this. It boils down to an array reference issue. I don’t want to steal Jon’s thunder here, but you need to reference the table dynamically. Take this reference for instance…

      =Table1[Column1]

      This will return a value that is in the same row as the reference. If no row can be matched you’ll get the #VALUE! error instead. It’s the same if you reference a cell range, like =A:A or something.

      The thought process for matching one row in a summary table, we’ll call it ‘tblSummary2′, against the same [table] row number of a data table, we’ll call it ‘tblData2′, would be to match the corresponding row number of the table. Since you can’t check the ROW() values, as the dimensions are different, this must be done dynamically.

      There are a few ways to do this, but basically what I do to get the table row number is a formula like this…

      =ROW()-ROW(Table1[[#Headers],[Column1]])

      This will have the effect of basically giving you an autonumber ID, like you would get in Access. If you then couple this with an INDEX() function, you can return a corresponding row of data from another table. Here is an example…

      =INDEX(tblData2[Column1],ROW()-ROW(tblSummary2[[#Headers],[Column1]]),1)

      Change the column numbers to match, but you get the point.

      Of course if you need these to be absolute [structured] references you’d need to implement what Jon detailed above.

      It shouldn’t make a difference if you’re using a Mac or PC with this.

      HTH
      Regards,
      Zack Barresse

  • great page! found it very useful

    i have the following question is there a simple way to do something like

    =SUM(Table1[*value of a cell*])

    where *value of a cell* is the velue i select from a dropdown list?

    thanks!

    • Hi Alfonso,

      Glad you found it useful.

      Yes, you can use the INDIRECT function for this. The formula would look like:

      =SUM(INDIRECT(“Table1[“&A1&”]”))

      This assumes that cell A1 contains your drop-down list of column header names.

      The INDIRECT function basically evaluates the value in cell A1 and returns the entire text string of the Table name and column name to the SUM function.

      Please let me know if you have any questions.

    • In addition to Jon’s formula (make sure you replace the quotes with Excel’s straight quotes), you could get a drop down of all your table headers by setting that to a named range. The easiest way to do it is to select all table header cells (place your cursor on the left-most edge of the left-most column until you get a right arrow, then click), press CTRL + F3 to open the Name Manager, click New (the table range will already be populated), enter the name for whatever you want (i.e. Heads). Then in your data validation cell use a list with the refers to as ‘=Heads’ (w/o the apostrophe’s).

    • Thanks Zack that’s a great suggestion! The ability to reference tables and their components is such a great feature. It’s much easier than creating dynamic formula references using the OFFSET or INDEX functions.

  • Great post, I didn’t know how to refer table columns as absolute instead of relative and this article solved my question.

  • Hi Jon. Great technique and love the addin. Does this also work on rows to create, for example, a running total – ie, something like SUM(table1[@[Amount]:[Amount]). I did try it but had no joy, so wondered if you knew whether this could be done.

    • Hi Ian,

      You’re talking about a running sum? You can do it by using the INDEX function, something like this…

      =SUM(INDEX([Amount],1):[@Amount])

      • Wow that’s awesome! Thanks Zack! Somebody should write a book on these Tables already… ;)

        • Ha! It’s at the copy editor as we speak! I’m really looking forward to it’s release. :)

          I think the name we finally settled on was “Excel Tables: A Complete Guide for Creating, Using, and Automating Tables”. I’m sooo ready for this book to be done. It’s not going to be huge, but I challenge anyone to pick it up and not learn at *least* one thing about tables. ;)

          • Challenge accepted :)

            I know I will loose, but I think I will be happy to!

            Thank you John and Zack! Keep writing, it’s realy pleasant to read you and learn from you.

            Miki

          • That’s awesome news Zack! I can’t wait! I know I will be learning a lot from it.

            @Miki – I love the challenge! I’m sure Sr. Table (aka Zack) will give you a run for your money… :-)

            And thanks for the comment. I am inspired!

      • Is it not simpler to do :
        =SUM(Table[[#header];[Amount]]:[@Amount])

        Since headers in tables can’t be number it’s not possible to get a wrong running total when summing header.

        • Very true! I’ve always stuck with the INDEX method because it’s easier than explaining the special identifier syntax with structured references. :)

        • assuming the running sum will be in column B, you can enter this formula in B2
          =SUM(B1, [@Amount])

          • Good point Jeff. Assuming the table header cell of the running sum is B1, that will work great. Also assuming the column to sum is in A, you could substitute the structured reference for A2. In fact, if you have very large data sets, this is the preferred method (i.e. 100,000+ rows in a table).

      • Hi Zack. As Jon says, truly awesome. Thank you, and thanks to Jon for a great post.

  • Thanks Jon.

    Great video and even better add-in.

    Cheers,
    Darren

  • Thanks a bunch, Jon! This article helped to fill in the gap and sort out one outstanding task, much appreciated!

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

    Search

    Learn Excel from Your Inbox


    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...


    MVP_Horizontal_BlueOnly

    Categories