How to Use Structured References in MS Excel
How to use structured references in MS Excel is a skill that you should develop to maximize your productivity when using MS Excel.
MS Excel has three types of ways to refer to a data cell:
- Relative reference (like B1)
- Absolute reference (like $B$1)
- Structured reference (you need to use a table)
“Why should I use structured reference?”
Imagine you have data on a worksheet that you’d like to access from another worksheet. How would you do it?
You cannot use a relative or absolute reference since they refer to the data within the same worksheet. That’s why you use a structured reference since it is indexed to a table name, no matter where the table is.
First thing, though, you need to wrap your data inside a table and then give table a name that makes sense.
“How do I wrap a table around my data?”
Imagine you have the following fictitious data, in a non-table format:
Click your cursor inside any data cell. Then select Insert > Table to display the Create Table dialog box:
As you can see, Excel is smart enough to detect that you have column headers. That option is automatically selected.
Click OK to insert your data inside a table:
This table has default style and colors but you can change that easily in any way you want.
“OK. I inserted my data inside a table. What now?”
Let’s consider the following sample table on Sheet 1:
Now we’ll access this data in Sheet 2 though structured references and you’ll understand how it works.
Let’s say, we’d like to calculate the amount of total sales in the table we’ve named “Sales.”
This is the formula we need:
=SUM(Sales[Price]) or =SUM(TableName[ColumnName])
See how easy it is with structured reference?
Answer is $21.49.75.
If we want to count the number of total items sold, the formula with structured references would be:
=COUNTA(Sales[Item])
Answer is 31.
If we want to know the maximum sales price for any one item:
=MAX(Sales[Price]) which is $365.98.
Then we can do any additional computations we like by utilizing those values, like calculating the average price per transaction by dividing B5 by B6 (and using relative references).
Here the important thing to remember is we are doing all these calculation on Sheet 2 by reaching over and grabbing data from Sheet 1, thanks to structural references.
