Geek, programmer, professor, e-discovery guy, web enthusiast, blogger.

I'm not crazy, I just speak code.

General Ramblings & Rants About E-Discovery, the Web and Code.

There's Hidden, and Very Hidden Data in Excel

There may be data that avoids the eye.

Programming, Metadata, Data Processing, Excel

Author: Trent Livingston

Hidden data Microsoft excel

While the coding world is aware of this, others may not know that data can be "very hidden" in Microsoft Excel. This very hidden data does not reveal itself through normal means and can only be revealed via a programmatic process. Don't let this hidden data go unrevealed, as it could pose a significant risk during discovery. What may be very hidden could be data you never thought you were disclosing.

Years of programming experience has taught me that if you want an application to stand on its head, it often requires some massaging to make it happen. Microsoft Office provided the VBA interface (Visual Basic for Applications) for this specific type of application development. VBA developers can get tricky, and while it may not be the initial purpose to hide data, the methods exposed via VBA could be leveraged in devious ways. 

For those of you reading that aren't familiar with the discovery process, let me quickly brief you. To summarize, discovery of data basically involves the collection of various types of electronic files and email (data), which may include documents such as Microsoft Excel worksheets. Once this data is collected, it is indexed and searched so that potentially relevant data may get reviewed by counsel. There are several other components, but I may have you asleep and drooling on your keyboard should I dare dive into the overall minutia. The key piece to remember here is the index and searching of documents. 

There are a couple of ways to hide data in an excel worksheet.  First, you can right click and hide data by selecting the Hide option from the popup menu (pictured below).  This menu can also be accessed from View > Hide.  By selecting this, you can hide columns, rows, cells, or entire worksheets. 

If a user wishes, all they have to do is highlight the entire workbook (all worksheets, etc.) and select Unhide to expose all data that was hidden via the above method. The only way to prevent the end-user from accomplishing this is to password protect the worksheet or workbook. However, there is one other way to hide data which will not respond to the Unhide command. You can make it Very Hidden by using the VBA editor and access the properties of the worksheet. Once this is done, the only way to unhide the worksheet is to use either the VBA editor again, or actual VBA code. Below is an example of the steps you can use quickly to access the VBA editor and worksheet properties.

1) Right click a worksheet tab and select View Code from the popup menu, which will open the VBA editor window.

view code

2) Once the VBA editor window is open, look to your project window (usually upper left corner by default) and expand the VBAProject to expose the worksheets (see example below)  and highlight the sheet you would like to make "Very Hidden".

3) Immediately below this window is the worksheet Properties window.  At the very bottom of the Properties window for the worksheet you've highlighted, you will see a property of Visible.  Click to the right of that on the default property "-1 - xlSheetVisible"and the drop down will appear. Change this to "2 - xlSheetVeryHidden".

Viola! Now your worksheet is "Very Hidden" and will not respond to the Unhide command! (Don't forget to save the sheet!)

That being said, now comes the statement, "So what?!" Well, this in itself is not a big deal at all, but here's the catch. Just because the sheet is set to xlSheetVeryHidden doesn't mean that the data is not responsive to indexing and search. That's right, only the visibility is off, but standard indexing tools such as dtSearch, or even file system tools like Mac's Spotlight, will find the document's contents using terms that may only be found on the very hidden worksheet.

For the layperson (and the professional), remember that we mentioned indexing and search being the key piece to remember? What this all means is that the Excel document, if it contains very hidden sheets that house relevant information, would be responsive to the search criteria. The issue is that this key data would likely get passed over during review because of the data's xlSheetVeryHiddenvalue. Or worse yet, actually produced without all of the data within the hidden worksheet being reviewed for privilege or confidentiality.

I will add that this is likely not a big deal for a myriad of discovery matters, but if Excel workbooks are of importance, it could prove to be a thorn in the side of a production to opposing counsel. So what do you do? The only way to Unhide data that is hidden with this method is through programmatic means or reversing the manual steps outlined above (resetting the sheet to Visible). To unhide all worksheets, the VBA statement below will need to be incorporated into a macro and executed against each worksheet in your Excel data population prior to review.

Sheets("SheetName").Visible = True

Note that if the user has decided to password protect their code, this could present additional obstacles, as it would prevent access to the VBA editor. 

So in the end, what is hidden can be made visible, but if very hidden, it may take some very elaborate steps to reveal.  Kind of like my ex-wife's alternate personality, but that's an entirely different story!

PREVIOUS POST   -   NEXT POST

The views and opinions expressed are those of the author and do not necessarily represent that of their company or organization. Any external links provided are for information purposes only and do not represent endorsement.