Excel Annoyances

CUSTOM FORMAT ANNOYANCES

CREATE CUSTOM NUMBER DISPLAY FORMATS

The Annoyance:

I keep the statistics for my rec-league hockey team. One of the statistics I’m always asked about is “plus/minus,” which is the number of times you’re on the ice when your team scores a goal (a plus) minus the number of times you’re on the ice when the other team scores (a minus). I want to display the negative numbers in red, as usual, but our team color is green and I’d like to display the positive numbers in green. Oh, and I’d like to display text values, such as a note that someone hasn’t played yet, in blue. How do I do it?

The Fix:

To define a custom format, choose Format → Cells, select Custom in the Category list, and enter your custom codes in the Type box. You can specify up to four format codes in a custom format. The codes apply (in order) to positive numbers, negative numbers, zero values, and text. In your case, the format to display positive numbers in green, negative numbers in red, and text in blue is [Green](###);[Red](###);;[Blue]"Has not played”.

As you can see, a semicolon separates each format. Because you don’t require special handling for zero values, I left that element empty (that’s why there’s nothing between the second and third semicolons). If you specify only two codes, Excel assumes the first is for values of zero or greater and the second is for negative numbers. If you specify only one code, Excel uses it for any value in the cell.

The available number codes are:

• #, which tells ...