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.

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

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.

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

Checkout the download page for more details.

### Download

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

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.

thank Guru, great post!!!

Excellent thank you

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??

Thanks – Learnt how to anchor table reference. Cheers !!

Thank you. this is very helpful.

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

Thanks from Spain

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.

I have the same issue and would love to know the answer!

Valuable info. Lucky me I found your site by accident, and I’m shocked why

this accident didn’t took place in advance! I bookmarked it.

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 article. Thanks.

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

Thank you very much

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

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.

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, great suggestion Neale! 🙂

Great suggestion Neale

You have saved the day!

well done!!!!

Thank you so much for this. This has been a constant frustration for quite some time.

Great post! Thank you! Any chance of updating the Add-In for Exceö 2016? Thank you!

Hi Nanna,

The add-in works for Excel 2016 for Windows. If the ribbon button is disappearing, checkout this article on how to stop the add-in ribbon from disappearing.

https://www.excelcampus.com/vba/add-in-ribbon-disappears/

I get Run-time error ’13’: Type mismatch in Excel 2016. Any ideas?

Thanks for this article – exactly what I was looking for!

you are a gentleman and a scholar sir

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.

Thank you very much. Just what I was looking for. And so lucidly explained.

What about Table1 $[Jan] much better, it is not?

Unfortunately that notation does not work with structured references. It would be great if it did.

Great, thanks !

Thanks George! 🙂

Thank you very much

thank you 🙂

Finally! Found a solution! Thank you!!!

Great work Jon