Display decimals only when the number is less than 1 [Excel number formatting tip]

Posted on October 2nd, 2008 in Learn Excel - 11 comments

Here is a quick excel number formatting tip. If you ever want to format numbers in such a way that it shows decimal values only if the number is less than 1 you can use conditional custom cell formatting (do not confuse with conditional formatting).

Here is an example:

number-formatting-tip-conditionally-showing-decimals

In such cases you can use conditions in custom cell formatting.

  • excel-cell-custom-formattingFirst select the numbers you want to format, hit CTRL+1 (or right mouse click > format cells)
  • In the “Number” tab, select category as “custom”
  • Now, write the formatting condition for custom formatting the cell. In our case the condition looks like [<1]_($#,##0.00_);_($#,##0_). See to the right. what it means is, if the cell value is less than 1 then format the cell in $#,##0.00 format otherwise format as $#,##0. Excel cell formatting is a tricky business and if you want to master it there is no better source than Peltier's article on Custom Number Formats.

More excel tips on formatting:

Formatting numbers in excel - few tips
Custom Cell formatting in Excel - Quick tips

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

11 Responses to “Display decimals only when the number is less than 1 [Excel number formatting tip]”

  1. Jon Peltier says:

    The problem with that format is that the numbers are poorly aligned. If you adjust the custom format to this:
     
      [<1]_($#,##0.00_);_($#,##0_._0_0_)
     
    and right align the cells (or choose the General alignment), the ones place will be lined up vertically, and the numbers will be easier to read.
     
    Is there a problem with displayed precision mismatch with this format? A value of 0.99 shows two decimal places, while 1.01 shows none. Perhaps changing the beginning to [<10] would be more appropriate.

  2. Chandoo says:

    @Jon, thanks for that tip. It sure helps to have the numbers aligned properly despite showing no decimals.

    Agree that changing <10 might be appropriate. But in a rare case my data has elements below 1 with decimals and everything above is a whole number. (dont ask me why :D)

  3. Jon Peltier says:

    I guess somebody upstream of you truncated them with a clever number format.

  4. [...] Display decimals only when the number is less than 1 [Excel number formatting tip] | Pointy Haired D... [...]

  5. [...] point only when number is less than one, use [<1]_($#,##0.00_);_($#,##0_) as formatting code… Get Full Tip 49. To remove grid lines from excel worksheet, go to menu > tools > options > and un-check [...]

  6. Dan says:

    What if I want to apply this for percents where anything below 100% has a decimal? I tried [<100]##.0%;###% and the results were 100.% or 96.5% so it's half right! What am I missing?

  7. Dan says:

    Figured it out - [=0]"N/A";[<1]##.0%;###%

  8. Rashid says:

    how can I display 0.193 as ¢19.3 on a report.

  9. AnnaK says:

    Hi I have a simple question
    What format do I put in the Custom opton to get 1.2 when I type 12. Is it possible?

  10. Rizwan says:

    Very Interesting.

Leave a Reply