Use Structured References in Excel Tables
Using structured references in Excel Tables provide many benefits. By using structured references in tables, you can optimize your Excel Functions. Excel Table structured references enhance data analysis and simplify Excel programming. The results are files that are easier to use, and easier to program. We suggest you work with Excel Tables as much as possible.
What are Structured References in Excel?
“Structured References” in Excel Tables are a way to refer to Excel Tables, using names and formula-syntax. Excel’s Structured References make it easier to reference Excel Tables. Using Structured References make understanding your Excel functions easier.
Excel’s Structured References Simplify Understanding the Functions:
Excel’s structured references make Excel functions easier to make sense of. By using names instead of cell references makes it easier to understand the formula.
Microsoft Excel Structured References Dynamic Updates:
When you use structured references in Excel functions, they dynamically adjust to include rows or columns added to the Excel Table. This eliminates the need to manually update cell references and it guarantees that your Excel functions work correctly as the table’s structure changes.
Excel Tables are Dynamic:
Excel Tables expand automatically as data is added to them. Excel’s structured references take advantage of this dynamically, instantly adapting to the resizes table.
Enhanced Analysis & Reporting:
Structured references enable you to perform powerful data analysis & reporting with ease. These refere3nces allow you to reference entire columns, not just cells, they allow you to perform calculations on specific rows or columns. You can also access table-specific elements such as tables headers.
By using structured references in your Excel tables, you improve the efficiency of your Excel files, via your Excel formulas.
Brief History on Excel Tables
Tables were introduced in Excel 2007 and are a spin-off from the lists feature of previous versions. This blog post will be based on working with structured formulas in tables, as opposed to standard relative/absolute referencing. I consider this a basic level post.
Refreshed Post 7/2023.
Resourses on Structured References in Excel Tables:
If you’re not up to speed with tables, here is some reference material before we get to the more advanced information:
Overview of tables:
http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx
Overview of tables by Debra Dalgleisha:
http://contextures.com/xlExcelTable01.html
Improvements by tables (part 1 of 6):
http://msdn.microsoft.com/en-us/library/office/bb693324(v=office.11).aspx
Overview of tables by Chandoo:
http://chandoo.org/wp/2009/09/10/data-tables/
I’m using a simple table for this example, one I’m writing another post for based on VBA.
Learn About using Structured References in Excel Tables
The first column is blank, ready for data entry. The following four columns have formulas in them, and all have structured references except one, which is COL4.
Pressing F2 on the formula in cell C15, the last data cell of COL3 in the table shows a formula of =SUM(INDEX([COL2],1):[@COL2]).
This is a trick for doing a running total via structured cell reference formulas (http://excelandaccess.wordpress.com/2012/07/12/running-total-in-an-excel-table/), and is the example I’m using to show how standard cell referencing won’t work in tables.
The formula in E15, the last data cell of COL4 in the table shows a formula of =SUM($C$6:C15).
Running Totals in Excel Tables
This is a very standard way of performing a running total when not using tables, and has been used successfully for years. The formula in E6, the top-most formula for this column is:
=SUM($C$6:C6)
The trick is the absolute versus relative referencing. When copied down, the first reference won’t move, but the second reference, being relative, will move according to where the cell was copied to, resulting in a running total formula in an Excel Table.
This is great – except when using this in a table. In the following picture I’ve inserted rows at the end of the table. Put the cursor in the bottom-right cell and press Tab will insert a new row. You can also right-click any row, select Insert, then choose to insert rows above or below. However, in doing so, the standard formula references don’t update correctly.
As you can see there are now 6 values resulting with 120. Looking at the adjacent column (directly to its left) the values do not match up. If we look at the table in formula view, we can see what’s happening to those formulas.
Standard Cell Referencing
The way standard cell referencing is handled in a table is so that when a new row is inserted, the formulas update to the new row, then the row is inserted. This means that at the point you started inserting rows (versus copying the formula down) the referencing will not be correct.
There are various workaround methods you can do to correct this, up to and including some elaborate VBA solutions and change events. This really isn’t an easy way to accomplish what you’re trying to do, which is be able to copy a formula down and have it ALWAYS update CORRECTLY. This is where structured table references work with tables (imagine that). Any time you can use structured table references you’ll be better off.
As with everything else in Excel, there are pro’s and con’s with this methodology. Here are my top bugaboo’s:
- There is no way to make a structured table reference absolute
- You can’t use formulas in any table header cells
Conclusion – Use Structured References in Excel Tables
Ok, so it’s a short list, but it’s a pain. Of course, the benefits much outweigh these and I’m an avid user of structured tables/formulas.
[…] http://excelandaccess.wordpress.com/2013/01/07/why-you-should-use-structured-references-in-tables/ […]
Hi…I have always wondered where are the formulas of a table (ListObject) stored, since you can effectively get rid of all data (empty table, DataBodyRange being nothing) and somehow the formulas are still “remembered” (i.e. as soon as a first row is inserted, the formulas are back!).
The bottom line issue for me is: How can you design a table (with formulas in some columns) that will start as en empty table?
Roberto,
Here is the blog post about how Excel tables remember column formulas:
http://excelandaccess.wordpress.com/2013/07/27/how-do-excel-tables-remember-formulas/
Sorry it took me so long to write it!
Regards,
ExcelAndAccess
Awesome !!. Thank you !
-R
Hello Roberto, as a matter of fact, that’s my next blog post – where table formulas are “remembered”! It’s really interesting. Look for it (hopefully) this week.
To pointedly answer your question, it’s actually always best to design your table *with* data in it. I generally start with only a few rows, 5-10 maybe.
Here’s an interesting fun fact – start a table with just one row, enter formulas (using structured table referencing – and get your references by using the mouse or keyboard, just not typing in the table references manually). You’ll notice there is no “@” sign in the table references. That’s because it thinks one row is all it really is, so if you add more than one row, every calculated column will reference the entire table, not just that row! Weird, but it’s one reason I always have more than one row of data when building tables. 10 is a manageable number.
Regards,
Microsoft MVP
[…] table references should be used, which shows how Excel doesn’t copy/paste table formulas: http://excelandaccess.wordpress.com/2013/01/07/why-you-should-use-structured-references-in-tables/) The Excel team at Microsoft were much smarter than that. While this has been available since […]
Interesting article. Thanks.
With regard to your first bugaboo, “There is no way to make a structured table reference absolute”, I’ve just been reading another blog on exactly that subject. Enjoy!
http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/
Thank you so very much for the link Trevor, that was awesome! While I don’t think it’s built that way it’s a BEAUTIFUL workaround!
ExcelAndAccess
Sometimes you just gotta go with what works 🙂
This evening, I know a lot more about working with tables than I did this morning.
Cheers, Trevor