Excel Protect Sheet (Table of Contents)
Introduction to Protect Sheet in Excel
It is very simple to edit and create formulae in Excel. Even the smallest change in any given formula can lead to strikingly different and/or incorrect results in our Excel report. It is highly probable that there might be some unintended change in one or more formulae as the Excel goes from one person to other. There are numerous scenarios where an incorrect key press such as the backspace, alphabet or the number key while going through a formula can lead to altering the formula completely. The chances of such things happening increases exponentially as the Excel is distributed among multiple channels.
Hence it is of utmost importance to protect the Excel worksheet and the cells where we have critical formulae etc. In order to address this kind of issues, Excel has included Lock and Protect functions in its releases, which essentially prevents any other users to make any changes – overwriting and deleting in a workbook.
How to Protect (Lock) Cells in Excel?
It is very simple and easy to protect cells in Excel. Let’s understand the working of protecting cells in excel with some examples.
Example #1
Suppose we have an Excel with three different values. Our objective is to calculate their average.
As we can see from the screenshot above, we have three values in cell A2, B2, and C2. We calculate the average of these numbers in cell D2. Now we wish to lock the cell D2 which contains the formula so that it doesn’t get accidentally altered when the excel is passed from one person to another.
Now, since all cells are locked in Excel by default, let us first unlock them. To do this we first need to select all the cells by pressing Ctrl+A or Command+A. Next, open the Format Cells dialog box by pressing Ctrl+1.
Now we will need to go to the “Protection” tab.
Now under the “Protection” tab, we will need to untick the “Locked” option and click OK. All the cells in the worksheet will then be unlocked.
Now, we will need to select the cell that contains the formula i.e. cell D2. We will now press Ctrl+1. This will once again open the Format Cells dialog box. We will navigate to the Protection tab.
This will lock the cell formula. Now, we have to protect the formula. To do this, we will need to navigate to the “Review” tab and click on the “Protect Sheet” option.
This will open up the next dialog box as shown below.
It is also possible to provide a password to protect the sheet. This will result in Excel asking for a password when a user tries to access this Excel sheet.
At this point, Excel will prompt to ask you to re-enter your password just to make sure that there are no mistakes with the password.
After this, the cell will be locked and it will no longer allow any further modifications to be made. If we click on this cell and try to press any keys in order to modify the formula, we will get the following pop up message.
It is interesting to note that only this particular cell is protected, and no other cell will result in a similar pop up upon modification. A locked cell does not guarantee that the underlying formula will be protected from modifications. Therefore, it is vital to protect the formula after locking the cell. Thus, we will need to first unlock all the cells first and then lock the formula cell and then proceed with protecting the formula. This will prevent the formula cell from any accidental modifications, while all the other cells are open to modifications.
Example #2
Now, let us suppose that we have sales data for several products in different zones. Our objective is to calculate the total sales for each product separately and then identify the product with the highest sales. We need to lock the cell with the formula to calculate the highest sales and protect it from modifications. However, the cells with the summation formula and the sales data itself should be modifiable.
We will first calculate the total sales for each product.
So, in order to do this, we applied the SUM () function and passed the array for the first row and then dragged the formula to the remaining rows since the column range for summation for all rows are the same.
Next, we will find the max sales. Adjacent to this table, we will select a cell and key in the index-match formula to find the product with the max sales.
Now, we need to lock and protect the cell with the formula for Max Sales. Since all cells are locked in Excel by default, let us first unlock them. To do this we first need to select all the cells by pressing Ctrl+A or Command+A. Next, open the Format Cells dialog box by pressing Ctrl+1.
Now we will need to go to the “Protection” tab.
Now under the “Protection” tab, we will need to untick the “Locked” option and click OK. All the cells in the worksheet will then be unlocked.
Now, we will need to select the cell that contains the formula i.e. cell D2. We will now press Ctrl+1. This will once again open the Format Cells dialog box. We will navigate to the Protection tab.
This will lock the cell formula. Now, we have to protect the formula. To do this, we will need to navigate to the “Review” tab and click on the “Protect Sheet” option.
This will open up the next dialog box as shown below.
It is also possible to provide a password to protect the sheet. This will result in Excel asking for a password when a user tries to access this Excel sheet.
At this point, Excel will prompt to ask you to re-enter your password just to make sure that there are no mistakes with the password.
After this, Cell will be locked and it will no longer allow any further modifications to be made. If we click on this cell and try to press any keys in order to modify the formula, we will get the following pop up message.
Alternatively, after unlocking all cells in the worksheet, we can go to the “Home” tab and then navigate to the “Editing” section and select “Find & Select” and from the dropdown, we can select “Go To Special”.
- Excel would then open up “Go To Special” dialog box and we would then need to select “Formula” and click on OK. This would then select all the cells which have the formula in its contents.
- We can now repeat the process of locking the formula cells and then protecting the formula.
Things to Remember About Protect Sheet in Excel
- Simply locking the cells in Excel does not guarantee protection from accidental modifications.
- We will need to both locks the cells as well as protect the formulae so as to completely make them tamper-proof.
- By default, all cells in the workbook are locked.
- Similar to the first point, simply protecting the formula cells without locking them first would not guarantee protection from accidental modifications.
Recommended Articles
This has been a guide to Protect Sheet in Excel. Here we discuss how to Protect Sheet in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles-
