After you have a developed an Excel spreadsheet that is going to be used by others you should consider protecting the spreadsheet to guard against any unplanned changes to formulas and formatting by less experienced users. Excel’s Cell Protection feature can provide this functionality ensuring that critical formulas are not inadvertently changed when using the spreadsheet.
We will start with a sample spreadsheet that contains Sales data for an organisation. The users of the spreadsheet need to include records for the Sales Staff, Region, Month and Total Sales. After providing that information the spreadsheet calculates the % of Total Sales and the Total Sales for all Sales Staff. Those calculations are contained in the cells highlighted in Red below. If a user were to mistakenly enter data into those cells the formulas would be overwritten and the calculations would no longer work.
Step 1 – Unlock all cells on Worksheet
The first step in protecting those cells containing formulas is to “Unlock” all cells on the worksheet. This will allow data to be entered into those cells that require it when we protect the worksheet. So select all cells in the worksheet and then right click on the worksheet and select “Format Cells”. Select Protection and uncheck the “Locked” option if it is selected and then select OK.
Step 2 – Lock Protected Cells
Now we will select only those cells that require protection for this worksheet. In this case that is E6:E10 and D13. With those cells selected choose Format Cells again and this time check the “Locked” option and then select OK.
Step 3 – Enable Worksheet Protection
Now before we can release the Spreadsheet Cell Protection needs to be enabled. To turn on protection for the cells that we have specified select “Protect Sheet” in the Review Tab on the Ribbon. In Excel 2003 select Tools -> Protection -> Protect Sheet.
In the dialog box you can select a number of options to allow for different actions to be taken on the cells that have been locked. For this sample we will select the default options that will restrict the entry and update of cells we have locked. You can also choose to specify a password for the worksheet protection, if you do this be sure to keep a record of the password somewhere should you need to unlock the worksheet and change the formulas at some future date.
For the purpose of this sample we will not specify a password and simply select OK to enable worksheet protection. Once we do that the cells we have “Locked” can no longer be modifed. All other cells on the worksheet that are “Unlocked” can still be changed and updated.
Step 4 – Protection Warning
If you now try and modify one of the cells we “Locked” then the following message will be returned to the user, and the changes made are ignored.
With Cell Protection in placed for this worksheet you can now release it for use safe in the knowledge that changes to the formulas you have developed will not be changed by those using the worksheet.
Things to note about Cell Protection
– Only the cells that are Locked will be protected by Worksheet Protection
– By default Excel “Locks” all cells on a worksheet and you need to specify which cells do not need protection
– Cell Protection is only enabled after Worksheet Protection is enabled
– If you specify a password during Worksheet Protection you need that password to modify the formulas in the future
– Be sure to thoroughly test a Worksheet that has been protected to ensure that the use and functionality of the Worksheet is not affected by the Protection.
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment