Introduction

Spreadsheets, and technology in general for that matter, can be fantastic. Until that is, things don’t go as expected. It’s all too easy for an entire month’s productivity gains to be lost when sorting out a single spreadsheet problem. In this series, we will be look at some examples of Excel not doing what we expect and at how to deal with the problems.

So far, we have looked at the basic problem of our numbers not adding up and looked at solving the problem using Excel’s ROUND() function before warning about the use of the Precision as Displayed option and examining the issues of ranges that don’t include all the cells that they should. Last time we looked at another cause of mysterious maths issues – numbers that are not always numbers (but are sometimes…), before moving on to the use of the IF() function last time.

This time we revisit a common inspiration for Excel Community articles – controlling how numbers are formatted.

Custom number formats

The Excel Community article archive already includes several articles detailing exactly how to create and apply a custom number format, including as part of our Accountants’ Guide series. We are going to concentrate on how number formats cope with zeros and values that round to zero.

Of the four sections of a custom number format, it is the third that defines how zero values will be formatted. In the following, typical, format we have set the third section to -?. This uses a dash for zeroes, with the ? moving it a standard space in from the right:

#,##0_);[Red](#,##0);-?

This look as though it works as we require it to when we have positive numbers, negative numbers and zeroes:

However, if we have values that round to zero when formatted, our custom format doesn’t use our dashes as we might have hoped:

Because our third section only applies when our value is 0, it is instead the first and second sections that apply to our 0.4 and -0.4 values. We could avoid this by using the ROUND() function that we looked at in the first part of the series, so our 0.4 and -0.4 are rounded to zero before being formatted. However, there is an alternative that avoids the use of the ROUND() function.

There is a little-known variant of our custom number format that incorporates custom conditions. Instead of our first three arguments being used for positive, negative and zero values respectively, we can add conditions to our first two arguments. Our three sections will then apply the first two sections on the basis of whether each condition is met, with the third section applying to any values that match neither of the conditions applied to the first or second sections.

In the following example, we have added a condition to the first format section so that it will apply to any values greater than or equal to 0.5, with a condition applied to our second section so that it will apply to any values less than or equal to -0.5. This ensures that all values that will round to zero, rather than just those that are exactly zero, will be formatted using the third section. We have added values of 0.5 and -0.5, together with a column using the ROUND() function, to our example to demonstrate the different treatments:

[>=0.5]#,##0_);[Red][<=-0.5](#,##0);-?

As we can see, our values that are not zero but that do round to zero are now also formatted as a dash using our third custom format section.

Next time

Next time we will look at another use of conditions in Excel – Conditional Formatting and consider why the order of Conditional Formats matters.

Additional resources

You can explore all aspects of Excel, including several articles on number formatting, in the ICAEW archive:

• Excel archive