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 ...
Get Excel Annoyances now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.