Learn how structured references in Excel simplify your formulas, improve data management, and enhance productivity. A complete guide to using structured references effectively.
Excel is acclaimed for its data processing capabilities, and specifically, it offers the best features when creating functional and powerful tables. However, one feature does not get nearly as much attention as it deserves—structured references. These references make working with Excel tables easier, making formulas easier and more flexible to use.
Let’s explore structured references, how to use them within and outside tables, and why they should become part of your Excel toolkit.
What Are Structured References?
Structured references are another way to reference data in Excel. They are widely accessible in Excel 2016 and later versions, including Excel 365 versions. Unlike normal address labels, which use row and column identifiers (for example, A1 or C10), we use table and field labels.
For instance, the formula:
=SUM([@[Daily profit]]*7)
multiplies the values in the column named “Daily profit” by seven for each row, creating a dynamic and clear reference.
By applying appropriate structured references, we can make our Excel formulas more readable and make the possible changes in data layout, not a problem.
Using Structured References Within a Table
To begin using structured references, you first need to convert your data range into a formatted Excel table:
- Add Column Headers: Ensure your dataset has headers for each column. If headers are missing, Excel will default to “Column1,” “Column2,” and so on, which can make your formulas harder to interpret.
- Format as a Table: Open a range in Excel and go to the Home tab. Then right-click any cell within your data and click “Format As Table.” You can select any design that you prefer.
- Confirm Table Settings: Verify the selected range in the Create Table dialog box and check the “My Table Has Headers” option before clicking “OK.”
Once the data is in a table format, you can use structured references in formulas. For example, to calculate weekly profits, we would:
- Select the cell where the calculation begins (e.g., C2).
- Type =SUM( and then click the first cell in the “Daily profit” column. Excel automatically inserts the structured reference [[@[Daily profit]]].
- Complete the formula with *7) and press Enter.
Excel copies the formula down the entire column, which automatically recalculates the sum in the next row. If we bring in more data, if a new row is added, the formula adjusts on its own, relieving ourselves of the burden of having to correct it.
Using Structured References Outside a Table
Structured references are not only within table formulas; they can also refer to external table data. Assuming there is a table named “Profits” and a table containing three attributes named “Site,” “Daily profit,” and “Weekly profit” in cell E4, we want to nest an XLOOKUP formula so that it is used to find the weekly profit of a specific site.
Here’s how:
- Name the Table: Choose any cell, and in the “Table Tools” tab, click the “Table Design” subtab and type the name you need (for example, “Profits”) into the Table Name field.
- Write the Formula: Start by typing =XLOOKUP(E2 in cell E4.
- Add Lookup and Return Arrays: Select the entire column of Site as the lookup array and Weekly profit as the return array. Excel converts these selections into structured references, resulting in a formula like: =XLOOKUP(E2,Profits[Site],Profits[Weekly profit])
Press Enter, and Excel retrieves the desired data. Structured references make this process seamless and adaptable, even if table data changes.
Why Use Structured References?
1. Improved Readability
Structured references are an upgrade on codes that only use letters and numbers to refer to specific cells in a worksheet. Instead of deciphering =SUM(A2:A10), we can instantly understand =SUM(Profits[Daily profit]).
2. Automatic Updates
If new rows or columns are added to the table or any modification is made, then the structured references made for the data adjust themselves. For instance, let’s add a new site; due to the chosen type, all the formulas associated with it will have to be recalculated by Excel on its own.
3. Dynamic Adaptability
Renaming the table columns doesn’t affect structural references. If I change the worksheet’s title from “Weekly profit” to “TOTALS”, Excel automatically recalculates all the formulas beneath it. This flexibility saves time and eliminates the opportunity for mistakes.
4. Consistency Across Workbooks
Consistencies are maintained in terms of values from the structured references even though tables expand or data alters. This reliability comes in handy, especially for businesses that involve analyzing a large database of data.
5. Efficient Memory Usage
Structured references consume less memory compared to direct references, making Excel spreadsheets faster and more efficient.
Best Practices for Structured References
To maximize the benefits of structured references, follow these tips:
- Always Name Tables: Make sure that the names you assign to the tables are descriptive to distinguishon between tables in the work book. Some things not to do include, naming your items with generic names such as Table 1.
- Stick to Naming Rules: Table names begin with letter, underscore or backslash; they cannot be the same as cell addresses, such as A1.
- Use Consistent Headers: Structured references should be as easily intelligible as absolute references due to clear column names.
Conclusion
Structured references are very useful in Excel as they improve the spreadsheet’s appearance, making it easy to create formulas for large data sets and self-update when closed. Modern software development, for example, offers the chance to use ‘structured references instead of cell references to not work as slowly and make as many mistakes as before.
From using structured references to manage data within a table or linking tables to formulas from other tables, structured references are a genius ride for anyone willing to unleash the power of Excel. Solve your business’ operational bottlenecks today by implementing them; they offer great value to your data.
Also Read: You Need to Stop Posting About Your Vacation Online—Here’s Why
About the Author
