Excel Data Table Reference Format: A Practical Guide

How-to basics · Updated November 19, 2026

In Excel, a table reference uses the table name and, when needed, a column or item in square brackets: Orders[Amount] refers to the Amount column in the table named Orders, while [@Amount] refers to the Amount value in the current table row. These structured references are easier to read than cell ranges and generally adjust as table data changes.

What an Excel table reference means

An Excel table is a formatted data range created with the Table feature, not simply a range that has colored rows. Excel gives it a name and treats its headers, data rows, and optional Total Row as named parts. A structured reference identifies those parts by name rather than by cell address.

For example, Orders[Amount] means the Amount column of the Orders table. It is not a reference to a worksheet column named Amount, and it does not mean the cell at a particular row. Excel interprets the reference using the table’s name and its column heading.

This format is useful in formulas because it explains what the formula uses. A formula that adds amounts can show Orders[Amount] instead of a range such as D2:D100. If you add rows to the table, the table expands and formulas that refer to its column can include the new data automatically.

Create and name a table first

To create a table in Excel for Microsoft 365 on Windows, select a cell in the data range, choose Insert > Table, check that the proposed range is correct, and select whether the range has headers. You can also use Ctrl+T. On a Mac, use Insert > Table or Command+T. In Excel for the web, use Insert > Table.

Give each column a useful, unique heading. Excel uses the heading text in structured references, so names such as Quantity, Unit Price, and Amount make formulas easier to understand than Column1 or a blank heading. To rename the table, click in it and use the Table Design tab’s Table Name box on Windows. The contextual tab may be labeled Table on Mac or in the web app.

Excel assigns a default name such as Table1 when a table is created. Table names must be unique within the workbook and cannot contain spaces. A name such as OrderDetails is valid; a name containing a space is not. Column headings, however, can contain spaces, and those spaces are included inside the reference brackets.

The basic structured reference patterns

The outer table name identifies the table. Brackets then identify a column or a particular part of the table. These are common patterns:

  • Orders[Amount] refers to all data cells in the Amount column, excluding its header and Total Row.
  • Orders[[#Headers],[Amount]] refers to the Amount header cell.
  • Orders[[#Totals],[Amount]] refers to the Amount cell in the Total Row, if that row is turned on.
  • Orders[#Data] refers to the table’s data body, without its headers or Total Row.
  • Orders[#Headers] refers to the header row.
  • Orders[#All] refers to the entire table, including headers, data, and the Total Row if present.

The item names beginning with # are called specifiers. They make the target more precise when a formula needs a header, totals, or the full table. If a reference includes only a table name and column name, Excel normally means the column’s data cells.

Current-row references and calculated columns

Inside a table row, the at sign identifies the current row. For example, [@Quantity] means the Quantity value on the same row as the formula. This is different from Orders[Quantity], which refers to the whole column.

Suppose the Orders table has Quantity and Unit Price columns, and you want an Amount calculated for each order. With the formula entered in the Amount column of the Orders table, where each row has a Quantity and Unit Price, use =[@Quantity]*[@[Unit Price]]. Excel calculates the product for that row and usually fills the formula down the calculated column. The doubled brackets around Unit Price are needed here because the current-row marker and the column heading are both being specified.

The shorter current-row form is often written with a single column name inside brackets, as in [@Quantity]. The more explicit form, Orders[@Quantity], can be useful when the context is less obvious. In a formula entered outside a table, a current-row reference may not have a clear row context; use an ordinary column reference or a specific cell reference when appropriate.

Use references in formulas

Structured references work with standard Excel functions. With an Orders table whose Amount values are 120, 80, and 50, enter this formula in a cell outside the table to add that column: =SUM(Orders[Amount]). It returns 250. Because the formula names the column, it remains understandable even if the table moves to another part of the worksheet.

A column reference can also be used as an input to functions such as AVERAGE, COUNT, or MAX. For example, with the same table and numeric Amount values, =AVERAGE(Orders[Amount]) returns approximately 83.33. Structured references do not change the usual rules of those functions: text and blank cells are treated according to the function’s normal behavior.

You can refer to more than one table column in a structured reference. With an Orders table containing adjacent Start Date and End Date columns, Orders[[Start Date]:[End Date]] identifies the columns from Start Date through End Date. The colon indicates a span of columns in the table, not a subtraction operation. If the columns are not adjacent, use separate references as required by the formula.

How references adjust when a table changes

Structured references are designed to follow table structure. When you type directly below a table, Excel commonly expands the table to include the new row. Formulas that refer to a whole table column then include that row. Pasting multiple rows next to a table can also expand it, depending on the paste location and settings. Check the table boundary if a new record does not appear in a result.

If you rename a table or a column through Excel’s table controls, Excel updates structured references that point to it. This is one advantage over formulas tied to fixed cell addresses. However, typing a different heading directly into a header cell or changing the table layout can affect formulas and other workbook features. When a formula displays an unexpected reference, inspect the exact table and header names rather than assuming the table is still named Table1.

In Microsoft 365, formulas that return multiple results may spill into neighboring cells. A structured reference can supply an array of table values to such a formula, but the destination cells must be clear for the result to spill. A formula placed inside a table column is generally treated as a calculated-column formula, so do not assume it will spill through the table in the same way as a formula entered in an ordinary worksheet area.

Structured references compared with cell ranges

A traditional reference such as D2:D100 tells Excel which cells to use. A structured reference such as Orders[Amount] tells Excel which named table column to use. The range is compact and familiar, and it can be appropriate for a fixed block of cells or for calculations involving cells outside a table. The structured reference is usually clearer when the source data is a table and may be more resilient as table rows are added.

Structured references are not automatically better for every task. A conventional cell reference can be easier when you need one particular cell, a fixed block, or a formula that depends on worksheet position. Structured references may also look lengthy when a table or header has a long name. Use whichever form makes the formula’s source and intended behavior clear, and verify the result after changing the table.

Do not confuse structured references with Excel’s What-If Analysis Data Table feature. A what-if data table tests one or two input values against a formula to compare possible results; it is a calculation tool found under Data > What-If Analysis > Data Table in desktop Excel. It is not the square-bracket reference format described here. A formatted Excel table can contain data used in a what-if analysis, but the two features are separate.

Common reference problems

  • The table name is wrong. Click in the table and check its name on the Table Design tab. Excel’s default name may have changed, or another table may be the one the formula needs.
  • The header text does not match. References use the column heading, including spaces and punctuation. Select or retype the reference with Excel’s formula autocomplete if the heading is long or easy to mistype.
  • The formula is outside the table. [@Amount] is a current-row reference and is most useful in a table row. To total the full column from outside the table, use a reference such as Orders[Amount].
  • The table has no data rows. A table with only headers may not behave as expected in formulas that need actual values. Add data and confirm the table’s data body is included.
  • The new records are outside the table. A visually similar row is not necessarily part of the table. Click in the data and check whether the Table Design contextual tab appears, or resize the table to include the missing rows.
  • Brackets appear confusing. Nested brackets are normal when a reference combines a table item and a column, such as Orders[[#Headers],[Amount]]. Let Excel insert the reference while editing if you are unsure of the exact punctuation.

For formula entry, type an equals sign and begin typing the table or column name; Excel’s autocomplete can offer available table references. You can also select the relevant table cells while building a formula, and Excel may insert the structured reference automatically. These approaches reduce spelling errors, especially with headers containing spaces.

Frequently Asked Questions

What is the format for an Excel table column reference?
Use the table name followed by the column heading in square brackets: TableName[ColumnName]. For instance, Orders[Amount] refers to the Amount data column in Orders.

What does the @ sign mean in an Excel table formula?
It means the current row. In [@Quantity], Excel uses the Quantity value in the row where that formula appears, rather than the entire Quantity column.

How do I reference a table column with spaces in its name?
Put the full heading, including spaces, inside the brackets. If the table is Orders and the heading is Unit Price, the column reference is Orders[Unit Price].

Can I use a table reference on another worksheet?
Yes. A structured reference can be used in a formula on another worksheet in the same workbook. The reference identifies the table by name, so it does not need a worksheet name in the way a typical cell address does.

How do I reference only the header or total row?
Use a specifier with the column name. For example, Orders[[#Headers],[Amount]] points to the Amount header, while Orders[[#Totals],[Amount]] points to its Total Row cell when that row is enabled.

Is an Excel data table the same as a structured reference?
No. A structured reference is formula notation for an Excel table’s columns and rows. A What-If Analysis Data Table evaluates a formula under different input values. They are distinct Excel features with different purposes.

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.