This week I want to talk a little about Excel and how to hide your sheets, but first a shameless plug. I will be speaking at the South Florida SQL Saturday this Saturday, June 29th. My first presentation will be on how to clean your data using SQL Server 2012 DQS (Data Quality Services). Good clean data is vital when performing good data analysis with your favorite BI tool (SSAS or Excel with PowerPivot). My second presentation will be an introductory presentation on using SSAS to build cubes. For those who may say it is not different than working with PowerPivot in Excel, you are maybe 90% correct. There are some differences, not just in the organization of the menus, but also in the way you build things. But I’m not going to give everything away here. Come to Ft Lauderdale to find out.
Ok, over the past 3 years, I’ve gained a new respect for the use of Excel in the work that I do. As you know, it began with the introduction of PowerPivot for Excel. Over the months I learned some new tricks when using Excel and I will share one with you today. When using the original PowerPivot for Excel, every time I created a new Pivot Chart, I would get an extra sheet in my workbook which essentially built the data needed for the chart. Normally I would not want to display that sheet to end-users because a) there is nothing for them to do on that sheet, and b) I did not want them to accidently change anything that might affect the chart. I suppose at the time, the code needed that extra physical sheet in order to serve as the data source for the chart just like any other chart in Excel. (In Excel 2013, that no longer appears to be the case, but keep reading anyway please.)
Anyway, I started to hide these extra sheets so the typical user would not know they were there. Initially, I used a very basic way of hiding these sheets. I would first open the Review ribbon and then select Protect Sheet from the Changes group.
By protecting the worksheet with a password, even if a user found the sheet I was about to hide, they would not be able to open and modify anything on it.
Next I would right click on the sheet tab (at the bottom of the spreadsheet) and select Hide.
This causes the sheet tab to disappear from the list of tabs at the bottom of the screen, but the data was still there and could be used by the chart. Of course you could use this technique to hide any sheet in your workbook.
To restore a hidden sheet, all I would have to do was to right click on the tab and select Unhide and pick the sheet (if there is more than one hidden) from the dialog. Obviously hiding a sheet provides no security. That is why I first protect the sheet with a password.
While this works fine for most cases, I found there was another way to hide a sheet. Microsoft, in their infinite ability to name things clearly, calls this Very Hidden. To do this, you must go to the Developer ribbon. If you do not see the Developer ribbon, you may not have turned it on. To turn on the Developer ribbon, go to File à Options. Then select Customize Ribbon. Check the box before Developer in the Customize the ribbon Section and click OK.
Now in the Developer ribbon, click on Properties in the Controls group.
Find the property Visible which should be at the bottom of the list. Open the dropdown box of possible values and select the value: 2 – xlSheetVeryHidden. (Yes, I know. This option is also very hidden.)
Immediately the screen will update and it may not appear that anything happened unless you notice that the sheet you were looking at is gone. Another sheet is displayed and the original sheet’s tab does not appear anymore at the bottom with the other page tabs either.
So how do you get the sheet back if you need it? Again the steps are a bit hidden from normal users. You begin by opening the VB Editor. You can do this by pressing Alt-F11 or by selecting the Visual Basic icon in the Developer ribbon. Next select sheet that was hidden from the Microsoft Excel Objects and look in the Properties window (open the Properties window by pressing F4 or by selecting it from the View menu). Again find the Visible property and change it back to: -1 – xlSheetVisible.
The sheet will now be visible again. The bad news is that since I told everyone that reads this blog how to recover a very hidden sheet, I guess you will still want to use the Protect Sheet option with a password that I mentioned at the beginning of this article.
C’ya next time.