SubscribeDisplay decimals only when the number is less than 1 [Excel number formatting tip]
Posted on October 2nd, 2008 in Learn Excel - 5 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
|
Trackbacks & Pingbacks
- Pingback by Display decimals only when the number is less than 1 [Excel number formatting tip] | CompuFiciency = Applied CompuTelligence on October 27, 2008 @ 2:54 am
- Pingback by 100 Excel Tips & Resources for Everyone | Pointy Haired Dilbert - Chandoo.org on January 28, 2009 @ 12:45 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
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
)
I guess somebody upstream of you truncated them with a clever number format.