How to Lock a Cell in Excel Formula (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you 2 easy methods of how to lock a cell in an Excel formula. You can quickly use these methods to add absolute references to your data very easily. Throughout this tutorial, you will also learn some important Excel tools and functions which will be very useful in any Excel-related task.

In Excel, we use formulas to perform various kinds of operations incorporating cell references, operators, and functions. Speaking about Cell Reference can be of three kinds.

  • Relative Cell Reference
  • Absolute Cell Reference
  • Mixed Cell Reference

To Lock a Cell Up in Formula

Before learning about how to lock a cell in an Excel formula, let’s learn briefly about the Absolute Cell Reference and the Mixed Cell Reference.

Reminder

Cell Address comprises letter(s) followed by a number where the letter(s) represent(s) the column number and the number represents the row number.

In the case of Absolute Cell Reference, both the column and the row are fixed i.e. they are locked up.

In the case of Mixed Cell Reference, either the column or the row is fixed and the rest can be varied.

Let’s have a clear understanding of the Absolute Cell Reference and the Mixed Cell Reference from the table below:

Column  Row
Absolute Cell Reference  Fixed  Fixed
Mixed Cell Reference  Fixed/Varied  Fixed/Varied

Mechanism to Lock a Cell up

Lock a Column: Assign Dollar Sign ($) before the column number. E.g. $E.
Lock a Row: Assign Dollar Sign ($) before the row number. E.g. $5.


How to Lock a Cell in Excel Formula: 2 Easy Ways

We have come up with 2 simple ways that you can use to lock a cell in an Excel formula. Without further discussion let’s learn them one by one:

1. Assigning Dollar Sign ($) Manually to Cell References

Now we know that we can lock up a specific cell by assigning Dollar Sign ($) before the column and row number. Let’s go through the whole process step by step:

Step-1:

  • Let’s calculate the speed of light for the Water medium.
  • First, select cell C9 to store the calculated value.
  • Then, type
    =B5*C8
  • Consequently, these are now relative cell references.

how to lock a cell in excel formula

Step-2:

  • Assign Dollar Sign ($) before all the row and column numbers like this:
    =$B$5*$C$8
  • Next, press the ENTER button.

how to lock a cell in excel formula manually

Read More: How to Lock Certain Cells in Excel


2. Using F4 Hotkey

You can use the F4 hotkey to toggle in between Relative, Absolute, and Mixed Cell References. Assigning Dollar Sign ($) manually before each column and the row number is a time-consuming process. While this method is the ultimate lifesaver. Here are the steps to follow:
Step-1:

  • To begin with, let’s calculate the speed of light for the Ice medium.
  • First, select cell D9 to store the calculated value.

Step-2:

  • Then, type “=” first.
  • Now, this is a crucial point:
  • Next, type B5 and then press the F4 key.
  • Now, type “*”.
  • Then, type D8 and then press the F4 key.

how to lock a cell in excel formula using F4

  • Finally, press the ENTER button.

Read More: Protect Excel Cells But Allow Data Entry


Additional Tips

You can easily toggle between Relative, Absolute, and Mixed Cell References by pressing the F4 hotkey.

A. Toggle from Relative to Absolute Cell Reference

For example, you are working currently with the Relative Cell Reference and want to switch to the Absolute Cell Reference. To do so:

  • Firstly, Select the Cell Reference in the Formula Bar.

  • Then, press the F4 key and you are done.


B. Toggle from Absolute to Relative Cell Reference

  • Again press the F4 key. The row numbers are locked up now.

  • Now, Press the F4 key again to lock the column number from the row number.


C. Toggle back to Relative Cell Reference

  • Lastly, just press the F4 key once again.

Read More: How to Protect Excel Cells from Being Edited


Things to Remember

  • Assign a Dollar Sign ($) before the row and the column number to lock a cell.
  • Use the F4 hotkey to lock a cell instantly.

Download Practice Workbook

In this practice workbook, we’ve tried to calculate the speed of light at different kinds of mediums such as Water, Ice, and Diamond. Each of the media has its corresponding Refractive Indices. So, the formula to calculate the speed of light at different mediums is:

Speed of Light at a Specific Medium = Refractive Index of that Medium * Speed of Light in Vacuum

In the dataset, the speed of light in a vacuum, and the refractive indices of water, ice, and diamond all are unique and located in different cells. To calculate the speed of light for water, ice and diamond we must lock the cell references in the multiplication formula.

You are recommended to download the practice workbook and practice along with it.


Conclusion

In this blog post, two methods to lock a cell in an Excel formula have been discussed with examples. The first method is about assigning the Dollar Sign ($) manually before the column and the row number. The second method is to use the F4 hotkey as the shortcut to lock up a cell. You are recommended to practice them both along with the given practice workbook and find the method best suits your cases.


Related Articles


<< Go Back to Protect Excel Cells | Excel Protect | 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.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo