Excel Tutorial – How to Color Code Financial Models (12:20)

This will cover how to color code your financial models so that hard-coded numbers and constants are in blue, formulas and text are in black.

Tutorial Summary & Description

This will cover how to color code your financial models so that hard-coded numbers and constants are in blue, formulas and text are in black.

You will also learn how to make the links to other worksheets show up in green.

In addition to a simple method using the F5 shortcut key to jump to different cell types, you’ll also learn how to use VBA code to accomplish this in a more elegant way that checks for different cases.

Here’s an outline of what we cover in the lesson, along with the shortcuts used and the VBA code that you can use in your own models and spreadsheets:

Why is Color Coding Important?

Makes it easy to see at a glance what is a formula vs. constant vs. text vs. link to other workbook or worksheet.

Standards vary between different groups and firms, but you almost always see the following:

Blue = Constants / hard-coded numbers.

Black = Formulas and text.

Green = Links to other worksheets.

Blue w/ yellow background and border = Input cell. (This one is less important)

Excel Shortcuts for Finding Different Cell Types:

F5 Jump to Cell (PC / Mac)

F5, Alt + S + O + X Highlight Constants (PC)
F5, Alt + S + F + X Highlight Formulas (PC)

F5, ⌘ + S, ⌘ + O Highlight Constants (Mac)
F5, ⌘ + S, ⌘ + F Highlight Formulas (Mac)

Alt + H + FC Font Color
Alt + H + H Cell Fill Color

On the Mac, you’ll have to use CMD + 1 to access font and fill colors instead.

Ctrl + F Find (PC)
CMD + F Find (Mac)

F2 Edit Cell (PC)
Ctrl + U Edit Cell (Mac)

How to Find Links to Other Worksheets / Workbooks:

Highlight formulas and…

Search for “!” but not 100% accurate, since hard-coded text might also have it.

And sometimes, a formula might have “!” but is NOT just a direct link elsewhere – it might just use a link from another worksheet IN a formula instead.

Better Solution – If You’re Comfortable with VBA:

Please see the Excel file at this link below – YouTube does not allow us to upload VBA code because of some of the characters included in the code. So you can get the full macro and the VBA code from the Excel file attached.