Excel tip: Make decimals display conditionally

Kashflow logo

Transatlantic trainer David H Ringstrom shows how to expand your formatting repertoire from cell colours and shades to include number formats.

Formatting decimal places is reasonably simple, but can cause grief for spreadsheet users. For example, if your list includes both large and small numbers, there’s a conflict between rounding the small numbers to whole values or adding trailing zeros, which would make the large numbers harder to read.

The tips in this article will simplify that conundrum; first by using a custom number format to add decimal places; and secondly by combining the MOD function with Conditional Formatting to achieve the same end.

Below is a sales report showing currency amounts in columns C and D, with common formats for each column. Column A, however, displays the amount in cell A2 as 3,383.00. How do you present this figure as 3,383, while simultaneously showing the amount in cell A3 as 0.50?

Register for free and log into AccountingWEB to see the full tutorial.

David Ringstrom CPA is the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and  AccountingWEB.com as well as contributing articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. More Excel tips from David H Ringstrom available here.

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About David H Ringstrom

Replies

Please login or register to join the discussion.

14th Mar 2014 13:50

Avoiding (0)

Thanks David - an additional practical use for custom number formats that include conditions is to avoid 0.4 appearing as (0) rather than a dash when using a number format that doesn't display pence:

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

Thanks (3)

Related content