Showing Decimals in Excel or Even a Pivot Table – Only if the Number is Less Than 1

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:

Cell Value Between =.0001 And =.999

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!

Before:

before2

After:
after2

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s