Excel Tables – Absolute cell & column references

Tables - Absolute References

One of the first things we learn in Excel is the magic of the $ symbol.  It freezes the row or column, so when copying a formula, the cell reference does not change.  With the introduction of Tables came a different (and more semantic) way to reference cells, called structured references.  However, structured references don’t follow the same principles as the standard A1 style referencing system we usually use.  As a result, the $ symbol approach won’t work.  But don’t worry, by the end of this post, you will learn that it is possible to switch between relative and absolute references even when using a Table.

The examples in this post all use the following Table.  It shows the costs a Safari Park might incur for owning different types of animals (I went to a zoo with the kids recently, so it’s on my mind, but I’ve made up the data purely for these examples).  The Table name is myTable, whilst it’s not a great name, it will work for this example.

Tables Absolute Reference Example

Relative and absolute column references

When using structured references, whole columns are referenced with this syntax:

tableName[columnName]

Using the example data, to sum the Total column the formula would be:

=SUM(myTable[Total])

If this were dragged or copied to another column, the formula would change automatically.  If copied to the left, it would change to:

=SUM(myTable[Qty])

If copied to the right, it would revert to the first column in the table and would change to:

=SUM(myTable[Animal])

If using the standard A1 style referencing we could add the $ signs and change the range from G2:G9 (a relative reference) to $G$2:$G$9 (an absolute reference).  To achieve the same with a Table, it is necessary to add more square brackets, a colon ( : ) and repeat the column name.

=SUM(myTable[[Total]:[Total]])

The difference between an absolute and relative reference is shown in blue above.

If you have been using Tables for a while, you will notice this is the same syntax as when using the mouse to select and reference multiple columns.

=SUM(myTable[[Food]:[Other]])

The reference above shows how to sum the columns from Food to Other in the example data.  This means multi-column references selected using the mouse are absolute by default.  To create a relative multi-column reference you’ll need to remove the outer square brackets and repeat the table name, as shown below.

=SUM(myTable[Food]:myTable[Other])

Or you could include each column individually within the calculation as shown below, that will achieve the same result.

=SUM(myTable[Food],myTable[Keepers],myTable[Other])

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

Relative and absolute row references

The syntax to reference a cell in the same row in a Table is as follows.  Please note, the 2nd syntax in each formula is for where the header contains a space or special character.

=[@Total]    or    =[@[Total Value]]

Also, if the formula is contained in a cell outside of the Table the table name must also be added:

=myTable[@Total]    or    =myTable[@[Total Value]

To make a row reference absolute, the same principles apply as we saw for column references.  It does not matter if the reference is inside or outside the Table, the Table name is required in both circumstances.

=myTable[@[Total]:[Total]]

To reference multiple columns the syntax is similar.

=SUM(myTable[@[Food]:[Other]])

The reference above shows how to sum the columns from Food to Other from the example data. To create a relative multi-column reference it is necessary to remove the outer square brackets.

=SUM([@Food]:[@Other])    or    =SUM(myTable[@Food]:myTable[@Other])

Absolute references in Headers and Totals

Referencing the Header and Total rows within a Table is slightly different again.

The relative reference for Headers is as shown below:

=myTable[[#Headers],[Food]]

To turn the Header reference into an absolute reference add the section marked in below.

=myTable[[#Headers],[Food]:[Food]]

If you’ve read this article all the way through, then the multi-column absolute reference will not surprise you.  The formula to count the column headings is shown below.

=COUNTA(myTable[[#Headers],[Food]:[Other]])

And the multi-column relative reference will not surprise you either.

=COUNTA(myTable[[#Headers],[Food]]:myTable[[#Headers],[Other]])

Totals

The Total row syntax is identical to the Header row noted above, but with [#Totals] in place of [#Headers].

Conclusion

With a Table, the differences between the various cell references are quite subtle.  An @ or square bracket out of place can be incredibly frustrating to find.  Persistence is key to success, before long it will be second nature.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

5 thoughts on “Excel Tables – Absolute cell & column references

    • Excel Off The Grid says:

      Hi Sunny – I don’t believe there is a way to do that with structured references. That’s not what the tables feature in Excel was intended for.

    • wavecentral says:

      Yes @sunny.

      If you wanted to get the column # to use in a formula like VLOOKUP which requires an INDEX #, you could do something like this:

      COLUMNS(myTable[[#Headers],[Animal]:[Keepers]],FALSE)

      Would return 4. What is valuable in this way of referencing the index # is that if you add columns to a table, you don’t have to update absolute index numbers. Also, if you rename the column in your table, all formulas referencing that table column name are updated as well.

      Cheers,
      -.Mark

  1. Ludo says:

    Hi,
    I have a table (Table1) with 5 columns.
    What I want is to get the value of a single cell on one of the 5 columns.
    Following is working, but i was asking myself if there is an other way to do it.
    =INDEX(Table1[Arsenal];10)

    i was thinking on something like:
    =Table1[Column Name],[Row Number]

    Best regards

    • Excel Off The Grid says:

      Hi Ludo – to my knowledge, there is no method for referencing a cell in a table using structured references. You would need a formula, such as INDEX to achieve that.

Leave a Reply

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