Excel – Quick Reference Guide
Formatting Practices for Finance (IB, PE, HF/AM, ER, CF, etc.)
Even if you do this, though, it still won’t work 100% of the time because you might come across
formulas that reference cells in other worksheets without directly linking to them. Fortunately, green
cells are rarer than black or blue cells, so the method above works fairly well in most models (and
you can format links to other worksheets manually).
Special Formatting for “Input Boxes”
Sometimes in financial models, you see other color
coding standards aside from what was mentioned
Specifically, for assumptions that appear only once
– such as Company Name, Share Price, Tax Rate, Purchase Premium, etc. – you can use the normal
blue font color but also make the cell background yellow and use a black border outline for the cell.
This is not done universally, and even in the models on this site we don’t always do this. It helps the
true input cells stand out a bit over normal historical data, but it’s less important than getting the
normal color coding above right.
How to Do This Automatically
It’s almost impossible to “detect” hard-coded historical data vs. true input cells, so don’t even bother
with that. Once again, however, you can record a macro for at least the formatting part of the exercise
– just open the Record Macro dialog and change the font color (Alt + H + FC), fill color (Alt + H + H),
and border (Alt + H + B)… or use Ctrl + 1 or ⌘ + 1 on the Mac for all of that.
Numbers, Percentages, Dates, and Valuation Multiples
You will see more divergent standards for formatting once you go beyond the simple color coding
discussed above – but it’s still worth the time to get it right, or at least to be internally consistent in
your own models. Here are the major numerical categories:
Dollar / Other Currency Signs
In general, you want to display the currency sign (i.e. ‘$’ for USD, ‘₤’ for GBP, ‘€’ for Euros, etc.) only
at the very top and very bottom of each schedule in the financial model.
For instance, for the income statement you would only display the ‘$’ currency symbol for total
revenues at the top, and then again for net income (and/or EPS) at the bottom of the statement.