How to Lock Formula in Excel with Dollar Sign

Step 1: Open the Spreadsheet

Open the sheet you want to lock formulas in.

Step 2: Lock the Formula Reference

In this sheet we want to multiply the price with the units to calculate total units for every day.

Enter the formula for one row as shown below.

Step 3: Locking the Reference to Cell B1

Now, we want to lock the reference to the cell “B1” because we want it to stay same for all the rows. When the formula is dragged the value of “B1” cell should remain the same.

Place the cursor at the end of B1 which is entered in the formula shown in the cell “C4” in the below image.

Press “F4”. You can confirm that the reference to the cell “B1” is locked when you see dollar sign($) placed in front of “B1” in the formula. As shown in the image below, like “$B$1”.

Step 4: Preview Result

Press “Enter”, you will see that the value is been calculated.

Step 5: Drag the formula to apply it to all the rows

Fed up with unintentionally messing up your Excel formulas and causing chaos in your meticulously designed spreadsheets? Don’t worry! Mastering the skill of locking formulas is your solution. In this brief tutorial, we’ll delve into the techniques to protect your calculations from unintended alterations, ensuring that your Excel wizardry remains undisturbed. Let’s jump into the straightforward practice of locking formulas and ensuring the security of your data sorcery.

In this article, we will discuss how to lock and protect the formulas within the Excel sheet along with an example.