How to Lock a Structured Reference in Excel (2 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for how to lock a structured reference in Excel, then you are in the right place. While using Excel frequently we need to make tables and need to give structured references in the case of adding formulas. In this article, we’ll try to discuss how to lock a structured reference in Excel.


What Is a Structured Reference?

A structured reference, also known as a table reference, is a unique method for accessing tables and their components that substitutes table and column names for cell addresses.
Excel tables are extremely useful and efficient, and regular cell references cannot alter dynamically as data is added to or withdrawn from a table. Some of the key features of structured references are-

  • Resilient and automatically updated,
  • We can use it inside and outside of the table,
  • Formulas for calculated columns are Auto Filled.

How to Create Structured Reference

We need to first create a structured reference before locking it. We have made a dataset named Sales of Months for the purpose of creating structured references.

how to lock a structured reference in excel

We’ll now work on the following data set.

  • Firstly, select the cells of B4:G9.
  • Secondly, press the CTRL+T keys.

how to create structured reference

  • Eventually, a Create Table window will appear.
  • We’ll see that the selected cell references are in the Where is the data for your table segment. Also, you will get the My table has headers box is checked.
  • Then, click OK.

Consequently, we’ll see that the tabular sign is attached to every column header.

  • Suppose, we want to find out the sum of sales of various products in the month of June. Then, write the following formula in the C10 cell like this.
=SUM(Table3[June])

Here, Table3 is our table name. It is created automatically. Table3[June] is the structured reference here. Generally, we give cell references directly like C5:C9 when we don’t use the table format. But using the table we have to give structured references like the above.

  • Then, press ENTER to get the output as $31,700.
  • Now, use Fill Handle by dragging the cursor rightwards while holding the cursor on the right-bottom corner of the C10 cell.

Consequently, the outputs will be like this.

Eventually, we’ll see that the structured reference for the output of the D10 cell is Table3[July].


How to Lock a Structured Reference in Excel: 2 Useful Ways

Now we’ll see the ways to lock a structured reference. We can lock the structured references both outside and inside the table.


1. Lock Structured References in Excel Outside Table

To lock structured reference outside the table we’ll use the following dataset where we can see that the reference for the C10 cell is structured, and it is Table35[June].

Lock Structed References in Excel Outside Table

To block the structured reference, let’s follow the below steps.

Steps:

  • Firstly, write the formula in the D10 cell like this.
=SUM(Table35[[June]:[June]])

Here, we have used the reference as Table35[[June]:[June]]. It means the reference is June to June of Table35.

Lock Structed References in Excel Outside Table

  • Secondly, press ENTER to get the output. We can see that the output is $31,700 which is the same as the output in cell C10.

Lock Structed References in Excel Outside Table

  • Thirdly, use Fill Handle rightwards and we’ll see that all the outputs in cells E10:G10 are the same, which means structured reference is locked.

Read More: Applications of Absolute Structured References in Excel Table Formulas


2. Lock Structured References in Excel Inside Table

We can also lock structured references inside tables.

  • Firstly, write the following formula in the D5 cell like this.
    =[@Sales]*(Table7[[#Headers],[10%]]+1)

Lock Structured References in Excel Inside Table

  • Secondly, press ENTER.
  • Eventually, we’ll see that with this we have automatically filled the columns of D5:D8 because this is one of the characteristics of a table.

Lock Structured References in Excel Inside Table

  • Thirdly, select cells D5:D8 and use Fill Handle rightwards to the F Column. The output is like this.

Lock Structured References in Excel Inside Table

We’ll now lock the structured reference of the D Column as well as the E and F Columns.

  • To do this, fourthly, place the cursor on the D5 Change the formula by selecting cells C5:D8 instead of @Sales, like this.
    =Table7[[Sales]:[10%]]*(Table7[[#Headers],[10%]]+1

Lock Structured References in Excel Inside Table

  • Fifthly, replace 10% with Sales in the formula like this.

  • Sixthly, press ENTER to find the output like this.

Consequently, we have locked the structured reference now.

Read More: How to Use IF Function and Structured Reference in Excel


Download Practice Workbook


Conclusion

We can lock structured references easily if we study this article properly. Please feel free to comment below for further queries.


Related Articles


<< Go Back to Structured Reference | Table Formula | Excel Table | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

2 Comments
  1. You offer an outstanding resource of pertinent explanations and examples of Excel functions and tools that busy data analysts can immediately and directly apply to solving their technical challenges!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo