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.

Lock Cells in Excel Formula


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:
=C5+D5
  • Next, input $ sign manually from the keyboard to lock cell D5. The formula looks like this:
=C5+$D$5

Manually Lock Cells with Absolute Reference in Excel Formula

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


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:
=C5+D5
  • After typing D5, press the F4 key. It’ll create your formula with the absolute reference like below.
=C5+$D$5
  • It’s shown in the following image also.

Lock Cells in Excel Formula Using F4 Key

  • 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.

Excel ‘Find & Select’ Feature to Lock Formula Cells

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.

Excel ‘Find & Select’ Feature to Lock Formula Cells

  • 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.

Excel ‘Find & Select’ Feature to Lock Formula Cells

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

Excel ‘Find & Select’ Feature to Lock Formula Cells

  • 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

Tags:

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo