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 might think in Euros. Large organizations think in thousands, small organizations might calculate in cents. And the English have their own units for everything.

Excel Guideline 14: Pay Attention to Units and Number Formats :: PerfectXL

Confusing Number Formats

Consider this example: In a cell there is a point in the number 3.456. In many countries this means three thousand four hundred and fifty-six. But in other countries, however, this is only three-and-a-half. Is this point a decimal or a thousands separator?

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

Let Excel do the Job

Our first advice is: use one of the standard number formats that Excel offers you. This makes numbers easy to read. Our favourite is the accounting style: thousands separated, some white space to the right of the number, and zero displayed as -. Excel automatically adjusts the notation, so when another user opens the spreadsheet with other settings, such as the point and decimal point reversed, they will see it in that form.

Please make sure that data you import from other programs match what you expect in Excel. Test this carefully, because it really often goes wrong. The consequence of unmatched import data can be that numbers are read as text, which Excel then doesn’t take into account in formulas. Well… not always….

Standard or in the Header

Financial units such as Dollar and Euro can also be set in Excel’s numerical format. Put a Euro or Dollar mark in front of a number or have Excel do this. But never put such a sign behind the number, because then Excel sees the entered value as a text rather than a number, with the result of skipping it in formulas.

Excel Guideline 14: Pay Attention to Units and Number Formats - Date Formats :: PerfectXL

If the unit of a used number is not entirely clear from the context, don’t hesitate to place the unit in brackets behind the title of the row or column. This prevents a lot of misunderstandings.

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 date and that could come from the rotation of day and month.

Never miss a Guideline!