Excel Table Structured References: Rules, Examples & Tips

How-to basics · Updated November 21, 2026

An Excel table structured reference uses the table name and a bracketed column or row specifier. Assuming a table named Sales has a column named Amount, the formula =SUM(Sales[Amount]) adds that column; inside a Sales table row, =[@Amount] refers to Amount in the current row. These references adjust as table data changes and are usually easier to read than ordinary cell addresses.

What a structured reference looks like

A structured reference is a formula reference to part of an Excel table. Its basic shape is TableName[ColumnName]. The table name identifies which table to use; brackets identify a column, row, or special part of the table. Unlike a range such as B2:B100, the reference describes the data by its table and field names.

For example, assume the Sales table has columns Region, Rep, and Amount, with sales records in its data rows. The reference Sales[Amount] means the Amount column’s data, and Sales[Region] means the Region column’s data. Table names and column headings are not case-sensitive in formulas, but spelling and spaces must match the actual table and heading.

Structured references work in formulas both inside and outside the table. Excel generally updates them when table rows are added, when a table or column is renamed, or when a formula is copied down a calculated column. That adaptability is the main reason to use them for growing lists and datasets.

Quick syntax cheatsheet

Assume the Sales table contains the columns Amount, Quantity, Unit Price, Q1, Q2, Q3, and Q4. The following patterns cover the references used most often:

  • Sales[Amount] — the Amount data column.
  • Sales[@Amount] — Amount in the current row, when used in a table row formula.
  • Sales[[#Headers],[Amount]] — the Amount heading.
  • Sales[[#Data],[Amount]] — the Amount data body, excluding headings and totals.
  • Sales[[#Totals],[Amount]] — the Amount cell in the totals row, if that row is enabled.
  • Sales[[#All],[Amount]] — the Amount column including its header, data, and totals cell if present.
  • Sales[[Q1]:[Q4]] — the contiguous columns from Q1 through Q4.
  • Sales[[#Data],[Q1]:[Q4]] — the Q1-to-Q4 columns in the data body only.

The special item specifiers are written with a hash sign: #Headers, #Data, #Totals, #All, and #This Row. #This Row refers to the record on the same row as the formula. The shorter at-sign form, such as [@Amount], is the common way to refer to one field in the current row from a calculated-column formula.

How to create and name a table

Structured references require an Excel table, not merely a formatted range. On Excel for Microsoft 365 for Windows, select a cell in the data and choose Insert > Table, or press Ctrl+T. Confirm whether the selected range has headers, then choose OK. On Mac or Excel for the web, use Insert > Table if the Windows shortcut differs or is unavailable.

To give the table a readable name, select a cell in it and use the Table Design tab’s Table Name box on Windows. Replace a default name such as Table1 with a descriptive name such as Sales. Table names cannot contain spaces, must be unique in the workbook, and cannot look like a cell address. Use a short name that makes the formula’s source obvious.

Excel uses the header row as the column names. If a header is blank or duplicated, Excel may supply or adjust a heading so each table column has a usable name. Rename headings deliberately before building many formulas; changing a heading later is usually handled by Excel in references, but it can make existing formulas harder for people to recognize.

Examples for common calculations

Assume Sales has numeric values in Amount. To total the full data column from a cell outside the table, use =SUM(Sales[Amount]). The result is the sum of the Amount records; when rows are added to the table, the reference includes the new records. The formula is also easier to interpret later than a range whose endpoint may no longer match the data.

Assume the Sales table has Quantity and Unit Price columns. Enter this in a new calculated column within Sales to compute a line value for each row: =[@Quantity]*[@[Unit Price]]. Excel fills the formula down the table column. The double set of brackets around Unit Price is needed in this form because the heading contains a space; the single-word Quantity reference uses the shorter form.

Assume Sales contains Q1 through Q4 numeric columns. To add all four quarters across all data rows, use =SUM(Sales[[#Data],[Q1]:[Q4]]). This totals the selected block. The colon between column specifiers indicates a continuous span of columns in the table; make sure the first and last headings are in the intended order.

Assume Sales has an enabled totals row and Amount is the field being summarized. The reference Sales[[#Totals],[Amount]] points to that column’s totals-row cell. Excel can also create a totals row from Table Design > Total Row. Choosing a calculation in that row can use a SUBTOTAL-based formula, which is useful when filtering because SUBTOTAL can ignore filtered-out records.

Current-row references and calculated columns

In a table formula, a current-row reference keeps the formula tied to the record on that row. For example, [@Region] means the Region value in the same row, while Sales[Region] means the column as a whole. This distinction matters: using a whole column when you intend a single row can produce an unexpected array or calculation.

For a heading with spaces, use the nested bracket form: [@[Unit Price]]. A formula can combine several current-row fields, as in the Quantity-times-Unit-Price example above. When you enter a formula in a table column, Excel commonly offers to fill it through the rest of the calculated column. If it does not, check that the formula was entered inside the table and that calculated-column formula filling has not been disabled.

Do not confuse the structured-reference at sign with an ordinary cell reference. In [@Amount], the at sign identifies the current table row. Excel also uses @ for implicit intersection in some formulas, so its meaning depends on the expression around it. A reference like Sales[@Amount] is most useful where Excel has a current-row context; it is not a general replacement for Sales[Amount] in every cell.

Choosing the right table part

Use a whole-column reference when a calculation should include every table record, such as a grand total or a count. Use a current-row reference in a calculated column when the calculation belongs to each individual record. Use #Data when a formula must explicitly exclude the header and totals row, and use #Headers when the heading itself is the value you need.

  • For a complete column calculation, use Sales[Amount].
  • For a calculation on one record, use [@Amount] inside that record’s table formula.
  • For a block of adjacent fields, use a column span such as Sales[[Q1]:[Q4]].
  • For the displayed total-row cell, use the #Totals specifier and ensure the table has a totals row.

A structured reference is not automatically the best choice for every task. If you need an exact, fixed set of cells that must not expand, a traditional cell range may communicate that intention better. If a formula is shared with software that does not understand Excel tables, ordinary ranges may also be more portable.

Why references change as a table grows

When you type directly below a table or add a table row, Excel typically expands the table and adjusts its structured references. A formula such as =SUM(Sales[Amount]) therefore tracks the table’s Amount records without requiring you to edit an ending row number. Sorting and filtering change the displayed order or visible records, not the identity of the named column.

Structured references do not guarantee that every formula has the intended meaning. A total made with SUM includes values in filtered-out rows; if the intended result is a filter-aware subtotal, choose a suitable SUBTOTAL calculation instead. Also, if data is pasted outside the table without expanding it, those cells are not part of the table and will not be included just because they sit nearby. Check the table boundary when a result seems too small.

Common problems and fixes

  • Excel does not recognize the name. Confirm the table name and heading in Table Design, and check brackets and punctuation. A table name is different from a worksheet tab name.
  • A formula returns an error after a column was deleted. A deleted field can leave a broken reference. Edit the formula to point to an existing column or restore the intended field.
  • The formula omits new records. Verify that the new records are inside the table. Use a table row insertion or extend the table rather than placing data just below it.
  • Structured references seem awkward to type. Start the formula with an equals sign and select a table column or cell; Excel can insert the reference syntax. Formula AutoComplete may also offer table names and headings.
  • A heading contains spaces or punctuation. Keep the actual heading inside the bracket syntax exactly as Excel recognizes it. Selecting the column while editing a formula is often safer than trying to construct complex brackets by hand.

Renaming a table or its column through the table interface is preferable to creating a second similarly named table. Excel generally updates references that point to renamed table fields. Still, after structural edits, inspect key formulas and results, especially if a workbook has external links, copied formulas, or many similarly named tables.

Frequently Asked Questions

Do structured references update when I add rows?
Usually, yes, if the new records become part of the table. Formulas that refer to a table column then include its expanded data. Data pasted below the visible table may remain outside it, so verify the table boundary.

What is the difference between Sales[Amount] and [@Amount]?
Sales[Amount] refers to the Amount data column. [@Amount] refers to Amount in the current row and is typically used inside a table’s calculated-column formula.

Can I use structured references outside the table?
Yes. A formula elsewhere in the workbook can refer to a table column, for example by using =SUM(Sales[Amount]), provided the Sales table and Amount column exist. The current-row shorthand is intended for a row context and behaves differently.

Why does Excel put extra brackets around a column name?
Brackets are part of the structured-reference syntax. A heading with a space, such as Unit Price, commonly appears in a nested form such as [@[Unit Price]]. Excel may add brackets automatically when you select a field in a formula.

Do table references work in Excel for Mac and the web?
Excel tables and structured references are supported in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon labels and shortcuts can differ by platform, but the reference syntax is substantially the same.

Are structured references the same as named ranges?
No. A structured reference points to a table and one or more of its parts, such as a column or current row. A named range is a separately defined name for a cell or range and does not automatically have table columns’ expand-with-the-data behavior.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.