About Brian DeChesare
Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.
How to Color Code in Excel for Financial Models (PC/Windows Version) [Tutorial Video] (32:42)
In this lesson, you’ll learn how to apply color coding and formatting to financial models and fix problems with decimals, currency signs, indentation, alignment, links vs. formulas vs. constants, and more – and you’ll practice by fixing the formatting on the Summary spreadsheet.
This sample lesson from our Excel/VBA course covers how to color code in Excel and some of the key principles for formatting financial models.
Table of Contents:
1:06: Formatting Principles
11:02: Demonstration of Walmart Model Fixes
19:59: Exercise – Summary Spreadsheet Fixes
30:34: Recap and Summary
We have some general guidelines for model formatting, but these are not like the laws of physics (gravity, momentum, speed of light, etc.) – they’re “rules of thumb” rather than “laws of nature.”
How to Color Code in Excel: Key PC and Mac Shortcuts
We use the following colors for different types of cells:
To find and select these cells, you can use the following PC/Windows shortcuts:
F5: Jump to Cell
F5, Alt + S, O, X: Select Constants (Drop the X if you want to highlight text constants)
F5, Alt + S, F, X: Select Formulas (Drop the X to highlight textual formulas)
Ctrl + 1: Format Dialog Box
Ctrl + F: Find
For example, if you press F5, Alt + S, O, X, that will highlight all constants on the spreadsheet. You can then press Ctrl + 1 to change their font color to blue, or you can access the font color commands from the “Home” tab in the ribbon menu.
To find direct links to other spreadsheets in the file, you can press Ctrl + F and search for the “!” character, which always indicates links to other sheets.
To find links to other files, search for “.xls” using Ctrl + F, and change the font color to red for each instance.
In the Mac version of Excel, you can use these shortcuts instead:
F5 or Ctrl + G: Jump to Cell
F5 or Ctrl + G, Special, Constants: Select Constants (Drop the X if you want to highlight text constants)
F5 or Ctrl + G, Special, Formulas: Select Formulas (Drop the X to highlight textual formulas)
⌘ + 1: Format Dialog Box
⌘ + F: Find
How to Color Code in Excel: What Financial Models Should Look Like
If you follow these standards for color coding, your financial models should look like the examples below.
Here’s the normal color-coding scheme:
We use a yellow background and grey border for “input cells” that are assumptions or drivers in the model, while we use a white background with no borders for historical financial data:
Here are a few other examples of cell colors based on their categories and contents:
Other Principles of Financial Model Formatting
In addition to the points above about color-coding, we follow these guidelines with other types of formatting:
Centering: We use the “Accounting” format and variations of the “Percentage” format for most numbers, so centering is not an issue there. For the input boxes, we prefer to center percentages, dates, text, and normal numbers, but we do not apply it to anything in the “Accounting” format.
Dollar or Other Currency Signs: Only display these in the top row and bottom row of schedules. Sometimes it’s a bit ambiguous what a “schedule” is – often, we’ll display these in the top and bottom rows of each major segment.
“Units”: We like to use an extra column to display the units, especially in schedules that mix $ per Sq Ft and Sq Ft and other figures with actual $, to remove ambiguity.
Indentation: We indent the individual rows within each category, and we use multiple indents for sub-categories. We also indent percentages used for informational purposes such as the margin formulas.
Signs: On the IS and CFS, we often use (+) and (-) when it is meaningful to do so – for example, if everything in a row is going to be positive or negative, and it’s easy to specify which is which.
Italics: We usually italicize percentages used for informational purposes – so, the overall margins and revenue growth rates, but not the assumptions used to calculate them in the first place. Those are in the yellow input boxes!
Decimals: It doesn’t matter what you do as long as you’re consistent. Use 1 decimal, 0 decimals, or 2 decimal places for all the financial figures in your model… exceptions apply for the Share Price and EPS figures, which almost always use 2 decimal places because of how share prices are displayed.
We also usually use at least 1 decimal place, sometimes up to 2-3, to display the company’s share count. Usually 1 decimal place for valuation multiples and percentages as well – even if the financial figures in the model have 0 decimal places, as is the case here.
Headers: We use a blue background color, white text, and the “FY” format for the top header with the Historical and Projected years. We then use a grey background color for the other headers in a schedule – sometimes display the years on top depending on the spacing.
You can view examples of these standards in action below:
How to Automate the Color-Coding Process in Excel
It’s difficult to automate “all” this formatting, but you can automate much of the color-coding process if you feel comfortable using VBA, which we cover in the final module of this course.
In short, you can use the .SpecialCells, Union, and Intersect commands to find the appropriate cells, loop through them, and change the colors appropriately.
You can get the full Excel file (.xlsm) or just the .xlam file for the VBA code itself below:
Full Excel File with Color-Code Macro
Note: You must know Excel fairly well to use macros. If not, you could do serious damage to your spreadsheets. If you have little experience in Excel, then you should not mess around with this macro.
