Structured References are special formulas used inside Excel Tables.
Instead of using normal cell references like A2:B10, Excel uses table names and column names.
This makes formulas easier to read, understand, and manage.
Why Use Structured References?
- Formulas become readable
- Automatically adjust when table grows
- No need to update ranges manually
- Reduce formula errors
- Professional reporting
Example Table
Suppose your table name is:
EmployeesTable
And columns are:
| ID | Name | Salary |
Normal Formula (Without Table)
=SUM(C2:C10)
Structured Reference Formula
=SUM(EmployeesTable[Salary])
Easier to understand
Automatically updates when new rows are added
Common Structured Reference Formats
Reference a Column
=EmployeesTable[Salary]
Reference Specific Cell in Table
=[@Salary]
@ means current row.
Reference Entire Table
=EmployeesTable
Reference Headers
=EmployeesTable[#Headers]
Reference Total Row
=EmployeesTable[#Totals]
Using Structured Reference in Calculated Column
If you want to calculate bonus (10% of Salary):
=[@Salary]*10%
Excel automatically fills the formula for all rows.
Special Structured Reference Keywords
| Keyword | Meaning |
|---|---|
| #All | Entire table |
| #Data | Only data (no header) |
| #Headers | Header row |
| #Totals | Total row |
| @ | Current row |
Important Tips
- Structured references only work inside Excel Tables
- Rename tables for better readability
- Avoid spaces in table names
- Do not delete column headers
Why Structured References Are Important
They make formulas dynamic, professional, and easy to manage — especially in large datasets and dashboards.
Structured References are essential for advanced Excel reporting and automation.