Most experienced Excel users know how to find and unhide hidden worksheets in an Excel file. Typically, you can right-click on any tab name and you will see the Unhide option if there are any hidden worksheets. (More about that below) Any attorney reviewing Excel files–whether for an outgoing production, or as incoming evidence–must know how to identify and expose the hidden worksheets.
But there is a deeper, more sinister level of hiding that will not even trigger the Unhide option. This level is called, in Microsoft’s parlance, “Very Hidden” – keep reading to learn how to identify and unhide these worksheets.
In this example file (download it by clicking here) there are 4 worksheets: two unhidden worksheets, one that is hidden normally, and one that is Very Hidden. In the default view you can see only the two unhidden worksheets: Perfectly Legal Deals and More Upstanding Deals
When you right-click on any of the tabs, you see the option to Unhide; when you click on it, there is only one worksheet offered for unhiding. It is called Sketchy Deals.
Once you unhide it, Excel will not offer you any more worksheets to unhide; the Unhide option is now grayed out. It seems as though you are done.
You have to dig deeper to find and expose the Very Hidden worksheet. Perform the following steps: Right-click on any visible tab, but rather than choosing Unhide (it should be grayed out at this point), choose the option View Code. This will launch a new window called VB Editor. Don’t panic! You won’t have to write any macros.
A Project window and a Properties window will pop up. In the Project window, you will see the Excel file listed as a “VBAProject” and each of its worksheets listed as Microsoft Excel Objects.
If you have multiple files open, all of the files and their worksheets will be visible in the Projects window, so you might want to close any other Excel files while you do this, or hit the – sign to collapse each workbook in the Project window.
Now, you’ll see all of the worksheets, by name, regardless of Hide level. Note the lurking worksheet called Downright Illegal Deals. Click on this worksheet and a list of Properties will pop up below. The visibility setting is the last property listed. For a Very Hidden worksheet such as this one the setting is “2 – xlSheetVeryHidden”
An unhidden worksheet will have a visibility value of -1 (“xl Sheet Visible”); a normally hidden worksheet will have a visibility value of 0 (“SheetHidden”). The higher the number, the more hidden the worksheet is.
Now for the fix: There is a drop-down here where you can change the visibility setting, so you can change the sheet from Very Hidden to normally hidden, or unhidden (or vice-versa). That simple!
Here, use the dropdown to change the visibility for Downright Illegal Deals from 2 to -1, making it unhidden.
When you are done adjusting the visibility settings, close out of the VBA editor by hitting the x for the Microsoft Visual Basic window. The Excel window will stay open.
The material in the formerly Very Hidden worksheet can now be viewed, analyzed, printed, and–my favorite part–entered into evidence. And beyond the evidentiary value of the exposed material, the fact that your adversary went to all the trouble of burying it in a Very Hidden worksheet should itself be fodder for a rough cross-examination at deposition or trial.
The possibility that your discovery may include Excel files with Very Hidden worksheets may be unsettling – do you have to open the Visual Basic editor for every Excel file to confirm there aren’t any Very Hidden worksheets lurking? Fortunately, no. The Document Inspector tool will flag the presence of any hidden worksheets, whether normally hidden or Very Hidden. Click on the File Ribbon and you will see critical issues listed under Prepare for Sharing ⇒ Inspect Workbook. Here it will indicate the presence of any hidden worksheets, whether they are normally hidden or Very Hidden.
Remember you can also run Check for Issues, and the report will tell you how many hidden worksheets there are. If you have already unhidden the normally hidden worksheets, but Check for Issues is telling you that there are still some hidden, this is your cue to follow the steps described above to find and unhide the Very Hidden worksheets.
Think about how your eDiscovery software handles files with Very Hidden worksheets. Can it flag those files as containing hidden content? Will that content make it into the OCR text? These are the questions that keep Excel Esquire up at night . . . . .
Thanks to the wmfExcel blog for bringing this issue to my attention. Read that post here.
I hope you enjoyed this post. If you would like to learn how to host an Excel workshop at your bar association, law firm, company, or public interest organization, please get in touch with Ben Kusmin of Excel Esquire.