Custom Number Formatting

Anyone that has worked on a client pitchbook for banking or an investment committee memo for private equity knows that formatting is of the utmost importance (some might say gross overimportance).  I can't speak for hedge funds, but I have a friend that proudly works at a health care hedge fund, and believes that formatting is for people who don't have a real job like him.

I'd rather not debate who has a real job or whether formatting is important or not, but let me give you some words of motivation on why I even attempt to format spreadsheets and presentations.  When I suffered Training the Street (are all these references a delayed onset of Stockholm Syndrome?), our instructor Scott threatened all bad formatters by saying, "Watch out, I am an anal monster."  Yeah.  Of course you are.

So some of you may be interested in what an anal monster does or who among your coworkers are anal monsters, but I just assume that everyone is one and that nice formatting can prevent me from finding out anything more.  For this particular post, let's talk about nice formatting in terms of number formats, starting with CTRL + 1, ALT + C, END, ALT + T:

I will assume you know the basics on formatting dollars in millions, with negative numbers in parentheses (rather than with a "minus") and one or two decimal places of significant digits.  If you're already doing this formatting with custom formats, then great; otherwise, read closely to find out what you've been missing.  In the "Type:" field of the "Number" format menu shown above, number formats are entered as: a;b;c where "a" represents positive numbers, "b" represents negative numbers, and "c" represents zero.  You can thus apply some of the following format types:

1. $#,##0.0_);($#,##0.0);"-" for dollars: The #'s ensure that if you enter the number 1.1, it will show up as $1.1 (not $0,001.1) but a number in the thousands or above will still have a comma separating every three digits.  Moreover, zero will appear as a dash, and not "0;" this is more of a personal preference to eliminate seas of zeros that sometimes make extensive models hard to read, and also to allow easy recognition between an absolute zero and a very small number like 0.001 that appears as 0.0.
2. $#,##0.0_);[RED]($#,##0.0);"-" for dollars with negative numbers automatically colored red: Basic colors like [RED] and [BLUE] work in the customer formatting framework when in brackets and preceding an expression.  You can also try [TURQUOISE], but that would make you an idiot.
3. #,##0.0x_);(#,##0.0x);"-" for multiples
4. #,##0.0%_);(#,##0.0%);"-" for percentages
5. "Yes";;"No" for binary code: Excel also has TRUE and FALSE built into the program as 1 and 0, respectively.
6. "" or ;;; to make cells blank
7. mm/dd/yy for dates: Other formats that work include mmm dd, yyyy or mmmm dd or some combination.  I have no idea how this follows the positive;negative;zero rule.

You get the idea.  And you have probably figured out by now that you can also format a number using one of the preset options, and later toggle to the custom option to see the code.  The possibilities are limitless.  Just don't use that stupid accountant format where you right align a number and the dollar sign is still all the way to the left.  If you do use it, why don't you try writing out the number 1,000 as 1,                  000?  Maybe that will satisfy your urge to make documents as illegible as possible.

Last thought: does an anal monster enjoy "anus" or "ani?"

I work for TTS and enjoy the plural of anus 

Helpful,

-F-One