This guide will explain how to lock a structured reference in Excel.
The structured reference syntax is a more readable format that can make working with tabular data much easier.
Structured references are cell references written in a specific syntax. They require you to set up an Excel Table in your worksheet.
Suppose you have an Excel dataset that keeps track of monthly sales. Instead of using SUM(B2:B10) to get the total of column B, we can use a structured reference instead.
After converting our dataset into an Excel table labeled Monthly_Sales, we can refer to an entire column by its header:
=SUM(Monthly_Sales[Amount])
References to single columns in a table are still relative references. If we try to copy the formula to another column, the column will also change.
This guide will explain how we can lock a structured reference by modifying the syntax used. After changing the syntax of our structured reference, we should now have an absolute reference that does not change when the formula is copied to multiple columns.
Now that we have a grasp on when to lock a structured reference in Excel, let’s learn how to do it on an actual sample spreadsheet.
A Real Example of Locking a Structured Reference in Excel
The following section shows how locking a structured reference can help build our table. We will also go into detail about the formulas and tools used in these examples.
First, let’s take a look at a real example of a structured reference in an Excel spreadsheet.
In the example seen below, we used a structured reference to get the sum of sales in 2019.
To get the sum of 2019 sales for all products, we’ll use the following formula:
=SUM(ProductSales[2019])
If we try to copy the formula to the adjacent cell, we’ll get the sum of 2020 sales instead.
While this can be useful for getting the totals for each year, we may want to use an absolute reference instead. For example, the spreadsheet below wants to get certain percentages of each year’s total sales. If we try to fill out the table, we’ll switch to different years within each row.
In the table below, we solved this issue by converting our structured reference to an absolute reference.
To get the values in the second column, we must use the following formula:
=SUM(ProductSales[[2019]:[2019]])
In general, we can use an absolute reference by using the following syntax:
=table_name[[column_name]:[column_name]]
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
Want to try using an absolute structured reference in your spreadsheet? Head over to the next section to read our step-by-step breakdown on how to do it!
How to Lock a Structured Reference in Excel
This section will guide you through each step needed to start locking a structured reference in Excel. You’ll learn how we can change the syntax of our reference to keep the formula from changing when using the Fill Handle tool.
Follow these steps to start using locked structured references in Excel:
- Before working with structured references, we must create an Excel table. Select the range you want to use as an Excel table. In this example, we’ll be using the range B2:F6.
- Type the keyboard shortcut Ctrl + T to open up the Create Table dialog box. Click OK to convert your selected range into a table.
- Your range may change in appearance. For example, each column should have its own filtering options.
- In the Table Design tab, you may choose to give your table a relevant name. In this example, we’ve named our table ‘ProductSales’.
- To use the structured reference format, type the name of the table followed by the column you want to refer to. The column name should be enclosed in square brackets. Since the output of the structured reference is a range, we can add it as an input to a
SUMfunction to get the total.
- We can convert our structured reference into an absolute reference by using the syntax seen below.
- Using absolute references, we can copy our formula to multiple columns without changing the table column.
These are all the steps needed to lock a structured reference in Excel.
This step-by-step guide should be your best introduction to locking structured references in Microsoft Excel. We’ve shown how you can make a simple change to the syntax to make your structured references an absolute references as well.
This function is just one example of the many Excel functions that you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one appropriate for your use case.
Subscribe to our newsletter, and you’ll get exclusive access to the latest spreadsheet guides and tutorials from us.
