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:

customformat1.png

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:

customformat2.png

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. 🙂

10 thoughts on “Custom Number Formats

  1. Thanks!
    Great post, wasn't aware of the "positive;negative;zero" format.

  2. Hi Ken,

    I'm trying to paste the number 5,006F and 15,000U into my excel . How do I make excel recognize these values?

    thanks for your help.

  3. Hi Darius,

    Those are text, not values. You'd need to paste them, convert them to values (by use of a formula). If you still wanted them displayed as above, but wanted to be able to actually add them as numbers, then you'd want to apply a number mask to the values as I described above.

    Assuming that you pasted either of the values you gave in cell C6, you could use the following formula to change the text into a positive or negative value:

    =VALUE(LEFT(C6,LEN(C6)-1))*IF(RIGHT(C6,1)="F",1,-1)

    Hope this helps,

    Ken

  4. Pingback: The Ken Puls Blog » Blog Archive » Making an Icon Set show only two conditions

  5. Not only is "positive, negative, zero text", can actually use the terms for the first two, plus positive and negative. The third time down to any value that does not fit these.

  6. Would there be any way to use a custom number format to display an ip address? An IP is a 32-bit number that is displayed in dotted decimal notation meaning 4 8-bit numbers separated by dots (periods). It is difficult or impssible to do many functions on these numbers when they are entered this way since excel will store them as text. Besides being able to store them as 32-bit decimal numbers and display as dotted decimal it would be great if Excel would detect them as an ip and convert from dotted decimal to ip number format 32-bit numbers. I can do all this with functions or VBA, but getting it into the custom number format would add a lot of flexibility.

    Thanks,
    Jason

  7. Jason,

    Good question... I think that you'd run into an issue with working out the length of the octets though... I.e. the rule wouldn't be consistent for IP's like the following: 24.69.123.1 vs 192.168.78.2

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *