Structured Cell References in Excel: How They Work

How-to basics · Updated November 21, 2026

A structured reference in Excel names a table and its columns instead of relying on cell addresses such as B2:B20. If your table is named Sales and has an Amount column, enter =SUM(Sales[Amount]) to total that column; the reference adjusts as the table changes.

What a structured reference means

Excel tables have names, and each table column has a header. A structured reference combines those names to identify data. In Sales[Amount], Sales is the table name and Amount is its column name. The notation is used in formulas, so a table formula can describe what it uses—sales amounts, dates, or product names—rather than where those values happen to sit on the worksheet.

Structured references are different from ordinary A1-style references such as C2:C100. A cell address points to a particular location; a table reference points to a named part of a table. If you add rows to a table, references to its data generally expand with it. This makes structured references useful for lists that grow over time, including order records, inventories, and survey results.

They work with regular worksheet formulas and functions. They are not a separate kind of calculation, and they do not replace tables: the table supplies the names and structure that make the references possible.

Turn a range into an Excel table

To use structured references, first make the data an Excel table. On Excel for Microsoft 365 for Windows, select a cell in the data and choose Insert > Table, or press Ctrl+T. Confirm the range and select My table has headers if the first row contains column names. On Mac, use Insert > Table; the ribbon layout can differ slightly from Windows.

Use a clear, unique header for each column. For example, an order table might have Date, Product, Region, Quantity, Unit Price, and Line Total headers. Excel uses those header labels in formulas. Once the range is a table, select a cell in it to see the Table Design tab in the Windows ribbon. The Table Name box there lets you replace a generic name such as Table1 with a descriptive name such as Sales.

  • Table names cannot contain spaces. Use a name such as Sales_2026 rather than “Sales 2026.”
  • Column headers can contain spaces, as in Unit Price; Excel accounts for them in the reference syntax.
  • Keep headers distinct and meaningful. If a header changes, Excel usually updates formulas that refer to it, but clear labels also make formulas easier to read.

How the reference syntax works

A basic reference such as Sales[Amount] selects the data cells in the Amount column of the Sales table, excluding the header and total row. In a formula entered in a table row, [@Amount] means the Amount value in the current row. The @ symbol is a compact way of specifying the current row.

Column names with spaces use an extra set of brackets when combined with the current-row marker. For example, [@[Unit Price]] means the Unit Price value in this row. The doubled brackets are part of the syntax, not a typo. Table and header names must match the workbook’s names; typing the opening bracket often prompts Excel to show available table columns.

Structured references can also include special item specifiers to select a particular part of a table:

  • Sales[#Headers] refers to the header row.
  • Sales[#Data] refers to the data rows, without the headers or total row.
  • Sales[#Totals] refers to the total row, when one is displayed.
  • Sales[#All] refers to the whole table, including headers, data, and totals where present.
  • Sales[#This Row] identifies the current row in a table formula. The shorter @ form is commonly used for this purpose.

You can combine a table part with a column name. For instance, Sales[[#Data],[Amount]] identifies the Amount data cells. A table’s total row is optional; turn it on from Table Design > Total Row in Excel for Windows.

Examples for everyday formulas

Suppose Sales contains three order rows. Their Quantity and Unit Price values are 2 and 10, 5 and 7, and 1 and 12. Add a Line Total column. With the formula entered in the first data cell of that column, Excel can fill the calculated column automatically:

With columns Quantity and Unit Price in the Sales table, enter this formula in the Line Total column: =[@Quantity]*[@[Unit Price]]. The three row results are 20, 35, and 12, respectively.

To add the Line Total values, place a formula outside the table. With Sales[Line Total] containing 20, 35, and 12, the formula =SUM(Sales[Line Total]) returns 67. If another order is added to the table, the column reference expands to include its Line Total.

You can combine structured references with criteria functions. Suppose the same three rows have Region values West, West, and East, and their Line Total values are 20, 35, and 12. With those columns in the Sales table, enter =SUMIFS(Sales[Line Total],Sales[Region],"West") outside the table. It returns 55 because 20 + 35 = 55.

For a total that responds to table filtering, show the table’s total row and choose Sum in its Line Total cell, or use a subtotal formula outside the table. With Sales[Line Total] containing 20, 35, and 12, =SUBTOTAL(109,Sales[Line Total]) returns 67 when all three rows are visible. The 109 function number sums visible values while ignoring manually hidden rows as well as filtered-out rows.

Why people use structured references

The main benefit is that the formula describes the data it uses. =SUM(Sales[Line Total]) is often easier to understand later than a formula that sums an unexplained range such as H2:H500. Named columns can also make formulas more resilient: when rows are added to a table, formulas that refer to its columns are designed to include the new table data.

Structured references are particularly helpful when a formula is copied down a table. Excel treats a formula in a table column as a calculated column and generally fills it through the other rows. Each row’s current-row reference changes appropriately. Adding a new table row can also carry the calculated-column formula into that row. This reduces the risk of leaving a row without a formula, though it is still sensible to check results after editing a workbook.

Tables provide other useful features alongside references: filter buttons, consistent formatting, and an optional total row. These are table features rather than special formula functions, but they make the structured-reference approach practical for managing ongoing lists.

Structured references compared with cell addresses

Ordinary references remain useful. A formula such as =SUM(B2:B20) is concise for a fixed block of cells, and A1 references are familiar when working with individual cells or ranges that are not tables. They can also make sense in a small calculation area where the location itself is meaningful.

Structured references are a stronger fit when the data has headers and may grow. They make table formulas more self-explanatory and help formulas follow the table as it expands. The trade-off is that the syntax may look unfamiliar at first, especially with spaces, current-row references, or special specifiers. A table reference also depends on the table and column names remaining understandable and available in the workbook.

These approaches can coexist. A workbook can use tables for its source records and regular cell references for a separate report or chart calculation. Choose based on how the data is organized and who needs to maintain the formula, rather than assuming one reference style is always better.

Common problems and how to fix them

  • The reference is not recognized: Confirm the range is an actual table, not just a formatted range. Select a cell in the data and look for the Table Design tab. Check the table name and header spelling.
  • A header contains a space: Use the bracket form for column names, such as Sales[Unit Price]. For a current-row reference, use [@[Unit Price]].
  • The formula returns an unexpected total: Check which table columns and rows the reference selects. Sales[Amount] excludes headers and the total row; a specifier such as [#All] includes more of the table.
  • A copied formula does not behave as expected: In a table formula, check whether the reference should mean the current row ([@Amount]) or the whole column (Sales[Amount]). Those two forms refer to different amounts of data.
  • The table name is unclear or duplicated: Rename it using the Table Name box on the Windows Table Design tab. Table names must be unique within the workbook and cannot include spaces.
  • A pasted formula changes into a different reference: Excel may adjust references when formulas are copied or pasted. Inspect the formula bar and verify the table and column names after copying.

If you need to stop using a table, on Windows choose Table Design > Convert to Range and confirm. This keeps the cell contents and formatting but removes table behavior, so formulas or features that rely on the table structure may need review. Converting to a range is not the same as deleting the data.

Frequently Asked Questions

How do I write a structured reference in Excel?
Use a table name followed by a column name in brackets, such as Orders[Quantity]. For the value in the current row of a table formula, use [@Quantity]. The source range must be formatted as an Excel table.

What does the @ symbol mean in an Excel table formula?
In a structured reference, @ indicates the current row. For example, [@Quantity] refers to that row’s Quantity value, rather than every value in the column.

Do structured references update when I add rows?
References to a table’s data column generally expand as you add rows to the table. If you type or paste directly below the table, Excel may expand it automatically; check that the new row is part of the table before relying on the formula.

Can I use structured references outside the table?
Yes. A formula elsewhere on the worksheet can refer to a table column, for example =SUM(Sales[Amount]). A current-row reference such as [@Amount] is intended for a row context inside a table formula.

Can I change a table back to a normal range?
Yes. Select a cell in the table and choose Table Design > Convert to Range on Windows. The data remains, but table behavior and structured-reference conveniences no longer apply in the same way.

Are structured references the same as named ranges?
No. A named range assigns a name to a cell or range; a structured reference uses a table name, column labels, and optional table-part specifiers. Tables also expand their data areas as they grow, which is useful for changing lists.

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.