A structured reference is an Excel formula reference that identifies data in a table by its table and column names instead of ordinary cell addresses such as B2:B50. For example, a formula can refer to the Amount column in a table named Sales, and Excel can adjust that reference as the table changes.
Why Excel has structured references
Structured references are designed to work with Excel tables. A table is a range of related data with a header row, such as a sales list with columns for Date, Region, Quantity, Unit Price, and Amount. When you convert that range into a table, Excel gives it a name and recognizes its headers as column names that formulas can use.
Traditional references are tied to worksheet coordinates: B2:B50 means cells in column B from row 2 through row 50. That is concise, but it does not tell a reader what those cells contain. A structured reference such as Sales[Amount] describes the data by purpose. It is also less likely to omit new records when the table expands, which makes it useful for ongoing lists and reports.
Structured references are not a separate kind of calculation. They are a way to identify cells within a table, and they work with regular Excel functions such as SUM, AVERAGE, and COUNTIF. They are available in Excel for Microsoft 365 on Windows and in current Excel versions that support tables.
How to create a table for structured references
First, make sure the data has one clear header row, with a distinct heading for each column and no completely blank rows or columns splitting the list. Select a cell in the data, then choose Insert > Table. In the Create Table dialog, confirm the range and check My table has headers if the first row contains headings. Select OK.
Excel applies table formatting and assigns a default name, often Table1. To give it a useful name, click in the table and use the Table Design tab. In the Table Name box, enter a short name such as Sales and press Enter. Table names cannot contain spaces, so use a name such as SalesData or Sales_2026 instead. The name should not look like a cell address.
On Windows, Ctrl+T also opens the Create Table dialog when a cell in the data is selected. On a Mac, use Insert > Table if the keyboard shortcut differs in your Excel setup. The ribbon route is straightforward on both platforms, although the tab’s appearance and location can vary by version.
Once the data is a table, Excel can use its headers in formulas. The table name and its column names are the building blocks of structured references. If you rename a table or header through Excel, references to it are generally updated to match.
Reading the parts of a structured reference
A reference such as Sales[Amount] has two parts: Sales is the table name, and Amount is the column name in square brackets. It means the data cells in the Amount column, without the header or totals row. The exact reference can be easier to understand when written as a formula.
With a table named Sales and an Amount column containing the sales values, this formula adds that column:
=SUM(Sales[Amount])
The reference includes all data rows in that table column. If rows are added to Sales, Excel normally extends the table and the reference includes the new rows. This is one reason structured references are helpful in formulas that summarize data maintained over time.
Square brackets are part of the syntax, not optional decoration. A header containing spaces, such as Unit Price, is written with its exact name inside brackets. Excel may add extra brackets when a formula combines a column name with a special item such as the header row. When entering a formula, typing the table name and opening bracket often brings up a list of available columns and reference options.
Common structured-reference forms
Structured references can identify a whole column, a particular part of a table, or a value on the current row. The main forms are:
Sales[Amount]refers to the Amount column’s data cells.Sales[#Headers]refers to the header row of Sales.Sales[#Data]refers to the table’s data rows, excluding headers and totals.Sales[#Totals]refers to the totals row when one is displayed.Sales[#All]refers to the entire table, including its headers and totals row if present.[@Amount]refers to the Amount value on the current row when the formula is being calculated in a table row.
The special item #This Row is another way to describe the current row. In modern Excel, the @ shorthand is commonly used for that row context. A current-row reference is especially useful in a calculated column, where each row should calculate using values from that same record rather than from the whole column.
For example, with columns Quantity and Unit Price in a table, enter this in the Amount column’s first data row:
=[@Quantity]*[@[Unit Price]]
Excel can fill the formula down the calculated column, using each row’s own quantity and price. If Quantity is 3 and Unit Price is 12, the row’s result is 36. The doubled-looking brackets around Unit Price are normal in this form: the outer brackets define the current-row reference and the inner ones enclose the column name with a space.
What happens when a table changes
Tables are built to accommodate changing lists. If you type directly below a table or paste an adjacent row of data, Excel will often expand the table to include it. Structured references to its columns then include the added table data. You can also add a row by pressing Tab from the last cell in the table’s last row. Check that Excel actually expanded the table if a formula seems to miss a new record; data placed apart from the table is not automatically part of it.
Calculated columns also adapt as rows are added. When you enter a formula in a table column, Excel typically copies it through the other rows and applies it to new rows. This behavior is useful for consistent calculations, but it can be surprising if you intended to put a one-off formula in just one cell. Excel’s table options control whether calculated-column formulas are automatically filled.
Tables can have a Total Row. Click in the table and enable Total Row on the Table Design tab. The row offers summary choices such as Sum or Average for columns. Excel commonly uses the SUBTOTAL function for these calculations, so the result can respond to filtering. For example, with a table named Sales whose Amount column contains numeric values, this formula sums visible amounts and ignores manually hidden rows as well:
=SUBTOTAL(109,Sales[Amount])
The function number 109 specifies SUM while excluding filtered-out and manually hidden rows. By contrast, 9 also specifies SUM but does not exclude manually hidden rows. If you use a table’s built-in Total Row, Excel creates an appropriate summary formula for the selected calculation.
Using structured references in everyday formulas
Structured references can be arguments to many standard functions. Suppose Sales has Region and Amount columns. To add the amounts for rows marked West, use:
=SUMIF(Sales[Region],"West",Sales[Amount])
This adds values from Sales[Amount] where the corresponding entry in Sales[Region] is West. It does not require you to update a range endpoint when the table grows. For counting matching regions, with the same table layout, use:
=COUNTIF(Sales[Region],"West")
The result is the number of data rows whose Region value matches West. These formulas illustrate that structured references name the ranges supplied to functions; they do not replace the functions’ normal arguments or rules.
A reference can also be used in a formula outside the table. For instance, a summary cell elsewhere on the worksheet can use SUM(Sales[Amount]) without being part of Sales itself. Within the table, a formula such as the Amount calculation uses the current row, while a reference to Sales[Amount] refers to the whole data column. Choosing between those forms depends on whether the calculation is row-by-row or a summary.
Structured references compared with cell references
A standard reference such as =SUM(E2:E100) is familiar and can point to any worksheet cells, whether or not they belong to a table. It can be a good fit for a fixed calculation or for a layout where converting data to a table is not appropriate. Its weakness in a growing list is that the range may need maintenance, and the formula gives little indication of what column E represents.
A structured reference such as =SUM(Sales[Amount]) is more descriptive and normally follows the table as it expands. It also makes formulas easier to inspect when columns move, because the reference is based on the column name rather than its worksheet position. But it requires the source range to be an Excel table, and a long table or column name can make a formula look more elaborate than a short cell reference.
Structured references do not make every workbook automatically safer or more accurate. A wrong table name, an incorrect heading, duplicate or unclear data, and a flawed formula can still produce an incorrect result. They improve readability and help with resizing; they do not validate the meaning or quality of the data.
Common problems and how to fix them
If Excel does not recognize a reference, check the table name and heading carefully. The reference must use the current table and column names. Click inside the table to confirm its name on Table Design, and inspect the header for an unnoticed extra space or a changed spelling. Using formula autocomplete can help select the right name.
- The new row is missing: Confirm that it is inside the table boundary. If not, extend or resize the table, or add the row directly beside the table so Excel can expand it.
- The header name looks odd: Table headers must be usable as column names. Excel may adjust repeated or blank headings, for example by adding a number or supplying a heading, so use the actual displayed name in the reference.
- A current-row reference gives an error outside the table: The @ form depends on a table row context. Use a full table-column reference or an ordinary cell reference when the formula is outside the table.
- The formula seems to include the wrong cells: Check whether you used a column reference, #Data, #All, or another item. #All can include headers and a totals row, while a plain column reference targets that column’s data.
- The formula uses separators Excel rejects: Formulas in this article use commas. Some regional Excel settings use semicolons between function arguments; follow the separator expected by your installation.
Another common misconception is that every use of square brackets means a structured reference. Brackets are characteristic of table references in Excel formulas, but the most important clue is the table-and-column pattern, such as Sales[Amount]. Also, a table is not the same thing as a named range: a table has headers, table behaviors, and structured-reference syntax, while a named range simply assigns a name to a selected range or formula.
Frequently Asked Questions
Do structured references update when I add rows?
Usually, yes, if the new rows are added to or become part of the table. References to a table column then include the table’s expanded data. A row located outside the table boundary will not be included just because it is nearby.
Can I use structured references outside a table?
Yes. A formula in an ordinary worksheet cell can refer to a table column, for example Sales[Amount]. The @ current-row shorthand is intended for row context inside a table formula; outside that context, refer to a specific cell or use a whole-table or column reference instead.
How do I find or change a table’s name?
Click a cell in the table and look for the Table Name box on the Table Design tab. You can enter a new name there and press Enter. Avoid spaces, and update formulas or other workbook elements that refer to the table if Excel does not adjust them automatically.
Can I turn structured references off?
In Excel for Windows, choose File > Options > Formulas and clear Use table names in formulas under the Working with formulas section. This setting affects whether Excel inserts structured references when you build formulas by selecting table cells; it does not convert existing tables into ordinary ranges. The precise options available can differ across Excel platforms.
Are structured references available in Google Sheets?
No. Structured references are an Excel table feature and use Excel’s table names and bracket syntax. Google Sheets has its own ways to refer to cells and named ranges, but it does not interpret Excel table references such as Sales[Amount] as the same feature.
Related
- Excel Slicer Examples: PivotTables, Tables, and Tips
- Excel Slicer Formatting: Styles, Size, Layout, and Use
- Excel Slicers: What They Do and How to Use Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Match Names on Two Excel Sheets with VLOOKUPYes. VLOOKUP can find a name on one Excel sheet in a second sheet and return a related…
- Excel SUMIF: Sum Columns When a Cell Contains TextTo add values in one column only when the corresponding cell in another column is exactly x, use…
- Create a Sort List in Excel: Methods, Steps, and TipsTo create a sorted list in Excel, select a cell in your data and choose Data > Sort…
- How to Find Variance in Excel: Formulas and ExamplesTo find variance in Excel, enter =VAR.S(B2:B6) for a sample or =VAR.P(B2:B6) for an entire population, replacing the…
- HLOOKUP Match in Excel: Exact and Approximate ResultsTo match a value across the top row of a range in Excel, use HLOOKUP and set its…
- Match Excel Formula for Two Columns: Compare ValuesTo compare two cells on the same row in Excel, enter =IF(A2=B2,"Match","Different") in C2 and fill it down.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.