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.
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:
Overview of tables by Debra Dalgleisha:
Improvements by tables (part 1 of 6):
Overview of tables by Chandoo:
I’m using a simple table for this example, one I’m writing another post for based on VBA.
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(
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).
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:
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.
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.
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
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.