Excel tip: Make decimals display conditionally
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? Here’s the step-by-step sequence:
- Pick the cells you want to format with the conditional treatment.
- Press Ctrl-1 to open the Format Cells dialogue. [In Excel 2007 and later, you can click the Number button on the Home tab, as shown above, or in Excel 2003 and Excel for Mac, choose Format and then Cells.]
- Choose Custom from the Number tab of the Format Cells dialog box.
- Enter the following formatting code in the Type field: [>=1]#,##0;[<1]0.00
- Click OK to close the Format Cells Dialog box.
- With this configuration, numbers less than 1 will appear with two decimal places. Unfortunately this reasonably simple approach may have an unintented side effect. If you change the value of cell A2 to 3383.75, Excel will display 3,384 instead of 3,383.75. If this is likely to be a problem, read on to find out how to Excel's MOD function can help you deal with it.
The conditional number format relies on tests for two different conditions:
- [>=1] – This test determines if the number in the cell is greater than or equal to 1. If so, then Excel formats the number to show commas as needed for thousands, millions, and so on, with no trailing decimal places.
- [<1] – This test determines if the number in the cell is less than 1, and if so, Excel formats the number with a leading zero and two trailing decimal places.
In custom number formats, # indicates placeholders to be used when needed, while 0 indicates that either an actual number or a zero placeholder will be shown. Conditional tests such as the ones we've used are placed within square brackets, and each set of conditions is separated by a semicolon.
Custom number formats apply only to a given workbook, so you'll need to create the formats again if you need this functionality in other spreadsheets as well. But you can apply the format to other cells in the same worksheet (or remove it from the list when it's no longer needed). Here’s how:
If you’ve read some of my previous tips, you’ll be aware of my enthusiasm for Conditional Formatting, which can help you handle a staggering array of situations. Here’s we’ll use conditional formatting along with the MOD function to identify when our conditional formatting should be applied.
The MOD function returns the remainder after a number is divided by a divisor and takes this form:
To put it another way, MOD returns the amount to the right of the decimal place. In this instance, for example, MOD would return zero because two divides evenly into four:
The nexts formula would return .5 because two doesn't divide evenly into three:
Since MOD returns either zero or an amount, we can use it to conditionally display decimal places.
We'll simply divide the amount in a cell by 1. If the cell contains a whole number, MOD will return zero, otherwise it will return the decimal portion of the cell's value. It's in this fashion that we'll conditionally format cells with either no decimal places or two decimal places.
To apply conditional formatting, enter numbers into cells A1 through A3 (as shown above), and then carry out these steps:
1. Select the cell or cells you wish to conditionally format.
2. Access the Conditional Formatting feature:
- Excel 2007 and later: Choose Home, Conditional Formatting, New Rule, and then Use a Formula to Determine Which Cells to Format.
- Excel 2003 and earlier: Choose Format and then Conditional Formatting.
- Excel 2011 for Mac: From the Home tab, choose Conditional Formatting, and then New Rule, or choose Format and then Conditional Formatting from the menu and then click the Plus sign. When the New Formatting Rule window appears, choose Classic from the Style List, and then specify Use a Formula to Determine Which Cells to Format.
3. Enter the following formula:
4. Click the Format button, click the Number tab, choose 0 decimal places, select Use 1000 Separator (,) and then click OK twice.
5. Add the second condition:
- Excel 2007 and later, Excel 2011 for Mac: Repeat steps 2 through 4, but this time use the formula =MOD(A1,1)<>0 for step 3 and specify two decimal places in step 4.
- Excel 2003 and earlier: Choose Format, Conditional Formatting, and then click Add. Specify =MOD(A1,1)<>0 for Condition 2, and then carry out step 4, but specify two decimal places.
Once applied, you can easily remove conditional formatting:
- Excel 2007 and later: Select one or more cells, and then choose Home, Conditional Formatting, and make a selection from the Clear Rules menu.
- Excel 2003 and earlier: Choose Format, Conditional Formatting, Delete, and then remove any unwanted conditions.
About the author
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.