Conditionally Displaying Decimal Places in Excel: Part 1

By David Ringstrom, CPA
 
The simple task of displaying decimal places sometimes causes angst for spreadsheet users. If you have a list of both large and small numbers, there's tension between rounding the small numbers to whole values and making the large numbers harder to read by adding two trailing zeros.
 
In this article, I'll describe how to add decimal places on demand by way of using a custom number format. In Part 2 of this series, I'll demonstrate an alternative that uses the MOD function along with Excel's Conditional Formatting feature.
 
Figure 1 shows a sample report with sales figures. Columns C and D both show currency amounts, so the same formatting can be applied to all cells in those columns. However, in column A, rather than displaying the amount in cell A2 as 3,383.00, we'd like to present it as 3,383, but simultaneously show the amount in cell A3 as 0.50. 
 
Figure 1: You can conditionally display decimals by way of a custom number format.
 
As shown in Figure 1, the simplest approach to our task is to create a custom number format:
 
1. Select the cells to which you wish to apply a conditional number format.
 
2. Press Ctrl-1 to display the Format Cells dialog box. In Excel 2007 and later, you can click the Number button on the Home tab, as shown in Figure 1. Or, in Excel 2003 and Excel for Mac, choose Format and then Cells.
 
3. Choose Custom from the Number tab of the Format Cells dialog box.
 
4. Enter the following formatting code in the Type field: [>=1]#,##0;[<1]0.00
 
5. Click OK to close the Format Cells Dialog box.
 
6. As Figure 1 shows, now numbers less than 1 will be displayed with two decimal places. However, as often is the case in Excel, this simple approach may yield an unintentional side effect. If you change the value of cell A2 to 3383.75, Excel will display 3,384 instead of 3,383.75. (In Part 2 of this series I'll provide a solution for this by way of Excel's Conditional Formatting feature and Excel's MOD function.)
 
Any discussion of custom number formats, such as the one shown in Figure 1, can get deep fast, so I can only offer the briefest of explanations in this article. In this case, I've crafted a conditional number format that 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.
Within 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. Figure 2 demonstrates how you can apply the custom number format you created to other worksheet cells or remove it from the list when it's no longer needed.
 
Note that custom number formats such as the one shown in Figure 1 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.
 
Figure 2: You can easily apply custom number formats you create again in the future.
 
Related article:
Read more articles by David Ringstrom. 
 
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

You may like these other stories...

How are you planning? What tools do you use (or fail to use) for forecasting? PlanGuru is a business budgeting, forecasting, and performance review software company based in White Plains, N.Y. AccountingWEB recently spoke...
Event Date: October 30, 2014, 2 pm ETMany Excel users have a love-hate relationship with workbook links. For the uninitiated, workbook links allow you to connect one Microsoft Excel spreadsheet to other spreadsheets, Word...
Event Date: September 9, 2014, 2:00 pm ETIn this session we'll discuss the types of technologies and their uses in a small accounting firm office. Included will be:The networked office: connecting everything together for...

Already a member? log in here.

Upcoming CPE Webinars

Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.
Aug 26
This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.