How to Lock a Structured Reference in Excel

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.

example of structured reference

 

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. 

structured reference is not absolute

 

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.

we need absolute structured references to maintain the same year

 

In the table below, we solved this issue by converting our structured reference to an absolute reference.

change the syntax to lock  a structured 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:

  1. 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.
    select table to convert into a excel table
  2. Type the keyboard shortcut Ctrl + T to open up the Create Table dialog box. Click OK to convert your selected range into a table.
    use Ctrl + T shortcut
  3. Your range may change in appearance. For example, each column should have its own filtering options.
    excel table contains filters for each column
  4. In the Table Design tab, you may choose to give your table a relevant name. In this example, we’ve named our table ‘ProductSales’.
    change name of table
  5. 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 SUM function to get the total.
    use structured reference syntax
  6. We can convert our structured reference into an absolute reference by using the syntax seen below.
    get SUM of locked structured reference in Excel
  7. Using absolute references, we can copy our formula to multiple columns without changing the table column.
    lock a structured reference

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.

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like