WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Protect Formulas

Excel Protect Formulas

Protect Formulas in Excel

Formulas are an integral part of Excel file, and without formulas, we cannot create reports or organize the data, so formulas are pivotal in excel. Once the formulas are applied, we can edit them at any point in time, that’s common, but there comes a possible error. Since we can edit the formula, we end up deleting or wrong editing of formula, so it will cause the wrong report summary, and it may cost you millions of dollars. If you can spot the error quickly, you are lucky, but if not, you will end up in a mess; but the good news is that we have an option of protecting our formulas so we will end up in a mess. In this article, we will show you how to protect formulas in excel.

How to Protect Formulas in Excel?

Protection is the key thing when it comes to excelling and sharing the same excel workbook with others. So the protection of formulas is as part of the protection of worksheets in excel; we need to follow simple steps to protect our formulas.

You can download this Protect Formulas Excel Template here – Protect Formulas Excel Template

For example, look at the below data in excel.

Protect Formula Example 1

In the above table, all the black-colored cells are formula cells, so we need to protect them. Assume we need to allow the users to work with other cells except for the cells which have formulas, follow the below steps, and protect them.

Step 1: By default, all the cells are locked in excel in excel, so if we protect the worksheet directly, all the cells will be protected, and users cannot work with any of the cells, so first, we need to unlock all the cells of the worksheet.

Select the entire worksheet and press Ctrl + 1 to open the Format Cells window.

Protect Formula Example 1-1

Step 2: In the above window, click on the “Protection” tab.

Protect Formula Example 1-2

Step 3: As you can see under “Protection,” the checkbox of “Locked” is ticked, so that means all the cells are clocked now, so uncheck this box.

Protect Formula Example 1-3

Step 4: Click on “Ok,” and all the cells are unlocked now.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Step 5: Once all the cells are unlocked, we need to lock only formula cells because we need to protect only formula cells, so how do you know which cell has the formula in it??

Select the entire worksheet and press the F5 key to open the “Go-To” window and press on the “Special” tab.

Protect Formula Example 1-4

Step 6: This will take you to the “Go To Special” window like the below one.

Protect Formula Example 1-5

From the above window, choose “Formula” as the option.

Protect Formula Example 1-6

Step 7: Click on “Ok,” and all the cells which have formulas will be selected.

Protect Formula Example 1-7

Look, it has selected only the black font colored cells.

Step 8: Now again, press Ctrl + 1 to open the Format Cell window, and this time makes only these cells as “Locked.”

Example 1-8

Click on “Ok” and only selected cells will be locked, and protection applies only for these cells.

Step 9: Now, we need to protect the worksheet in order to protect formulas in excel. So under the REVIEW tab, click on the “Protect Sheet” option.

Example 1-9

Step 10: In the “Protect Sheet” window, we need to enter the password to protect the locked cells, so enter the formulas as you would like to give. (Make sure you remember the formula)

Example 1-10

In the above window, we can choose other actions that can be performed with locked cells, so by default, first two options are selected; if you want to give any other actions to the user, you can check those boxes. As of now, we are not allowing users to do any action with locked cells except for the selection of cells.

Step 11: Click on “Ok” and reenter the password in the next window.

Example 1-11

Click on “Ok,” and your formulas are protected.

If you try to do any kind of action in formula cells, it will show the below message for you.

Example 1-12

Ok, like this, we can protect formulas in excel.

How to Hide Formulas in Excel?

Formulas are protected, and it is fine, but we can go one step further as well i.e., we can hide formulas from viewing in the formula bar.

  • As of now, we can see the formula in the formula bar even after protection.

Hide Formulas Example 1-13

So in order to hide them, first unprotect the worksheet that we have protected, then choose only formula cell and open the “Format Cell” dialogue box.

  • Under the “Protection” tab, check the box of “Hidden.”

Hide Formulas Example 1-14

Click on “Ok” to close the above window.

  • Now again, protect the worksheet, and all the formula cells will not show any formulas in the formula bar.

Hide Formulas Example 1-15

Things to Remember

  • By default, all the cells are locked, so to protect only formula cells unlock other cells.
  • To select only formula cells, use the “Go To Special” option (F5 is the shortcut key) and under “Go To Special,” click on “Formulas.”

Recommended Articles

This has been a guide to Excel Protect Formulas. Here we learn how to protect formulas in excel and also hide the formulas and downloadable excel templates. You may learn more about excel from the following articles –

  • Column Total in Excel
  • Troubleshoot in Excel
  • How to Unprotect Excel Workbook?
  • Protect Sheet in VBA
  • External Links in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Protect Formulas Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More