Absolute Structured References in Excel Table Formulas

Bottom line: This post will explain a trick for creating absolute structured references in Excel Table formulas.  Also known as locking or anchoring the column references.

Skill level: Intermediate

Absolute Reference in Structured Reference Formula

Intro

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

156 comments

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

  • Is there a way to keep excel from automatically changing structured references which reference table data to a regular range? It seems to do this on its own at random times.

    Example:
    I enter this
    =COUNTIF(table1[[type]:[type]],$A4

    Next time I open excel the formula is this
    =COUNTIF(‘1’!$G$11:$G$12,$A4)

    Once excel makes this change it sometimes does not include all rows in the table as the table expands… it’s driving me crazy!

    • Hi Zachary,
      I’m not sure what would cause the references to change from structured references to regular range references unless you are converting the Table to a regular range.

  • Thanks for sharing

    I find when working with tables it is often better to use range names.

    Define a name for each column in the table based on the table column reference. Then if you want a fixed reference use the range name, if you want a relative reference use the table name.

    Range names are shorter and easier to use in the rest of the file.

    Regards
    Neale

  • Thanks for the info. I have tried this for a single line, but it doesnt work. i have this “[@Category]&[@Number]”. if i put =Table1[[@Category]:[@Category]]&table1[[@Number]:[@Number]]

    where is my error?

    thank you,
    Sorin

    • Hi Sorin,
      You do not need the @ symbol in the second column reference. Try the following.

      =Table1[[@[Category]:[Category]]&table1[@[Number]:[Number]]

      Notice that I also moved the @ symbol outside the second bracket.

      You can also create this notation by selecting two cells in the same row, then change the reference of the second column to match the first.

      I hope that helps.

  • I have tried this for a single line, but it doesnt work. i have this “[@Category]&[@Number]”. if i put =Table1[[@Category]:[@Category]]&table1[[@Number]:[@Number]]

    where is my error?

    thank you,
    Sorin

  • Jon,

    I have looked all over the net and your example is by far the best I have seen; however, I too am having troubles.

    =IF(AND([@[Revised Credence Score]]>tblBayesOutput[@[Range One]:[Range One]],[@[Revised Credence Score]]<=tblBayesOutput[@[Range Two]:[Range Two]]),tblBayesOutput[@Output],"NEXT IF(AND() RANGE ARGUMENT")

    I have five other IF(AND range arguments to add. I want to figure out the absolute reference before I continue. As you can see, this is still relative. Can you help?

    Warmest regards, Perry

  • Jon,

    I have looked all over the net and your example is by far the best I have seen; however, I too am having troubles.

    “=IF(AND([@[Revised Credence Score]]>tblBayesOutput[@[Range One]:[Range One]],[@[Revised Credence Score]]<=tblBayesOutput[@[Range Two]:[Range Two]]),tblBayesOutput[@Output],"NEXT IF(AND() RANGE ARGUMENT")"

    I have five other IF(AND range arguments to add. I want to figure out the absolute reference before I continue. As you can see, this is still relative. Can you help?

    Warmest regards, Perry

  • Hi Jon,

    I have a very large data set with over 500K lines of data. From this structured table, I created a pivot in a separate file. I wanted to use the GetPivot function, but I cannot simply replace [ABC] with a referenced cell like I would normally do with GetPivot. How can I reference the cell using this structured reference table?

    =GETPIVOTDATA(“[Measures].[Sum of Sales]”,PVT!$B$5,”[SalesTable].[VLS]”,”[SalesTable].[VLS].&[ABC]”,”[SalesTable].[Month (Month)]”,”[SalesTable].[Month (Month)].&[Jan]”,”[SalesTable].[Month (Year)]”,”[SalesTable].[Month (Year)].&[2016]”,”[SalesTable].[gcn]”,”[SalesTable].[gcn].&[000780]”)

    Your help is greatly appreciated!
    Thanks,
    Rio

  • Great information and I refer back to it when working with formulas referencing tables.

    I am trying to write a COUNTIF formula that will include columns as they are added to the table but I am not having any luck with the formula expanding to include the new column added. I have tried =COUNTIF(Table2[[#All],[Column2]:[Column8]],”* C”) and =COUNTIF(Table2[[#All],”* C”) but when Column9 or 10 are added the new column is not included in the calculations. The table is set up with each team member’s name listed on a row in column1 (A) in their rotational order. As assignments are received the next member down in the rotation receives the assignment and it is entered on their row. This has thrown off how I am accustom to writing formulas for a Table. Your article Jon is the closest I can find on the internet on how to work with this problem.

    Thank you Jon or anyone that can provide help.

  • You’re ther greatest, I’ve been dealing with these problem for a long time.

    Thanks from Spain

  • Thank you Jon for creating this page to clearly explain how absolute references work with Excel Tables. The Add-in was very useful bonus to me. Why doesn’t Microsoft add this to their built-in F4 capability??

  • This is very clever, but there is much easier way which I saw guy from Microsoft doing.

    You select cell with formula and range to the right, that you want to fill in and click CTRL + R.

  • Thanks a Lot Sir,
    The Form of AbsoluteReferenceAddin is not wide enough to accommodate the Full Text and some part of the text remains invisible in both Contracted and expanded Form.

  • This is absolutely beautiful. Great explination. Had trouble when using the @symbol, but when removed the formula worked as it should!

  • Jon, I’m getting a #Value! error when trying to use your Blog example with CONCATENATE($A2,”: “,Table_owssvr_1[@[Project Name]:[Project Name]]), which I will want to copy down. The same relative reference would be CONCATENATE($A2,”: “,$B2). Any thoughts?

  • I found the easiest way to make an entire table absolute is to copy 1 column and add a $ after the column letter (e.g. D$23)
    Then drag the entire column across the range of columns required. Finally replace = with =$ in the new table.
    Now you have an absolute reference copy of the original.

  • THANK YOU!!!!! You are my GO TO hands down for all my excel issues (which are consistent and many, lol). You have a gift for explaining things.

  • Hello there..

    I did check on the add-in.. Badly needed.. Hopefully those morons at Microsoft will get making structured references right without the need to duplicate the column name one day..
    this add on however, it solved few things while causing several problems..
    I have the habit of indenting my Excel formulas for better readability. The add-in did not work for me in this case. It couldn’t correctly jump to the next brackets of column names and produced an error message.
    I could only use it for match functions that are not nested in other functions.
    Also, for some unknown reasons, the add-in sometimes added @ beginning of any function involved with in the formula..

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter