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

Need for Speed

How fast can a human being possibly use Excel?  This is not an easy question to answer.  However, it's frequently a very important question to the younger people that work in investment banking, PE or hedge funds, because much of their livelihoods are tied to Excel.  Particularly in banking, the faster your execution speed is overall, the less miserable life becomes.  Speaking of miserable, for the week that I was exposed to Training the Street, they introduced us to their version of the "Excel speed test," which was a formatting exercise typically completed in a minute, but sometimes completed as quickly as 30 seconds.  I never finished it, so you could probably call me slow.

Regardless, I do know a thing or two about speed relative to Excel. Here is what it takes:

How fast (or slow) of a typist are you?

1. Typing speed - If you can't type at least capably, you can give up any hope of using Excel any faster than that old MD that doesn't check e-mails or use Excel since he started working before computers and high-yield debt existed. Typing speed is like the 40-yard dash for football players: raw speed. You could type 130 WPM (probably equivalent to a 4.2 second 40), or you could only type 50 WPM (offensive lineman speed). Anything below that range, and you should probably consider changing professions.
2. Shortcut/function knowledge - Even if you are the greatest typist in the world, you can't do much financial modeling (at least quickly) without knowing your way around Excel.  This is the same reason track stars cannot always be great football players; speed is no guarantee that they know the playbook and how to play (think of John Capel vs. Randy Moss).  So for maximum speed, you need to know things like CTRL + 1, CTRL + arrow keys, etc.  Having the ability to type out a Steven King novel in a couple of hours may help, but not much since you aren't writing novels in the spreadsheet.
3. Brainpower - Some might argue that this is tied to typing speed, which is partly true, but I want to highlight the importance of a good memory, quick recall function, etc.  You may have posted on the side of your cubicle (a good Asian friend of mine used to call it a "cubic") a sheet listing commonly used keyboard shortcuts.  Does this help you?  Maybe, but certainly not in significantly increasing your speed.  If you just transitioned from Excel 2003 to 2007 and are looking for the command for "Page Setup" (ALT, P, S, P), scanning the sheet and then typing the command is not very efficient.  Similarly, if you somewhat remember the function and have to think about it for 10 seconds, that's not very efficient either.  But if you know every function off the top of your head, you'll obviously operate much faster than others who need to refer to either their physical or mental cheat sheets.

The good news: all 3 of the aforementioned attributes can be significantly developed with the proper amount of repetition.  One year of banking analyst experience will yield roughly 5,000 hours of work, much of which is sadly just improving your computer skills through repetition.

So how fast is fast?  Well, Mrs. F-One can type 120 WPM.  That's pretty fast.  I can build a 3-statement model while using one hand to eat buffalo wings in less than 15 minutes.  Maybe you're not impressed, but given that I only type 80 WPM with TWO hands, that's not bad.  I've often mentioned to friends that if Mrs. F-One and I were to produce a child that gets all the right traits, this child would be the Lebron James of investment banking.  The other child would probably get cut from middle school basketball.  Anyway, I would gladly let Le-One go straight from high school to the pros.  The only part I want to avoid is Gloria James banging Delonte West in the team hotel.

I'm taking my talents to Wall Street...as soon as I unlodge this basketball from my dong.

There are obviously many ways to improve Excel speed, some of which I will discuss in future posts.  In the mean time, I strongly suggest 1) continuing to work hard while making an effort to memorize more functions/keystrokes and 2) http://www.freetetris.org/

Enjoy while Mrs. F-One gives me a beatdown for suggesting that our future child should become a banker,

F-One

Automatically Updating Page Titles for Sheets

Has anyone ever asked you how to automatically link a cell to the title of a sheet?  For example, if the current worksheet is called "Sheet1," how do you link to this name such that a change in the name will be reflected in your cell link?  More importantly, isn't it amazing how often a senior person asks you to do something in Excel without even being sure of whether it's possible or how the hell it could work?

You might be in the process of creating a product model in which there are 25 different products, and each product occupies a sheet within the model.  Let's say that you name each sheet for each different product, and have a title on each sheet that also matches the name of the product.  If you decide to change the nomenclature, you may be making 25 changes twice over (or even more than that, if you are using the product name in multiple cells on each sheet).

How do we link to sheet names?

Obviously, the more you link things together, the less manual updating you will need to do.  So here is a way to link to a sheet name, for whatever purpose you might need (make sure your file is saved on the hard drive).

1. Start with a formula that will return a string of text containing the sheet name.  Assume you are on "Sheet1."  You can go to cell B3 and enter: =+CELL("filename",$B$3), which returns "C:\Folder\[filename.xlsx]Sheet1"
2. Having the filepath and sheet name, you can create a function that returns only the part of this string of text that follows the "]," which would be "Sheet1"
3. One function that will work to return only the text following "]" is the RIGHT function: =+RIGHT(CELL("filename",$B$3),a) where a is the number of characters following "]"
4. Obviously, if all of your sheet names are 6 characters in length, you can just use the function from step 3 with a = 6,.  But a truly dynamic function will require solving for "a" in all cases.  So first, let's assume a = b - c, where b is the total length of the filename and c is the total number of characters through "]"
5. To find "b," use =+LEN(CELL("filename",$B$3)), which tells you that the filename is 31 characters in length
6. To find "c," use =+SEARCH("]",CELL("filename",$B$3),1), which tells you that "]" occupies the 25th position starting from the left in "C:\Folder\[filename.xlsx]Sheet1"
7. Using a = b - c, we have a = 31 - 26 = 5.  In formulaic terms, a = LEN(CELL("filename",$B$3))-SEARCH("]",CELL("filename",$B$3),1)
8. Thus, using the formulae from steps 3 and 7, the sheet name is =+RIGHT(CELL("filename",$B$3),LEN(CELL("filename",$B$3))-SEARCH("]",CELL("filename",$B$3),1))

Holy sheet!

-F-One

My Favorite Cousin: The F5 Key

 Keep going...keep going

Everyone knows that the fastest way to navigate to a random cell is entering the cell reference into the "Go To" menu.  In other words, if you want to get to cell AZ355, type: F5, AZ355, ENTER

Similarly, you know that if you have named a cell or cell range, you can select that name on this menu, and Excel will highlight the selection for you.

But what happens if you want to select some other group of cells that isn't named and isn't contiguous?  What if you're a first-year analyst who is being forced to color all of the hard inputs in a model to blue?  What if the model is 100 pages?  What if the penalty for forgetting to color one of the numbers is being called an idiot even though you sit next to an admin from Brooklyn that doesn't know how to read?

There is a solution for everything in Excel, and in this particular situation, it would be: F5, ALT + S, which brings you to the "Go To Special" menu.

...there!

At this menu, you can select "Constants" (ALT + O, ENTER), and then uncheck all of the boxes except "Numbers."  Now, you can highlight only the hard inputs in your model, and turn all of those numbers blue with just a couple of keystrokes, rather than selecting range after unbearable range and hoping you didn't miss anything along the way.

Step 1: Select a range

Step 2: Select criteria

Step 3: Only format these numbers...

Think of some of the other possibilities!  If you have hidden or grouped data that you do not want to format, you can select only visible cells and format those.  If you want to find out what cells are conditionally formatted, rather than trying to guess, you can do that easily.  In any case, my cousin F5 is extremely underutilized for how powerful it is.

Comments/questions welcome.

-F-One

Greetings!

Welcome!  Many (3) people have asked me to share my perceived wealth of excel knowledge with the rest of the world, so I have created this blog with the plan of regularly posting facts, "how-tos" and other updates on Excel for financial modeling and corporate finance in general.

What qualifications suggest that I can teach you, the reader, anything useful about Excel?  Well, I graduated from college (barely) in 2006 having taken one semester of an Excel class (B-), worked 3 years at a large investment bank, and currently work at a private equity firm.  So I have no legitimate credentials to support my case, but you can be the judges of whether my contributions are useful.  Here are some brief notes on the intended focus of this blog:

1. Formulas that improve the efficiency of financial modeling, data analyses and other commonly needed functions within Excel
2. Commands that improve efficiency, formatting and accessibility within Excel
3. I am currently using Excel 2007
4. Topics I do not intend to cover in-depth are: visual basic (not my expertise), statistics (my worst class in college), and pivot tables

The key word is efficiency.  Everywhere I have looked, there is someone using Excel less efficiently than they could, and in turn being less productive for no good reason.

As mentioned, the Excel information on this blog will be most relevant to corporate finance, particularly those people that work in investment banking (and related areas) and frequently use Excel.  But hopefully anyone looking to save some time or learn some interesting things about Excel will benefit.

If you need Excel help or have a topic you want to learn more about, feel free to leave a comment!

Thanks so much,

Mr. F-One