Excel Table Reference Syntax: A Practical Guide

How-to basics · Updated November 18, 2026

Excel table references use a table name and, usually, a column name in square brackets. For example, with a table named Sales whose Amount values are 120, 80, and 50, =SUM(Sales[Amount]) returns 250. This structured-reference syntax adjusts as table rows are added or removed, making it useful for formulas that should follow changing data.

What an Excel table reference is

An Excel table is a formatted range with features such as header filters, automatic expansion, and an optional totals row. When you create a table, Excel gives it a name—often Table1—and treats its headers as column names. A structured reference uses those names instead of ordinary cell addresses such as B2:B100.

For instance, Sales[Amount] means the Amount data column in the Sales table. The reference is readable without checking which worksheet column holds the amounts. It also stays tied to that table column if the table moves or grows. Structured references work in formulas, including formulas outside the table and calculated-column formulas inside it.

To make a table in Excel for Microsoft 365 on Windows, select the data and choose Insert > Table. Confirm the selected range and whether it has headers. Click within the table to see the Table Design tab, where you can edit the table name. On Mac and Excel for the web, the contextual table tab or its label can differ slightly, but the structured-reference notation is substantially the same.

The basic syntax

The simplest pattern is TableName[ColumnName]. Replace the first part with the table’s name and the second with a column header. The brackets are part of the syntax; do not put a worksheet name or cell coordinates inside them. Examples of references, not complete formulas, include Sales[Amount], Orders[Quantity], and Inventory[On hand].

Column headers containing spaces are written as they appear in the table. For example, Orders[Unit Price] refers to a column headed Unit Price. Excel generally inserts the correct brackets when you build a formula by selecting table cells, so selecting the column is often safer than typing a complicated reference by hand.

Table names and column headers have different rules. A table name cannot contain spaces, while a column header can. Excel may adjust a table name to a permitted form when you create or rename it. Use a descriptive, unique table name such as Sales or Orders rather than leaving several tables with generic names; this makes formulas easier to interpret.

Reference parts and special specifiers

Structured references can identify more than a whole data column. A special item specifier, written with a # sign, selects a particular part of a table. The common specifiers are:

  • [#Headers] refers to the table’s header row.
  • [#Data] refers to the data rows, excluding the header and totals row.
  • [#Totals] refers to the totals row, if the table has one.
  • [#All] refers to the table’s headers, data, and totals row if present.
  • [@] or [#This Row] identifies the current row in a table formula.

A column can be combined with a specifier to narrow the reference. For example, Sales[[#Headers],[Amount]] selects the Amount header, while Sales[[#Data],[Amount]] selects the Amount values only. The extra brackets may look unusual, but they separate the table part from the column name. Excel will often construct this form automatically when you point to the relevant cells.

Inside a table formula, @ is a convenient way to mean this row. Thus [@Amount] means the Amount value on the row where the formula is being calculated. It does not mean the entire Amount column. A reference such as Sales[Amount], by contrast, means the column’s data and is commonly used by summary formulas.

Using table references in formulas

Structured references become especially useful when the formula describes a calculation in terms of table fields. Suppose an Orders table has Qty, Unit Price, and Line Total columns. At a row where Qty is 2 and Unit Price is 12, enter this in the Line Total calculated column: =[@Qty]*[@[Unit Price]]. Excel returns 24 for that row and typically fills the formula down the calculated column.

The double bracket around Unit Price in the row reference is intentional: the column heading contains a space. The reference identifies that field on the current row. When the formula is inside a table, Excel can also propagate an edited calculated-column formula to the other rows, keeping the calculation consistent.

For a summary, with a Sales table whose Amount data is 120, 80, and 50, =SUM(Sales[Amount]) returns 250. If a new sales row is added to the table, the table-column reference expands to include it, and the sum includes the new value. A conventional fixed range may not expand unless it is adjusted or otherwise set up to do so.

Referencing several columns and the totals row

To refer to a continuous set of table columns, use the first and last column names separated by a colon inside the brackets. For example, Sales[[Region]:[Amount]] covers the columns from Region through Amount in their current table order. It is a contiguous range: it does not mean only those two endpoint columns if other columns lie between them. Reordering columns can therefore change which columns fall inside that span.

To refer to an entire table area, use its name with the appropriate specifier, such as Sales[#All]. This is useful when a formula needs the table as a whole, though many summaries should instead name the specific column they use. A narrowly defined reference is easier to review and less likely to include unrelated fields.

You can add a totals row from the Table Design tab by selecting the Total Row option. Excel displays a total at the bottom of the table, and the total cell can use a built-in summary choice such as Sum or Average. A structured reference using #Totals refers to that row; it is not a substitute for #Data. If no totals row exists, there is no totals-row cell to reference.

How table names and references behave

Renaming a table updates structured references that point to it, which is one reason to use meaningful names. On Windows, click inside the table, open Table Design, and edit the Table Name box. Column references use the header text, so changing a header can also update linked formulas. This helps preserve connections when labels are revised, though it is still sensible to check important formulas after restructuring a workbook.

Structured references are designed to adapt as table data changes. Adding a row directly below a table commonly expands the table, and formulas that reference its columns then include the added row. Pasting data or inserting rows in unusual locations can behave differently depending on the action and surrounding worksheet contents. Check the table boundary after a large paste rather than assuming every adjacent cell became part of the table.

When you copy a formula, Excel’s behavior depends on the reference type and where the formula is placed. A whole-column reference such as Sales[Amount] continues to point to that table column. A current-row reference such as [@Amount] refers to the row where the formula is evaluated inside the table. That row-aware behavior is a major difference from copying a formula built from ordinary relative cell references.

Common syntax problems

Many errors come from confusing a table reference with a sheet or cell reference. A worksheet name is not a table name, and the table name should not be followed by a dollar sign or a cell coordinate. If Excel reports an invalid name or fails to interpret a reference, check the table’s actual name and the header spelling first.

  • Missing brackets: A column reference needs brackets around its header, as in Sales[Amount].
  • Wrong table name: The name may still be Table1, or may have been changed. Check it on the Table Design tab.
  • Header mismatch: Spaces and punctuation in a heading matter. Select the table column while entering the formula to have Excel insert its current name.
  • Using @ outside the table: The current-row shorthand is meant for a row context inside a table. Outside it, specify a column or an appropriate range instead.
  • Expecting #Data to include totals: It excludes both the headers and totals row. Use a different specifier if the calculation needs another table part.

Headers containing special characters or bracket-like punctuation can make a typed reference harder to read. Rather than guessing how to escape the characters, start the formula, select the relevant table cell or column, and let Excel insert the structured reference. If a formula stops working after a table edit, verify the actual header and table boundary before rewriting it.

Structured references compared with A1 references

An A1 reference such as B2:B20 identifies worksheet positions; a structured reference such as Sales[Amount] identifies a table field. A1 notation can be compact and is useful for ordinary ranges, charts, or calculations that depend on precise cell positions. Structured references are generally more self-explanatory and can expand with table data, which is helpful for growing lists and shared workbooks.

Neither form is universally better. A structured reference may be longer, especially when it includes several specifiers, and a formula tied to a specific table column depends on the table and header remaining meaningful. A1 references can be harder to audit when columns move or when the reader does not know the sheet layout. Choose the form that makes the formula’s intent clearest and matches how the data is maintained.

Frequently Asked Questions

What does an Excel table reference look like?
A basic column reference looks like TableName[ColumnName], for example Sales[Amount]. A current-row reference inside a table uses the @ form, such as [@Amount].

How do I find an Excel table’s name?
Click a cell in the table and check the Table Name box on the Table Design tab in Excel for Windows. The same contextual table tools are available in Mac and web versions, though the tab layout can vary.

Do structured references expand when I add rows?
They refer to table columns rather than a fixed set of row addresses, so they include rows that become part of the table. Confirm that pasted or inserted data is actually inside the table.

What does the @ symbol mean in a table formula?
It means the value from the named column in the current table row. It is a concise alternative to the longer #This Row specifier in the appropriate row context.

Can I use a table reference outside the table?
Yes. A formula elsewhere on the worksheet can refer to a table column, for example to summarize it. The @ current-row shorthand, however, is intended for a formula with a table-row context.

Why does Excel show extra brackets in a reference?
Nested brackets separate a table part such as #Headers from the selected column, or clarify a column name containing spaces. They are normal structured-reference syntax, not a sign that the formula is malformed.

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.