Excel financial model formatting guide

Tl;dr: Building a huge Excel model is great, using it easily is awesome. The foundation is in your formatting and knowing the code of what colors to use. It’s simple, but you need to know the rules. Then you have to apply them as frequently as you breathe. I’m going to teach you how to use font colors consistently and to use cell background colors.

Watch the video

I’ve made a video version. I made this five days ago so I actually teach some different things in the blog and in the video as I forgot what I talked about! So check out both.

I messed up the lighting on my green screen, so my hair is on acid…

Why colors matter

If you have a large Excel model, you want to make it easy for you to use. It’s one thing to have all sorts of fancy formulas, it’s another to know where to make changes. Colors help you to know:

• What is a link
• What is a formula
• What is an assumption (input)

There are some other types, but these are the key ones to focus on. We’ll cover the others later.

You can track your model

If you have made, bought, or inherited a model and you are going to use it for some time, you need to be able to come in and out of it, knowing that you’ll forget how things work.

If it is not formatted well, this will be a pain the bum bum. You’ll be thinking “feck, where the hell do I change churn?” and waste time trying to find the assumption cell. That’s a major waste of time.

If you think there are errors in your model and you need to audit how things work, then knowing a cell link to another sheet can help you triangulate where the issue might be.

I can go on and on, but you get the point? If models look like trash you don’t want to use them.

Others can track your model

If you have a great model, you may well have to interact with others in the team on it. You might have someone in marketing update a sheet, similarly with someone in finance. If you are sharing your model with investors, they need to look at it and understand what’s going on.

Well-formatted models make life easier for everyone, especially for those new to your Excel sheets.

You look professional

Some models are important. People need to trust them. Why do you think people turn up to interviews in a suit, or at least a nice shirt/dress? You look the part before you have the part.

Models are the same. Well-formatted models just convey more confidence. If a big model looks nice, well you must have put in even more time into the basics, right?

Of course, it is more important to have a model that works, but a bit of extra time making it look good is time well spent.

The basic colors

There are three key colors you want to consider. These relate to the color of what’s in a cell. These are what investment bankers use.

Here is how I explain things in my financial models.

Blue

If it is an input, it is blue. All assumptions should be in blue.

Black

If it is a formula within a sheet, it is black. This should be most of your sheet/model.

Green

If a cell links to another sheet, then it is green.

Even if there is a formula with a link to another sheet, then it should be green. The best practice is to separate these though.

Advanced colors by using background colors

Now you have the basics, let’s add some more details. The background colors of cells are the best secret to formatting as they make it so much easier to follow. Fonts are a great place to start, but they’re still hard to see due to the contrast. If you have no background colors you’re faced with a sea of white, hoping to see a glint of color change.

Most people don’t use background colors well, if at all. In fact, from when I started, I use background colors more and more.

Yellow assumption box

As standard, I make every assumption a yellow box with blue text. See it below for staff costs in my staff sheet.

It’s just so easy to spot. Here is a horrendous thousand-mile view set at 40%. You can’t even read the text, but you can easily see what is an assumption and what is not.

Yellow assumption text

Ok, now an edge case to be complete. I never had a rule for this, I do now. If you want to add text as an input, it’s blue text and yellow background. The same as a cell with a number in it.

I used to have them as blue text and white background, but I decided it just didn’t make it obvious you could use those cells. You could in theory use the black font, but I’ve decided any input is blue. Anything dependent on another cell on the same page is black.

Orange dropdown box

This is the newest change I added. I used to use the same yellow background, but I added this differentiation as I know it is a drop down and there are limited options. It’s a small change, but I like it.

You can see an example of the dropdown below, as well as yellow backgrounds.

Headers to break things up

This isn’t a course on formatting, but since we are talking about cell colors, let’s cover using colors to break things up.

When you make massive models like my enterprise SaaS fundraising model, it gets hard to track where you are in a sheet. I use extensive use of shades of colors to help people track my sheets like headers. The highest level is dark and they get lighter to level three.

Now if you have a monster sheet, then I sometimes use a super bright color like orange to break it up at a macro level. I admit I haven’t decided on a rule for this yet ;).

Look at this high-level image. You can see all the colors I use. You can also see that two sections of the sheet are broken up with an orange bar.

Master level

Now a master tip for those that make models, or want to update things.

Shock colors as a to do

Cell colors can be used as a to-do list as you change models. They are super-duper useful!

I use the ugliest brightest color as a to-do note. This is normally that ugly-ass stock yellow.

Say I want to change how the Staff sheet links to a new sheet to automate some COGS, I’m going to highlight that row of formulas and maybe leave a note on what I need to do. I can then go spend a day making the new sheet, and easily see that I need to ensure I’ve linked the sheet properly. This is super important if you are making a lot of changes and will keep getting side tracked.

Here’s a real example of a model I am updating. You can see the formulas are messed up as I deleted something.

When to format nice

Formatting sounds boring as a boarding school on a bath night. It’s important though, but do you really have to format everything you touch in Excel?

No. I’ll explain when you should.

Things get big

When you start making something with more than 30 rows, you should definitely start doing the basics. Mainly for assumptions. As you get more proficient in Excel, using shortcuts and the like, formatting becomes easy. You can also format a cell and replicate it as you build so it’s simple.

If you are doing some quick math, forget it, I’m not formatting anything beyond what my OCD can tolerate.

Used in future

Following from the last point, if you are going to use a model again or repeatedly then I absolutely format from the start. I know I’m going to forget how even a simple sheet works, so some basic formatting and structuring spares me from thinking.

Multiple sheets with links

Finally, if you get to the point where a sheet is not only reasonably large, but you now have more than one sheet, you absolutely want to be formatted well.

Let’s say you want to delete a sheet, well the other sheet is going to blow up as the links break. That’s a huge pain. If you know where the links are, you can disconnect them.

When not to format nice

If I’m on a call with you and I’m helping you figure out your margins, and the sheet is going to be deleted as soon as you hang up, I do almost no formatting. I simply don’t care. Fancy formatting is a waste of time. I’ll just format currencies in case I want to read things better.

Finally, if you are really crappy at Excel, then messing about with formats for something unimportant is not efficient. Focus on just making something good enough within your capability. If you are planning on getting more proficient, then it’s worthwhile putting in some time to getting faster.

What time-savers are ok

If you are making something scrappy, there are some shortcuts that are OK from a visibility point of view. These are not huge points, but if you want to be a master you want to be aware of these slight differences in the back of your mind.

Blue vs black

I love making inputs yellow because everything is not that important after. The contrast between blue and black is not that huge. If I can format one thing, it’s the yellow input boxes.

This is a scrappy model I made to decide if I wanted to do a startup. Minimal color formatting, other than the yellow boxes. No blue anywhere. I couldn’t be arsed.  The only real font formatting I did was for percentages because I hate seeing 0.005 as that slows me down. I haven’t even capitalized the text properly. Why? It’s for me. I don’t care. I just did what helped me be efficient.

Now I don’t want to sound like a wanker, but you might look at this and think “um, Alexander, that’s fancy compared to scrappy for me!” Don’t benchmark yourself to me. I’m really proficient. Also, I spent like 8 hours on this project including doing desktop research on how many women have babies a year, lol. I thought it would be more!

I don’t like doing sales, but I made a super useful plugin for Excel with only the shortcuts that I use regularly. The most useful one for this topic is auto-formatting blue, black, and green fonts. I just hit a button and everything is done for me. Fixing this just isn’t an issue for me anymore. So I can knock something up quickly, hit a button, and keep going.

Green vs black

Similar to the above, formatting green vs black is one of the lowest priorities I have. It doesn’t add huge value to me. Again, I have my plugin, so it’s zero stress. In fact, I mainly format green cells if you are using my tools as you know squat about how the model works. So seeing green lets you understand “ok, this comes from there.”

For best practice, I always just auto format my models before I share with anyone. For the scrappy model I showed above, there are no green links. The model is simple enough for me to know how it works. If I was making a massive model, then I need all the help I can get, so I format with green properly. There are many nerdy reasons why I do this, but you don’t need to know if you’re reading this blog.

When do you go against the rules?

I’ve sort of explained this already, but I have a point I want to make.

Presentation when outputs are clear

So, there are two places in my model where I don’t really like sticking to the rules. Whilst I’m a renegade at heart, with Excel you need to follow rules.

• KPI sheet
• P&L for deck (a sheet designed to go in a pitch deck etc)

The thing these two sheets have in common is they are outputs. You won’t find an assumption on them. Basically, there are almost all links to the sheet. There might be some calculations for CAC or LTV, but really it’s a summary of all the hard work done on other sheets.

Green is ugly… just like jealously.

KPI sheet

I flip between wanting to follow the rules and using common sense. It’s nicer to read black text because of the contrast. However, there are calculations in my KPI sheets and they can be almost a thousand rows long, so for clients, do you want to track how things work, or just check things are how you want them?

I think I decided to follow the rules on this sheet with the new update I’m working on.

P&L for deck

Stupid title, but it’s what I call the sheet in my models. I want you to edit this sheet and add whatever rows you want. You are only going to pull in links from other sheets. You might copy this into a PowerPoint and it’s easier when it’s preformatted (I used to be a banker so I’m already thinking of the image paste options… feck I’m such a nerd).

I normally warn you to not mess about with sheets unless you know what you are doing and you all actually do what I say (ha!). So I get people asking “can I really change this sheet?” And I’m like yeah, I tell you to!

So if you are making your own model, and you want to add images to a PowerPoint, I would be a naughty girl (want to chuck in some girl pronouns now and then as I know a lot of women read my blog too!) and just format the font in black.

When to know to start formatting

We’re almost done. I just want to drop more knowledge so you really know what you are doing and don’t have to figure out everything I did over years.

You have something reasonably important you want to figure out. Maybe you are planning sales headcount requirements for the year to give to the HR team? You start coding up a sheet and you get a quick answer. Ok. Well, actually, this is interesting stuff. You’re thinking about some new concepts and maybe you could start thinking about a new compensation plan.

Now stop. Are you going to spend an hour or more on this sheet? If so, get out of ghetto mode and start formatting as you go. You don’t have to be perfect, but use the three colors and absolutely use the yellow background color for assumptions.

If you end up making a useful tool others will see, then spend 20 minutes following my best practice. People will appreciate it.

At some point, I might make a video on how I format models on the fly just like this shift. Request things like this in the comments.

How to format quickly

I do a lot of Excel. I have all sorts of plugins, but it annoyed me there are like 300 tools and I just do a few things, and some don’t do things like saving (in the way I want) and my font recoloring. So I made my own plugin. Mainly so I officially join the nerd brotherhood.

It’s the cheapest thing I sell and it really useful if you spend time in Excel.

You can buy my 50Folds Excel Productivity Addin For Modeling here.

Oh btw, forgot I made this but I made a free cheat sheet. If you want to check it out, hit the link: Excel number format cheat sheet

 

We’re done now. The next blogs/videos on this topic will be around how to model with assumptions and something I forget.