Excel worksheet protection is a great way to prevent people accidentally messing up the reports you’ve spent hours creating.
However, it is not going to keep confidential data a secret. If someone really wants to get to your data, or see how your model was put together and all the amazing formulas you’ve used then there is nothing built into Excel security that’s going to stop them. The best you can do is make it difficult (for the majority or people) to get to your data/model.
Bottom line: Excel workbook protection is designed to keep honest people honest.
That’s not to say it isn’t worth using, so let’s look at how can set up our workbooks to leverage it.
For best results view in full screen HD
Things you can Protect
First of all let’s talk about the different elements of the workbook that we can protect:
- Cells – including formulas, formatting and data inside the cells
- Structure – rows/columns/sheets
- Objects – pictures, charts, Slicers, form controls i.e. anything that floats above the cells of the worksheet.
Note: you can still interact with Form Controls (check boxes, buttons etc.) even when they are ‘locked’ and the sheet is protected. Makes sense, right?
By default all cells and objects in a worksheet are protected. However that protection only becomes effective when you actually protect the sheet. You’ll find the sheet and workbook protection on the Review tab of the ribbon:
Clicking on Protect Sheet brings up a dialog box which asks you what you want to protect and you can set a password if you want to use one. The Protect Workbook dialog box asks you if you want to protect the structure and/or the windows.
The Windows option under Protect Workbook restricts changing the size and position of the windows for the workbook when the workbook is opened and moving, resizing, or closing the windows.
Tip: Before setting protection you must unprotect any cells or objects you want people to edit. So let's look at that next.
Cell Protection – open the Format Cells dialog box (CTRL+1) > on the Protection tab uncheck ‘Locked’:
Tip: Checking the Hidden box will prevent a cell’s contents being visible in the formula bar. You can still see the result in the cell.
Object Protection – this varies from object to object but you’ll typically find it by right-clicking the object > Size and Properties > locate the ‘Locked’ check box and set it accordingly. E.g. here is the Properties dialog box for a Shape:
Protection – Humans vs Excel
Protection in Excel is preventing humans from editing your worksheets/workbooks, Excel can still edit objects, values, charts, PivotTables and the like. So if a human makes a change to a cell that is not protected, which in turn affects a formula in a cell that is protected, Excel will still update the formula.
VBA/Macros are considered human in this context unless the UserInterfaceOnly parameter is used:
Ok, so now you know where to find the protection tools let’s look at how to use them.
Protecting Dashboard Reports
One of the FAQ’s from my Excel Dashboard webinar series where I built the interactive dashboard shown below was:
“How can I protect my dashboard but allow people to select the months in the Slicer, and still have the dashboard, PivotTables and formulas all update?”
Well, like I mentioned above, Excel can still edit protected cells so all you need to do is edit the Slicer protection; right-click on the Slicer and choose Size and Properties. The dialog box will open:
- Under Position and Layout - check ‘Disable resizing and moving'
- Under Properties - select ‘Don’t move or size with cells’ and uncheck ‘Locked’
Once you’ve set the properties for the Slicer you can go ahead and protect the worksheet.
And if you don’t want people to see your formulas you can also uncheck ‘Select locked cells’ which will prevent any protected cells being selected:
These settings will allow the user to interact with the Slicer but they can’t mess it up. When selections are made in the Slicer it will automatically update the dashboard, but the user can’t select any protected cells in the dashboard itself.
You can also hide the workings sheets in your file and then protect the workbook so they underlying data isn’t accessible either.
Protection Helps Navigation
Another benefit of protecting cells is for data entry; when cells are protected the Tab key will move through the unprotected cells, skipping any protected cells. This makes data entry quick and easy.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.