Stockertim Dictionary · Technology
Structured reference
Also called structured references, table reference, table column reference
In short
A way of referring to a formatted table by its table and column names rather than by cell addresses, so formulas grow automatically as rows are added.
- Intermediate
- Concept
- Checked
In detail
When a range is turned into a formatted table, the table gets a name such as Sales and each column is known by its header. Formulas can then refer to the parts by name: =SUM(Sales[Amount]) totals the whole Amount column, however many rows it has. Inside the table, Sales[@Amount], or just [@Amount], means the Amount on the current row, so a calculated column such as =[@Quantity]*[@Price] reads like a sentence.
The references follow the table as it changes. A new row typed or pasted directly below the table becomes part of it, every calculated column fills itself into that row, and every formula that refers to the column includes the new figure. Renaming a header updates every formula that uses it. Special items such as Sales[#Totals] and Sales[#Headers] reach the total row and the header row.
This removes several classic mistakes. Totals no longer stop short of newly added rows, copied formulas cannot drift because each row uses the same named pattern, and lookups written as =XLOOKUP(A2,Prices[Code],Prices[Price]) say plainly what they read. Pivot tables and charts built on a table also pick up new rows when refreshed.
There are limits. Table names cannot contain spaces, and structured references written in other workbooks break when the source file is closed or moved. Some older features, such as certain array formulas, do not work inside tables. Very wide tables with long names can make formulas lengthy, though still clearer than bare addresses.
Worked example
A bakery in Swansea keeps daily sales in a table named Sales and reports =SUM(Sales[Amount]).
- Monday's report covers 30 rows totalling 4,500
- Two new rows of 150 and 250 are added below the table: 150 + 250 = 400
- Updated total: 4,500 + 400 = 4,900
The total includes the new rows without anyone editing the formula.
In practice
Analysts store growing lists, such as transactions, timesheets and stock movements, as formatted tables and build reports on their names, so monthly updates only involve adding rows.
Watch out
- Rows typed with a blank row between them and the table are not added to it.
- Links to tables in other workbooks fail when the source file moves.
Learn it properly
Official sources
Facts last checked on 9 November 2026.
Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.