# 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:**

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

- First 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

### Leave a Reply

Visualizations at Seattle Public Library – What Community is Reading? |
Info-graphics of the week [Oct 3] |

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

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.

@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)

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

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

[...] 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 [...]

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?

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

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

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?

@AnnaK

Can I refer you to: http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10/

Very Interesting.