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.
No comments:
Post a Comment