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.

 

Structured References in Excel Tables:

 

 

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

 

Use Structured References in Excel Tables, and use Functions such as Index.

 

 

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.