The more I use Excel, the more I am amazed at the number of ways I can construct a spreadsheet that obscures data and misleads people. I thought that a comprehensive list would be helpful to spreadsheet auditors, if only because forewarned is forearmed. There is nothing in Excel to discourage this kind of thing, and this list will give you an idea what to look for:
Method 1 - Hidden Rows/Columns
This is probably the first cell hiding method most people learn. After a while the slightly thicker column breaks are obvious, and while a clever designer might use protection to keep their hidden things hidden, Excel’s built in protection is weak at best.
Method 2 - White-on-white
Another very common method for hiding data is using matching foreground and background colors. Click on a cell and change the text color to white, and there you go, we’ve got an invisible cell, at least sort of. Certainly, Excel add-ins and macros can still see it:
It is also fairly easy to turn off. Now you can get a little cleverer here–black on black, red on red–it’s somewhat less obvious. If you play with conditional formatting, you’ll find it’s even better. With normal formatting, the toolbar to fix it is right there staring you in the face, but with conditional formatting, you might not even know its there. What’s more, you can click all you want on that black foreground option, and the text will still stay white until someone actually goes digging into file menus and disables the conditional formatting.
Method 3 - Graphs and Images
Rather than changing text colors or hiding entire rows, another option is to just stick something right on top of the value you want to hide. You can use images, graphs, embedded Visio documents, or any of the other strange things Excel will let you insert into a sheet. Once it’s covered up many people won’t even think to look. Interestingly, Audit’s overlay draws right on top of these objects, so it’s hard to use them to hide anything from someone using it:
Method 4 - Hidden and “Very Hidden” Sheets
Rather than hide a row or column, it’s also possible to hide an entire sheet in Excel. One advantage to this method is that unless someone is looking at each and every formula, it will be easy for them to miss the reference that leads off to the other sheet. Using Excel alone, the only way someone is going to notice those hidden sheets is by going into the menus and selecting the unhide sheet option. To get around that you can create a “very hidden” sheet with VBA. Once you’ve done that, the sheet won’t show up in Excel’s list. Audit, on the other hand, ignores this property, so you can still see a list of every sheet in the Regions Navigator–hidden, “very hidden” or whatever.
Method 5 - Hidden Formulas
While this method doesn’t hide the formula, it hides the logic behind the formula which is often just as good, especially in conjunction with a hidden sheet or one of the other methods above. To hide the formula, just go into Format Cells | Protection and you can check the ‘Hidden’ box. Then turn on protection and you’ll see that you can’t get the underlying formula for that cell. This, unlike the other methods, does hide things from programs, so if you’re using Audit on a spreadsheet you’ll want to check and see if protection is on and turn it off if you can.
Method 6 - Custom Format
This is what I’ve been looking at recently, and if you’ve ever played around with custom formats you can find some extremely strange behavior. The easiest way to hide something is go into the custom format option in the Format Cells dialog and type in three semicolons (;;;) and you can see that the cell doesn’t display anymore. That’s interesting, but possibly more interesting is what happens if you type in a number, or some words:
“Foo”, is in this case, 5. It’s a number, it behaves like a number in the sum below, but it shows up as something else entirely. It’s easy to see how you could create a column heading that was a number, and then overextend a formula below to grab it, and no one would ever suspect a thing.
Posted by: Erik Rehn, Product Manager, RedRover Software Inc
28
2008
” I thought that a comprehensive list would be helpful to spreadsheet auditors”
Yes, the list of the above techniques, and how to detect and defeat them, and others, are in my book “Spreadsheet Check and Control”
http://www.sysmod.com/scc.htm
Regards
Patrick