Financial Modeling Plans
  • Unlimited Download Access
  • Doubt Solving By Experts
  • Excel School
  • 1000+ mins of Video Tutorials
  • 1200 mins of Project Finance

  • More

Financial Modeling Aesthetics - Color Coding

Financial Modeling Online
Formatting excel models is an art in itself. Properly formatting excel models is important because formatting makes it easy for others to read and understand the analysis. One would realize its importance when one sets out to make changes to the model created by him/her.

Working with Excel models is often collaborative, either in creation or in their use. It comes as no surprise that there are few universal standards for formatting points – such as color coding formulas vs. constants but not for other aspects. Different groups at different firms still use slightly different standards.

Proper formatting is most important and perhaps the soul for actual financial models than for other types of data sets (sales reports, customer details etc.,) but still makes your data in this case very readable and easy to use.

If you are an Investment Banking professional, formatting reflects your professionalism and that of your firm. Not just in Investment Banking Aesthetics play an important role in any client driven business. It always pays to make your analysis well and models properly formatted to make them easy-to-read and highly professional.

We’ll cover color coding in today’s article and go forward with Number, text and other important formatting options.

Color Coding in Models

Even if you’ve got everything wrong and your model is wildly inconsistent, get your color coding right. By color coding, you can convey the information of what can be edited and not, what is important and not, etc.,

This makes both auditing and reading the model very easy.

Links, Constants and Formulas

Blue Font: All manually entered (hard-coded) numbers; used for historical financial data and also for many input cells in the “Assumptions” part of models.

Black Font: 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: All direct links to cells in other worksheets or workbooks

 Financial Modeling Aesthetics - Color Coding

It is preferable to leave the text in Solid Black colour. Yes, technically it’s hardcoded but using Blue font makes it difficult to read text. However, if there’s an input cell that accepts text, we do use the blue font colour.

Free Download

Macro Instructions

There is no built in formula to help you do this, you have to either record a macro or use VBA. Below are the steps to record the macro.

  1. Press Alt + W + M + R (or Alt + T + M + R, no equivalent on the Mac) to name and begin recording your macro.
  2. Press F5 (“Jump to Cell”) and then Alt + S on the PC, or ⌘ + S on the Mac, to go to the “Go to Special” menu.
  3. Press “O” (or ⌘ + O on the Mac) to select Constants and “X” to uncheck text.
  4. Now press Alt + H + FC (or Ctrl + 1 on the PC, or ⌘ + 1 on the Mac) and select a Blue font color for these constants.
  5. Press Esc.
  6. Now do the same thing, starting with F5, but select Formulas (F, ⌘ + F on the Mac) instead of constants and press “X” to uncheck text.
  7. Now press Alt + H + FC (or Ctrl + 1 on the PC, or ⌘ + 1 on the Mac) and select a Black font color for these constants.
  8. Stop recording the macro with Alt + W + M + R or Alt + T + M + R.

It is tricky to find links to other workbooks and worksheets, and most likely, VBA has to be used to get this working correctly.

The basic idea: search for “!” in each cell that contains a formula, and then change the font color to green.

But with the way macros work, you would need to modify this in the VBA Editor and make it a For Each loop through all instances of “!” you find, and then change the font color for all of those.

It blended with my experience as a controller for a construction company. Specifically integrating the BS, P&L and CFS. I like modeling, budgeting and releasing Pro Forma statements. Aslo, I liked the way dates were used.
I followed the course closely, it was helpful both from the perpective of understanding financial modeling as well as to improve my knowledge of excel. Hoping to master my skills over time with EduPristine's support.
I am a rookie with MS Excel. Will it be difficult to learn Financial Modeling? Difference between learning MS Excel and Financial Modeling? What career can I build by learning Financial Modeling?

Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by Edu for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP is not responsible for any fees or costs paid by the user to Edu nor is GARP responsible for any fees or costs of any person or entity providing any services to Edu Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas®, and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

2015 © Edupristine. ALL Rights Reserved.

ca_final_overall_course.php Post ID = 68102