Shajratul Alam Towhid
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.
We’ll now work on the following data set.
- Firstly, select the cells of B4:G9.
- Secondly, press the CTRL + T
- 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].
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.
- 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
- 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)
- 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.
- Thirdly, select cells D5:D8 and use Fill Handle rightwards to the F Column. The output is like this.
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
- 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.
