How to Lock or Protect and Hide Excel Cells Formulas or Sheets

So far we have discussed about various excel formulas, excel functions, we also discussed how to use PivotTable effectively etc., today we will discuss how to protect an excel sheet or protect excel cells and hide and lock formulas.

I hope you have seen few Excel Workbooks where you are only allowed to view some part and are authorize to edit few cells only, you cannot delete or see the formula in it. These excel files or cells are locked by passwords which you cannot edit or delete.

Why we need to protect an Excel workbook?

There are many reasons why we need to protect excel cell or excel formulas. Suppose your report needs to be ‘viewed’ by third party, not directly related to your organization or let suppose you have written such a ‘formula’ which would not to be wise to displayed to anyone except you. The reasons are many why one should protect or freeze Excel Cell containing formula or data, now, let us learn how to lock excel formula.

How to lock Cells in Excel? Or, how to protect or freeze Excel cell?

Please remember, by default all cells in an Excel Sheet are LOCKED.

How to know whether cells are locked or not?

It’s very simple, select any cell or range of cells in an Excel Sheet, right click and in the popup menu select “Format Cells…” or you can click on the small arrow button (as shown in the below image) in the Home Tab under Font section.

Format Cell Option in the Ribbon in Microsoft Excel

This will open ‘Format Cells’ window, now click on the Protection Tab. In this tab two Options are AVAILABLE, first LOCKED which is checked and the second is HIDE which is unchecked. 

Note that in the beginning of this article I told you that by default every cell is LOCKED in every Excel sheet. Please have a look at the below diagram.

Format Cells window Protection Tab in Excel

Excel allows us to lock all or specific cells and sheets or the whole workbook to be locked by password.

You will find PROTECT SHEET option in the REVIEW Tab under 'Changes' menu as shown in the below image.

Password Protection - Protect Sheet Option in Microsoft Excel

If you click in this button a new window will appear asking for password, if you enter password and click OK it will ask to confirm the password again. Enter the password again and the Excel cells are locked.

In this process, each cell in the Excel Sheet will be LOCKED, no one can edit or delete any cell in this protected Excel sheet. Why? 

Because I have instructed Excel to LOCK the 'LOCKED' cells and by default all cells are LOCKED.

But this is not our requirement, our requirement is to lock few cells and allow the other cells or allow few  cells to edit and rest cells to be locked

How to lock specific cells in Excel Sheet?

Steps :

1.       Select the cells you don’t want to lock

2.       Right click to get the Format Cells option from here (shown in the below image)

Format Cell Option by right Click in Microsoft Excel

Or you can click on the small arrow in the 'Font' menu.

Format Cell Option in the Ribbon in Microsoft Excel

3.       'Format cells...' window will appear, click on the PROTECTION tab

4.       Uncheck the Locked option.

5.       Click OK

6.       Go to Review Tab, Click on Protect Sheet button.

7.       A small window will appear named Protect Sheet asking for password.

8.       Type your password (please don’t forget it)

9.       Click OK, another window will appear asking Confirm Password, reenter your password.

10.   Click OK… and Save…

Now in your Excel Sheet, cells are password protected or locked by password, the cells you selected and unlocked by unchecking ‘LOCKED’ can be Edited or Deleted but other cells are freezed.

Protect Sheet Option to enter Password

Protect Sheet Option to Confirm Password

Suppose I have an Excel table as shown below and I need to lock the cells EXCEPT highlighted in yellow (which includes a single cell ‘H6’).

How to hide excel formula 

How to unlock or unprotect two different cells separated by one or more locked cells?

To complete this task the steps are same as above only you need to UNLOCK the single cells also and then implement Locking in the Sheet from Review à Protect Sheet à Password à Confirm Password.

Note : Please UNLOCK the cells you don’t want to protect and do the reverse if you want to lock them.

How to hide and lock excel formula?

In the above picture, you can see the formula in the formula bar what to do if I want to HIDE this formula from viewing.

Steps :

1.       Select the Cell(s) to Hide

2.       Right click and click on ‘Format Cells…’ option

3.       Go to Protection Tab

4.       Check the Hidden check box (as shown below)

5.       Protect the sheet by password using above methods.

6.       The formula will not be displayed in the formula bar, its’ now hidden from viewing

How to hide excel formula 

How to lock a column or row?

To lock a single or multiple COLUMN or ROW all the steps are to be followed, the only exception is that instead of selecting the range of cells, select the whole COLUMN or ROW and then apply the Password Protection steps. If you hide these columns and rows before password protection, these columns or rows will also be locked, user will not be able to unhide these columns.

How to allow user to use filter in Protected Excel Sheet or Locked Cell?

To allow specific jobs like allowing to use AutoFilter can be done in a protected Excel Sheet, you should stop before entering password in the Protect Sheet option, check the AutoFiler option in the Protect Sheet window.

If you look carefully the Protect Sheet window you will realize that it is like a control panel to provide specific authorization to the user what they can use or not.

For example, if you want to allow the user to use FILTER in the protected sheet, you must first apply filter in the column and then TICK “Use AutoFilter” option and then protect the sheet by password as shown in the below image.

How to Use AutoFilter or Filter in Excel Protected Sheet

In the same way, you can give permission to Insert columns or rows or delete columns or rows in a password protected Excel Sheet.

How to unlock a password protected Excel Sheet or cells?

Unlocking a password protected Excel Sheet is very simple, go to Review à Unprotect Sheet à Enter password in the popup window, click OK.

I would like to share you one thing regarding password protection in Excel, although Excel Cells or Sheets are protected using this method but this password protection can also be broken using some technique, I will show you this process in another article, till then keep reading and keep learning.

I hope this article is informative regarding protection of excel formula, cell & sheet.

Thank you for reading… If you have any question to ask feel free to ask me in the comment section… and please share this article if you like this article…

Thanks again.