Absolute Structured References in Excel Table Formulas

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.


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.


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:


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.


Absolute References in Structured Reference Tables.xlsx (14.8 KB)

Please share this post:
Please share
Jon Acampora

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 73 comments
Martin - December 1, 2015

It does not seem to work if the table is on another sheet. Or is there something else that I am doing wrong?

I use it in a VLOOKUP function. If I refer to the worksheet and cell with an absolute reference it works.

Not working

See attached file.

Grateful for any help!

    Lawrence - April 8, 2016

    Too late for Martin but for other readers, it works fine with other sheets.

    Martin’s problem seems to be misunderstanding usage of the “@” as it will still point to a corresponding row, like $E2 not $E$2.

    My own problem in getting this to work was not noticing the extra square brackets [] around the columns. e.g. t_data[color]:[color] will still work like a relative reference when dragged.

    Helpful article 🙂

      Jon Acampora - April 8, 2016

      Hi Lawrence,
      Thanks for the comment. I totally missed Martin’s comment and just replied to him. He could also use INDEX(Types[Types],1) to create an absolute reference to row 1 of the column. It might be easier to just reference the cell address though.

      And I know what you mean, those extra brackets get me sometimes too. Hopefully MS will make these structured references a little easier to type in the future with some advanced auto complete features.

      Thanks! 🙂

    Jon Acampora - April 8, 2016

    Hi Martin,
    Sorry to not get back to you. I must have missed your comment. This technique only works for referencing columns. If you want to reference the first row in the table and make it an absolute reference, then the cell reference you have will probably work best.

    You could also use an INDEX formula to get that cell reference for the Table, but it might be overkill.


    The @ symbol references the same row that the formula is in. It does not anchor the row and unfortunately there is no way to do that with structured referencing. I hope that helps. Let me know if you still have questions. I will try to respond quicker next time. =)

Doug Glancy - August 30, 2015

Jon, I just found this post when somebody showed a bit of this technique in a StackOverflow comment. I had been trying to figure this out five years ago but never had any luck.

Anyways, I answered the SO question (http://stackoverflow.com/q/32107549/293078) and linked to this post, which is most excellent!

    Jon Acampora - August 30, 2015

    Thanks for sharing Doug! I found this solution buried in a forum post as well, and thought it had to be shared and explained. It’s one of those tiny nuances of Tables that make them a little different than regular cell references.

indzara - July 31, 2015

Thanks for this article. It came in handy for a template I am building. Thank you very much.

Bangipool - May 24, 2015

Thanks Jon,
it’s great excel tips, i very useful to work with any tables in excel.

Jeff - March 6, 2015

Hi Jon, Great article thanks! I had a question around whether you could reference the table name in a formula as a cell? So, for example, we have two “tables” with two separate sources of data, but in the exact same format with exact same column header titles. What we’d like to do is only use one formula (instead of an “if” statement), and then just change the cell value to determine which “table” to pull from. Any thoughts?



    Jon Acampora - March 9, 2015

    Hi Jeff,
    Great question! You can use the INDIRECT function for that. I created a quick example file that you can download at the following link.


    In the example file the Table Name is in cell C2 = “Table1″
    The formula in cell C3 references the Table Name in cell C2 and a column from the Table. =INDIRECT(C2&”[Column3]”)

    The INDIRECT function allows you to specify a string to references a cell/range in a formula. You can build the string by referencing other cells that make up the address. This can also include the workbook and worksheet names. With Tables you don’t need the worksheet name though. So the string inside the INDIRECT function can basically be a concatenation of other cell values.

    In the example file I wrapped the formula in a SUM function to return the sum of Column3 for the table selected in cell C2. Cell C2 contains a validation list (drop-down) where the user can select either of the two tables (Table1 or Table2).

    Take a look and let me know if you have any questions.


Stan - July 7, 2014

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

AusSteelMan - June 25, 2014

Thanks Jon.

Great video and even better add-in.


Ian - April 29, 2014

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.

    Zack Barresse - April 30, 2014

    Hi Ian,

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


      Jon Acampora - April 30, 2014

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

        Zack Barresse - May 1, 2014

        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. 😉

          Miki - May 1, 2014

          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.


          Jon Acampora - May 1, 2014

          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!

      Matth78 - May 1, 2014

      Is it not simpler to do :

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

        Zack Barresse - May 1, 2014

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

        Jeff - November 3, 2014

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

          Zack Barresse - November 20, 2014

          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).

      Ian - May 1, 2014

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

Marc - March 7, 2014

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

Alfonso - February 26, 2014

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?


    Jon Acampora - February 26, 2014

    Hi Alfonso,

    Glad you found it useful.

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


    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.

    Zack Barresse - February 26, 2014

    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).

    Jon Acampora - March 1, 2014

    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.

Doug Scott - January 12, 2014

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

    Zack Barresse - January 16, 2014

    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…


    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…


    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…


    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.

    Zack Barresse

      Jon Acampora - January 16, 2014

      Awesome! Thanks Zack! I couldn’t have said it better. 🙂 You can checkout some great articles written by Zack over at the Excel and Access blog. He has a lot of great content on Tables.


      Kevin Roth - February 3, 2016

      Another way, if you can place it outside of the other table, is to enter the reference inside an array formula. So in a non-table column alongside of tblSummary2, with all the rows selected, enter “=tblData2[Column1]” and then press Ctrl-Enter. This appears to get the relative row references correct, however it comes with all the usual restrictions inherent in array formulas.

Jose Lobo - October 4, 2013

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”.

    Jon Acampora - October 4, 2013

    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.


      Jose Lobo - October 6, 2013

      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).

      Renee Keel - October 7, 2013

      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! 🙂

        Jon Acampora - October 7, 2013

        Thank you for the kind words Renee! I hope your students find it useful and please let me know if you have any questions.


Miki - September 9, 2013

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 😉


Brad S. - August 14, 2013

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!

    Jon Acampora - August 15, 2013

    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,

Renee - August 13, 2013

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!

    Jon Acampora - August 13, 2013

    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.

Trevor - August 6, 2013

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.


    Jon Acampora - August 6, 2013

    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.

Sylvain Champagne - July 12, 2013

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.

    Jon Acampora - July 12, 2013

    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.


Jon Acampora - July 5, 2013

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,

Temitope - July 5, 2013

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.

Jon Acampora - June 19, 2013

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,

    Jeff Weir - January 24, 2014

    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.

      Jon Acampora - January 24, 2014

      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.

      Jeff Weir - January 24, 2014

      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.

        Jon Acampora - January 25, 2014

        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!

sriram vangara - June 19, 2013

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!



Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.