Rowing Machine // PerfectXL Spreadsheet Validation

PerfectXL Risk Eliminator

Pay attention to units and number formats

Excel horror stories are often related to accidentally changing, shifting or changing units or number formats. Where Americans think in Dollars, Europeans think in Euros. Large organizations think in thousands, while small organizations might calculate in cents, and the English have their own units for everything.

Confusing number formats

Consider this example: In a cell there is a point in the number 3.456. In some countries this means three thousand four hundred and fifty-six, in other countries this is only three-and-a-half. So was this point a meant as decimal or a thousands separator?

Also notorious is the very annoying difference between the two common date formats: day-month-year and month-day-year. Is 11-02-18 February 11th, 2018 or November 2nd 2018?

Pay attention to units and number formats in Excel

Let Excel do the job

Our first advice is that you use one of the standard number formats that Excel offers you. This makes numbers easy to read. Our favorite format is the accounting style, but this is of course a matter of personal preference. Excel automatically adjusts the notation so that when another user opens the same spreadsheet with other settings, they will see it in their own format.

Please make sure that data you import from other programs match what you expect in Excel. Test this carefully, because it can easily go very wrong. One of the consequences of import data that doesn’t match your formatting can be that numbers are read as text, which Excel then doesn’t take into account in formulas, generally speaking.

Custom formats

As we mentioned before, your information is changed according to the next user’s personal format settings. Units and number formats like dates and currencies, but also measurements. We suggest you apply formats to anything that contains any type of measurement of length, weight, height, anything really. You can also add a column or clarify in your header what the format of the data presented is, because it is important to make this information clear to future users, and even to your future self.

Date fields

Then there is the problem of the two date formats Day-Month-Year and Month-Day-Year. If users enter date fields themselves, it usually goes well. People know which setting their Excel has. But the problem often lies with import dates from other systems in the form of CSV files. There is a way to see if Excel can determine all the dates correctly: Make the column containing the dates a bit wider than necessary. If in the series of date fields one or more dates are aligned to the left instead of the right, then something is wrong, because then these fields are not recognized by Excel as dates and that could be caused by the rotation of day and month.

  • In depth risk analysis
  • Improvement tips
  • Smart Reporting

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.

VLOOKUP and the combination of INDEX and MATCH are well known ways to search in Excel, and to our great frustration, VLOOKUP is used a lot more often.

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

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.

Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run.

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. Make sure that you 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.

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.

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.

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.