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. Suppose you want to lock cell E2 so that it remains constant when you copy a formula to adjacent cells.

  1. First, write the formula. =C5*D5*E2
  2. Press E2 in the formula to place the cursor between E and 2.
  3. Press the F4 key on the keyboard. Allows you to insert dollar signs ($) into the formula.
  4. Press the Enter key to apply the formula.
  5. Drag the formula with the fill handle to the remaining cells and copy it.

If you’d rather see how to lock (or protect) cells to prevent users from making changes to them, click here.

rooms

If you would like a detailed explanation of the above scenario, read on
.

It is often necessary to copy the contents of a cell to other cells or ranges. This situation often occurs when you are working with Excel formulas.

It may happen that, when copying or moving formulas, a cell must keep a constant in the formula (this constant always applies to a specific cell).

This trick can be done with absolute references. Allows you to lock the cells in Excel formulas so that they remain constant.

To keep the cell constant in a formula that uses an absolute reference, a dollar sign ($) is added to the reference. It can precede a column reference, a row reference, or both.

alt=Lock cells in an Excel formula (make a cell a constant)) width=640 height=113 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/How-to-Lock-Cells-in-Excel-formula-keeping-a-cell.png />

Message: In most cases, you will use the cell reference format $A$2 when creating
formulas. The other two formats are used much less frequently.

Lock or save a permanent cell

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

You can download the sample file below:

alt=Lock cells in an Excel formula (make a cell a constant)) width=640 height=410 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618797877_642_How-to-Lock-Cells-in-Excel-formula-keeping-a-cell.png />

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

So the formula would go something like this:

= Innite price * quantity * discount percentage

Here is the formula to calculate the discount for the first item
(Product 1):

=C5*D5*E2

alt=Lock cells in an Excel formula (make a cell a constant)) width=545 height=212 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618797878_623_How-to-Lock-Cells-in-Excel-formula-keeping-a-cell.png />

The analysis of the above formula explains exactly what Formula
does. He uses the 9.5% discount rate in cell E2 to calculate the
discount rate for product 1 in column E.

Take another look at the screenshot:

alt=Lock cells in an Excel formula (make a cell a constant)) width=640 height=410 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618797877_642_How-to-Lock-Cells-in-Excel-formula-keeping-a-cell.png />

Because the formula does not use an absolute reference for cell E2
, which contains the discount rate, when you copy the formula to fill the rest of cells
, the cell containing the discount rate changes, leading to errors.

See the screenshot :

alt=Lock cells in an Excel formula (make a cell a constant)) width=640 height=412 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618797879_978_How-to-Lock-Cells-in-Excel-formula-keeping-a-cell.png />

To avoid this mess, lock the cell and keep it constantly on
during copying.

The following paragraphs describe how to proceed:

  • Select the cell that should contain the formula. In this example we select cell E5.

alt=Lock cells in an Excel formula (make a cell a constant)) width=640 height=411 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618797880_394_How-to-Lock-Cells-in-Excel-formula-keeping-a-cell.png />

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

alt=Lock cells in an Excel formula (make a cell a constant)) width=640 height=413 data-recalc-dims=1 data-ez= data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618797881_96_How-to-Lock-Cells-in-Excel-formula-keeping-a-cell.png />

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

alt=Lock cells in an Excel formula (make a cell a constant)) width=626 height=422 data-recalc-dims=1 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618797882_141_How-to-Lock-Cells-in-Excel-formula-keeping-a-cell.gif />

  • Press the Enter key to apply the formula. The formula calculates and displays the result in the cell.
  • Now copy the formula with the fountain pen into the remaining cells. Drag the fill handle over the cells you want to fill.

alt=Lock cells in an Excel formula (make a cell a constant)) width=626 height=422 data-recalc-dims=1 data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/04/1618797883_881_How-to-Lock-Cells-in-Excel-formula-keeping-a-cell.gif />

Now double click on the filled cells, one by one, and you will see that cell E2 is used everywhere. In Excel, this is called an absolute cell reference. It locks the formula cells to make them permanent when the formula is copied.

Related Tags:

Feedback,how to make fixed cell in excel formulalock formula $ excel dollar signlock cell value in excel formulaexcel keep column constant change rowhow to keep a cell fixed in excelhow to keep cell constant in excel formula,People also search for,how to keep cell constant in excel formula,how to make fixed cell in excel formula,lock formula $ excel dollar sign,lock cell value in excel formula,how to retain the original value when a referenced cell changes in excel?,how do you keep a cell constant in a formula in google sheets?,excel keep column constant change row,how to keep a cell fixed in excel

Share

Leave a Reply

Your email address will not be published.