How To Lock Cells with Formulas in Excel

Locking or protecting cells with formulas is crucial for calculations. This will stop other people from deliberately or mistakenly editing or deleting your formula in Excel.

Almost each of us has used formulas and functions in excel. Without function, excel is meaningless. The best power excel has is complex calculation. But without formula it is impossible. But if you misspelled a formula, remove a character from a formula or mistakenly change the formula, you won’t be able to get the proper calculation. Now if you are using a workbook by yourself and you know every cell where you have formula, you still have chances to mistakenly edit your formula cell. If you are working with a colleague or someone else on a single workbook, or you sent your workbook for review. The chance of removing or editing your formula is greater. In this situation, the best solution is to protect formulas. But you can’t just lock the entire worksheet or the workbook. You’ll need to enter data and you don’t want to enter the password every time you enter something on your worksheet.

All you need is a sophisticated technique that will only protect cells with formulas in excel. All the cells that don’t have any formula or function in it will be open for all. Also, for the checking purpose, you need to be able to see the formula itself. In this tutorial I’ll show you an excellent way on how to lock formulas in excel. Following steps will do the job.

Step 1: Select the entire worksheet.

Step 2: Click on your right mouse button and click on format cells

Step 3: Now go to the protection tab from the popup menu.

Step 4: Uncehck the locked checkmark.

Step 5: Now select all the cells with formulas. You can do that by going to the find and select menu. It is at the right corner under the home tab. Now click on go to special. Now click on the formula radio button. And click ok. All the cells that have formula are now selected.

Step 6: Now hover your mouse on any one of the selected cells and press the right mouse button and select format cells.

Step 7: Now under the Protection tab checkmark Locked and click ok

Step 8: Now under the review tab select protect sheet.

Step 9: Now enter the password twice and then click ok. Done.

You’ve just locked formulas in excel. From now on no one will be able to edit or remove your formula without the password. You can see the formula; you can use the formula but you can’t edit or remove it. All we did is lock cells with formulas not the formula itself. You can do everything else on this worksheet just like before. This is the best way to protect formula in excel but allow input. If you don’t need to lock formula, you can hide it. Here is how to hide formula in excel.

Thanks a million for reading this far. If possible, support us by becoming a Patron.

follow the MS Excel tutorial


You may also like:

How To Hide Formulas in Excel

Sometimes we need to hide sensitive formulas so that no one can see them. You can hide formulas with and without a password, and it only takes a minute.......

How to Lock and Hide Formulas in Excel

Locking a formula means no one can edit or delete your formula except you, and hiding a formula in Excel means no one can see your formula. In this tutorial, you’ll learn to do both at once.......