How to Lock Cells in Excel formula (keeping a cell constant)

Here are some quick steps to lock cells in Excel using this formula: =C5*D5*E2. Say you want to lock cell E2 to remain constant as you copy the formula to adjacent cells.

1. First write the formula. =C5*D5*E2
2. Click E2 in the formula to place the cursor between E and 2.
3. Press F4 on your keyboard. This will insert the dollar ($) symbols in the formula.
4. Press the Enter key to apply the formula.
5. Using the fill handle, drag and copy the formula to the rest of the cells.

If you are rather looking for how to lock (or protect cells) to prevent users from making changes to them, click here.

Details

If you need a detailed explanation of the above scenario, just keep reading.

Often, you need to copy a cell content to other cells or range. This is common when you’re working with Excel formulas.

There may be times when you want to keep a cell constant in a formula (that’s constantly referring to a particular cell) when copying or moving cell formulas.

This trick can be done using absolute references. It lets you lock cells in Excel formulas to keep them constant.

To keep a cell constant in a formula using absolute reference, you add a dollar sign ($) to the reference. It can precede the column reference, the row reference, or both.  

Note: More often, you will find yourself using the $A$2 format of cell reference when creating formulas. The other two formats are used much less frequently.

How to lock or keep a cell constant

Now let’s illustrate with an example on how to lock cells in Excel formula.

You can download the example file below:

In the above worksheet, our task is to write a formula that will calculate the discount column given a discount rate of 9.5%.

Thus, the formula is going to be something like this:

=Unit Price * Quantity * Discount Rate

Below is the formula to calculate the discount for the first record (Product 1):

=C5*D5*E2

The above formula analysis has explained what the formula does exactly. It uses the 9.5% sales discount rate in cell E2 to calculate the discount for the product1 in column E.

See the screenshot again:

Since the formula is not using absolute reference for cell E2 that contains the discount rate, when you copy the formula to fill the rest of the cells, the discount rate cell will change as you copy, resulting to errors.

See screenshot:

To avoid this mess, you need to lock the cell and keep it constant when copying.

Below is how to do that:

• Select the Cell that will contain the formula. In this example, we’ll select cell E5.

• Now enter the formula to calculate the desired result. In this example, we’ll enter =C5*D5*E2

• Inside the formula, click the cell reference you want to lock (Cell E2), and press the F4 key. Excel will introduce the dollar ($) into your formula. You can also type the dollar ($) manually. The formula should now look like this: =C5*D5*$E$2

• Press Enter to apply the formula. The formula will calculate and display the result in the cell.
• Now, copy the formula down to the rest of the cells using the fill handle. Just drag the fill handle over the cells you wish to fill.

Now double click the filled cells one after the other and you’ll see that cell E2 is used throughout. This is referred to as Absolute cell reference in Excel. It Locks cells in a formula to make it constant when copying a formula.