In Excel, we use formulas to perform various kinds of operations incorporating cell references, operators, and functions. Speaking about Cell Reference, it can be of three kinds.
You can learn more about cell references from here.
By default, all the cell references are relative.
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.
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:
|Absolute Cell Reference||Fixed||Fixed|
|Mixed Cell Reference||Fixed/Varied||Fixed/Varied|
Mechanism to Lock a Cell up
Lock a Row: Assign Dollar Sign ($) before the row number. E.g. $5.
How Absolute Cell Reference looks like: It will look like $E$5 for cell E5.
How Mixed Cell Reference looks like: It will look like either $E5 or E$5 for cell E5.
Download the 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:
In the dataset, the speed of light in vacuum, refractive indices of water, ice, and diamond all are unique and located at different cells. To calculate the speed of light for water, ice and diamond we must lock the cell references in the multiplication formula.
As locking up the cell references in this particular example is mandatory, we will show you how many ways you can lock the cell references in Excel formula.
You are recommended to download the practice workbook and practice along with it.
2 Ways to Lock a Cell in Excel Formula
We have come up with 2 simple ways that you can use to lock a cell in Excel formula. Without further discussion let’s learn them one by one:
1. Assigning Dollar Sign ($) Manually to the 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:
- Let’s first calculate the speed of light for the Water medium.
- Select cell C10 to store the calculated value.
- TypeThese are now relative cell references.=B6*C9
- Assign Dollar Sign ($) before all the row and column numbers like this:=$B$6*$C$9
- Press the ENTER button.
- What is and How to Do Absolute Cell Reference in Excel?
- Reference Another Sheet in Excel (3 Methods)
- Example of Mixed Cell Reference in Excel (3 Types)
- How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)
- Excel VBA: R1C1 Formula with Variable (3 Examples)
2. Using the 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:
- For now, let’s calculate the speed of light for the Ice medium.
- Select cell D10 to store the calculated value.
- Type “=” first.
- Now, this is a crucial point:
- Type B6 and then press the F4 key.
- Type “*”.
- Type D9 and then press the F4 key.
- Press the ENTER button.
You can easily toggle in 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:
- Select the Cell Reference in the Formula Bar.
- 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.
- Press the F4 key again to lock the column number from the row number.
C. Toggle back to Relative Cell Reference
- Just press the F4 key once again.
Things to Remember
- Assign Dollar Sign ($) before the row and the column number to lock a cell.
- Use the F4 hotkey to lock a cell instantly.
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.