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?

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.