How To Keep A Fixed Value In Excel Formula

Hither are some quick steps to lock cells in Excel using this formula: =C5*D5*E2. Say you desire to lock cell E2 to remain abiding as you lot copy the formula to adjacent cells.

Starting time write the formula. =C5*D5*E2
Click E2 in the formula to place the cursor between Eastward and two.
Press F4 on your keyboard. This volition insert the dollar ($) symbols in the formula.
Press the Enter key to apply the formula.
Using the fill up handle, drag and re-create the formula to the rest of the cells.

Lock cells in Excel formula (make cell constant))

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

Details

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

Often, you need to re-create a cell content to other cells or range. This is common when yous're working with Excel formulas.

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

This flim-flam can be washed using absolute references. It lets you lock cells in Excel formulas to proceed them abiding.

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

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

How to lock or go along a prison cell constant

Now allow's illustrate with an example on how to lock cells in Excel formula.

You can download the example file beneath:

In the above worksheet, our task is to write a formula that will summate the disbelieve cavalcade given a disbelieve rate of 9.v%.

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 assay has explained what the formula does exactly. It uses the ix.5% sales discount rate in jail cell E2 to calculate the discount for the product1 in column E.

Come across the screenshot again:

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

See screenshot:

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

Below is how to do that:

Select the Cell that will comprise the formula. In this example, we'll select cell E5.

Now enter the formula to calculate the desired upshot. In this example, we'll enter =C5*D5*E2

Within the formula, click the prison cell reference yous want to lock (Jail cell E2), and press the F4 central. Excel will introduce the dollar ($) into your formula. You can as well type the dollar ($) manually. The formula should now look similar this: =C5*D5*$E$ii

Press Enter to apply the formula. The formula volition summate and display the effect in the cell.
Now, copy the formula down to the rest of the cells using the fill handle. Just elevate 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 jail cell E2 is used throughout. This is referred to as Absolute cell reference in Excel. It Locks cells in a formula to brand it constant when copying a formula.