Excel Tricks for Financial Model Formatting

Why is formatting important?

Everything from color, numbers, formulas, and text has specific formatting logic when it comes to creating a financial model. Great financial model formatting is key to being a great financial analyst.

Formatting a financial model is important for two reasons. First, proper formatting helps keep the financial model clear and easy to understand. Secondly, a sloppy model may obscure good financial analysis. It may pass off a bad impression on the client, manager, or executive using the model.

A good financial model should be consistent, efficient, and clear.

Recommended number, currency and percentage formatting

While the following are just practical guidelines, they are quite recommendable as industry-wide standards. Not all financial models will follow these formats for decimals.

a) No decimals: Years, dollar values in thousands or less.

b) One decimal: Percentages, multiples, dollar values “in millions or more”.

c) Two decimals: EPS (earnings per share), share price, prices less than $100, some multiples, some percentages.

d) Three decimals: Shares outstanding when expressed “in millions”.

These guidance tips are commonly followed by financial analysts. But, they are not written in stone and might vary from model to model, from company to company. The most important thing is to remember to remain consistent across the model.

How to use Excel shortcuts?

Now, let’s learn some Excel tricks to format the financial model in our example on the screenshots.

First, let’s highlight C6 – J22 to correct the number formatting as we want to see no decimals, one thousand separators, and negatives in parenthesis.

We will use the “Go To” window in Excel to highlight such a big range of cells quickly and mouse-free:

1. Use the Excel shortcut F5 to open the “Go To” window.
2. Your cursor is already in the cell C6, we should put J22 into the “Go To” window to point the cell range to highlight.
3. Ctrl + Shift + Enter will highlight the range from the current cell to the cell mentioned in the “Go To” window.

Now let’s use the “Format Cells” window to format the highlighted numbers:

1. Use Excel shortcut Ctrl + 1 to open the “Format Cells” window in Excel.
2. Next, use TAB  to move within the window clockwise and Shift + TAB to move within the window counterclockwise.
3. Finally, use Spacebar to check the box for one thousand separators.

How to use the $ sign in the right way?

We want to keep our model clean, so we wouldn’t want to use the dollar sign on every line. But let’s make it bold and with the dollar sign on the last row 22 “Unlevered Free Cash Flow”.

1. Use Excel shortcut Ctrl + Shift + Right Arrow to highlight C22 – J22.
2. Open the “Format Cells” window with Ctrl +1.
3. Use TAB  to move within the window clockwise and Shift + TAB to move within the window counterclockwise.
4. We like the format with no decimals and one thousand separators. We just want to add a dollar sign to it.
5. Use Excel hotkey Ctrl + B  to make the highlighted cells bold.

How to use the % the right way?

Now let’s work with percentages. Note that in our example the format in K27 (Step) is desirable for all the % in the model (C32 – J27). We can use Paste Special Format here.

1. Copy the cell with a desirable format (k27) using Ctrl + C.
2. Highlight the range of cells where you would like to use the same format (C32 – J27) using Ctrl + Shift + Arrows.
3. Use Excel shortcut Ctrl + Alt + V to open “Paste Special” Window.
4. “T” is highlighted for Format which means that clicking “T” on our keyboard will bring the cursor there. Then Enter for “Ok”.

Recommended formatting for text color

The first and easiest method of formatting a financial model is to use a consistent color scheme. It helps to annotate different types of cells and data.

Here is a recommended color scheme. It is commonly known amongst financial analysts and other users of financial models:

1. Blue: Inputs, or any hardcoded data, such as historical values, assumptions, and drivers.
2. Black: Calculations and references to the same sheet.
3. Green: Calculations and references to other sheets. (Note that some models skip this step and use black for these cells).
4. Red: References to separate files or external links.

How to format the text color?

Coloring the text and contents of cells helps the user understand the different types of data working within the model, as well as the flow between the different pieces of data.

Let’s make all inputs in blue color for our example model and leave the formulas in black.

1. Use the “Go To” Window (F5 Excel shortcut). Then, choose “Special” to highlight all constant numbers.
2. When navigating through the window “Alt + Highlighted letter” will make actions mouse free for you.

Font color is already on our Quick Access Toolbar, so we can access it easily. To learn how to put font color or other necessary functions to the Quick Access Toolbar on your Excel Ribbon, read our article “5 Best TIME SAVING TIPS in Excel”

Borders

The best practice is to use the top and right borders in Excel. Then, escape the bottom and left borders. The reason is a cleaner format if inserting additional rows (top borders). Otherwise, when filling the formulas to the right and don’t want to drag the left border with it.

Let’s input the right and top borders in our example model.

1. Use Excel shortcut F5 to open the “Go To” window in Excel, and then Ctrl + Shift + Enter to highlight the necessary cells range.
2. Excel shortcut Shift + F8  will allow you to highlight several uncontiguous cells ranges.

Don’t Embed Inputs in Formulas

Instead, break out inputs into a separate line item/cell.

In our model, we assume that revenue growth in 2019 is 14.3%. Then it goes down each projected year by 0.5%. Instead of just using (0.5%) in the formula, we have created a cell with the step (K27).

It’s important in our example to anchor $K$27, so we can fill the formula to the right freely with keeping the correct formula for all projected years.

Never Input the Same Number Twice

USE FLATLINES. Make it a link to the cell. This way Excel will flow and be dynamic in this sense.

An example in our model would be the projected Cost of Revenue (% of sales) in 2020 that we assume to be the same as in 2019. Instead of just inputting the same number in the cell G28, we’ll link it to F28.

We assume that for all projected years 2020 – 2023 and for all value drivers except revenue growth, the numbers will be equal to the previous year 2019. So, let’s use a flatline here.

1. Highlight the cells range G28 – J32 using Excel shortcuts Shift + Right / Down Arrows.
2. Fill formula and format to the right with Ctrl + R.
3. Then, fill the formula and format down with Ctrl + D.

Financial Model Formatting Matters

Formatting for financial models is very important because your work represents you and your company. In  addition, sloppy, inconsistent work may give the wrong impression about the work quality. The numbers can be 100% accurate, but a poor presentation could lose a deal.

Take a few extra minutes to format properly and make the work easy to understand. Learn more about Financial Modeling best practices and Excel tricks in our Educational Games for finance professionals

‍