Custom Number Formats

Over the last couple of weeks, I've led 3 sessions of my course on "Building Spreadsheets That Last". It's a course that focuses on good design practices in Excel, making things easy and intuitive for users, and generally teaches skills that I've used to set up my solutions so that they have the best chance possible of coming back to my in one piece when passed on to other users. The course always goes over very well, and I really have a great time teaching it.

One of the things that gets discussed, although only briefly, is custom number formats. This is more of an aside than a topic, but people always want to know about them when I quickly use one to display, say, a phone number. It usually ends up in a discussion, with me showing my favourite custom number format:

$ #,##0.00 F;$ #,##0.00 U;"-"??

(To set this up, right click your cell and choose Format Cells-->Number-->Custom, and enter the characters above in the Type box.)

Here's a look at what the output shows up like for a positive number, negative number, and zero, in that order:

Cost accountants usually get this right away, but I'll admit that a few of my department managers have called me before asking what the "Eff You" is all about in the report. 🙂

The deal is that, when reporting financial numbers, it can be hard to really get the gist of whether a positive or negative number is a good thing, especially in variances. For example, revenues less than budget are a bad thing, but expenses less than budget are a good thing. This little custom number format removes the mystery, as it tells me that a positive number is "F" for "favourable". "U", of course, is "Unfavourable". (That would be favorable and unfavorable if you are one of my southern neighbours... err.. neighbors. 😀 )

So here's how this works...

The number format above is broken into three parts, separated by semi-colons. Those three parts work in this order: Positive Format;Negative Format;Zero Format. Using this logic, you can see that we have:

• Positive Format: $ #,##0.00 F
• Negative Format: $ #,##0.00 U
• Zero Format:  "-"??

So what do these pieces all mean?

• The dollar sign followed by the space is a dollar sign followed by a space. (Go figure.)
• The # sign displays a numeric digit if one exists for that position
• The comma, in this case, will only display if the characters to the left of the comma in the format exist. (i.e. It shows if the number is 1,000 or greater only)
• The 0 will always show a numeric value, placing a 0 if one doesn't exist for that position
• Because there will always be a leading zero, the decimal will show
• The space followed by the F (or U) will display that text at the end

The Zero format is a little bit trickier, I suppose. It places a dash character, and pads the cell from the right by two full character widths. You could also place two spaces after the final quote, but the spaces are narrower, and you can't see them when you go back to edit your number format.

The great thing about custom number formats is that they don't change the underlying value on the cell. So even if my cell is displaying $ 56.00 U, it actually still only contains the value -56. This means that I can still base calculations off it, without having to fool around with stripping text and converting it to a value. Very cool. In fact, date formats work the exact same way, since all dates are really numbers. (Format your date as a number if you don't believe me.)

One question I got asked the other day was, "How did you learn to do this?" The answer is far less glorious than you might think... I simply went into the Format Cells dialog, clicked the number tab, and chose one of the many pre-defined formats that got me as close to my final format as I could get. Once I'd selected it, I then went to the Custom format area. By doing this, the current format was already in the "Type" box, so I started changing it gently, watching the output change in the "Sample" field above it, as shown below:

The power in these formats is very cool, and I've used them to display things in a variety of ways. Phone numbers and SIN numbers are pre-defined in the Special section, but you can do Credit Cards with a custom format, for example. The possibilities are only limited by your imagination, really. 🙂