Nov 14th 2013

24

Advert Advertise with us

*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**.*

## Replies

## Please login or register to join the discussion.

This is a lifesaver for a researcher who makes A LOT of tables...

Fantastic! Thank you for the feedback.

Good info

Thanks, Dery! I appreciate the feedback.

I want to round up or down a calculation in a cell. how do I do this

Excel offers several ways to round calculations. I'd start with the =ROUND() function (http://office.microsoft.com/en.... If you have a situation that this won't cover, post some additional details and I'll be glad to elaborate further.

In the same format, can I add an extra condition that says if 0 then show "-"

I don't think this is possible, because once Excel gets to the [<1] portion it returns that format and stops testing. You'd need to take a different approach. One way would be to build three different Conditional Formatting tests that apply the desired number formats.

I need to take a number that is actually currency and add the decimal point in the correct place. In other words, the number 12345 is actually 123.45. How can I make this happen? Or is it impossible?

This can't be done with conditional number formatting. I would put 100 in a worksheet cell, copy it to the clipboard, and then use Paste Special - Divide to transform your existing set of numbers.

I want to add 0 in front of all values which ends with +11

Conditional formatting cannot be used in this fashion. You'll need to use an IF statement and concatenation instead.

I modified the format to [=0]#,##0;[<>0]0.000 to avoid 0 values converts to decimal point. Or is there any better method?

Btw, thank youuuuuuuuuuuuuuuuuu a lot!! You saved me.

You could shorten it to:

[=0]0;[<>0]0.000

Otherwise that's a perfectly valid approach. If you wanted to hide the zero values you can use

[=0]"";[<>0]0.000

Thank you for your feedback!

Great advice! You have saved somebody with a whole lot of bland spreadsheets a lot of time! I can't thank you enough!

Fantastic! Thanks for taking a moment to post your thoughts!

I was looking for a way to only display the decimal where there is one. Let's say I have 1/3, 2 even, and 4.3. I'd want it to display 0.33, 2, and 4.33. Instead I have either 0.33, 2.00, and 4.33 or 0.33, 2, and 4. With custom number formats I can get 0.33 and 2. and 4.33, but I want to remove that extra decimal.

Am I making any sense?

I'm just now running across your question. I'm not quite clear what your goal is. Are you trying to display 0.3, 2.0, and 4.3?

please advise me to how to add non-roundup decimal cell with round up decimal cell which result will be shown in non roundup format and also the result will be display as the sum after the roundup not the result calculate by the excel as its done exactly.

I want the exact formula for solving for e.g.

add. Result of 10.7+25.7+24.4+25=85.8(as excel done)

but i want the ans as 11(round up 10.7)+26(round up 25.7)+24.4(non-round up )+25(non-round up)=86.4(non-round up) rather than 85.8 or 86.

urgently revert soon

I cannot figure out how to keep my .00 in the column. For example I try to convert a cell containing 4500 into 45.00 but the zeros keep disappearing with the "100" formula or if I try to change the decimal point it just adds 4500.00. Please help. Thank you!

I'm just now running across your question. As far as I know conditional formatting cannot be used in the fashion that you're describing. You'll have to physically divide the numbers by 100.

Looks like Excel has an undocumented limit of a maximum of two conditions per number format. Also, the bracketed conditions can either come before or after the number format that they apply to.

Thank you for improving my article! I appreciate you taking the time to share your thoughts.