Excel Formatting: Best Practices in Financial Models

Properly formatting your Excel models is important because it makes it easier for others to read and understand your analysis… and for you to read and understand it if and when you change it later on.

There are universal standards for certain formatting points – such as color coding formulas vs. constants – but not for other aspects, since different groups at different firms all use slightly different standards.

Proper formatting is most important for actual financial models, but is less important for other types of data sets (e.g. data on orders placed, customers, or sales reps for the due diligence process). But all your spreadsheets should still be readable and easy to use.

Remember that in investment banking the formatting of your Excel analysis also reflects on your professionalism. Poorly formatted Excel files reflect poorly on yourself and your bank.

Aesthetics matter a lot in investment banking and other client-driven businesses, so it always pays to make your analysis clean, easy-to-read, and as professional as possible.

Color Coding in Models

If you do nothing else correctly and you’re wildly inconsistent elsewhere in your model, at the very least get the color coding correct.

Color coding allows anyone to immediately pick up your model and know what can be changed (assumptions) and what should not be altered (formulas).

Yes, you could figure this out yourself by pressing Ctrl + ~ or pressing F2 or Ctrl + U in every cell, but both those methods are more time-consuming and clumsy to use. So get the color coding right.

Links vs. Constants vs. Formulas

Here are the universal standards for direct links to cells in other spreadsheets vs. constants (hard- coded numbers) vs. formulas:

• Blue Font Color: All hard-coded (i.e. manually entered) numbers; used for historical financial data and also for many input cells in the “Assumptions” part of models
• Black Font Color: All formulas; also used for formulas that include links to other worksheets or workbooks, but which are not direct links (i.e. if you’re adding two numbers that are both from other spreadsheets)
• Green Font Color: All direct links to cells in other worksheets or workbooks
• (optional) Purple Font Color: Formulas containing hidden assumptions in the form of hardcoded numbers (e.g. =A1*1.21)

What about text? We prefer to leave it all formatted in the black font color. Technically, you should use blue because it’s “hard-coded” but that makes it harder to read models and so we avoid doing it.

We do use the blue font color if there’s an input cell that accepts text, however.

Can You Format All Your Cells Automatically?

One of the most common questions we’ve received is how to automatically color code your Excel spreadsheets according to the universal color coding standards above.

There’s no built-in function to do this, but you can use an addin to accomplish this and press a single button to automatically color code everything.

Formatting more than what is manually possible

The above universal guidelines sound simple to apply (just select and colour), but the more advanced your colouring rules, the harder they are to manually apply.

Imagine a spreadsheet with actuals and forecasts on a monthly basis, where each line is a project. If projects start at different dates, you can't simply select a rectangle and apply the blue formatting on all hardcoded numbers! You need to select each project line manually and color the actuals/forecasts appropriately.

Furthermore, what if you want to colour the single references differently (e.g. =B52) than other formulas; or color formulas containing suspicious hardcoded numbers (e.g. =B52*1.21)?
You would have to inspect each and every cell, and apply formatting.

That's why an addin offers superior consistency.

Instantly format your Excel model

ProForma is an Excel add-in that smartly color-codes cells to give you an edge in building complex financial models.

Get it from the Microsoft AppStore

Special Formatting for “Input Boxes”

Sometimes in financial models, you see other color coding standards aside from what was mentioned above.

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 we built 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.

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.

You would display the currency symbol minimally (if at all) in between those two line items.

Here’s what the Wal-Mart income statement looks like:

The same goes for the balance sheet and cash flow statement.
In certain cases, you may display the currency symbol at the bottom of major sections.

For instance, you would display the ‘$’ symbol on the balance sheet for Cash (since it’s the first line item on balance sheet), and again for Total Assets (end of a major section), and again for Accounts Payable (the first line item for Liabilities section), and then at the bottom for Total Liabilities and Equity.

When using the currency symbol, note that the proper formatting is ‘Accounting Number Format’ (Ctrl + 1 or ⌘ + 1 and then N for number and then A for the accounting number format).

Technically, you could use the “Currency” format as well but we prefer the “Accounting” format since it offers proper alignment for both positive and negative numbers.

Decimal Places

Normally, you do not want to show any decimal places for numbers that represent financial figures in your models – the only exceptions are for share prices (2 decimals), or for per share quantities, such as dividend per share or EPS (also 2 decimals).

If the company is smaller and has revenue in the millions, tens of millions, or hundreds of millions rather than the billions, then you may show up to one single decimal place (e.g. ‘$98.5’ million) throughout the model.

You can see in the Wal-Mart model above that we’re excluding decimal places entirely because their revenue is in the hundreds of billions.

You can use Alt + H + 0 to increase decimal places on the PC, and Alt + H + 9 to decrease decimal places (sorry, no Mac equivalent shortcuts).

Percentages

Normally, you display all percentages to one decimal place (e.g. 0.0 %). You should italicise percentages so that they will be visually distinct from other numbers in your financial model.

This format ensures that negative percentages are listed as “(5.1%)” and that positive percentages are correctly aligned with negatives.

Note that certain exceptions may apply to these rules. For example, if you have assumptions which take the form of percentages (e.g. revenue growth), then you may NOT want to italicize those numbers since assumptions are different from calculated percentages, such as gross margin figures.

Dates

Usually, you display dates formatted with either the full month, day, and year (or year, month, and day depending on your region), or simply the year by itself.

See the screenshots below for both methods and the formats we are using:

Remember that your dates MUST be designated as ‘Date Formatting’ (after pressing CTRL + 1 to get to the cell format dialog box under Number and select ‘Date’ for formatting type) or “Custom” with the components of dates in the “Type” field (see above).

If you inadvertently let Excel format your dates as ‘Text Formatting’ rather than as ‘Date Formatting,’ they may not show up correctly.

To calculate the date of the next year and previous year in financial models, you can use the following formulas:

We are taking the same month and day, but adding or subtracting 1 to the year each time.

Share Counts

For the share counts shown in your models, you can use the “Number” format and display two or three decimal places.

Note that if you display the share count this way, its alignment relative to the other numbers in your financial model may be slightly different since you’re not using the “Accounting” format.

Valuation Multiples

For valuation multiples in financial models – such as EV/EBITDA, EV/Revenue, or even P/E – the best practice is to use a “0.0 x” format or a close variation of that.

You may apply “Conditional Formatting” or even “Custom Number Formatting” if the multiple is above 0.0 x or 100.0 x, but even that is not necessarily required.

In situations where the multiple is below 0.0x (i.e. negative valuation multiple) or above 100.0x, it might be better to use the IF(ISERROR()) or IFERROR() built-in function in Excel so that you display “NM” or “N/A” or equivalent text rather than applying formatting at all.

Text

We prefer to format all text in the black font color; some people make the argument that since text is also hard-coded, it should be formatted in blue.

However, using blue text makes it more difficult to read the document so we don’t follow this practice.

Different banks will have different fonts selected as the ‘default’ font style to make the appearance of the bank’s analysis consistent.

“Calibri” was the most common type font, although since 2021, Microsoft changed the default font to "Aptos" across all of the Office suite. This makes "Aptos" the natural default choice for anyone creating a new spreadsheet, and will make your spreadsheet fit in better.

Font size doesn’t matter quite that much as long as you’re consistent; you might see anything from 10 point font to 12 point font used in models.

Borders, Fonts, and Fill Colors

Normally, you only include borders around the financial statements and supporting schedules, such as those for revenue and expense models.

You should NOT overuse borders or your model will be much more difficult to read and interpret.

You can also include bottom borders beneath or above date rows, but you should not make a “box” there.

In normal cells, you would only apply outside borders if you have an input cell and you want to change it to the blue/yellow/black border format discussed above.

As mentioned above, 10, 11, or 12 point font sizes are all common, and standard font types include Aptos, Calibri, Arial, and Helvetica.

You very rarely want to use any type of fill color to change the background color of cells – keep most areas white, and only do this for the titles of all the schedules, key summary rows (such as those in a DCF or the WACC calculation), and perhaps on any graphs or charts you create.

Alignment

In financial models, you should keep most things left-aligned and set to ‘General’ horizontal alignment.

You can indent any rows that show supporting calculations, such as revenue growth or gross margins:

To indent, prefer to use different columns instead of spaces in the same column. This means that you put the more indented texts into their own column, and play with the column width to indent, instead of putting spaces before in the text itself.

You can center align any important input cells and use center across selection for section headers and titles, where appropriate.

Do NOT use the “merge cells” function in Excel or you will regret it later – that feature makes it too easy to copy down formulas incorrectly or accidentally misalign other elements of the model.

Some people prefer to keep all text left-aligned in cells, and keep all numbers and calculations right- aligned – that’s not mandatory, but it can make it easier to spot errors and separate text from calculations.

Normal vs. Bold vs. Italics

You should use bold text for:

• Important section summaries or titles (Total Revenue, Net Income, Total Assets, etc.).
• Dates at the top of your model (sometimes – varies by model setup).
• Parts of the Assumptions or Inputs section to draw attention to.
• Sometimes, you will use bold for the entire column of the most recent year’s historical financial data in a model.

Italics are mainly for percentages and changes over time (as mentioned above).

Aside from that, everything else in your financial models should just be set to normal font weight (i.e. non-bold, non-italicized).

This is the least important point of everything we’ve been through so far, but you might as well get it right if you’re trying to get everything else right as well:

Balance Check Formatting

When building the balance sheet as part of your 3-statement operating model, you should always include a “balance check” at the very bottom to ensure that the balance sheet actually balances… it’s kind of a big deal.

Essentially this ensures that we always have at least three decimal places after the decimal and that positives and negatives are aligned properly; if it’s 0 because the balance sheet balances, we display “OK!” instead.

The Bottom-Line on Formatting

We’ve covered a lot of points, but the bottom-line on formatting in financial models is that consistency is more important than minutiae. Yes, get the color coding right and make sure your model is legible…

You can get there by either applying simplified formatting rules, or by using an automated addin to set the formatting for you in one click and moving on.

But don’t spend hours and hours on tiny details unless your team is unreasonably demanding (this happens an unfortunately high proportion of the time in investment banking).

And in time-pressured situations such as modelling case studies (there’s almost a 100% guarantee you’ll get these in buy-side interviews), definitely do NOT obsess over formatting or even worry about these issues.

In that context, numbers, model structure, and correct formulas matter more than aesthetic formatting issues.

Instantly format your Excel model

ProForma is an Excel add-in that smartly color-codes cells to give you an edge in building complex financial models.

Get it from the Microsoft AppStore