Excel Tables and Structured References

What Ctrl+T actually gives you

Select any tidy block and press Ctrl+T (Insert → Table). Excel converts the range into a Table object with a name, and four things change at once. The range grows by itself when you add a row, so every formula, chart and PivotTable that points at the Table follows. Formulas can refer to columns by name instead of by letter. A new formula typed into one cell of a column fills the whole column. And filter buttons and a switchable Total row arrive for free — with the total living outside the data block, which is exactly where the first lesson said it belongs.

Structured references read like sentences

Rename the Table in Table Design (a short name with no spaces, e.g. Sales), and the formulas describe themselves:

=SUM(Sales[Revenue])
=SUMIFS(Sales[Revenue],Sales[Canton],"Zurich",Sales[Channel],"Shop")
=AVERAGEIFS(Sales[Revenue],Sales[Channel],"Online")
=COUNTIFS(Sales[Channel],"Online")
=[@Revenue]/[@Units]        ' this row's revenue divided by this row's units

The equivalent A1 versions do the same arithmetic but must be re-pointed every time the data grows. Which matters is a question of how often the file is updated: for a one-off analysis either is fine, for anything monthly the Table pays for itself on the second run.

The rules a Table imposes, and why they are a feature

• Header names must be unique and non-empty — Excel renames duplicates rather than letting you create an ambiguous reference.
• One header row only. Two-line headers and merged cells must be flattened first, which is what tidy data wanted anyway.
• No blank rows inside the block; the Table ends where the data ends.
• The Total row is part of the Table but excluded from Sales[Revenue], so it never contaminates a calculation.

Where Tables do not help

A Table cannot fix a wide layout — it will happily hold twelve month columns and give them names, which is a tidy-looking version of the same problem. It cannot convert text to numbers. It does not support merged cells at all. And a workbook full of large Tables recalculates more slowly than plain ranges, which becomes noticeable in the tens of thousands of rows. Tables are structure, not cleaning; you still have to do lessons two through five first.

Named ranges are not the same thing

A named range labels a fixed block of cells. A Table names a growing object and its columns. If your name has to be redefined every time a row is added, you built a named range where you wanted a Table.