Excel Styles for Financial Modeling: Introduction & Best Practices

Overview

Here’s Pivotal180’s guide on how to use Excel styles, along with recommendations on financial modeling formatting and best practices. This is an extract from our Renewable Energy & Infrastructure Project Finance Modeling course.

Video

 

Why are Excel styles so important?

I’m sure your company, like every other company, has pre-defined colors and formats for things like titles and headings, font types, tables and graphs. They may even be so kind (or I think evil enough) to provide templates in Word and PowerPoint for you to use, generally considered important to maintain consistent branding across the firm.

But how many of you have defined Excel templates? Surprisingly, it appears this is much less common than within Word or PowerPoint, even though you may share your spreadsheet with multiple external parties. So, don’t you want them to look good too?

In my opinion, Excel has an even higher need for specific formats than a Word document. It isn’t just about font types and table colors. We need to communicate what each cell is used for. For example, if it’s an input, an output, or a row total.

How to create and edit Excel styles

This lesson covers how to create and edit a specific type of Excel set formats called styles. What this means is you don’t need to format every line. You just pre-define and then select a style you think looks good. This helps to ensure consistency of use across the whole organization. Knowing that, let’s go into Excel and learn how to create and edit styles.

Video Transcript

Haydn: I am in the style sheet called L of the pre-course demos file. The sheet is in all of our demo models and the quarterly model that you will also build. You can see here in column C we have the name of the style. Column E applies that specific style, showing the user what it looks like, and column G includes the description of a style. We won’t go through every style on the sheet because they are best explained as we need them, but we will learn how to create a style in Excel in E40.

How to create an Excel style

So, in cell E40, I’ve started with a value, I’ve started with Dan’s age of 80 years old. And so to create a style we go up into Home, Cell Styles, and then New Cell Style, and I click on that.

What we can see here is, first of all, a style name, and I’m going to type in here Pivotal. Now, this has to be one word. I can’t type in Pivotal, space, 180. It doesn’t work. The convention in Excel is that it must be one word. So I have Pivotal written at the top.

Each of these tick boxes, for number alignment, font border, fill, and protection, are our selection for if I want to include these various items as part of my style. So I could for example just tick number and none of the other ones. Meaning I only want to apply a number format to a particular style.

Excel style example

In our case, as an example, we are only going to tick borders and fill. So with that, I’m going to go up and now press the format, and I get the typical format cells menu box up top. And because I didn’t tick number, alignment, or font, I’m going to jump straight to border. And I can format this however I want.

I’m going to start here by picking this thick black line here and applying an outline. So I’m putting a black outline around my cell, and I’m going to pick a fill. In this case, I’m just going to pick the green color here. Click that, and then come down to okay. I’ve now added shading to my fill, it’s green, and my border is showing as left right top and bottom borders. And I can press okay.

But you’ll notice that I haven’t actually styled or colored cell E40, I’ve just created the style itself. So to apply a style, I go up to Home, Cell Styles, and now I could click on Pivotal or output, and as I hover over these cells, you can see cell E40 is changing in its color, showing me what it will look like if I select the style. And I could just click now to apply Pivotal.

How to edit or delete an Excel style

Before I do that, I’m going to show you one other quick thing. If I right click this, I also have the option of modifying a style, changing its color, or deleting the style. And if I had many, many cases of Pivotal being applied throughout my model, I could just modify this and change the color throughout the model, just like a header one or a header two in Word.

Right now I don’t want to modify the style or delete it, I’m just going to apply it. You can now see that I have the green format for Pivotal. That’s how we create and edit a style. But we still haven’t finished. There are a couple other points.

How to copy and paste an Excel style

First of all, we can copy and paste styles within a workbook. So rather than me going up to this cell, now back up to Home, Cell Styles, and picking Pivotal180, I could instead just copy and paste the format. You can also copy styles between workbooks. So if I just wanted a copy this whole sheet, right click, move or copy, I can copy this whole sheet to another workbook and copy in all of my styles that I need for a new spreadsheet.

How to combine Excel styles

Finally, we can add styles. So as an example, I’m going to click here on E39 and I’m going to apply the output style to it. And I’m going to put a number in here. Now if I now went and changed the style to say, Linkin format… The Linkin format, if I right click to modify, only includes a font. So it’s just saying that whenever I use the Linkin style, I’ll use Calibri body size 10 with green font, it’s got no fill included.

I press okay. It’s going to apply that style. And because it doesn’t have a fill style, it’s just overlaying this green text from Linkin onto the red output. So it looks like it’s combined the two styles, but it hasn’t exactly done that. It’s just combined the formats.

So let me show you. If I go to Home, Cell Styles, and I modify the output format… So I modify this and maybe I’ll make the fill here a different color to make it really obvious, I’ll make it purple. And press okay. This cell didn’t change, but my cell up here, which was styled as an output, has changed. That’s because Excel only recognizes this cell now as a Linkin format, even though it maintained the red color. This is quite useful, but it’s also a pain. So just be aware of this when you’re using styles.