Excel Table Column Reference: Syntax and Examples

How-to basics · Updated November 18, 2026

An Excel table column reference is a structured reference that identifies a table column by its name instead of by worksheet coordinates. For example, with a table named Sales and a column named Amount, Sales[Amount] refers to that column’s data; inside a table row, [@Amount] refers to the Amount value in the current row.

What a table column reference means

Excel calls references to table parts structured references. Rather than writing a cell range such as D2:D100, you can refer to a table and its named column. The reference is tied to the table’s labels and structure, so it is easier to read and can adjust when the table changes.

For example, a formula using Sales[Amount] describes the Amount column in the Sales table. It does not mean column D on the worksheet. If the table moves to another location, the reference still identifies the same table column. Table and column names are not case-sensitive, but spelling and spaces must match the names in the workbook.

Structured references are useful in ordinary formulas, calculated columns, and formulas outside the table. They work in current Excel for Microsoft 365 on Windows, and are also supported in Excel for Mac and Excel for the web. Excel introduced tables and structured references in Excel 2007.

How to create and name the table

Structured references require an Excel table, not just a range of cells with headings. To create one, select the data and choose Insert > Table. Check that the selected range is correct, and select My table has headers if the first row contains field names. Each column needs a heading.

Excel assigns a default name, such as Table1. To give it a useful name, click inside the table and use the Table Design tab’s Table Name box. On Excel for Mac, the contextual tab is also named Table Design in current versions; its position can differ by window layout. Choose a concise name such as Sales or Inventory. Table names cannot contain spaces, so use an underscore or capital letters to separate words.

For the examples below, assume a table named Sales has the columns Item, Qty, Unit Price, and Amount. It contains a pen with quantity 4 and unit price 1.50, a notebook with quantity 2 and unit price 3.25, and a folder with quantity 5 and unit price 0.80. The amount values are 6.00, 6.50, and 4.00.

Basic structured-reference syntax

The general pattern for a whole table column is table name, followed by the column name in square brackets: TableName[ColumnName]. Brackets distinguish the column identifier from the table name. If a table is named Sales and the column is Amount, then Sales[Amount] selects the Amount data column.

With the Sales table described above, a formula entered in a worksheet cell outside the table can total the Amount column like this:

Assuming Sales contains the three Amount values 6.00, 6.50, and 4.00, enter =SUM(Sales[Amount]). The result is 16.50.

Excel often supplies structured-reference text as you type a formula. Start entering the table name or click a table cell while building the formula, then choose the suggested column. This helps avoid typing a heading incorrectly. If a heading includes spaces, keep them inside the brackets: for instance, a column named Unit Price is referenced as Sales[Unit Price].

Whole-column references and current-row references

A whole-column reference such as Sales[Qty] refers to the table’s data values in Qty. This is often the right choice for a function that summarizes the column. It is not the same as selecting the worksheet’s entire physical column, and it does not include the table’s header or total row by default.

Inside a table’s calculated column, use the at sign to refer to the current row. For example, in the Amount column, the formula below multiplies Qty by Unit Price for each row:

Assuming the active row in Sales has Qty equal to 4 and Unit Price equal to 1.50, enter =[@Qty]*[@[Unit Price]] in its Amount cell. That row’s result is 6.00. Excel normally fills the formula down the rest of the calculated column.

The syntax is deliberately different for the two references. Sales[Qty] identifies the full Qty data column, while [@Qty] identifies Qty on the row where the formula is being evaluated. When a column name has a space, the current-row form commonly uses the nested brackets shown in [@[Unit Price]].

A reference such as [@Amount] is most meaningful in a table row. If you put it in a cell outside the table, there may be no current table row to identify. For formulas outside a table, use a whole-column reference or a conventional cell reference appropriate to the task.

Headers, data, totals, and other table parts

Structured references can identify more than a data column. Special item names select different parts of a table. The most useful are:

  • #Headers refers to the 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 entire table, including its headers and totals row when present.
  • #This Row identifies the current row within a table; the shorter at-sign form is commonly used for a current-row column reference.

For a whole-column total, Sales[Amount] is usually the clearest form. A more explicit reference to the data portion is Sales[[#Data],[Amount]]. The longer form is helpful when you need to distinguish data from headings or totals, or when combining table parts in a more involved reference. If the table has a totals row, turn it on from Table Design > Total Row; Excel then provides a row for summary calculations.

Why use structured references instead of cell ranges?

Structured references are descriptive: Sales[Amount] tells a reader what is being summed, while D2:D4 does not explain what those numbers represent. They also adapt as the table grows. When you add a row directly below a table or type into the row immediately beneath it, Excel generally expands the table, and formulas using the table column include the new data.

Table formulas can also be easier to maintain. If you rename a table column, Excel updates references that point to it. If you insert or rearrange table columns, a reference continues to identify the named field rather than relying on a fixed column letter. This is particularly useful for lists that are regularly extended, such as sales logs, inventories, or expense records.

Cell references still have a place. They may be more suitable when a formula intentionally uses a fixed range, refers to a single cell, or must work with data that is not formatted as a table. Structured references are not automatically better for every calculation; the main benefit is clearer, table-aware references.

Common problems and how to fix them

  • The table name is not recognized: Click inside the table and check its name in Table Design > Table Name. A worksheet tab name is not the same thing as a table name.
  • The column name gives an error: Check the header for spaces or spelling differences. Use Excel’s formula autocomplete to insert the reference, especially if the heading contains punctuation or unusual characters.
  • The formula returns an unexpected total: Confirm that the formula uses the intended table and column. A structured reference to a data column excludes the header and totals row; a formula referring to another table with a similar name may still be syntactically valid.
  • The formula does not fill down: In a calculated column, Excel usually propagates a formula, but calculated-column behavior can be affected by workbook settings or by different formulas already present in the column. Check the other rows and copy the formula down if needed.
  • A new record is missing: Confirm that the new row became part of the table. If it was entered with a blank row separating it from the table, Excel may not have expanded the table to include it.

When a header contains characters that make a reference hard to type, selecting the column through formula autocomplete is safer than guessing the bracket and punctuation syntax. Structured references have specific escaping rules for special characters, so copying a generated reference from Excel reduces mistakes.

Frequently Asked Questions

What is the difference between Sales[Amount] and [@Amount]? Sales[Amount] refers to the Amount data column in the Sales table. [@Amount] refers to the Amount cell in the current row, typically from a formula inside that table.

Do table column references expand when I add rows? Usually, yes. When a new row is added to the table, references to its data columns include the added row. Make sure the row is actually part of the table; a blank row between the table and new data can prevent automatic expansion.

How do I reference a column with a space in its name? Put the exact heading inside the brackets. For example, with a table named Sales and a heading named Unit Price, use Sales[Unit Price]. In a current-row reference, use [@[Unit Price]].

Can I use a structured reference outside the table? Yes. A formula elsewhere on the sheet can use references such as Sales[Amount] in a function. The at-sign current-row form is intended for a row context inside the table.

Does a structured reference include the header and total row? A regular column reference such as Sales[Amount] refers to the data values, not the header or totals row. Use the appropriate item specifier, such as #Headers or #Totals, when you specifically need those parts.

Choosing the right reference

Use a whole-column structured reference when a formula should work over all the data in a table column, and use the at-sign form when a calculation is about one record at a time. Use explicit table-part specifiers when the difference between data, headers, and totals matters. For a stable, named dataset, these references make formulas easier to interpret and maintain; for isolated cells or fixed ranges, ordinary cell references may be simpler.

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.