How to Lock Formulas in Excel?

2 Mins read

How to Lock Formulas in Excel? Protect formula in Excel? Confused? Here is the blog to know how to lock excel formulas.

Imagine Your boss wants you to protect a workbook, but she also wants to be able to change a few cells after you are done. So, before you password protected the workbook (or a worksheet), you unlocked some cells. Now that your boss is done, you can lock the cells.

Think Is it possible for you to protect your particular cells contain formulas?

The answer is Yes!

The most common way of preventing people from tampering with your Excel formulas is to Protect the worksheet.

Excel Vlookup formula – Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

How to Lock Formulas in Excel (Step By Step Guide)

Here are the steps to lock formulas in Excel (explained in detail later on):

  • Step 1: Select the cell with formulas that you want to lock & Press Ctrl + 1
  • Step 2: In the format cells dialog box, select the protection tab.
  • Step 3: Check the “Locked” Option in Excel
  • Step 4: Click Ok & Apply

Let see how to lock formulas in Excel by following steps!

We are going to lock only those formulas

Step 1: Create a table the same as the above picture. This table is showing students’ marks of two subjects Maths and Geography. In the cells of column H, we have used a formula that calculates the total marks of each student in these two subjects. Say, in cell H2 we have used the formula given below:


We are going to lock only those formulas in column H.

Here we have two objectives to lock only formulas

Step 2: Here we have two objectives to lock only formulas. Firstly, we have to unlock the whole cells of the worksheet,. Secondly, we will protect only formulas. So now Select the whole table as like the above picture. Now click the Home tab and in the Alignment group, choose the small arrow to open the Format Cells dialog box.

On the Protection tab, Unchecked the Locked

Step 3: On the Protection tab, Unchecked the Locked check box, and then click OK.

Go to the Home tab. From Editing group, click Find & Select button and choose Go To Special

Step 4: Go to the Home tab. From the Editing group, click Find & Select button and choose Go To Special.

Go To Special dialog box, check the Formulas radio button. This will select the check boxes with all formula types

15 Excel Data Cleaning Tricks Guidebook

Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages) 

Step 5: In the Go To Special dialog box, check the Formulas radio button. This will select the checkboxes for all formula types. Now click OK.

Now locked again only these selected formula from Format Cells dialog box

Step 6: Now it has selected only those cells to contain a formula. Now locked again only this selected formula from Format Cells dialog box following previous steps. Now on the Review tab in the ribbon select Protect Sheet from Changes group.

The Protect Sheet dialog window will appear

Step 7: The Protect Sheet dialog window will appear. Check “Select locked Cell” and “Select Unlocked Cell”. Now click OK.

Tips: In the Protect Sheet dialog window You can use a password in the text box named “Password to unprotect sheet”. In this way when someone will try to unprotect this, it will not be possible for him to unprotect without your given password. Only you can Unprotect or Unlock this formula. So, be careful and remember your given password to unprotect this formula.

Now We have successfully locked our formulas in Excel!

Step 8: Click on any cell of column H that contains a formula, It will show a message like the above picture. But You can edit another cell. This is the simple way you can hide and lock particular cells that contain formulas in Excel.

Tips: When you want to unprotect it again, just simply select the Review tab, then click the Unprotect Sheet and use your given password. Then it will be unprotected again for you. If you want to learn more about Excel Tips & Tricks. Please check our latest Excel Ninja Course that offers high-quality videos with lifetime access.

Now We have successfully locked our formulas in Excel!

15 Pivot Tables Tricks for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.

Most Popular Tricks are #3, #7 & #12

Related posts

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…