How To Lock Formulas In Excel Using $

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

• Relative Prison cell Reference
• Accented Cell Reference
• Mixed Jail cell Reference

You can learn more than about cell references from hither.

Past default, all the cell references are relative.

To lock a cell upwardly in Excel formula means, converting a Relative Cell Reference into an Absolute Cell Reference or a Mixed Cell Reference.

To Lock a Cell up in Formula

Before learning nigh how to lock a cell in an Excel formula, let'southward acquire briefly well-nigh the Accented Cell Reference and the Mixed Cell Reference.

Reminder:

Jail cell Address comprises letter(due south) followed by a number where the letter of the alphabet(due south) stand for(due south) the cavalcade number and the number represents the row number.

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

In the case of Mixed Cell Reference, either the cavalcade or the row is fixed and the rest tin can exist varied.

Let's take a clear understanding of the Absolute Cell Reference and the Mixed Cell Reference from the table beneath:

	Cavalcade	Row
Absolute Jail 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. Eastward.g. $E.
Lock a Row: Assign Dollar Sign ($) before the row number. Due east.grand. $5.

How Absolute Cell Reference looks like: It will look like $Eastward$5 for cell E5.
How Mixed Cell Reference looks like: It will look like either $E5 or E$v for cell E5.

Download the Practice Workbook

In this practice workbook, nosotros've tried to summate the speed of lite at different kinds of mediums such every bit 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 Low-cal at a Specific Medium = Refractive Alphabetize of that Medium * Speed of Light in Vacuum

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 summate the speed of calorie-free for water, ice and diamond we must lock the cell references in the multiplication formula.

As locking up the cell references in this detail example is mandatory, we volition testify you how many means you can lock the jail cell references in Excel formula.

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

2 Means to Lock a Cell in Excel Formula

We accept come up with 2 unproblematic ways that you lot tin utilise to lock a cell in Excel formula. Without further discussion allow'southward learn them one by one:

1. Assigning Dollar Sign ($) Manually to the Prison cell References

Now we know that we can lock upward a specific jail cell by assigning Dollar Sign ($) before the cavalcade and row number. Allow's go through the whole process stride by step:

Step-1:

• Let's outset summate the speed of light for the Water medium.
• Select cell C10 to store the calculated value.
• BlazonThese are now relative jail cell references.

Pace-ii:

• Assign Dollar Sign ($) earlier all the row and column numbers like this:
• Printing the ENTER push button.

---

Farther Readings:

• Difference Betwixt Absolute and Relative Reference in Excel
• What is and How to Do Accented Jail cell Reference in Excel?

---

two. Using the F4 Hotkey

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

• For at present, let's calculate the speed of light for the Ice medium.
• Select cell D10 to store the calculated value.

Step-2:

• Type "=" beginning.
• At present, this is a crucial point:

• Blazon B6 and and then press the F4 key.
• Type "*".
• Type D9 and so printing the F4 key.

• Press the ENTER push button.

Additional Tips

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

A. Toggle from Relative to Accented Cell Reference

For instance, you are working currently with the Relative Prison cell Reference and want to switch to the Absolute Jail cell Reference. To exercise so:

• Select the Cell Reference in the Formula Bar.

• Press the F4 central and you are done.

B. Toggle from Absolute to Relative Cell Reference

• Again press the F4 key. The row numbers are locked upward at present.

• Press the F4 fundamental once more to lock the cavalcade number from the row number.

C. Toggle back to Relative Cell Reference

• Just printing the F4 central once over 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.

Determination

In this web log post, 2 methods to lock a cell in an Excel formula have been discussed with examples. The kickoff method is nearly assigning the Dollar Sign ($) manually before the cavalcade and the row number. The second method is to use the F4 hotkey every bit the shortcut to lock up a cell. You are recommended to practice them both forth with the given practice workbook and discover the method best suits your cases.

---

Further Readings:

• Reference Another Canvas in Excel (3 Methods)
• Excel Canvas Proper name in Formula Dynamic (3 Approaches)

Source: https://www.exceldemy.com/lock-a-cell-in-excel-formula/

Posted by: oakescamed1949.blogspot.com

Share this post