I don’t know about you (I always say that, but it’s true! I don’t know about anybody!) but I hate the default in Excel for showing one decimal place. There should really be a built-in format which knows how to behave itself! There’s always 0.##, but that adds the stupid trailing decimal, which is just ugly AND stupid.
I have seen answers (such as this one) which tell you how to fix the format if you want to show decimals on non-round numbers, but frankly I find that annoying when I’m looking at something like work allocations – I want it to round unless the number is greater than 0 and less than 1.
Here’s my solution:
Set your range or pivot table field formatting to show no decimals.
Create a conditional formatting rule as follows:
Format Only Cells that Contain:
Bume, now your pivot table or range will show .5 for half an hour (or half a kit-kat, or whatever) and 2 for 1.5 hours. (or delicious milk chocolate covered wafers, your choice) You could also chose to show the decimal to a certain point, like 5 or something if you wanted.
I don’t know about you (give me a break?) but it looks much cleaner to me!