How to Lock Cells in Excel Formula (2 Easy Ways)

We create many formulas involving different cells in our Excel worksheet. But, when we try to paste the formula somewhere else, the cells present inside the formula tend to adjust by themselves. And a lot of times, that is not our desired action. In this article, we’ll show you the easiest ways to Lock Cells in Excel Formula.

Additionally, we may need to lock only the Formula Cells in our datasheet. Because we know that Excel locks all the cells by default. So, you’ll also get to know about the process to Lock only the Formula Cells in Excel.

Download Practice Workbook

To practice by yourself, download the following workbook.

Dataset Introduction

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Income, and Bonus of a company. Here, the bonus amount is the same for everyone. For that reason, it’s mentioned only in the first row.

2 Easy Ways to Lock Cells in Excel Formula

1. Manually Lock Cells with Absolute Reference in Excel Formula

By locking the cells with absolute reference means, the cells present inside the formula won’t change to other relative locations even if we paste the formula somewhere else in our worksheet, or when we use the AutoFill tool to complete a series. In Excel, the ‘$’ sign represents the Absolute Reference. In our first method, we’ll manually input the ‘$’ sign in the formula. Therefore, follow the steps below to perform the task.

STEPS:

• First, select cell E5 and type the formula:

• Next, input $ sign manually from the keyboard to lock cell D5. The formula looks like this:

Here, C5 will get changed to relative locations but D5 won’t.

• Then, press Enter and use the AutoFill tool to fill the series.
• Finally, it’ll return the desired calculations with the Bonus amount.

Read More: How to Lock a Group of Cells in Excel (7 Different Methods)

Similar Readings

• Select All Cells with Data in a Column in Excel (5 Methods+Shortcuts)
• Multiple Excel Cells Are Selected with One Click (4 Causes+Solutions)
• How to Shift Cells Down in Excel without Changing Formula (4 Methods)
• Select All Cells with Data in Excel (5 Easy Methods)
• [Fix]: Arrow Keys Not Moving Cells in Excel (2 Methods)

2. Lock Cells in Excel Formula Using F4 Key

Moreover, we can use the F4 key instead of manually typing the ‘$’ sign in our formula to create the absolute reference. So, learn the process given below to know how to Lock Cells in Excel Formula with the F4 key.

STEPS:

• Firstly, select cell E5. Here, type the formula:

• After typing D5, press the F4 key. It’ll create your formula with the absolute reference like below.

• It’s shown in the following image also.

• Next, press Enter.
• Subsequently, use the AutoFill tool to complete the series.
• Thus, you’ll get your required outcome.

Read More: How to Drag Cells in Excel Using Keyboard (5 Smooth Ways)

2 Quick Ways to Lock Formula Cells in Excel

1. Excel ‘Find & Select’ Feature to Lock Formula Cells

By default, Excel locks all cells in a worksheet. And protecting the locked cells can only restrict the users from making any changes in the cells. But, we may need to lock only the formula cells and not all the cells. In order to do that, we can use the ‘Find & Select’ feature to search for the formula cells and then lock them. Hence, follow the below process to Lock only the Formula Cells present in column E in the following dataset.

STEPS:

• In the beginning, select all the cells. You can use the ‘Ctrl’ and ‘A’ keys together to do that.
• Next, press the ‘Ctrl’ and ‘1’ keys simultaneously to open the Format Cells dialog box.
• Then, under the Protection tab, uncheck the Locked box.
• Subsequently, press OK.

• Again, select all the cells.
• Now, select Go To Special from the ‘Find & Select’ drop-down list in the Editing group under the Home tab.

• As a result, the Go To Special dialog box will pop out.
• There, select Formulas and press OK.

• Consequently, it’ll select the cells with Formulas only.
• After that, press the ‘Ctrl’ and ‘1’ keys.
• Thus, the Format Cells dialog box will pop out, and there check the Locked box.
• Next, press OK.

• Now, select Protect Sheet in the Protect drop-down under the Review tab.

• Consequently, the Protect Sheet dialog box will pop out. There, check the box Protect worksheet and contents of locked cells.
• Then, press OK.

• Finally, your formula cells are locked and protected.

Read More: How to Protect Selected Cells in Excel (4 Methods)

2. Choose Formula Cells Manually to Freeze

However, we can also select the Formula Cells manually and lock them like the previous method. And finally, protect those locked cells to prevent any changes.

Conclusion

Henceforth, you will be able to Lock Cells in Excel Formula with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Display the Cell Formulas in Excel (6 Methods)
• How to Check If Multiple Cells Are Equal in Excel (4 Methods)
• Excel if One Cell Equals another then Return another Cell
• How to Delete a Cell in Excel (4 Easy Ways)
• Selecting Non-Adjacent or Non-Contiguous Cells in Excel (5 Simple Methods)
• How to shift cells in Excel