Display decimals only when the number is less than 1 [Excel number formatting tip]
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:
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Visualizations at Seattle Public Library – What Community is Reading?||Info-graphics of the week [Oct 3] »|