Excel Article

Excel Tables (Part 3) - Structured References

When using Excel Tables you should notice that references in formulas look different to normal cell references. If they don't see the footnote at the end of the article. Oddly although an Excel Table reference is usual longer than a standard reference, they are easier to understand.

The Excel help is pretty good for getting a deeper understanding of the Table references, search for “Structured Reference”.

However as an overview consider a small Excel Table as below. In cell C4, we have entered a formula by typing “=” then clicking on cell A4, and then Enter

The resulting formula highlights a structured reference, rather than the conventional "=A4".

Let’s break this down into the key parts. For the reference we start with the table name, in this case “DemoTable”. This is followed by a set of square brackets which is the table specifier

Inside the table specifer in our example we have 2 parts. The first part is a special item specifier encased in its own set of square brackets, which in our example means “this row”.

The second part is the column specifier, again in its own set of square brackets.

The two specifiers are separated by a comma, which can be seen in the complete formula below.

It should be noted that in some cases only the special item specifier or the column specifier is needed. In the example below we have put a sum formula in cell A9 for the Widget data. In the formula only the column specifier is present.

In the next case we have put a sum formula in cell E4 - for illustration only as this is not a good place to stick a formula - which just totals the row data. In this instance only the special item specifier is needed.

The special item specifiers include

• [#All] - The entire table including column headers, data and totals (if any)
• [#Data] – Just the data
• [#Headers] – Just the header row
• [#Totals] – Just the total row
• [#This Row] – Just the portion of the columns in the current row

For Excel 2010 and upwards, instead of using the special “#This Row” qualifier you can use “@”. The following two formulas do exactly the same thing.

Excel 2007 version

Excel 2010 and upward version

Personally I am not sure if this is easier to understand, but it is more succinct. One more example, in cell C4 we have added a sum formula to Sum the widget and gadget cells in the same row.

Excel 2007 version

Excel 2010 and upward version

If you pass a workbook created in one version (eg Excel 2010) and open with the other version (eg Excel 2007) the formula gets automatically converted to the opposite format. Also in Excel 2010 you can type in the “#This Row” form and it will converted to the “@” form, but the converse is not true, in Excel 2007 will throw an error if you try to use the “@” form.

One footnote, there is an Excel option called “Use table names in formulas” which should be ticked to enable this special Table formula referencing.

Screen shots created in Excel 2007 and Excel 2010 where stated, all on Windows 7