Formulas in Microsoft Excel perform different functions by using cell references. These references can be either fixed/constant references, or they may be relative and change their values when copied to other cells.
In Excel, fixing a cell reference in your formula is a very simple task which can be done in two simple ways.
These methods of locking a cell in your formula are presented in this guide. If you have been looking for how to lock a cell in a formula then look no more!
So check out the ways of locking cell reference in a formula in Microsoft Excel here.
What is A Cell Reference in Excel?
In Microsoft Excel, when you are writing a formula that will process values from other cells, you will have to type in the cell references of those cells in the formula. The format of writing cell reference is to write the column name followed by row number. For example: E6, A1, G23, etc.
Cell references are an integral part of Excel formulas when you are dealing with a huge amount of data on your spreadsheet.
After you have written a formula, you can also copy that formula into other cells of a column by dragging the filler on the bottom-right of the cell. Now, when you copy the formula, cell references within it WILL CHANGE if they are relative cell references.
If you want those cell references to remain constant, you have to change them to absolute cell references.
What does this mean? It is all explained in the next section.
Here’s a complete guide on how to sort by date in Excel.
What Does Locking A Cell Reference Mean?
Locking a cell reference in a formula generally means keeping the value of that cell reference constant so that, even when copied into other cells, the reference does not change its value. It is done when you want your formula to use a constant value from a cell in every other cell it is copied to.
Locking a cell reference in Excel is done by putting the dollar sign ($) in a formula before the column name and the row number. You can lock either the column or row, or both. Based on which part of the reference is locked, cell references can be of three types.
These are the types of cell references based on which part is locked:
- Relative cell reference: A reference is relative when none of it is fixed/locked. It will change when copied into other cells.
- Absolute cell reference: An absolute reference means that both the column and row are fixed in it. They will not change if copied onto other cells.
- Mixed cell reference: Cell references are said to be mixed if either the column or the row is fixed. The locked part will not change if copied, only the relative part will.
Making a cell reference absolute by locking it in a formula in Excel is a very simple task that can done in a matter of few moments by using two different but equally effortless methods. These are discussed in the next section of this guide.
So, without further ado, move on to the next section to learn how to lock a cell in a formula in Microsoft Excel.
Check out the easiest way to do a subscript in Excel.
How to Lock A Cell In A Formula in Microsoft Excel
In Excel, you can lock the cell references in your formula in two simple ways, by adding dollar sign ($) or by using keyboard shortcut key (F4). It does not get any simpler than this. The cell references you will be locking will then turn into absolute cell references and won’t change if copied.
The methods of locking the cell reference in your formula which you want to keep constant are briefly elaborated here so that you do not face any trouble while trying to fix your cell reference.
Follow these steps to lock a cell in a formula in Excel:
1. Use Dollar Sign ($) to Lock Cell Reference
The dollar sign used behind the column or row in a cell reference indicates that the column or row has been fixed. If both have the sign before them then it is an absolute cell reference. If the dollar sign exists before either the row or the column then it is a mixed cell reference.
Here’s how you can use the dollar sign to lock cell reference in Excel:
- Place the typing cursor before the column name and/or row number of the cell reference in your formula.
- Type in the dollar sign ($) by pressing Shift + 4.
Type the $ before either row or column if you want a mixed cell reference or type it before both the row and column if you want an absolute reference.
After you have done this, the cell reference you have locked will now remain constant throughout your spreadsheet when you copy it onto other cells. This can be done even more easily by following the next process.
Follow our guide step-by-step to remove page break in Excel.
2. Press The F4 Key To Lock Cell Reference
In Microsoft Excel, you can turn your cell reference into an absolute one just by pressing the F4 key on your keyboard. This is a great feature to be employed by Excel users.
This is how you can use F4 key to lock cell reference in Excel:
- Select the cell reference in your formula that you want to lock OR click on the cell reference you want to lock.
- Press the F4 key once to lock the whole cell reference.
As you go through these steps, you will then see $ appearing behind the row and column of your cell reference. This means that you have successfully locked your cell reference.
Check out our recent article on add months to date in Excel.
How to Toggle Between Different Cell Reference Types
You can actually toggle between relative, absolute and mixed cell reference types just by pressing the F4 key on your keyboard. This is one of the versatile features in Excel that allows you to quickly change your cell reference type just by the press of a single key.
On the last section, you have seen that you can use the F4 key to lock your cell reference. This key actually toggles between the different types of references.
The toggling stages of cell reference type by pressing F4 are:
Relative > Absolute > Mixed (constant row) > Mixed (constant column) > Relative > …
This toggling cycle is maintained whenever you are using the F4 key to lock any cell reference in your Excel formulas.
For example, suppose you are trying to lock the Following reference: D8. You select this reference or place the cursor to the right of the reference. Then, if you press F4 key, the following changes will occur to your cell reference:
D8 > $D$8 (press F4 once) > D$8 (press F4 twice) > $D8 (press F4 thrice) > D8 (press F4 four times) > …
Toggling your cell references is a powerful tool in Excel that you can use to get work done in a very short time. You can get a firm grasp of this skill from this section.
Go and check our other article on change row height in Excel.
Conclusion
Microsoft Excel’s formulas allow you to manage your spreadsheet data with a lot functionality and flexibility. Formulas are the basis of any spreadsheet that contains cells with data that are dependent on other data from other cells.
To bring them all together, we use cell references in our formulas.
If we need the formulas to use constant values from a certain cells, then we have to lock those cells’ references within the formula so that it doesn’t change if we copy that formula onto other cells.
Going through the methods of locking cell reference that are mentioned in this guide will give you a very clear idea of when and how you can lock the cell reference in a formula in Excel.
