tip #3: password protect your dashboards

Once you've created your interactive dashboards in Excel using my famous PARCS method, you will be tempted to share your Excel workbooks with clients and colleagues. You designed something amazing, and it only makes sense that you would want to share it with the world, right?

HOLD ON! 
Aren't you forgetting something???

 

Theoretically, if you shared your Excel workbook as is, your clients and colleagues will have access to not only your amazing dashboard, but also the raw data and all of the pivot tables that were used to painstakingly build your dashboard. Do you really want to give them access to all of this? More importantly, aren't you violating FERPA, HIPA and/or IRB rules and regulations by freely sharing the raw data? Ouch!

Here is a step-by-step guide to protect both your dashboard and your Excel workbook:
​
There are two aspects to protecting your dashboards:

• Protecting your workbook structure, and
• Protecting the integrity of your actual dashboard

Let's start with the first one: Protecting your workbook structure
​

• Step 1: To make sure that your user does not have access to your raw data and pivot tables, you're going to want to first "hide" those sheets in your Excel workbook. To hide a sheet, just right click on it and then select "hide" from the dialogue box. Easy enough?

 

• Step 2: Once you have hidden all of the sheets that you do not want users to gain access to, the next step is to password protect your workbook.  To do this, go to the Review ribbon and select "Protect Workbook." In the dialogue box, make sure that "Structure" is checked. Next, enter your password and viola! If you have done this correctly, your user will not able to unhide any of the sheets in your workbook. Try it out for good measure!

​

​Let's do the second one, now:  Protecting the integrity of your dashboard

Sometimes, when a user is playing around with the dashboard and slicing and dicing the data to their hearts content, they might encounter two nuisances: one, the "Format Chart Area" side bar might pop up when they click on specific charts; and/or two, they may inadvertently move your charts and figures around on your dashboard. Ugh!

Both of these things have happened to me...and my clients thought that the dashboard was broken or "not working correctly." 

Please learn from my mistakes and do the following steps to avoid these mishaps:

• Step 1:  Unlock your slicers before protecting your sheet. You still want the user to use the slicers, right? If you protect your sheet  before unlocking your slicers, the user will NOT be able to slice and dice the data. Here's how to unlock your slicers:​
  • First, select all of your slicers by holding down the shift key. Then, right click on your slicers and go to "Size and Properties." Once you select "Size and Properties," a side bar called "Format Slicer" should appear. Within this side bar, you will want to go to "Properties" and UNCHECK "Locked."

• Step 2: Once you have unlocked your slicers (make sure that each slice is unlocked!), then you'll want to lock your sheet. 
  • Go to the Review ribbon. Select "Protect Sheet." Finally, put in your password. Done!
  • If you've done this correctly, you should NOT be able to move your charts around. 

***As a bonus, consider fixing your slicers in place. That is, if you want to keep your slicers from moving, you will need check the "Disable resizing and moving" option. To do this, right click on your slicers; select "Size and Properties"; go to "Position and Layout" and make sure that "Disable and resizing and moving" has a checkmark next to it. 

Another fully protected dashboard has been born. You're welcome, HIPA, FERPA and IRB boards!