W

orking with Custom Number Formats

If you’ve worked with Excel very long, you have probably gone through all of the number formats available to you and seen some custom ones that you wish you knew how to do.

BTW, just to be clear we’re talking about Format Cells –> Number –> Custom

I’m going to cover a couple of my current favorites just to get the conversation going but it would be great if others chimed in with their favorite custom number formats or with their questions!

In science we have scientific notation, which Excel handles but it’s ugly and somewhat mysterious. Ten Billion (10,000,000,000) would be displayed as 1.00+E10 (one times 10 to the tenth power). In business we often shorten big numbers too right? 10 billion, 300K, 15M(illion), etc and it would be helpful if we could shorten really big numbers in a similar fashion because we often don’t need the full degree of accuracy to be shown (it will still be there but we want to shorten the number to a reasonable bite-size). So here’s the trick that I learned recently…

We’re probably all familiar with this number format:

#,##0.00 – this is Excel’s default number format (in the US) and displays the number with commas separating thousands, millions etc. and two significant digits e.g. 10,000,000,000.00. Well we get a lot of business speak around here and don’t need that level of accuracy – in most cases – so why not have the same shorthand in the reports that we do in language?

By adding successive commas to the custom number format, we can “divide” the original number by a thousand.

For instance, take our 10,000,000,000 and apply the custom number format of #,##0.00,

Now our number is 10,000,000.00 thousands I can even indicate this by adding the following to my number format: #,##0.00,”T” (or “M” for you Romans) and the number is displayed as 10,000,000.00T

Two commas (#,##0.00,,”M”) reduces it to millions as in 10,000.00M

Three commas (#,##0.00,,,”B”) reduces to billion – 10.00B

Effectively, we’ve taken a very large number and reduced the space it takes to display it – higher data density. We’ve given up a bit of the precision, from a display perspective, but when working with very large numbers, the significant digits can be effectively reduced and still convey the important information.

My other example today relates to a pet-peeve of mine – consistency. When working with currency – $1,000.00 – one of the choice formats is to display negative values in red and in parenthesis. Apparently people who spend all their time in ledgers appreciate this format. However, there is no matching format for percentages and I’m always calculating percent change. So, I want my negative percent changes in revenue to match my negative revenue numbers.

Again, the default currency format for negative currency is

($1,000.00) which is the equivalent of the custom format $#,##0.00_);[Red]($#,##0.00)

Ok, first an admission I don’t know what some of the above means – specifically the _) just before the semicolon, if someone wants to explain that would be awesome. But I do know that everything before the semicolon deals with positive numbers and everything after it deals with negative numbers. So [Red] in brackets tells Excel to display the text in red and the parentheses surrounding #,##0.00 tells Excel to surround it in parentheses.

So it’s pretty easy to map the same formats to percentages. First we take the custom format equivalent of Excel’s standard percentage display: 0.00%. Notice that it doesn’t do anything with thousands or with negative, so we just plug it into the custom format from above thusly:

#,##0.00%_);[Red](#,##0.00%)

1,000.00% | (1,000.00%)

Effectively, all we did was add the percent sign (%) after the significant digit declaration and we now have percentages that will be similarly formatted to currency.

What about you? What’s your favorite number formatting trick in Excel?

Tags: data visualization, excel, number fromats

This entry was posted on Tuesday, April 1st, 2008 at 1:55 pm and is filed under excel, visualization. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.