Protecting Dashboards and Sheets
One of the things that you might want to do after you have created you super awesome dashboard is –protect it. An obvious question would be, why protect it? Here are a few reasons:
1) To protect your intellectual property (your codes, design, formulas) from ethical users.
[We say ethical users, because we know that those who are hell bent on stealing you intellectual property will anyway steal it and MS Excel, or for that matter any other software cannot protect it. ]
2) To avoid those casual clicks messing up your formulas.
[Dashboards are like toys—you can’t stop yourself from clicking here and there. A couple of casual clicks on cell containing formulas, references change, and your entire dashboard goes haywire]
3) To protect the design
[A typical dashboard will use a large number of shapes, pictures, lines, charts, etc in its design and casual user clicks may move these objects from their desired place, the result, a visually deformed dashboard]
4) To protect the structure of dashboard
[The structure (sheets, rows, columns) of the dashboard is critical. We would ideally not like someone to add sheets to our dashboard for doing rough calculations. Similarly, there may be certain sheets that we would like to keep hidden—say the control sheet with all those scary formulas and geeky stuff]
Let’s find out the various options MS Excel provides for protecting the workbook/worksheet.
Protecting the sheet/cells
You can protect the selected/all cells within your worksheet by following the steps given below.
Step 1: Select all cells within your worksheet. You can do this by pressing ‘Ctrl +A’ or by clicking on the left-upper-top-corner of the sheet (see picture below)
Step 2: On the home tab, go to cells > format
Step 3: select ‘format cells’
Step 4: On the form that pops up, go to ‘protection’ and un-check the check boxes against ‘hidden’ and ‘locked’ and press ‘Ok‘.
Step 5: Now select all the cells that you want to protect, they would ideally be cells containing formulas or text that you would not like the user to change. Then go to the cells on the home tab once again, go to format and this time check/select the ‘hidden’ and ‘locked’ properties.
Step 6: Go to the ‘review tab’ and under ‘changes’ click ‘protect sheet’. It will prompt you for various things that you would want to protect on the sheet (please note that these would only be applicable on the cells we chose in Step 5 above). We would suggest you to explore what all these mean and what all they protect, by selecting them one by one—they are pretty self explanatory.
The most important thing—you also need to enter a password at this stage. Remember this password as you would need it if you were to edit any protected cells on the dashboard. All your desired cells would be protected now and if the user double clicks on any of them he/she will get a message stating that those cells are protected and cannot be changed.
It is desirable to hide certain sheets on the workbook—such as the control sheet that has all the formulas and inputs on which your dashboard operates.
There are two ways to hide your worksheet:
Option 1 – simple hide
Just right click on the sheet tab that you want to hide and select ‘hide’. You can unhide the sheet by right clicking on the sheet tab and selecting ‘unhide’.
Option 1 – a better approach is to hide the worksheet through the VBA interface. To do this, follow the steps below:
Step 1: press ‘Alt + F11’ or go to developer > visual basic
Note: If you do not see the developers tab, go-to ‘excel options’ and in the ‘popular’ tab click ‘show developers tab in the ribbon’
Now you should ideally see something like this:
Step 2: Select the sheet that you want to hide and then in the properties look for ‘Visible’ and in the cell next to it change the value to ‘2 – XlSheetVeryHidden’.
Step 3: The worksheet is now hidden and cannot be unhidden using the normal excel interface. But it can still be done from the VBA interface. So, we need to set a password for the VBA interface. To do this, right-click where it says ‘VBAProject’ and select ‘VBA Project properties’. In the form that pops up, go to ‘protection’, check ‘lock project for viewing’ and set a password. Now the worksheet cannot be unhidden unless someone knows the password.
Protecting workbook structure
Ok , so now we have locked the cells, and hid our important sheets, but still people can make changes to the structure of the workbook i.e. delete any unhidden sheets, insert new sheets, etc. if you wish you can restrict this activity as well.
To do this go to review > protect workbook > restrict editing > protect structure and windows; on the pop up form select ‘protect workbook for structure’, provide a password and click ok.
You might be tempted to find out what the ‘Protect Workbook for Windows’ option does—it basically protects you current window size; so the users cannot change the window size if you select this option.
All these options and protection methods will provide your dashboard a fair amount of protection from the items discussed during the start of this page.
Feel free to reach out to us at firstname.lastname@example.org in case you face any difficulties in understanding the process and we would be happy to address your queries.