2 Quick Methods to Lock Formulas in Excel using $ sign

Key Takeaways:

• The $ sign helps lock cell references in Excel.
• Relative references change when copied to another cell.
• Absolute references stay fixed when copied.
• Mixed references lock only the row or the column.
• You can use the F4 key to add or change $ signs quickly.

 

Understand Cell Reference

Before we understand how to lock formulas in Excel, it is important to understand the concept of cell references. There are three types of cell references:

#1 – Relative Reference

This reference is relative i.e. when you copy a formula from one cell to another, the reference changes. By default, cell referencing in Excel is relative. It does not include any dollar sign.

For example, if you have a formula “=A2+B2” in cell C2 and copy it to cell C3, the formula in C3 will become “=A3+B3.” The cell references change in relation to the new location of the formula.
 

#2 – Absolute Reference

This reference is fixed i.e. it will not alter when you copy the formula from one cell to another. In absolute reference, dollar signs are attached to each letter or number in the reference.

For example, if you have a formula “=$A$2+$B$2” in cell C2 and copy it to cell C3, the formula in C3 will still be “=$A$2+$B$2”. . The reference remains constant as the dollar signs lock the cells’ positions.

  

#3 – Mixed Reference

This reference is a combination of absolute and relative references. It allows us to lock either the column or the row while leaving the other part of the reference adjustable. We can either lock the column by attaching a dollar sign to the letter ($A2) or lock the row by attaching a dollar sign to the number (A$3).

For example, if you have a formula “=A$2+B2” in cell C2 and copy it to cell C3, the formula in C3 will become “=A$2+B3.” Here, row 2 is locked while the columns can still adjust as needed. So, when we move the formula from row 2 to row 3, A$2 remains the same but B2 changes to B3.

 

How to Lock Formulas in Excel

Method 1: Manual addition

We can manually insert the $ sign before the letter or number of the cell reference that we wish to lock. By doing so, we convert the reference into an absolute reference.

For example, if we want to lock the cell reference A1 in a formula, we can change it to $A$1. This ensures that the reference remains constant when copied to other cells. The dollar signs before the column letter and the row number fix the reference and prevent any adjustments.

Method 2: Use the F4 key

Excel provides a convenient keyboard shortcut – the F4 key – to automate the process of locking formulas. By selecting the cell reference within the formula and pressing the F4 key, Excel automatically inserts the $ sign.

By repeatedly pressing the F4 key, Excel cycles through all available absolute reference options, allowing for quick and efficient locking of formulas. This functionality saves time and minimizes the risk of errors when working with complex formulas and large datasets.

STEP 1: Select the cell reference and press the F4 key. This will result in the addition of a $ sign before the row and column.

STEP 2: Press F4 again. This will result in the addition of a $ sign before the row only.

STEP 3: Press F4 again. This will result in the addition of a $ sign before the column only.

STEP 4: Press F4 again. This will remove $ signs before both row and column and it results in relative reference.

 

FAQs

What does the $ sign do in Excel?

The $ sign in Excel helps lock a row, column, or cell reference.

What is an absolute reference?

An absolute reference is a fixed cell reference like $A$1. This reference will not change when you copy the formula elsewhere.

What is a relative reference?

A relative reference is a cell reference that changes when copied.

What is a mixed reference?

A mixed reference locks either the row or the column.

How to quickly add a $ sign in Excel?

To add a $ sign in Excel:

• Select the reference
• Press the F4 key.