Published on
A formula such as =SUM(C2:C25) depends on knowing what the cell range contains and whether later rows should be included. A structured reference instead names a table and a column, such as =SUM(Sales[Net]). Microsoft documents structured table references as available since Microsoft Excel 2007. Microsoft on structured references.
A table in Microsoft Excel is an object containing related data. Merely colouring cells does not create one. Use Format as Table, give the table a name and assign meaningful column headings. For this example, name the following table Sales:
| Customer | Net | Status |
|---|---|---|
| Alpha | 100 | Open |
| Beta | 200 | Paid |
A formula outside the table adds the net values:
=SUM(Sales[Net])The result is 300. Adding a table row with a net value of 50 changes the result to 350 without extending a fixed cell range manually. This behaviour follows the table's data extent, rather than merely assigning a name to an ordinary fixed range. Microsoft on tables.
Sales[Net] refers to the net column's data. Within a table row, [@Net] refers to that row's net value. A calculated column applying a ten percent uplift can use:
=[@Net]*1.1The original rows return 110 and 220. Microsoft Excel can propagate this formula through a calculated table column. Structured references also distinguish data, headers and totals; the exact specifiers depend on the language version. Microsoft on structured reference syntax.
| Version | Documented significance |
|---|---|
| Microsoft Excel 2007 | Structured table references become available and follow changes in table size. |
| Microsoft Excel 2010 and Microsoft Excel 2013 | Microsoft describes slow table editing when workbook formulas use structured references. |
| Microsoft Excel 2016 | Microsoft documents that this particular editing performance problem no longer occurs. |
| Microsoft Excel with dynamic arrays | Tables can supply expanding source data while formulas outside the table return variable-sized results. |
The improvement in Microsoft Excel 2016 is a documented performance change, rather than a new reference syntax. Microsoft's example concerns a particular editing operation and is not a general speed promise for every workbook. Microsoft on performance improvements.
In a version supporting FILTER, a formula outside the table can list open items:
=FILTER(Sales,Sales[Status]="Open","No open items")With the original data, the result contains Alpha's row. If another open item is added to the source table, it also becomes part of the result. The source table grows with its data; the spilled result grows with matching rows. Leave sufficient space outside the table for that result. Spilled array formulas are not supported inside tables themselves. Microsoft on dynamic arrays and tables.
When saving for Microsoft Excel 97–2003, structured references are converted to ordinary cell references. Check whether later rows remain included, not just whether an existing result is still visible. Microsoft on formula compatibility.
Other spreadsheet programs may offer table references, named ranges or database ranges, but those mechanisms are not automatically interchangeable. A useful compatibility test includes the initial data, an added row and a recalculation in the receiving program.
Continue with LET and LAMBDA in Microsoft Excel to make the calculations themselves easier to understand. The Grassroot IT framework explains how an operationally important workbook fits into application development and operation.
Technical review: 14 November 2026. The examples were checked in the installed Microsoft Excel version. Historical versions and other spreadsheet programs were not run for this review.
