Omni-directional, Vertical-lift, Helicopter Drone by Vanderlip // PerfectXL Spreadsheet Validation

PerfectXL Risk Eliminator

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 guideline we talk about the dangers of hidden information.

A little exercise

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.

The Dangers of Hidden Information // PerfectXL Risk Eliminator

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 think back to Guideline 4: Distinguish between input, calculation, and output sheets. If you head 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.

The Dangers of Hidden Information // PerfectXL Risk Eliminator

How can PerfectXL help you?

Suppose you get a spreadsheet with all kinds of hidden information, try the PerfectXL add-in! In the menu bar of the add-in you will find a button “Unhide Everything”. All rows, columns, and hidden or very hidden tabs will be made visible. What’s nice is that, the button remembers what has been made visible and changes itself into “Rehide,” when you click on this, all information goes back to the way it was. In addition, the risk overview of PerfectXL can also help you: Once you’ve analyzed your sheet you can find a list of all hidden information in the risk overview.

Find hidden information in spreadsheet // PerfectXL Risk Eliminator

  • Avoid >70 types of risks
  • Improvement suggestions
  • Quality reports

Read more about risks in Excel files

On this page you will find a step-by-step walkthrough of the PerfectXL Risk Eliminator.

Here you will find information like system requirements, release notes, and answers to most questions about our software.

Here you will find information like system requirements, release notes, and answers to most questions about our software.

Our software inspects spreadsheets on all kinds of spreadsheet risk. Read more about some of the risks we detect in spreadsheets here.

Excel horror stories are often related to accidentally changing, shifting or changing units or number formats.

Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them.

(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references.

Whenever possible, keep your Excel formulas short and simple. Avoid long formulas; they are harder to read, harder to understand, difficult to check and tough to improve.

The use of fixed numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.

Sometimes, you need to use the exact same formula, based on the same numbers, in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation. Calculate your formulas only once!

It is so tempting to merge cells in Excel so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous!

It’s a bad idea to leave standard Excel errors in your workbook. Take the time to clean up, because after a while, you no longer know whether you left a mistake consciously or if there is something wrong with your spreadsheet.

Place a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else.

To be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet.

Reporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel.

Good, error-free spreadsheets are essential for using them reliably in business. This is why risk detection is an important aspect of spreadsheet validation.

Excel is a powerful tool. And when it can’t do what you need, VBA can fill the gap. Unfortunately, using VBA can lead to unpredictable behaviour, slow performance, and corrupt Excel files.

Do you wish to refine PerfectXL Risk Eliminator’s analysis to cater for your specific requirements? PerfectXL allows for full customization of your user interface.

How can PerfectXL help you?

Share your questions with us, we are more than happy to help you. We will get back to you within 48 hours.