Excel Structured Reference Tutorial: Step-by-Step Guide

By using structured references in Excel, you can write formulas that refer to entire columns in your tables using the column name directly, rather than referencing traditional cell ranges. This makes your formula more readable and less error-prone, and allows your formula to adapt as your data changes naturally. 

Explore how to use Excel structured references to streamline your workflow by simplifying formula writing, enhancing your analysis, and ensuring your Excel functions work as your table structures change.

What is a structured reference in Excel?

A “structured reference” is a way to use names and formula syntax to reference Excel tables. They work like regular cell references (e.g., A1:A10) but are easier to understand and only work with tables. Plus, referencing a column name rather than a cell range makes the formula dynamic, meaning it automatically adjusts to the data you enter. This keeps your analysis current as you gather or edit your data.

For example, you might have a SalesData table with a “Number of Sales” column spanning rows C2:C15. A general Excel formula to sum the total in the “Number of Sales” column would be:

=SUM(C2:C15)

With a structured reference, you would write this as:

=SUM(SalesData[Number of Sales])

Why use structured references?

Structured references offer several benefits related to formula writing and maintainability. However, deciding whether learning a new syntax is worthwhile will depend on your project priorities. Structured references are an excellent choice for your Excel table if you want to:

• Build spreadsheets that automatically expand with new data

• Simplify formula writing by referencing column headers and table names directly

• Reduce errors due to manual cell range adjustments

What are the 3 types of cell references in Excel?

Excel's three types of references are relative, absolute, and mixed. Imagine you have a formula that refers to C1. If you copy this formula from C2 to A2, the formula will adjust its reference to A1. This is a relative reference. 

If you then alter the formula to $C$1, you are using a “$” to make the row and column absolute references. If you copy it from C2 to A2, the formula will still refer to C1. This is an absolute reference.

Finally, if you type something like C$1, the column is relative and the row is absolute. This means that if you copy it from C2 to A3, the reference will be A1. This is a mixed reference.

How to create and use Excel structured references

Creating an Excel structured reference requires you to create an Excel table and reference it correctly within your formula. To do this, follow these steps.

1. Convert a data set into an Excel table.

First, you will need to format your cells into an Excel table. To do this, select a cell within your data and select Home > Format as Table. Next, choose a table style and set your cell range in the Create Table dialog box. Make sure your table has headers. Then select OK.

Once you do this, Excel assigns a name to the table and to each column header. You can rename your table and headers as desired.

2. Understand structured reference syntax.

In general, your structured reference will include the format:

=TableName[ColumnName]

You’ll put this format inside the functions you are interested in. Other syntax that may be helpful include:

• #All: References the entire table

• #Data: References just the data rows

• #Headers: References just the header row

• #This Row: References just the cells in the formula row

• @: References the current row in a calculated column

One of the keys to choosing the proper syntax is understanding qualified and unqualified references. Using [@Column] is an unqualified reference, adjusting per row. Using TableName[Column] is a qualified reference, meaning it refers to that entire column regardless of what cell you type the formula in. 

3. Use your structured references in formulas.

Once you understand how to create your structured reference, you can start putting it in functions. Examples might include:

=SUM(SalesData[Price]) 

This would provide the sum of the “price” column in a table.

=COUNTA(SalesData[[#All],[Number of Sales]])

This would count the number of non-blank cells, including the column header, in the Number of Sales column.

=[@Price]*[@Number of Units]

This would multiply the Price and Number of Units values from the current row only and populate the cell. This is useful if you want to create calculated columns with your data.

Advanced structured reference techniques: XLOOKUP

Once you master the basics, you can expand to more applications for your structured references. One example is XLOOKUP, a function that allows you to find things in your table or range by row. For example, you could look up the price of an item by its product ID or the email of an employee with their employee ID number.

If you were using the original XLOOKUP syntax, you might write:

=XLOOKUP(A1, B2:B10, D2:D10) 

This would search B2:B10 to find the lookup value of A1, then return the matching value in column D. To make this more intuitive, you could use structured references to write something like:

=XLOOKUP([@ProductID], InventoryTable[ProductID], InventoryTable[Price]) 

This would use @ProductID to reference the current row of the ProductID column, then use InventoryTable[ProductID] to search the column for a match, then use InventoryTable[Price] to return the corresponding price for the matching product ID. 

Related tasks: Named ranges

If you don’t want to use structured references but want to improve the readability of your functions or perform actions on your table columns, you can take a few alternative routes. Consider the following options. 

Use named ranges instead of structured references.

You can name your range by selecting the cells and then going to Formulas > Create from Selection. From here, you can choose the header location and name your range. Next, press OK. Once you do this, you can enter the name into your formulas instead of the cell locations.

Use summary functions.

Without writing functions, you can use built-in Excel functions to manipulate and analyze your data. Once you build your table, choose a cell at the bottom of the column you want to explore, click the Design tab, and then check Total Row. You will then have a drop-down menu where you can choose functions like sum, average, count, min, max, and so on.

Structured reference limitations and solutions

While structured references can make your formulas more dynamic and easier to read, it’s essential to be aware of specific limitations and how to address them. Below are common challenges and ideas for how to solve or mitigate them:

• You’re returning #REF! with external workbooks: Structured references break in other workbooks if you close your source workbook. Ensure it’s open while you are in alternate workbooks.

• Your formulas aren’t working when converting between ranges and tables: If you convert a table to a range, your structured reference formulas will automatically become cell references. If you convert the range back to a table, your structured reference formulas won’t automatically update. You’ll have to adjust manually to avoid disrupting your workflow.

• Your syntax leads to errors: You can use Formula AutoComplete to avoid mistakes. This ensures you build your structured references correctly and can help you identify where the syntax you entered was incorrect.

• You’re copying formulas to other sheets and getting unexpected results: Structured references don’t always behave as expected when copied across workbooks, especially with unqualified references like [@column]. To mitigate this, use fully qualified references like Table[@Column].

Summary of steps

To create structured references, you must have tabular data in an Excel sheet. Once you have this, you can follow these simple steps:

1. Convert your data set into an Excel table.

2. Reference structured reference syntax to build your formula.

3. Insert the formula within your functions.

Learn more about structured references in Excel on Coursera

Structured references allow you to write functions readably, automatically update cell ranges, and reference tables across workbooks. To continue learning more exciting Excel functions and how you can apply them in your workflow, you can take courses and earn Professional Certificates on Coursera. The Excel Skills for Data Analytics and Visualization Specialization is a great place to start, providing a pathway to use advanced Excel functions, create creative visualizations, and explore powerful automation features.