Structured Reference Excel Formula: Syntax and Examples

Formulas & functions · Updated November 21, 2026

A structured reference formula uses an Excel table’s name and column headings instead of ordinary cell addresses. For example, with a table named Sales and an Amount column, the formula is =SUM(Sales[Amount]); it adds the table’s Amount values and expands automatically when rows are added.

What a structured reference means

In a conventional formula, a range such as B2:B20 identifies cells by their positions. A structured reference identifies data by its table and column names. Excel creates these references when you make a range into a table, so formulas can describe what they use rather than where the data happens to sit.

Structured references work with Excel tables, not arbitrary cell ranges. A table has a name, column headings, and data rows. Excel normally gives a new table a name such as Table1; you can rename it to something meaningful, such as Sales or Inventory. Column headings become part of the reference, which makes a formula easier to interpret later.

For example, a reference to the Amount column in the Sales table includes all its data rows. If you add another sales record to the table, the reference normally includes that new row without requiring you to edit the formula. This automatic resizing is one of the main reasons to use structured references.

How to create a table and name it

In Excel for Microsoft 365 on Windows, select a cell in your data and choose Insert > Table. Check that the selected range is correct and that My table has headers is selected if the first row contains headings. After creating the table, select a cell in it and use Table Design > Table Name to enter a descriptive name. Table names cannot contain spaces; use a name such as SalesData or Sales_2026 instead.

Column headings can contain spaces, and Excel handles them in structured references. Clear, stable headings such as Unit Price or Order Date are usually more useful than generic headings such as Column1. Avoid duplicate headings: Excel adjusts duplicate table headings to make them unique, which can make the resulting reference less obvious.

In current Excel for Mac and Excel for the web, tables and structured references are also supported. The contextual table-design controls may appear or be labeled differently depending on the interface, but the table name and column-heading principles are the same. In Excel for Mac, use the Table tab when it appears after selecting a table cell to find table settings.

Basic structured reference syntax

A basic reference has the form TableName[ColumnName]. The table name identifies the table, and the bracketed heading identifies a column. A column heading containing a space is still written inside brackets. When a heading includes certain punctuation or characters that overlap with reference syntax, Excel may add extra brackets or escape characters automatically; selecting the column while building the formula is safer than typing an unfamiliar reference from memory.

Suppose the Sales table has Product, Region, Quantity, Unit Price, and Amount columns. To total the Amount column, first assume those headings and a table named Sales, with amounts of 120, 80, and 150 in its three data rows:

With the three Amount values 120, 80, and 150 in Sales[Amount], enter =SUM(Sales[Amount]) in a cell outside the table. It returns 350.

Structured references can also be used in functions that take ranges, including SUMIFS, COUNTIF, and AVERAGE. They do not change what those functions calculate; they make the input range easier to identify and maintain.

Current-row references in calculated columns

Inside a table, the at sign refers to the current row. This is useful when a formula needs to combine values from different columns on the same record. Excel often fills the formula down the rest of the table column as a calculated column, and continues the pattern when new table rows are added.

Assume a row in Sales has Quantity 3 and Unit Price 20, and the formula is entered in that row’s Amount cell. Enter =[@Quantity]*[@[Unit Price]]. The current row’s values produce 60. The nested brackets around Unit Price are needed because its heading contains a space.

The at-sign shorthand represents the current row; a longer form can identify it with the special item name This Row. In normal table formulas, the shorthand is generally easier to read. Outside a table, a current-row reference has no table row to refer to, so use a full column reference or a conventional cell reference instead.

Headers, totals, and other table sections

Structured references can select more than a whole data column. Special item specifiers identify parts of a table: Headers refers to the heading row, Data to data rows, Totals to the totals row, and All to the entire table area. These are useful when a formula specifically needs a heading or needs to distinguish data from the table’s other parts.

Assume Sales has three data rows, a column named Amount, and a totals row is enabled. A sum that explicitly identifies only the data cells in that column can be written as =SUM(Sales[[#Data],[Amount]]). With values 120, 80, and 150, the result is 350. The explicit Data specifier is not necessary for many ordinary column calculations, but it can make the intended part of the table clear.

A totals row can also calculate a column summary. Select a cell in the table and enable the Total Row option on the Table Design tab, then choose the desired calculation from the totals-row cell’s drop-down list. Excel may use SUBTOTAL so filtered or hidden rows can be treated appropriately. If you write a total formula yourself, consider whether it should include filtered-out rows; SUM and SUBTOTAL do not behave identically in that situation.

Useful formula examples

Structured references are especially convenient when several formulas use the same table columns. The examples below assume a table named Sales with Region, Amount, Quantity, and Unit Price columns. Each example states the relevant data so its result can be checked.

  • Assume the three Amount values in Sales are 120, 80, and 150. Enter =SUM(Sales[Amount]) outside the table to return 350.
  • Assume the three Region values are West, East, and West, and the matching Amount values are 120, 80, and 150. Enter =SUMIFS(Sales[Amount],Sales[Region],"West") outside the table to return 270.
  • Assume Sales has three data rows. Enter =COUNTA(Sales[Region]) outside the table to count the nonblank Region cells and return 3.
  • Assume a particular Sales row has Quantity 3 and Unit Price 20. Enter =[@Quantity]*[@[Unit Price]] in that row’s Amount cell to return 60.

In the SUMIFS example, the first column reference supplies the values to add, the second supplies the criteria range, and the text criterion is enclosed in straight double quotes. Structured references do not replace the need to match range sizes or use valid criteria: each criteria range should correspond to the sum range’s table rows.

Why use structured references instead of cell ranges?

Cell-address formulas remain useful, especially for calculations involving unrelated cells or fixed areas. Structured references are often a better fit when the data is a list that grows over time. They make formulas self-describing, adjust as table rows are added or removed, and use named columns rather than positions that can become confusing after columns are moved.

There are trade-offs. A long table name combined with several long headings can make a formula visually wide. Structured references also require the data to be in a table, and formulas copied or edited outside the table may not use the current-row shorthand in the way a formula inside the table does. For a small, fixed calculation, ordinary cell references may be simpler. For recurring lists, reports, and data that expands, a named table often makes maintenance easier.

Structured references are not a special calculation engine and do not make a formula inherently faster. Their central benefit is clarity and adaptation to table structure. They also do not guarantee that a table contains correct or complete data; adding a row only updates the reference, not the accuracy of its contents.

Common problems and how to fix them

  • The table name is wrong. Select a table cell and check its name on the Table Design tab. Table names are not the same as worksheet names, and a reference to the wrong table may return an error or unintended data.
  • A column heading changed. Structured references follow table headings, but a manually typed formula can be invalid if its heading is misspelled or no longer exists. Click the relevant table column while editing the formula to insert the current heading.
  • The formula shows an error outside the table. The shorthand [@Column] means the current row of a table. Outside the table, refer to the full column, such as TableName[Column], or use an ordinary cell address.
  • The table formula did not fill down. Excel normally propagates calculated-column formulas, but settings or differing existing formulas can affect that behavior. Check the other rows and fill the formula down if necessary; do not assume every row has the same calculation just because the first one does.
  • A total differs from the visible filtered values. SUM includes values hidden by filtering. If the result should respond to filtering, use a suitable SUBTOTAL calculation or the table’s Total Row options, and confirm which rows the chosen calculation includes.

When debugging, click the formula cell and inspect whether its table name and column heading match the actual table. If Excel inserts a longer reference than expected, check whether it includes a special section such as Headers or Data. This can explain why a formula includes or excludes cells that look close together on the worksheet.

Frequently Asked Questions

How do I write a structured reference in Excel?
Use the table name followed by a column heading in square brackets, such as Sales[Amount]. Inside a table, use [@Amount] to refer to the Amount value in the current row.

Do structured references update when I add rows?
Yes, a reference to a table column normally includes new rows added to that table. Enter or paste the new records as part of the table; data placed outside the table does not automatically become a table row in every situation.

Can I use structured references outside the table?
Yes. A full reference such as Sales[Amount] can be used in a worksheet cell outside the table. The current-row shorthand depends on a table row, so use it in a table formula rather than as a stand-alone reference elsewhere.

Why does Excel put @ in my formula?
The at sign is the current-row shorthand in a structured reference. In a table calculated column, it lets the formula refer to the value from the same row instead of the entire column.

Can I use a structured reference in SUMIFS?
Yes. Use a table column as the sum range and other table columns as criteria ranges, then supply the criteria. Make sure the referenced columns belong to the intended table and that the criteria match the values you want to include.

Can I use structured references in Google Sheets?
No. Structured references are an Excel table feature. Google Sheets uses regular ranges and other spreadsheet features rather than Excel’s table-name-and-column-heading reference syntax.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.