Structured References in Excel Tables, Explained

Structured references in Excel are formulas that use a table's name and column names instead of cell addresses. You write =SUM(Sales[Amount]) instead of =SUM(E2:E7), and the formula keeps working when the table grows. This guide explains every part of the syntax, shows the same formulas with and without tables, and covers the few places where structured references don't work.

What is a structured reference?#

A structured reference is a name-based pointer to part of an Excel table. A table here means a range you have turned into a table with Ctrl+T (Mac: ⌘T), not just any list. If you haven't made one before, start with the guide to Excel tables and Ctrl+T.

Every table has a name, such as Sales, and every column has a header. Structured references combine the two:

The @ sign means "this row". In Excel 2007, the same idea was written [#This Row]; later versions show @ instead.

How to name a table#

Excel calls new tables Table1, Table2 and so on. Rename them so formulas are easy to read.

1. Click any cell in the table.
2. Open the Table Design tab (on a Mac, the Table tab).
3. Type a new name in the Table Name box, such as Sales, and press Enter.

Table names must start with a letter or underscore and can't contain spaces. Every formula that uses the old name updates automatically.

All the examples below use a table named Sales in A1:E7:

How to write a formula inside the table#

Inside the table, use [@Column] to work row by row. Click E2, type =, click C2, type *, click D2 and press Enter. Excel writes:

=[@Units]*[@Price]

It then fills the formula down the whole column, so every row gets its own amount: 12 × 25 = 300 in row 2, 8 × 40 = 320 in row 3, and so on. This is called a calculated column. New rows you add at the bottom get the formula too.

If a column name has a space or a special character, Excel adds a second pair of brackets: =[@[Unit Price]]*[@Units]. Keep headers short and simple and you rarely see these.

How to use structured references outside the table#

Outside the table, add the table name in front of the column. To total the Amount column:

=SUM(Sales[Amount])

The result is 1825. To total only the North rows, use SUMIF with two columns of the same table:

=SUMIF(Sales[Region],"North",Sales[Amount])

That gives 880 (300 + 300 + 280). With normal cell references, the same formula is =SUMIF($A$2:$A$7,"North",$E$2:$E$7). Both work, but only the structured version picks up row 8 when you add a new sale. The SUMIF function guide and SUM function guide list the arguments.

The table name works from any sheet in the workbook, so you don't need a sheet name like Data! in front. If you point at a table in a different workbook, that workbook must be open, or the formula returns #REF!.

Structured references vs cell references#

Structured references are easier to read and grow with the data. Cell references are shorter and work everywhere.

The locking row needs a closer look. Copying and pasting a formula keeps its structured references as they are. Dragging it sideways with the fill handle moves Sales[Amount] to the next column, just as E2:E7 would become F2:F7. To lock a column, write it as a one-column range: Sales[[Amount]:[Amount]]. This is the table version of the $ sign; the guide to cell references explained covers $ in plain ranges.

Named ranges are the other way to give cells a readable name. The comparison of named ranges vs cell references explains when a name beats a table.

Common problems with structured references#

These are the issues people run into most:

1. The formula shows cell addresses, not names. The option is off. Go to File > Options > Formulas and tick Use table names in formulas. On a Mac, look under Excel > Preferences.
2. Conditional formatting rejects the formula. Rules can't use Sales[Region] directly. Use the cell range, or wrap the text in INDIRECT, such as INDIRECT("Sales[Region]"). The same applies to data validation lists.
3. New rows are not included. A row typed below a blank row is not part of the table. Type directly under the last row, or use Table Design > Resize Table to extend it. If you ever convert the table back with Convert to Range, Excel rewrites its structured references as normal cell addresses.
4. A typo in a name. Sales[Ammount] returns an error. Type Sales[ and pick the column from the list Excel offers instead.

If you are not sure whether to use a table at all, the article on Excel tables vs normal ranges weighs the pros and cons.

Practise it#

Structured references are just a readable way to write ranges, so the skills underneath are the same. Practise SUMIF in a live spreadsheet: you type each formula and it checks it instantly. Then practise relative and absolute references so locking with $ becomes automatic, and filter a table to work with table data the way job tests do.