The Dangers of Hidden Information
Do not hide anything except some sheets. Excel has too many nice options. In Guideline 6, we already advised not to use the option of merging cells, or only if really necessary. In this guideline we talk about the dangers of hidden information.
A little exercise
Open a new document in Excel and enter a number in A1 and B1. Sum them in C1 and hide column B. Now copy A1: C1 (apparently 2 cells) to elsewhere. Three newly filled cells appear. Logical, if you think about it. But surprisingly and dangerous if you were unaware of the hidden column.
There are many opportunities in Excel to hide parts of your spreadsheet:
- You can hide columns or rows. One or more at a time;
- You can hide information by giving text and background the same color;
- You can use the custom formatting feature. By “;;;” To use as formatting coding nothing is shown;
- 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 hide entire tabs;
- And finally, you can make a tab – seriously – very hidden through the developer menu and the VBA code page. Then you can not retrieve a tab right-click but only through this VBA screen.
We think there is only 1 way of hiding acceptable and often wise: hiding entire tabs (not very hidden). The dangers of clip and insertion problems are minimal. 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.
What to do with complicated calculations?
The situation is common: you have some input numbers, a series of calculation steps and a result. All those steps can be confusing to the reading user. Hiding the calculation columns seems attractive. But … think of Guideline 4: Distinguish between input, calculation, output sheets and possibly a dashboard. In this case, do not worry about having a spreadsheet containing all the columns neatly and one output sheet with only a repeat of the input and output. Much better maintainable and more stable than hidden columns.
How can PerfectXL help you?
Suppose you get a spreadsheet with all kinds of hidden information. Then try the PerfectXL plugin! Not yet officially launched, but on request you can try it. In this menu bar plugin you will find a button “Unhide Everything”. All rows, columns, and hidden or very hidden tabs will be made visible. And it’s nice, the button remembers what has been made visible and changes itself into “Rehide”. In case the end user really does not want anything else. In addition, the usual edition of PerfectXL can also help you: After uploading your spreadsheet, a list is made of all hidden information.