Structured Reference is a special feature of Excel that references tables. It makes our formulas easier to understand and automatically adjusts whenever there are changes in our data.
Figure 1. Final result: Structured Reference
How to create a structured reference
A structured reference uses table names instead of the usual cell references. In order to create a structured reference, we follow these steps:
- Select our data and create a table by pressing Ctrl + T
Figure 2. Create Table dialog box
- In the Create Table dialog box, select the check box for “My table has headers” and click OK. A table will be created, using the default table style.
Figure 3. Output: Creating a table
- Click anywhere in the table to display the Design tab on the ribbon
- Change the table name to “Production”
Figure 4. Changing the table name in Design tab
- Let’s create a formula referring to our table. In cell E2, enter “%Rejects”
Figure 5. Adding a column
Note that as we press Enter, the new column is automatically formatted similar to the rest of the table.
Figure 6. Output: Column automatically formatted
- In cell E3, enter the equal sign “=”, then press D3.
Instead of D3, Excel returns the structured reference [@Rejects] referring to the column for Rejects. The symbol “@” means current row.
Figure 7. Column name as reference
- Now type a forward slash “/” then a left bracket “[“. A list of structured references appear in a drop-down list.
Figure 8. List of structured references
- We want to divide Rejects by the Output. We complete the formula in E3 by entering:
=[@Rejects]/[@Output]
Figure 9. Output: Create a structured reference
As we press Enter, the formula is automatically copied down the cells in column E. Another important feature when using structured reference is that the formula along a column has exactly the same references “[@Reject]” and “[@Output], since we are referencing the table and columns, not the individual cells.
Qualified and unqualified structured reference
A formula that includes a structured reference can be fully qualified or unqualified. When we calculate within the table, we can use an unqualified reference like the example above. For an unqualified reference, there is no need to indicate the table name.
Figure 10. Example of unqualified reference
When we enter a formula outside the table, we use a qualified reference through these steps:
- In cell C9, enter the first part of the formula =sum(
- Then click the cells C3:C7. Excel will automatically rename the range C3:C7 as “Production[Output]”.
Figure 11. Entering the formula
The result in C9 is the total output for all lines, as implied by the formula “=SUM(Production[Output])”. Note that a qualified reference makes use of both the table name “Production” and column name “Output”.
Figure 12. Output: Qualified Reference
By using structured references, we are able to better understand the formulas. They are also very dynamic, wherein the formulas automatically adapt to any change in the table. Inserting a column within the table, or changing the name of column headers won’t break the formulas.
In the example below, we have changed the header name from “Rejects” to “Defects” and inserted a column between them. The table still works perfectly fine.
Figure 13. Output: Structured reference adapting to changes
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment