The dangers of hidden information

Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run. In this article we talk about the dangers of hidden information.

A little excercise

Open a new document in Excel and enter a value in the cells A1 and B1. Use “=A1+B1” to add them in cell C1 and then hide column B. Now copy A1: C1 (which now looks like 2 cells) to somewhere else in the worksheet. Three newly filled cells appear. Logical, if you think about it, but surprising and dangerous if you were unaware of the hidden column.

There are many ways you can hide stuff in Excel:

• You can hide columns or rows. One or more at a time;
• You can hide information by giving the text and background the same color;
• You can also hide information using the custom number format feature. By typing in“;;;” under custom number formatting your information is made hidden;
• With cell properties -> protection -> hidden, you will not see the underlying formulas of cells after you have protected the worksheet. The result is still displayed in the cell, but the formula bar remains empty;
• You can also hide entire tabs;
• Finally, you can make a tab – seriously – very hidden through the developer menu and the VBA code page. Then you can not retrieve a this tab with right-click but only through this VBA screen.

We think there is only 1 way of hiding information that is acceptable and often even wise: hiding entire tabs (not very hidden). The dangers of clip and insertion problems are minimal as the whole sheet is hidden. The user-friendliness of the spreadsheet is really increased and, with the right-click button on one of the tabs, it’s easy to see which tabs are exactly hidden. Even in this situation we suggest you clearly notify the user in the documentation sheet that the tab exists and is hidden.

What to do with complicated calculations

This situation is fairly common: you have some input numbers, a series of calculation steps and a result. All those steps can be confusing to the user who didn’t create the sheet. Hiding the calculation columns seems attractive but read our article ‘Separate input and output‘ and distinguish between input, calculation, and output sheets. If you follow the advice in that guideline, hiding calculations is not necessary. This is much easier to maintain and it’s a more stable solution than hiding columns.