Excel Data Validation as an unsupported feature in SharePoint and the perils of hidden worksheets

Several, if not all, of my posts so far have been based on SharePoint.  We have several teams where I work that publish and analyze reports, and several others that rely on the data of said reports.  As such, Im tasked with taking much of our business data and making it more "web enabled" (what a novel concept!).

So I moved one of our report's into SharePoint, which I received the following error (side note.. wouldn't it be great to validate the file before publishing it Microsoft?)

The workbook cannot be opened because it contains the following features that are not supported by Excel in the browser:

- Data validation

Would you like to try and open this file in Excel?

OK, after a few minutes of Googling, I found that there's a command to find data validation conditions.  In each workbook, choose Find and Select from the Home tab, Go To Special, and Data validation.  If the search finds something, it'll move to the appropriate cell, otherwise you receive an alert that there is no validation on the worksheet.

Once you happen upon your cell with validation, choose the Data tab, click Data Validation, and choose clear all.

Just repeat the steps listed above for each worksheet to remove all your validation.

Make sure you scrub ALL your worksheets..

Upon completing these steps, I published the file again, but still receive the same error.  I re-checked for data validation, published as different file names, restarted IIS, anything thinking it wasn't my excel spreadsheet... because I couldn't find the data validation anywhere else. 

Then, I found out that there were HIDDEN worksheets in my workbook too.  I right clicked the worksheet area, clicked unhide, and chose the hidden worksheets.  Wouldn't you know it, the hidden worksheets also had data validation.

Simplify, simplify, simplify:

So in closing, I learned the following:

• You can "find" data validation in Excel via the Find and Select command
• You need to "guess" if worksheets are hidden, and be sure to unhide them when removing any type of "unsupported features" for Excel Web Access in SharePoint
• When publishing a workbook to SharePoint, the unsupported features must be removed from EVERY worksheet\item\range in the file, not just what you are choosing to "publish".