Skip to main content

How to Use Structured References in an Excel Table

· 4 min read
Jagdish Kumawat
Jagdish Kumawat
Founder @ Dewiride

Inside an Excel table, formulas can name columns instead of cells. =[@Finish]-[@Start] reads as "this row's Finish minus this row's Start", which is clearer than =E2-D2 and keeps working when rows are added or moved. These are structured references.

The examples are from the OvertimeLog table in the old overtime timesheet, from Part 1 of the Overtime Timesheet series.

Prerequisites​

The Three Forms You'll Use​

You writeIt meansUsed in the timesheet for
[@Finish]This row's value in the Finish columnHours worked in each row
[@[Break (mins)]]This row's value in a column whose name has spaces or bracketsSubtracting the break
OvertimeLog[Hours]The whole Hours column of the OvertimeLog tableTotals on another sheet

The @ means "this row". The extra square brackets in [@[Break (mins)]] are needed because the column name contains a space and brackets.

Step 1: Type a This-Row Formula​

In the first data row of a table column, type a formula that names columns. I added a Time worked column by typing its heading in I1, then typed in I2:

OvertimeLog → Time worked (first version)
=[@Finish]-[@Start]

As you type, Excel colours each reference and outlines the cell it uses in this row, D2 and E2:

Cell I2 of the OvertimeLog table being edited with =[@Finish]-[@Start], with [@Finish] in blue and [@Start] in red, and the Finish and Start cells of row 2 outlined in the same colours

Step 2: Press Enter​

Press Enter. Each row's formula is the same text, =[@Finish]-[@Start], and each one uses its own row's values:

The OvertimeLog table with the Time worked column filled in every row, I5 selected with the formula bar showing =[@Finish]-[@Start], 04:00 and 04:30 in daytime rows and ######## in the overnight rows 5, 9 and 14

The overnight rows show ######## because their finish time is earlier than their start; Fixed: Excel Shows ######## for Overnight Shift Hours fixes that.

Step 3: Refer to a Whole Column from Another Sheet​

From outside the table, use the table's name and the column's name. The Monthly totals sheet adds up hours with:

Monthly totals → B7
=SUM(OvertimeLog[Time worked])

OvertimeLog[Time worked] grows with the table, so new rows are included without changing the formula.

Common Mistakes​

MistakeWhat happensDo this instead
Writing [@Break (mins)]Excel can't read a name with spaces in single bracketsUse [@[Break (mins)]]
Typing the column name differently from its headingExcel can't find the columnMatch the heading exactly, or select the cell while typing the formula and let Excel write it
Using OvertimeLog[Hours] inside the table when you mean this rowYou get the whole column, not one valueUse [@Hours]

Troubleshooting​

SymptomLikely causeFix
Excel says the formula has a problemA bracket is missing or a name is misspeltCheck each [ has its ]
A formula refers to the wrong tableTwo tables have similar namesRename tables clearly

FAQ​

Do structured references work outside tables?​

TableName[Column] works anywhere in the workbook. [@Column] only makes sense inside the table, where there's a "this row".

Are structured references slower than cell references?​

They're the same calculation written with names, and they're what Excel writes for you when you select cells inside a table while typing a formula.

Conclusion​

In a table, name columns instead of cells: [@Column] for this row, [@[Column with spaces]] when the name has spaces, and Table[Column] for the whole column.

Additional Resources​