How to Use Structured Reference in Excel: Simple Guide

In Excel, structured references let you use table names and column headers in formulas instead of cell addresses. This makes formulas easier to read and automatically updates when you add or remove data in the table.

Syntax

Structured references use the table name and column headers inside square brackets to refer to data. The basic syntax is:

• TableName[ColumnName] - refers to all data in a column.
• TableName[[#This Row],[ColumnName]] - refers to the value in the current row of a column.
• TableName[[#Headers],[ColumnName]] - refers to the header cell of a column.

This syntax replaces normal cell references like A2:A10 with meaningful names.

excel

=Table1[Sales]
=Table1[[#This Row],[Sales]]

Example

This example shows how to calculate a 10% commission for each sale using structured references in a table named SalesData with a column Amount.

excel

=SalesData[[#This Row],[Amount]] * 0.1

Output

If the current row's Amount is 100, the formula returns 10

Common Pitfalls

Common mistakes include:

• Using structured references outside a table, which causes errors.
• Typing column names incorrectly or with extra spaces.
• Not using the [[#This Row],[ColumnName]] syntax when you want to refer to the current row, causing formulas to return entire columns.

Always make sure your data is formatted as a table (Insert > Table) before using structured references.

excel

=SalesData[Amount]  <em>(returns whole column, not single value)</em>
=SalesData[[#This Row],[Amount]]  <em>(correct for current row)</em>

Quick Reference

Structured Reference Part	Meaning	Example
TableName[ColumnName]	All data in a column	SalesData[Amount]
TableName[[#This Row],[ColumnName]]	Value in current row of a column	SalesData[[#This Row],[Amount]]
TableName[[#Headers],[ColumnName]]	Header cell of a column	SalesData[[#Headers],[Amount]]
TableName[[#Totals],[ColumnName]]	Total row cell of a column	SalesData[[#Totals],[Amount]]

Key Takeaways

Structured references use table and column names for clearer, dynamic formulas.

Always format your data as a table before using structured references.

Use [[#This Row],[ColumnName]] to refer to the current row's value in a formula.

Check column names carefully to avoid errors in structured references.

Structured references update automatically when you add or remove table rows.