Absolute Structured References in Excel Tables | Excel Campus
134

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.

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

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 134 comments
Oken - January 25, 2017

Actaually, if you treat the Table formula as an array formula then you’d have absolute referencing. Instead of pressing the Enter key after typing press Ctrl+Shift+Enter and you would have curly braces around your Table formula life array formulas. The trick works when you drag across columns.

Reply
    Jon Acampora - February 4, 2017

    Great tip Oken. The issue is when you have mixed referencing. Some of the arguments are absolute and some are relative. In that case, I believe the only way is to duplicate the column reference.

    Thanks!

    Reply
Adrian - January 19, 2017

Thanks a lot.

I needed this for some tax mumbo jumbo and works like a charm.

Reply
Rod - December 15, 2016

Thanks bud, saved me a lot of time! Keep up the good work.

Reply
Des - December 14, 2016

Great tutorial. You,just saved me a lot of time scratching around for a solution. I’ll definitely come back to your site with my next challenge.

Reply
gijeet - December 8, 2016

Typo! In the section titled Problem with Absolute References in Tables you state “By default, all table references are absolute…” By default structured references are relative not absolute. Hence the issue of trying to make them absolute…

Reply
    Jon Acampora - December 10, 2016

    Thanks Gijeet! I fixed it to remove that part of the sentence. I feel like they are neither relative or absolute, or maybe both depending on the copy method that you use. So I just removed that portion of the sentence. Thanks again! 🙂

    Reply
David - December 6, 2016

I am working on a formula that counts the number on inputs from another table and column. However, I want to change the column reference based on a depentdent dropdown. for example. if the drop down is “Fabrication”, I want the formula to read =countif(Table[Fabrication],”x”) but if the drop down is “Engineering” I want it to read =countif(Table[Engineering],”x”). Is there a way to do that?

Reply
    Jon Acampora - December 10, 2016

    Hi David,
    Great question! You can use an INDEX/MATCH formula for this. Here is an example of how the formula would look.

    =COUNTIF(INDEX(tblPractice,,MATCH(“Region”,tblPractice[#Headers],0)),”South”)

    This formula looks at a table with a lot of columns, and returns the count of number of cells that contain “South” in the Region column. I will write an article on this when I get a chance.

    I also have a free video series on the lookup formulas that covers Index/Match in more detail.

    I hope that helps.

    Reply
Jim - November 29, 2016

Need major help with structured table, spent 2 hrs can’t find an answer online.

SEEKING: find the distinct data in a filtered structured table.

My formula:

{=IFERROR(INDEX(submitted[Q1], MATCH(0,COUNTIF($BK$1050:BK1051, submitted[Q1]), 0)),””)}

My formula works, but it ONLY extracts the distinct values from my filtered structured table.

IT extracts distinct data from the full table including hidden cells.
I want it to only extract distinct data from viewable filtered cells.

Thanks in advance!

Reply
    Jon Acampora - December 1, 2016

    Hi Jim,
    The AGGREGATE function allows you to ignore hidden rows and return results from visible cells only. I’m not exactly sure how you would apply it to this scenario though.

    Reply
scott - November 22, 2016

if I’m old school and want to remain that way, is there a way to turn off structured references and keep with cell references? i find the additional syntax annoying. what I used to be able to type as =sum(a1:b4), now looks like =[+(@potential value)]+[@realized value)]…and when I get into much more lengthy formulas the formula fills the entire formula bar. I just want to have the option of going without structured references. thanks.

Reply
    Jon Acampora - December 1, 2016

    Hey Scott,
    You’re not the only one. I think this is one of the main reasons more people don’t use Tables. Tables contain some awesome features, but the structured references scare most people away.

    The good news is that you can turn them off. I just wrote an article on how to turn off the structured reference table formulas. It’s an option in the Excel Options window. This is an application level setting and I explain that in more detail in that article.

    I hope that helps. Thanks!

    Reply
M Moerk - November 16, 2016

Hi Jon,
Very good article!

But I have a question, since I see a behaviour in Excel 2016, that is different from what you explain:
Even when making the absolute reference with double field name [[field5]:[field5]], these names are changed if I add a column to the left in the data table! F.ex. when I add [fieldA] as the leftmost column, the above reference is changed to [[field4]:[field4]].
Can this be avoided?

Reply
    Jon Acampora - November 21, 2016

    Hi M,
    I’m not experiencing that behavior. What method are you using to add the column? Are you adding a Table column or a worksheet column?

    Reply
3 Different Ways to Create a Drop Down List in Excel - Excel Efficiency - August 22, 2016

[…] For more references on using Tables in Excel formulas, try this helpful article/video from Chandoo, or this helpful explanation from Excel Campus. […]

Reply
Neale Blackwood - August 11, 2016

One way around the dragging issue is to use named ranges that refer to the columns/fields in the table – this also shortens the formulas because you don’t need to refer to the table.

Reply
    Jon Acampora - August 14, 2016

    Thanks for the suggestion Neale!

    Reply
      H - September 30, 2016

      Hi Jon

      Thank you – I have a coupld of Qs

      if i use this method to lock the column ref
      t_Data[ [Color]:[Color] ]

      Does this lock the reference to the color column even if i added columns..ie say i added 3 columns (would it still be locked on the coloe column)?

      My 2nd question is that method referring to a whole column like A:A ? If yes then is that not going to slow the spreadsheet down?

      If that is the case, would it be better to name each column in the table and then use that named range in the formulas? Im guessing the named range will expand when the rows increase right and it doesn’t reger to a whole column like A:A?

      Hopefully youncan advise more

      Thanks

      Reply
        Jon Acampora - October 4, 2016

        Hi H,
        Great questions. Here are my answers.
        1. The reference t_Data[ [Color]:[Color] ] will NOT change when you add new columns.
        2. The reference is referring to the whole column of the Table’s data body range only. So if the table is in rows 1:100, it is only referencing those rows, which would be all the rows in the table.
        3. The beauty of Tables is that all this referencing is done for you without having to create named ranges. Referencing the column name is essentially the same thing as creating a dynamic named range. Tables just do it all for you. The references automatically expand as you add new data (rows) to the table.

        I hope that helps.

        Reply
          H - October 4, 2016

          That explains alot for what im doing – i guess ill go back and in future always reference the table like you have suggested

          Thank you

          Reply
Caleb - June 24, 2016

Thanks Jon you da man

Reply
Grant - June 24, 2016

Do you know how this would work with an array formula. For Example

{=SUM(SUMIFS(SUM RANGE,CRITERIA RANGE, MULTIPLE CRITERIA { A,B,C })}

Does that make sense?

Thanks, Grant

Reply
    Jon Acampora - June 30, 2016

    Hi Grant,
    The absolute references for the criteria should work, although I have not tested it. Send me your file if you are having issues with it. Thanks!

    Reply
Ben - May 25, 2016

Great post, just what I was looking for and well explained.
Thanks!

Reply
absolute reference to a table column? - May 24, 2016

[…] document.write(''); probably not by accident. Absolute Structured References in Excel Tables | Excel Campus […]

Reply
Excel Table - February 9, 2016

[…] document.write(''); I think this might help. I conducted a search of the internet for you. 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. source: Absolute Structured References in Excel Tables | Excel Campus […]

Reply
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
=IF(VLOOKUP(B2;xList;2;0)=Types[@[Types]:[Types]];TRUE;FALSE)
Works
=IF(VLOOKUP(B2;xList;2;0)=Sheet3!$E$2;TRUE;FALSE)

See attached file.
https://www.dropbox.com/s/opto23dbwrf530n/Compare%20List%20A%20%26%20List%20B.xlsx?dl=0

Grateful for any help!
/martin

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

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

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

    =INDEX(Types[Types],1)

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

    Reply
      Carlos - May 31, 2016

      Hi Jon,

      So, why next doen’t work?

      =IF([[@Column3]=>[@Column2]];”OK”;”NO”)

      TX

      Carlos

      Reply
        Jon Acampora - June 2, 2016

        Hi Carlos,
        I believe you have an extra set of brackets around the column names. Try the following instead.

        =IF([@Column3]=>[@Column2];”OK”;”NO”)

        Reply
Excel Tutorial: 3 Tips on How to Create an Automated Budget & Expense Tracker - BRAD EDGAR - September 24, 2015

[…] expense entries and data that is being referred to is put into an Excel Table so that you can use structured references in the array formula that you will be […]

Reply
totaling costs over multiple criteria points in a table - September 9, 2015

[…] Structured references with Excel tables are confusing… See if this helps Absolute Structured References in Excel Tables | Excel Campus In your case, in Table4, maybe a formula like this copied across and down =SUMIFS(Table3[[Rental […]

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

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

    Reply
indzara - July 31, 2015

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

Reply
Sumifs need fields to stay static - July 2, 2015

[…] See if this helps Absolute Structured References in Excel Tables | Excel Campus […]

Reply
Bangipool - May 24, 2015

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

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

Thanks!!

Jeff

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

    https://www.excelcampus.com/filedownload/Reference%20Table%20Names%20with%20INDIRECT%20Function.xlsx

    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.

    Thanks!
    Jon

    Reply
Stan - July 7, 2014

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

Reply
AusSteelMan - June 25, 2014

Thanks Jon.

Great video and even better add-in.

Cheers,
Darren

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

Reply
    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…

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

    Reply
      Jon Acampora - April 30, 2014

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

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

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

          Miki

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

          Reply
      Matth78 - May 1, 2014

      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.

      Reply
        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. 🙂

        Reply
        Jeff - November 3, 2014

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

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

          Reply
      Ian - May 1, 2014

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

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

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

thanks!

Reply
    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:

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

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

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

    Reply
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

Reply
    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…

    =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

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

      exceltables.com/blog

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

      Reply
dataprose.org » Sur La [Excel] Table - January 3, 2014

[…] Absolute Formula References In Structured Tables Overview Of Excel Tables Using Structured References With Excel Tables Working With Tables In Excel 2013, 2010 And 2007 […]

Reply
Structured reference changes to A1 style reference when spreadsheet opened on different computer - November 13, 2013

[…] i think below video link might help youAbsolute Formula References in Structured Tables | Excel Campus […]

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

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

    Thanks!
    Jon

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

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

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

        Jon

        Reply
Absolute cell range when using Table nomenclature - September 22, 2013

[…] Absolute Formula References in Structured Tables | Excel Campus […]

Reply
W.T.Formula with fixed lookup. - September 13, 2013

[…] If you copy and paste rather than dragging the reference will remain absolute. Also you can use this syntax: Table1[[ProductID]:[ProductID]] as explained here: Absolute Formula References in Structured Tables | Excel Campus […]

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

Miki

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

Reply
    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,
    Jon

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

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

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

Trevor

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

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

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

    Thanks,
    Jon

    Reply
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,
Jon

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

Reply
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,
Jon

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

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

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

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

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

Thanks,
Sriram

Reply
Absolute cell reference when using tables - May 8, 2013

[…] I posted the following answer here XL2007 Table's "Structured References" & Absolute ref For the columns you want to anchor with an absolute reference you need to duplicate the reference as if it were a range of multiple columns. The following will be anchored to the Choice column. Table1[[Choice]:[Choice]] The notation is similar if you want anchor a cell in the same row of the table. Table1[@[Choice]:[Choice]] will create an absolute reference to the same row in the Choice column if the formula is in Table1. It's important to note that you must drag the formula across the columns, copy/paste won't work. I have a post and video on my blog that gives detailed explanation. Absolute Formula References in Structured Table | Excel Campus […]

Reply
XL2007 Table's "Structured References" & Absolute ref - May 8, 2013

[…] For the columns you want to anchor with an absolute reference you need to duplicate the reference as if it were a range of multiple columns. The following will be anchored to the Choice column. Table1[[Choice]:[Choice]] The notation is similar if you want anchor a cell in the same row of the table. Table1[@[Choice]:[Choice]] will create an absolute reference to the same row in the Choice column if the formula is in Table1. It's important to note that you must drag the formula across the columns, copy/paste won't work. I have a post and video on my blog that gives detailed explanation. Absolute Formula References in Structured Table | Excel Campus […]

Reply

Leave a Reply: