How to☝️ Lock References in a Cell Formula in Excel

To set up a cell with locked references in a formula in Microsoft Excel, select the cell, navigate to the Formula bar, and type the formula you need, using dollar signs in front of the row and column references. For example, =$A$1-$B$1, where A1 and B1 are the cells with values. Finally, press Enter.

Let’s look at a few methods for accomplishing this.

Method #1: Typing the Dollar Sign Manually

Difficulty: Beginner

Time Estimate: 10 seconds

1. Select the cell where you want your formula and its result.

2. In the Formula bar, enter the formula you need, typing dollar signs in front of the row and column references. Here we use the formula =$A$1-$B$1, where A1 and B1 are the cells with values used by the formula.

How to Lock References in a Cell Formula in Excel

3. Press Enter on your keyboard.

Dollar Sign with the F4 Key

Here you have it! Now if you copy the formula into a new cell, the row and column references will not change.

Method #2: Adding the Dollar Sign with the F4 Key

Difficulty: Beginner

Time Estimate: 15 seconds

1. Click on the cell where you want the formula and its result.

2. Go to the Formula bar and type the formula you need. In our case, we use =A1-B1. When entering the formula, press the F4 key. Do this twice, once after entering the first cell reference and again for the second cell reference: = > A1 > [F4] > – > B1 > [F4].

How to Lock References in a Cell Formula in Excel

3. Hit Enter on your keyboard.

Dollar Sign with the F4 Key

Easy as ABC! The references in the formula are now locked in place.

Locking Cell References in Excel FAQs

Here are some frequently asked questions and answers to help you understand more about cells and their data in Excel.

What can a cell contain in Excel?

A cell in Excel can contain many different values. The most common type of data is a number, date, or time. A cell can also contain text, which can be entered by the user or generated by a formula. 

What are the three types of cell references in Excel?

There are three types of cell references in Excel: relative, absolute, and mixed.

  • Relative cell references change when a formula is copied or moved to another cell.
  • Absolute cell references do not change (they are locked) when a formula is copied or moved to another cell.
  • Mixed cell references contain both absolute and relative components.

We suggest you head on over to take a look at this article, which goes over cell references and the dollar sign in detail.

What is a cell range?

A cell range is a group of cells that can be created by selecting multiple cells in a worksheet all at once or by using the Range Object in VBA code.

What is an active cell?

An active cell is the cell into which data is currently being added. Only one cell may be active at a time.

What is a cell address?

A cell address is the location of a cell in a worksheet. Cell addresses are used in formulas to reference specific cells. A cell address consists of the column letter and row number of the cell.

More Google Sheets Tutorials
More Microsoft Excel Tutorials
Share This Post
Daniel Smith
Daniel Smith
Daniel Smith is automation consultant with a passion for technology, data, AI, and machine learning.

The Spreadsheet Daddy add-on rebrands to Synterrix. Click here to learn more.

X