# Conditionally Displaying Decimal Places in Excel: Part 1

istock_JohnnyLye_excel
Tags

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.

To access all of the content on our site, register (it's free!) or login to your existing account.

### Replies

By Dr Taylor
Jun 26th 2015 01:11

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

Thanks (0)
By David Ringstrom
to khushbu
Jun 26th 2015 01:11

Fantastic! Thank you for the feedback.

Thanks (0)
By dery
Jun 26th 2015 01:11

Good info

Thanks (0)
By David Ringstrom
to khushbu
Jun 26th 2015 01:11

Thanks, Dery! I appreciate the feedback.

Thanks (0)
By pamela
Jun 26th 2015 01:11

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

Thanks (0)
By David Ringstrom
to sarahtaylor
Jun 26th 2015 01:11

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.

Thanks (0)
By Vivek
Jun 26th 2015 01:11

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

Thanks (0)
By David Ringstrom
to alicealbert
Jun 26th 2015 01:11

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.

Thanks (0)
By star
Jun 26th 2015 01:11

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?

Thanks (0)
By David Ringstrom
to jowyang
Jun 26th 2015 01:11

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.

Thanks (0)
By Anurag
Jun 26th 2015 01:11

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

Thanks (0)
By David Ringstrom
Jun 26th 2015 01:12

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

Thanks (0)
By Snamyna
Jun 26th 2015 01:11

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.

Thanks (0)
By David Ringstrom
Jun 26th 2015 01:11

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

Thanks (0)
By Will
Jun 26th 2015 01:12

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

Thanks (0)
By David Ringstrom
Jun 26th 2015 01:12

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

Thanks (0)
By Auditor
Jun 26th 2015 01:12

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?

Thanks (0)
By David Ringstrom
Jun 26th 2015 01:12

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?

Thanks (0)
By Bikash
Jun 26th 2015 01:12

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.

Thanks (0)
By Bikash
Jun 26th 2015 01:12

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

Thanks (0)
By Igor
Jun 26th 2015 01:12

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!

Thanks (0)
By David Ringstrom
Jun 26th 2015 01:12

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.

Thanks (0)
By Tmdean
Jun 26th 2015 01:12

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.

Thanks (0)
By David Ringstrom
Jun 26th 2015 01:12

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

Thanks (0)
By zopfan
Sep 4th 2015 12:36

Thank you man. You saved my day. This was exactly what I was looking for the past 2 days. God bless you man.

Btw, how did you get this idea? Means how did you that the cell formatting takes precedence over normal conditional formatting or otherwise?

Thanks (0)
By twiggy
Mar 30th 2017 17:23

I've searched all over for a total solution to my en devour with no avail. I am doing a commission table. The commission amount is to show with the % sign....so it as an example 10% or if its a partial than 10.5%. It than needs to calculate the amount of the sale by that percentage - say the sale is \$10,000.00 and the commission is 10.5% than the commissioned earn would be \$1050.00

I've tried everything to get the commision % to show the decimal only when it involves a 1/2 of a percentage, with no luck. Any suggestions?

Thanks

Thanks (0)