How to Create a Structured Reference in ExcelLearn how to create a qualified or unqualified structured reference in Exel
In Excel, if you want to make a reference to a cell or range of cells, you’ll have to specifically refer to them.
For example, if I want to make a reference to cell A1, I need to enter the formula =A1.
Now, if I want to make a reference to the cells A1 down to A26, I need to enter the formula =A1:A27.
However, things are a bit different when you’re making a reference to a cell or columns in an Excel table.
When you refer to a table in Excel, rather than making a simple reference, you instead make a structured reference.
In this article, I’ll be explaining to you what a structured reference is and what it does.
We’ll also be discussing the two types of structured reference: unqualified structure reference, and qualified structured reference.
By the end of the article, you should have an understanding of what a structured reference is, and thus, you’ll be able to make use of it.
Let’s get started.
What is a Structured Reference (in Excel)?
You automatically make a structured reference in Excel when you refer to a table or a part of a table (via a formula).
What’s neat about a structured reference is that when you enter a formula that usually applies to only one cell, it will instead automatically apply to the rest of the column.
For example, here’s a comparison of the usual reference and a structured reference:
In both illustrations above, I only referred to two cells.
When I press the Enter key, the usual cell reference should only return the result in the cell where I entered the formula.
But when I press the Enter key to execute the formula with the structured reference, it will automatically apply to the rest of the column:
With a structured reference, you can easily refer to apply a formula to multiple cells without having to do the usual copy-pasting.
It also makes copying data to other parts of the Excel sheet quick and easy.
Structured references are dynamic. This means that if there changes are made to the referenced data, the structured reference will automatically update.
They’re also easier to understand, provided the table has column headers.
Creating a Structured Reference in Excel
To create a structured reference in Excel, you’ll first need a table.
Suppose that we have the following dataset:
We need to convert this dataset into an Excel table.
To do so, select the dataset:
While the dataset is selected, press the keyboard shortcut Ctrl + T.
A Create Table window should pop up.
There will be a textbook where we can select the data to insert into the table.
But since we have selected the dataset already, this textbox should be already filled in.
Next, since our dataset has headers, we’ll check the box before “My table has headers”.
Press the OK button after doing so.
And there we have it. The dataset has been successfully converted into an Excel table.
Let’s rename the table to “Membership”.
To do so, select the table, and then open the Table Design.
There should be a section on the ribbon that allows you to edit the name of the table (found on the left side).
Now that there is an Excel table, let’s start creating a structured reference.
How to Create an Unqualified Structured Reference
To create an unqualified structured reference, the formula that uses the reference must be entered within the table.
Additionally, the data being referred to must be in the same table.
For example, let’s say that we want to calculate the percentage of inactive members per branch.
To do so, we’ll have to divide the number of inactive members by the total number of members.
Since we’re aiming to create an unqualified structured reference, the formula to do the calculation must be within the table.
So let’s add a new column to the table. To do so, just select the cell next to the header of the rightmost column of the table.
In this cell, we’ll enter %Inactive. This will be the header of the new column.
When we press the Enter key, the new column should be automatically formatted the same way as the other columns of the tables.
This ensures that the new column is a part of the table.
With this new column, we’ll be making an unqualified structured reference.
Select cell D2, and then press the equals key (=) to start a formula.
Then, click on cell C2, press the slash key (/) then click on cell B2. This should divide the value in C2 by the value in B2.
But rather than the formula looking like this: =C2/B, it will look like this instead:
=[@[Inactive Members]]/[@[No. of Members]]
This ensures that our formula is using an unqualified structured reference.
Press the Enter key to execute the formula. It should automatically apply to the rest of the column:
We have successfully created an unqualified structured reference.
How to Create a Qualified Structured Reference
When you create a structured reference outside of a table, it becomes a qualified structured reference.
Unlike an unqualified structured reference where we don’t have to indicate the table name, we have to when using a qualified structured reference.
For example, let’s say that we want to calculate the total number of members all across the branches.
To do so, we need to add all the values in “No. of Members” column of the table.
Let’s have the total appear in cell B10. In this cell, we will enter =SUM(Membership[ first.
This is the first part of the qualified structured reference. Notice that when we entered [, a list of the table’s columns appeared.
This allows us to make a structured reference to a particular column of the table.
Since we want to calculate the total number of members, double-click on “No. of Members”.
Then, close the formula by typing ]).
The formula should look like this:
=SUM(Membership[No. of Members])
Press the Enter key to run the formula.
We have successfully created a qualified structured reference.
Qualified and Unqualified Structured Reference Differences
To summarize the differences between the qualified and unqualified structured references, here’s a list:
- A structured reference that is created within a table is an unqualified structured reference. The data being referenced must be within the same table. A structured reference that is created outside a table is a qualified structured reference
- You don’t have to indicate the table name when creating an unqualified structured reference. On the other hand, you need to indicate the table name when creating a qualified structured reference.
Conclusion
In this article, I was able to show you what a structured reference is in Excel and how you could use it.
Using a structured reference makes copying data or running formulas quick and easy.
If you deal with a lot of data in Excel, you might want to use structured references more often.
I hope that you’re able to use you learnings here in your future endeavors.