Excel 365 Table Formulas: Structured References Explained

Formulas & functions · Updated November 22, 2026

Excel 365 table formulas are formulas entered in an Excel table that can use readable column names, such as Sales[Amount], instead of ordinary cell addresses. Enter a formula in a table column and Excel can fill it down as a calculated column, then extend it automatically when you add rows.

What table formulas mean

An Excel table is a structured range of data with headers, filter buttons, and table-specific behavior. It is not the same as a formatted range: when you create a table, Excel gives it a name and lets formulas refer to its columns by header. A table formula is simply a worksheet formula used with those table features; the formula still uses familiar functions such as SUM, IF, and SUMIFS.

For example, a reference such as Sales[Amount] means the Amount column in the table named Sales. If the table grows, the reference grows with it. This makes table formulas useful for lists that change over time, including invoices, orders, inventory, survey responses, and task trackers.

The key benefit is not that table formulas calculate differently from normal formulas. It is that they are easier to read and often need less maintenance. A formula that describes the Amount column is usually more understandable than one pointing to a particular stretch of cells, especially after someone inserts rows or moves the table.

How structured references are built

Structured references combine a table name, an optional row or section marker, and a column name. Excel usually inserts the correct syntax when you click a table cell while building a formula. Common forms include:

  • Sales[Amount] refers to the data cells in the Amount column of Sales.
  • Sales[#Headers] refers to the table’s header row.
  • Sales[#Totals] refers to the totals row, if the table has one.
  • Sales[#All] refers to the entire table, including headers and totals when present.
  • [@Quantity] refers to the Quantity cell on the current row when the formula is inside that table.

Column names containing spaces or punctuation need extra brackets in some forms. For example, the current-row reference for a column named Unit Price is written with nested brackets. When selecting columns with the mouse, Excel generates the required brackets for you, which is often safer than typing a complicated reference from memory.

Table names are workbook-level identifiers, so a useful name such as Sales or Inventory is clearer than the default Table1. Names cannot contain spaces; use a short word or an underscore if needed. Column headers should also be distinct and descriptive. Excel may adjust duplicate headers to keep them unique, so check the actual header text before writing a reference by hand.

Create a table and enter a formula

In Excel 365 for Windows, select a cell in the data range and press Ctrl+T. Check that the range is correct and select My table has headers if the first row contains field names. You can also use Insert > Table. On Mac, Command+T opens the Create Table dialog; the ribbon command is also available. If the selection includes blank rows or unrelated data, correct the range before confirming.

To name the table, click inside it and use the Table Design tab’s Table Name box. Then enter a formula in the first data cell of a new table column. For a column formula, press Enter: Excel normally copies the formula through that column and treats it as a calculated column. If Excel does not fill the cells, check the calculated-column setting under File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type on Windows. The exact options available can depend on the Excel build and workbook settings.

  1. Make sure every column has a header and that each row represents one record.
  2. Create the table and give it a descriptive name on Table Design.
  3. Type the formula in the first data row of the intended calculated column.
  4. Check the values in several rows, especially where inputs are blank or unusual.
  5. Add a new row and confirm that the formula is carried into it.

If you paste data beneath a table, Excel often expands the table automatically, but this depends on the surrounding workbook and settings. If a new record does not receive table formatting or formulas, use the table’s last cell and press Tab to create a row, or resize the table from Table Design. A structured reference does not automatically convert an unrelated range into a table.

Practical table formula examples

Suppose a table named Sales has columns Date, Item, Quantity, Unit Price, and Amount. Each example below states the assumed layout so that the formula’s row context is clear. Enter a calculated-column formula in the first data row of the named column, unless the example says to put it elsewhere.

With a Sales table whose current row has Quantity 3 and Unit Price 12.50, enter the following in the Amount column; the calculated result for that row is 37.50:

=[@Quantity]*[@[Unit Price]]

Excel will normally copy the formula to the other Amount cells. The at sign means the value from the current record, so each row multiplies its own quantity by its own price. If you add another sales row, the same calculation can be filled in automatically.

With the same Sales table, where its Amount values are 37.50, 25.00, and 18.00, put the following formula in a worksheet cell outside the table; it returns 80.50:

=SUM(Sales[Amount])

This is a convenient way to include all current entries in one column without adjusting a cell range after each addition. The SUM includes rows hidden by a filter, however. If the desired figure should reflect only currently visible rows, use a subtotal formula instead, as described below.

With the same Sales table and G2 containing the text Notebook, enter this formula in a cell outside the table; if Notebook rows have Amount values of 37.50 and 25.00, the result is 62.50:

=SUMIFS(Sales[Amount],Sales[Item],G2)

This illustrates a useful pattern: structured references supply the data columns, while an ordinary cell reference supplies the criterion. The formula adds amounts only where the Item value matches G2. If the matching item is absent, SUMIFS returns zero.

Totals, filters, and summaries

To display a total row, click in the table and select Total Row on the Table Design tab. A drop-down in the total row lets you choose a calculation such as Sum, Average, or Count for a column. Excel uses a subtotal-style calculation for many of these choices, so filtering the table can change the displayed result to reflect visible records.

With the Sales table described above, enter the following formula in an ordinary worksheet cell to sum visible Amount values while ignoring manually hidden rows; with all three example rows visible it returns 80.50, and after filtering out the 18.00 row it returns 62.50:

=SUBTOTAL(109,Sales[Amount])

In this function, 109 specifies a sum that ignores filtered-out rows and manually hidden rows. The distinction matters when comparing it with SUM: filtering rows does not make a regular SUM omit them. Use SUBTOTAL when the result should respond to a filter, and use SUM when the intention is to add every record regardless of visibility.

Tables also work well with sorting and filtering because the structured references stay attached to the named columns rather than a fixed position on the sheet. A filter changes which records are displayed, not the underlying data. If a formula is intended to summarize only displayed records, select a function that has the required visibility behavior rather than assuming every table reference follows the filter.

Table formulas versus ordinary cell references

A conventional formula might refer to a fixed range such as cells C2 through C100. That can be perfectly suitable for stable data, but it may omit new rows if the range is not extended. A structured reference to a table column is generally easier to interpret and expands with the table. It also tends to survive inserted table rows and column moves better than a formula built around fixed coordinates.

Structured references are not always shorter. A long table name and a header such as Annual Contract Value can make a formula visually lengthy, and formulas copied from a table to an unrelated location may need a different reference style. Ordinary cell references can be clearer for a small one-off calculation or for a formula involving a specific position. A practical approach is to use tables for growing datasets and choose the reference style that makes each formula easiest to audit.

Inside a table formula, [@Amount] means the current row’s Amount. Outside the table, use a full column reference such as Sales[Amount] when you mean the whole column. A current-row reference has no natural meaning in an ordinary cell outside the table, so it should not be treated as a general replacement for a cell address.

Dynamic arrays and table limits

Excel 365 includes dynamic-array functions such as SORT and UNIQUE. They can use table columns as inputs and update as the table changes, but a spilled array cannot spill into the cells of an Excel table. Put a formula that returns multiple results in a clear area outside the table, with enough empty cells for the results.

With a Sales table whose Item column contains Notebook, Pen, and Notebook, enter this formula in a blank cell outside the table; it returns the distinct item names in sorted order, Notebook and Pen, spilling into the cell below:

=SORT(UNIQUE(Sales[Item]))

Because the formula refers to the table column, adding a new item to the table can update the output. If a result cell is occupied, Excel may show a spill error rather than overwrite existing content. Clear the obstructing cells or move the formula. Dynamic-array functions are available in Microsoft 365 and Excel 2021 or later for functions such as SORT and UNIQUE; availability can vary in older Excel versions.

Common problems and fixes

  • The formula shows an error after a header change. Structured references depend on table and column names. Check for a renamed or misspelled header, then select the correct column while editing the formula.
  • Only one table row has the formula. Confirm that the formula was entered in a table data cell, not directly below the table. Check the calculated-column behavior and fill the formula down if necessary.
  • A new record is missing from a result. Verify that the row is part of the table. Resize the table or add the record through the table’s last row; formatting alone does not prove that a row belongs to the table.
  • A total does not change when a filter is applied. The formula may use SUM, which includes filtered-out records. Use SUBTOTAL when the required result should respond to filtered visibility.
  • A formula is displayed as text. The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the format to General, then re-enter the formula.
  • Excel changes a formula you just entered. In a calculated column, Excel tries to keep formulas consistent down the column. If you intentionally need a different formula in one row, check whether automatic calculated-column filling is changing it.

Structured references can look unfamiliar at first because brackets mark table elements, not because they are a separate calculation language. For fewer typing mistakes, start the formula, then click the relevant table header or data cells so Excel inserts the reference. Check the displayed formula in the formula bar if the result is unexpected.

Frequently Asked Questions

Do Excel table formulas update when I add rows?
Usually, yes. A structured reference to a table column expands as records are added to that table, and a calculated-column formula is normally copied into new rows. A row pasted outside the table may not count as a table row, so confirm that the table itself has expanded.

Can I use a table formula outside the table?
Yes. A full structured reference such as Sales[Amount] can be used in a worksheet formula outside the table. The current-row form, such as [@Amount], is intended for a formula within a table row.

Why does Excel repeat my formula down the whole column?
Excel recognizes consistent formulas in table columns as calculated columns and fills the formula through the table. This is designed to keep each row consistent. If that is not wanted, review the AutoCorrect options for calculated columns or use a regular range instead of a table.

Can I use structured references in charts or data validation?
Structured references are useful in worksheet formulas, but not every Excel feature accepts them directly in every source box. If a chart or validation dialog rejects one, use a supported range reference or define a named range that points to the table data.

Do table formulas work in Excel for the web and on Mac?
Excel for the web and Excel for Mac support Excel tables and structured references, although ribbon placement and some dialog details differ from Windows. The same basic table-reference ideas apply; on Mac, use Command+T rather than Ctrl+T to create a table.

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.