You are currently viewing How to keep one cell constant in Excel

How to keep one cell constant in Excel

  • Post author:
  • Post category:Cells

In excel, you can lock and keep one cell constant. There are two types of cell references in excel namely the relative and absolute cell references. The Relative reference will change when the formula is copied to another cell. On the other hand, the absolute reference cell will remain constant no matter.

Most excel users prefer when their cell reference remains constant rather than adjusting automatically. It is most preferred when dealing with complicated or v\even uncomplicated formulas. Here, we show you how to keep one cell constant in excel.

How to use the F4 key to keep a cell reference constant

It is straightforward when you want to keep a cell reference constant using keyboard keys. You need to add the dollar sign $ to the cell reference while pressing the F4 key on your keyboard. The $ sign is used to hold a column, a row, or a column and a row reference constant in excel. To do this;

1. In your open excel worksheet, select the cell with the formula you want to make constant.

2. Go to your formula bar and place the cursor in the cell that you want to make constant. In other words, type a cell reference.

3. Press the F4 key. Afterward, Excel will automatically make the cell reference absolute. When you continue to press the F4 key, Excel will cycle through all the available absolute reference possibilities.

Here are examples of format entries you can make for the absolute cell reference;

$A$2- this format shows the column and the row references will not change when copied.

A$2 shows that the row will not change when copied or when using the Autofill feature. The column reference will change.

$A2 shows the column does not change when copied but the row reference will change.

Therefore, users mostly use the $A$2 format to ensure that the cell reference remains constant.

Let's use an example in a games store sales entry.

Given the data below, you are asked to calculate the prices of items in stock using two different price discounts. Let's take a look at how your worksheet will be;

From the above example, our entry in cell E4 shows that we made C4 our absolute reference cell by typing $C4. Here it means we will keep the column constant when copying across while the row will keep on changing when copying down. The row keeps on changing to accommodate the prices of the different items going down. Afterward, we make our last cell in the row an absolute reference cell to prevent the row number from changing when copied down. A$12 ensures only the column will change to reflect discount B values when copied across.

We reference the cells that contain the discount rates of 10% and 15% and do not type in the actual figures. We do this so that when the percentage changes all we have to do is change the percentage values one at a time in the cells that contain the discount values instead of having to rebuild all the formulas. As a rule, excel will automatically update your discount prices to reflect your discount percentage change.