What is a structured reference in Excel? Definition and syntax

Microsoft's before-and-after is the whole idea in two lines. Instead of the explicit =Sum(C2:C7), "Excel uses table and column names" and you get =SUM(DeptSales[Sales Amount]). Ablebits calls the same thing a table reference: a "special way for referencing tables and their parts that uses a combination of table and column names instead of cell addresses." This assumes you already have a table — turning a range into one is a separate subject, covered under Excel tables.

You mostly do not type them

The intended workflow is point-and-click. Microsoft: "To include structured references in your formula, select the table cells you want to reference instead of typing their cell reference in the formula." Exceljet says the same from the other side — "Excel will enter structured references automatically when you reference parts of a table with point and click" — and names the switch that controls it, the "Use table names in formulas" preference, which lives under File → Options → Formulas → Working with formulas.

Microsoft's worked example is worth following once. In a table named DeptSales, type = in E2 and click C2: the formula bar shows [@[Sales Amount]]. Type *, click D2, and [@[% Commission]] appears. Press Enter and "Excel automatically creates a calculated column and copies the formula down the entire column for you." Type =C2*D2 instead and you get the same number with none of the benefits — "if, for example, you add a column between the existing columns C and D, you'd have to revise your formula."

The parts of a reference

Manual editing needs the grammar. Microsoft breaks =SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]]) into a table name, column specifiers, item specifiers and table specifiers. The table name "references the table data, without any header or total rows". A column specifier such as [Sales Amount] references "the column data, without any column header or total row" — and specifiers are "always" enclosed in brackets. Item specifiers pick out a slice:

Item specifier	Refers to
[#All]	"The entire table, including column headers, data, and totals (if any)."
[#Data]	"Just the data rows."
[#Headers]	"Just the header row."
[#Totals]	"Just the total row. If none exists, then it returns null."
[#This Row] or [@…]	"Just the cells in the same row as the formula."

Three operators combine them: a colon for adjacent columns (DeptSales[[Sales Person]:[Region]]), a comma to union non-adjacent ones, and a space for the intersection of two references. Ablebits flags the trap in the last case — the intersection =Regions[#Totals] Regions[[#All],[West]] needs [#All] "because the column specifier does not include the total row. Without it, the formula would return #NULL!."

Two spelling rules cause most manual-editing failures. Column headers are text but "they don't require quotes when they're used in a structured reference", and a header containing a special character needs its own brackets, which is why [[Total $ Amount]] is doubled. A few characters — [, ], #, ' and @ — also need a single quotation mark as an escape, so "Item #" is written [Item '#].

The @ that means "this row"

Inside a table you will mostly see the short form. "Excel automatically changes #This Row specifiers to the shorter @ specifier in tables that have more than one row of data", and Exceljet's shorthand is =[@color] for "current row in color column". The documented gotcha: "if your table has only one row, Excel doesn't replace the #This Row specifier, which may cause unexpected calculation results when you add more rows."

The other half of that is qualification. Inside the table you can write the unqualified =[Sales Amount]*[% Commission]; outside it you need the fully qualified =DeptSales[Sales Amount]*DeptSales[% Commission]. Microsoft's rule: "If you're using structured references within a table … you can use an unqualified structured reference, but if you use the structured reference outside of the table, you need to use a fully qualified structured reference."

Copying, filling, and the absolute question

Structured references have no dollar signs, so the habits from Absolute references do not transfer. Copying is safe — "all structured references remain the same when you copy or move a formula that uses a structured reference" — but filling is not the same operation. Microsoft: "when you fill a formula, fully qualified structured references adjust the column specifiers like a series." Fill right or left and the column specifier walks along with you; fill up or down and nothing shifts unless you hold Ctrl.

Exceljet states the default plainly: "References to single columns in a table are relative by default … References to multiple columns are absolute and will not change when formulas are copied." Ablebits turns that into the fix — repeat the column name so a single column becomes a range. A SUMIF dragged across three month columns breaks because [Item] drifts; written =SUMIF(Sales[[Item]:[Item]], $F$2, Sales[Jan]) the criteria column is pinned while [Jan] still advances.

Table references in Google Sheets

Sheets uses the same idea and calls it table references. Google's example is identical down to the table name — "instead of explicit cell references: =Sum(C2:C7), you can use table references: =SUM(DeptSales[Sales Amount])" — and the supported forms cover Table1[Column 1], Table1[#ALL], Table1[#HEADERS], Table1[#TOTALS] and bare Table1 for the data alone.

Two differences matter before you paste a formula across. Google notes that "#This Row currently is not supported", so the @ form has no equivalent, and that "in formulas, spaces are replaced with underscores", giving Sales_Tracker[Sales Amount]. Sheets also lists features that "don't currently support the use of table references when you select a range": conditional formatting, charts and pivot tables.

How Formula Foundry handles this

Formula Foundry does not create tables or manage their names — that stays a spreadsheet operation. What it changes is the readability of the formulas built on top of them, and structured references need it, because they trade short addresses for long names. =SUMIFS(Orders[Amount], Orders[Region], "EMEA", Orders[Date], ">="&@@CutOff) is one unbroken line in the formula bar and four separate ideas.

Opened in the Formula Foundry sidebar, the same formula spreads across multiple lines, indented along the nesting, with functions, ranges, constants and named references each in their own colour and matching parentheses highlighted — so a bracket closed one specifier early is something you see rather than something you count. Auto-suggest covers range names as well as function names as you type, and the visual builder renders a nested call as a form with every argument labelled, which is the fastest way to spot the misaligned pair in a six-argument SUMIFS.

Two honest limits. The add-on parses and formats formulas; it does not evaluate your workbook, so it can tell you a reference is malformed but not that [#Totals] returned null because there is no total row. And it reads the names in the workbook you have open — a structured reference into a table in another file arrives as an opaque token and is flagged as one rather than silently resolved. The features overview shows the editor, the builder and the variables panel in both applications.

FAQ