Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


Download Practice Workbook


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

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
  • Thirdly, click OK.

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

  • Fourthly, 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.

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

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].


2 Ways to Lock a Structured Reference in Excel

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

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 editing @Sales by selecting cells C5:D8 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


Conclusion

We can lock structured references easily if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy, a one-stop Excel solution provider, for further queries.


Related Articles

Towhid

Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo