There are times when protecting certain cells, formulas, or entire worksheets in Excel is a useful practice to adopt. Read on to find out how to prevent your Excel worksheet from unplanned changes…
Protect Cells and Formulas in Excel
When you protect a cell in Microsoft Excel it means you stop anyone else from making changes, unless they know the password and can unlock the worksheet. You can protect 1 cell, lots of different cells or ranges, or entire worksheets depending on the purpose.
One sure fire way of stopping any changes to your Excel worksheet is to protect the entire sheet from any changes. The can be useful for reports that you never want anything changing, like an annual performance or profit report that is going external to the media.
In order to protect the entire worksheet from any changes first right click on the worksheet name tab at the bottom of the Excel window, and then select “Protect Sheet”:
When you have selected “Protect Sheet” the options box will appear where you can control the protection settings.
By default the top box “Protect worksheet and contents of locked cells” will now be checked, along with the Top 2 options from the list, “Select locked cells” and “Select unlocked cells”:
In order to prevent users making any changes to any cell within the worksheet de-select “Select unlocked cells”, notice that by doing so it automatically de-selects “Select locked cells” as well:
Now it is time to set a password. Standard password logic applies in that you should be creative, especially if the data is of any importance. Remember that you can still protect the worksheet without a password by leaving the password field blank and clicking on OK. Note: This does mean a user only has to select “Unprotect Sheet” by right clicking the worksheet name tab and they will be able to make changes.
With your password set, left-click OK and your Worksheet is now fully locked. If you test clicking within the worksheet cells you will notice you can’t, the only thing you can do is scroll up and down, or left and right.
Protect certain parts of a worksheet
Sometimes you will need the end-user to be able to alter parts of the worksheet, but keep other parts free from tampering. A good example of this might be a scoring questionnaire that gets sent out to all staff, you want the users to be able to edit their answers onto the sheet but you might have a formula calculating a score which you want to ensure remains locked.
In order to lock specific cells the best practice is to make sure everything is first unlocked, that way you can be sure only the cells you specify later will be the actual ones impacted.
Select the entire worksheet by left-clicking in the top left corner (the square that sits above row number 1 and before column A):
Next right click the mouse and select “Format Cells”:
Then select the far right tab “Protection” and uncheck the “Locked” box so that it is clear:
Left-click on OK and that’s the entire worksheet unlocked.
The last step in the process is to relock the specific cells we want to protect – which is just a reversal of what we have just been through, followed by setting the protection on the worksheet as done in the previous section.
First highlight the cells that you want to protect and right-click, select “Format Cells”:
Then once again go to the “Protection” tab but this time make sure the “Locked” box is checked:
Left click OK. Now check the protection level on the worksheet.
Right-click the worksheet name tab and select “Protect Sheet”. In the options box that appears you now need to create your settings, here we make sure that the user is unable to select the locked cells, which will prevent them from altering the specific cells we just locked, but it still allows the user to do everything else:
When you have you completed your settings enter a password, left-lick OK, save the file and you have completed the task.
The biggest factor to consider when working on protection settings in your Excel workbook is the end user and what they will be using the file for.
Reports that go outside the business, perhaps to clients, media and the like tend to have a greater need for locking down the entire sheet, and usually every sheet within the entire workbook. It keeps an excellent level of control for your business and should always be considered.
In-house reports tend to be more open-source but even then if you produce enough Excel reports and dashboards you will find reasons for locking down certain cells. Formulas can easily be altered or accidentally deleted and protecting those cells is the best way to prevent that from happening.
When you have completed the exercise a few times it becomes very quick to do, and importantly easy to remember, I’m sure you will find plenty of great uses.