Here are some quick steps to lock cells 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 curser 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.
Lock cells in Excel formula (make cell constant))

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.  

Lock cells in Excel formula (make cell constant))

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:

Lock cells in Excel formula (make cell constant))

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

Lock cells in Excel formula (make cell constant))

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:

Lock cells in Excel formula (make cell constant))

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:

Lock cells in Excel formula (make cell constant))

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.
Lock cells in Excel formula (make cell constant))
  • Now enter the formula to calculate the desired result. In this example, we’ll enter =C5*D5*E2
Lock cells in Excel formula (make cell constant))
  • 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
Lock cells in Excel formula (make cell constant))
  • 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.
Lock cells in Excel formula (make cell constant))

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 Lock cells in a formula to make it constant when copying a formula.