Excel styles

Overview

This is an extract from our pre-course videos for our renewable energy and infrastructure project finance modeling course.

Video

Video Transcript 

I’m sure your company, like every other company has defined colors and formats that need to be used for things like titles and headings, font types, tables and graphs. They may even be so kind or I think really evil enough to provide templates in word and PowerPoint for you to use. Generally considered important to maintain consistent branding across the firm or to look good.

So a question for you. 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, don’t you want them to look good too? In my opinion, Excel actually has a higher need for specific formats than a word document. It isn’t just about font types and table colors in Excel. We also need to communicate what each cell is used for. Say if the cell is an input or an output or the total of a row. In this lesson we will cover the creation and editing of set formats in Excel called styles. Even if you haven’t seen these in Excel before, chances are you may have seen them in Word. Does this look familiar maybe? I remember as a young engineer it was almost a criminal offense to not use the heading and font formats. These were the predefined styles set by my firm. Now what this means is you don’t need to format every line specifically. You just pre-define a style that you think looks good.

Okay, good try Dan. Definitely not that sort of style. You can predefine a style that you think looks good and just select the one you want to use. Ensuring consistency of use across the whole organization. Knowing that, let’s go into Excel and learn how to create and edit styles.

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 would 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 E 40

So in cell E 40, I’ve started with a value, I’ve started with Dan’s age of 80 years old and so to create a style we’re going to 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.

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 particular style.

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. And 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 star, 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 it’s color, showing me what it will look like if I select the style. And I could just click now to apply pivotal.

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. 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, so that’s how we create the style.

But before we are done, there are a couple of other points.

First of all, styles can be copied and pasted 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. Styles can also be copied 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

,and finally, styles can be added. 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 Lincoln format, the Lincoln format, if I right click to modify, only includes a font. So it’s just saying that whenever I use the Lincoln style, I 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 Lincoln 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 link in 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.