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.
Using kutools
After installing kutools on your excel, you can use it to keep a cell constant in your sheet.
1. After entering your data, click on the Kutools tab.
2. Click on the more button located on the formula section to open its drop-down button.
3. Then, click on the Convert Refers, and a dialogue box opens.
4. Using the Ctrl button, highlight all the cells or specific cells that contain the formula you want to make the reference to. Then, select the "To absolute" button.
5. Click Apply, and then Ok.
