Thank you for joining.

For those of you that I haven't met, my name is Maria Weber.

I'm one of the trainers at Financial Edge, and I'm going to be doing today's session on advanced formatting in Excel.

We're going to be having a look at three things. This is a short session.

It's just 30 minutes, and in those 30 minutes, we're going to be looking at three things.

Those three things are cell styles, conditional formatting, and data validation.

I have given you a copy of the PDF slides because I think they could be quite useful. I'll show you specifically which ones, but we are going to be spending the bulk of our time in the Excel workout.

Okay, so let us get started with cell styles. Okay, so first of all, what are cell styles? Cell styles allow you to quickly format cells in Excel. Right? You set up predefined styles, and you apply those styles. What's good about this is you get consistency. If I want something to be formatted in a blue font, I don't run the risk of then picking a slightly different color blue and then my work looks a little bit messy.

Consistent formatting throughout, and the ability to update your formatting, change things as required.

And in terms of usage, yes, it requires a little bit of setup, but once set up, you can quickly apply the styles.

Let's go into our workouts. So I'm going to be using that empty file.

Okay, so Advanced Formatting Workout Empty.

You have the full file as well, as well as the slides.

If we come into the empty worksheet, first of all, if you're looking at a model or any kind of Excel spreadsheet where it's a bit involved in terms of different numbers coming from different places, doing different things, you will most likely have a formatting key.

Now, this formatting key, it could be different, different institutions, but typically, you would want to format in a model your inputs, so say your assumptions, as being one format. So here we've got a light blue background with a blue font.

That's quite typical. Then we would also want to show which numbers are hard coded, so something like historic numbers from historic financials. They shouldn't change.

They are actual input numbers. It's not formulas that should be updating. We want a separate color for that, and then formulas, so calculations linking to other cells.

So we're not doing modeling today, but if you've looked at a financial model, I've just brought this one up on screen.

Here you can very easily see from the start, oh, all of these numbers, those are inputs. Okay? Those are assumptions.

All of these numbers here, they're calculations, they're formulas.

And then all of the blue numbers, those are all hard-coded inputs. Right? So hard codes.

So how do we set up these styles? Now, if you just open up a blank Excel document, so a new document, unless you have a template saved with the styles in them, you'll find that there's certain preset ones in Excel, and we would want to set up our own.

To navigate to the cell styles, it's on the home ribbon, and you'll see there Cell Styles. If you want to use your shortcut keys, not on a Mac, Alt, H, J. That will bring up the cell styles. So here, as you can see, these are the default ones in Excel.

We want to create our own cell styles.

Now, in the spreadsheet that we've got, if I just navigate to the first tab, this normal cell style, we have set up some of our own, so Alt, H, J, or just on the home ribbon and Cell Styles. This looks quite messy.

I would probably want to delete the ones that I'm not using.

Also, what's not great here is we've got Normal 2, Normal 3.

Okay, so this is a little messy, but let's work with what we've got.

Okay? So let's start with a normal style.

You can see here I've got just some numbers typed in, and they don't have decimal places.

They don't have thousand separators. Negatives are shown with a dash.

I want to put negatives in brackets.

So that's how I would set up my normal cell style.

So I'm not going to set it up with you.

We already have a normal style set up.

Let's apply that. Let's select the cells that we want to apply that cell style to. Right? So I'm just going to select these cells that I want to apply the style to. Then if I go to Cell Styles, Alt, H, J, or just click on the dropdown, let's apply Normal 2. Okay. If I click on Normal 2, you can see I have beautifully formatted numbers with one decimal place with 1,000 separator. Negatives are shown in brackets.

Now, I'm not going to spend time actually setting this up with you.

I will show you how to set up a style with something a bit simpler, but that's why I've included the slides for you, because if you look at slide four, this is how we set up the normal style.

So under Number, you would have that comma to show the thousand separator. You would have that dot zero to show one decimal place.

And then this looks a bit odd. We've got a space the size of a square bracket. Now, the reason we want a space the size of a square bracket after our positive number is because let's look at how negative numbers are shown.

So the first part of this shows positive numbers, second part of this shows negative numbers. So negative numbers, we put in brackets. So same comma separator, one decimal place, but we bracket it. So I want the positive numbers to also have a space the size of a square bracket so that my numbers line up nicely And then after that, we've got how we want zeros to be shown.

So I could say I want a dash for a zero.

I'm saying no, I want 0.0 and the space the size of a square bracket at the end, and then that's also how I want my text to be shown. Right? So just coming back to our spreadsheet, if, for example, looking at our negative numbers, if I now were to put some positive numbers underneath here, okay, and then I change to negative again, those aren't formatted nicely. Okay? And you can also see they're not lined up nicely.

But let me apply my normal cell style, Alt H J, navigate to the style you want to apply.

Normal two is what I'm applying, and there you can see, look, all the decimal places are nicely lined up because we've got that little space after the positive numbers where the bracket for the negatives is.

So that is what our normal cell style looks like.

Now, you can set up cell styles for anything.

Some common useful ones to set up we've included in the slides. So percent, date, multiples, hard-coded numbers that you're just formatting the font color. Let's apply some of these, and then I'll show you how to set up a cell style because we haven't done that yet.

Going back to our spreadsheet, I'm just going to go to the next tab along.

Okay, so here, percent, select what you want, Alt H J, and then go find your percent.

So this, a little messy again, we've got percent two.

I would just want to have clean, just what I need, delete what I don't need.

So there I've got my percent. Date, you can see I don't want it to be formatted here with the dashes. I want to show rather the month name. And again, if we go apply our date format, that's how we've set it up. Hard codes, this is us setting up our blue font, and we can pick a very specific blue, and that will always then be applied to our hard codes.

Then let's set up a style for multiples.

Now what's nice about this is we can format a cell in a way that looks like text but actually is still a number.

So if I'm doing a debt to EBITDA multiple, for example, debt is two and a half times EBITDA, I want an X to show its times.

But if I just put an X in, my formula's not going to work because Excel doesn't read that as a number. So let me style it as a number.

So let's set up a new cell style. So Alt H J, and then if you look at the bottom, you can see there N for new cell style right at the bottom. If you click into that, let's give our style a name. So now it's called Style One.

So I'm going to call this Multiple.

And now I've got to decide, well, what do I want this to format? And I actually just wanted to format the number. So I'm going to untick everything else, because everything else will then be formatted normal.

So the normal style will prevail. But then on top of that, this will have a special number format.

And you can see at the moment, the number format is in that normal style that we've set up.

But let me click on this Format button, and that Format button will open up this number format.

And then all I want to do is I want to add a space and an X to show times after the number.

So coming in here and editing where it says Type, after the 0.0, I'm going to do a space, and then I'm going to put X because I want to show the number, and then a space with the X afterwards. Same for a negative number.

Negative number, after the 0.0, I'm going to put a space and an X.

And then I don't need to show X after text, so I'm just deleting that last bit, and I'm deleting the zero because I'm not going to show a zero times multiple.

So that is my multiple, and this is, if we just click OK, and OK, that, in case it was a bit fast or too small on my screen, we've got that over there.

So that's that multiple style.

Let's now see if we select these numbers, Alt H J.

Let's go find-- Look, there we go. I've now got the multiple style set up.

So multiple style, click on that, and I have got my numbers being formatted as a multiple.

So now, if I say EBITDA is 1,000, debt is one times EBITDA, it works. If I were to try format that as text, obviously it doesn't work. Okay, so that is quite a useful thing to have.

You can modify styles as well. Right? If you just right-click on the style and modify it.

So if we go back in Alt H J, here, say I want to modify what I've just done. If I right-click on that multiple style, I can modify it, and then go in and change what I don't want.

And that is the final benefit of cell styles that I want to show you is how easy it is to blanket change everything.

So say, for example, these numbers, if I had formatted them as inputs, right? So these are assuming assumptions formatted as inputs.

Let's maybe format these multiples as inputs as well.

And now, for whatever reason, we decide actually we don't want that blue color. This is going into a client presentation.

We want a different color scheme, or we've used a client's model that's a different color scheme. We want to put it into our bank color scheme, whatever it is. We, instead of having to go and manually change and find all of these cells, if you just go in and edit It will change everything. So if I go into cell styles, let's go to input two, that's what I applied.

Right-click, Modify, and then what do I want to modify? I want to modify the Format.

Okay? And in the format, I want to modify the Fill, and let's say I want to change this to a light yellow color.

Click OK.

OK again, and anything that had been formatted in that blue has now been updated.

Okay, final point to note on cell styles.

Unfortunately, this doesn't save across your workbooks.

So if you now were to open a brand-new workbook, you wouldn't have the cell styles there.

You could import cell styles from another workbook.

So if you have another workbook that's open, you can say Merge Cell Styles. That's a little dangerous if you've already got data in your workbook formatted in a certain way if the styles clash a bit. But what you can do is once you've got your style set up, you can just delete all the numbers out, so it's a blank workbook. Delete the numbers out, you've got your style set up, and then you can do File, Save As, and instead of saving as a workbook, you can save as a template. I think it's XLST. And then that will be saved as a template, and then you can open that template up, and that will have your cell styles already there.

Okay. So guys, that is it for our first part on cell styles. Are there any questions before we move on to the next bit, which is conditional formatting? If you are typing, please do carry on typing.

In the interest of time, I am going to keep going, but please don't stop your question.

I will come back to it.

Okay, let's talk about conditional formatting.

So what is conditional formatting? Cell formatting changes depending on the value of the cell or the formula. We like it because you can have automatic changes in real time, and it's a nice visual aid, so things can clearly stand out, like on a dashboard, things above a certain number, below a certain number, et cetera.

And you can use existing preset cell styles, which are great, but you can also then customize, and not cell styles, formats.

You can also then customize using formulas.

So let's go back to our worksheet, and let's go to Conditional Formatting. So here we have got some data on transactions, salespeople, products they've sold, how much they've sold, values, et cetera. So say I'm looking at the sales amount. I select everything.

I want to highlight all the sales that are above 200. So if we go to Conditional Formatting, it's also on the home ribbon. If you're using accelerator keys, that would be Alt + H + L for conditional formatting.

The preset cell styles, if you start with Highlight Cell Rules, there we go. I'm not going to obviously go through every one, but let's look at, say, Greater than. You can now put what do we want it to be greater than. You could link it to another cell in your spreadsheet, or you can just type in a value. I want it to be greater than 200, and then you can decide how do you want that to be formatted.

I think greater than 200's good. I want to put it green, or you can even customize your format, but let's say green fill with green text.

And what we mean by this is dynamic.

Say, for example, we update and Halligan has now sold more units or... Yeah, there we go. So say that 31.8.

Oh, no, actually, that should be 231.8.

Now it will get formatted accordingly.

So there are preset styles that you can use if not... I keep saying styles. I'm stuck on the style.

Preset formats you can use if you go to the Conditional Formatting.

You can use things like data bars, so like showing gradients. You can use color scales, like a heat map. You can do icons.

I would just say use this sparingly.

First of all, it can get confusing if there's too many things happening.

It should be a visual aid to draw attention to certain things, and also just be aware of what looks professional. Right? You can manage your cell styles. If I've now done this and I realize, whoa, this is looking a bit messy, if I select this, I can just have a look, and I've set cell styles again, conditional formatting. Okay? You can manage this by going in, selecting the area you want to look at, and then you can just have a look at what rules apply.

So if you go to Manage rules, Manage rules, there we go, we can see, oh, there's two sets of rules are being applied to the current selection. Okay? You can also look at the rules in the whole worksheet.

Okay? And if I then decide, okay, wait a minute, those arrows are a bit much, I want to get rid of them, you just select it and delete that rule, and then it's gone. Okay, so those are the preset formats that you can use.

Now, this doesn't do everything we need it to do.

So if I move to the next sheet, conditional formatting with formulas, what I want to do here is I want to highlight the name of the salesperson where they've sold more than 70 units and the sales amount is more than 200.

Okay? So that is going to be something that Excel doesn't just have built in.

So I can use a formula to do that. So I'm going to start by selecting what I want to apply the conditional formatting to. So it's the names. Then what I do is I come here and I say, "Okay, let's go to conditional formatting." I want to make a new rule, and I'm going to go right down to the bottom, use a formula to determine which cells to format. Okay, so use a formula.

Now, in this formula bar, I put the formula that I want. So I say equals, and I'm being a bit, I want to say fancy here, but we're saying we want two things to be true.

So I'm saying equals and open brackets.

If the sales units are more than 70, comma, and the sales amount is more than 200.

Okay, so that's my formula.

Then what do I want to format this as? I want to format this, I click on the format button, and then I want to fill it with blue.

Now when I click okay, this is going to be wrong.

Okay.

Because it's clearly wrong, it's highlighted every person's name, and that's because of how Excel puts the dollar signs.

Now, this is really tricky, and it's something that, you can learn and do, but this is where AI can be really helpful. So I'm not going to do it now, but guys, I've got Copilot built in here. If you click on Copilot and ask it to apply conditional formatting to these cells to do this, highlight the name where units is more than 70 and sales amount is more than 200, highlight it blue, it can do it for you. I've tested it out.

But you obviously have to know that this exists and how it works so that you can then ask Excel to do it, and you can then play around with it if you want to change it.

So let's go in and fix it. If I just select what I've done and I go to conditional formatting, and I go to manage rules, there's the rule that I want to manage.

If I click on that rule, I want to edit the rule. Okay? And here, the problem is with these dollar signs.

Because I want... Oops. So let's go back in there. I don't want the dollar signs there.

I want Excel to be able to move up and down.

So I'm going to-- Sorry, and I keep clicking into the wrong thing. There we go. I just want to delete those dollar signs.

So it's just going to be G5, and it's going to be H5, deleting those dollar signs. Now, if you click okay, and okay again, now we can see that it works. Right? It's only highlighting those names.

And if I just test it, what if I change the first one sales amount to 10? There we go. It's not meeting those conditions anymore, so it's not highlighted.

Okay, so that is conditional formatting using a formula.

What you could also do, I'm not going to do that now, but you could, in a scenario, we'll talk about scenarios in a moment, but you could highlight in a model, if there's three scenarios, base case, base case, worst case, you could highlight the case that is active in the model. So if base is being selected, I want to highlight base, okay? And that is in your solution file.

If you click on there, you will see the formula or practice it with AI.

Ask it to do that. Highlight base and the number if that is being selected in cell D17.

What I want to end the session with is linking with the scenario picker, is I want to end off with data validation. Okay? If you have any questions, guys, please type away. I'll just take a breath before we do our last little bit.

So what is data validation? It makes sure users can only enter certain values into a cell.

The benefits, it avoids people putting erroneous inputs that shouldn't be there, and it also, you can give instructions to users, and then you can do various things with it, right? You can put error messages, you can use a custom list, and I'm going to show you what that looks like. So onto the last tab, we here have got a scenario picker.

Now, the purpose of this webinar is not to explain these choose offset index, et cetera. I'll show you where you can find information on that if you're interested. But what I want to show you here is in a model, we've got three scenarios, and if you change this yellow cell, so let's change this to worst case.

What should happen is those numbers in rows 12 to 14 should be picking up the worst case, which is the threes, right? Now, what I want to do with this yellow cell is I want to give people a dropdown menu so they can just pick from these three scenarios. Because the risk is that someone types something else in here, so let's say management case, but there is no management case, or there's a typo or something, and look what happens to my model N/A, and that's definitely not something we want to see.

So I can use data validation to do this.

Data validation is on the data ribbon.

So it's Alt A for data, and then V for data validation. So I'll just get rid of these letters because it can be quite hard to see. It's that one there, data validation with a dropdown menu, and then if you press V again, you'll get that data validation box that we need.

Now, if you click into that box, first thing it says, "Okay, what do you want to allow in this box?" We don't want any value. We're going to go pick from a list. So if I click on List, under Source, I then click and say, "Okay, well, what is the list that you can choose from?" And I go select those three cells.

Those are the three options you can choose from.

We could stop here, but I can give people an input message.

So if you click on the next tab, you can show this input message. So title here can be Scenario Picker, and in terms of instructions, I don't think anyone needs instructions, but you could say, "Choose one of the following scenarios," or whatever you want.

This is useful if, for example, you've got a switch in a model, and you want to just make clear what that switch does.

So you could say a refinancing switch, zero means don't refinance, one means do refinance.

Okay, so that's useful to the user.

So here we could put choose one of the following. Okay? And then on the final tab, Error Alert.

What do you want to happen if someone puts something in other than one of those three options? You can just stop and not let them carry on, you could give a warning, okay, or you could just give information.

So if I just want people to pick from the list, I'm going to say stop, and then again, you can give it a title, Scenario Picker.

And I think again, it's pretty clear, but if it's something a bit more complicated, you could write an explanation, but you can say, "Only choose from available options," or something like that. If we click OK, you can see we now have, if you click on the cell, that nice little arrow.

When you hover over it, we see the name of it and the instruction.

Click on the drop-down. I can pick best case, I can pick base, I can pick worst, and then if someone tries to type something else in there, like management case or makes a spelling error, can you see? They stop, and it says, "Only choose from the available options." So they have to retry or cancel. Guys, and that is your data validation.

Again, I played around with AI to see could it do this in Copilot if you give it the instruction to do what we did. So use data validation to pick from the list, so you've got to be quite specific.

It can do it. Okay. When I tested it in Copilot, it did it.

But guys, the point is, if you don't know about data validation, you're not going to know what to ask Copilot to do.

So hopefully now you know what data validation is.

You can also come in, and you can then edit it.

So if I just go back, if I'm on the cell where I've added that data validation, I can just click back into it, the data validation, and I can edit it. So if my AI has created this thing and I don't like it, I don't maybe like the message it's given. Instead of prompting it again to fix it, I could just come in here myself and just change whatever I want.

So guys, that is it for our agenda today.

We have been through cell styles, how to set them up, how to change them, the benefits of them, conditional formatting, standard formats in Excel, as well as using a formula, and then data validation.

If you want to have a look at other Excel content in Felix, you'll find that under Microsoft Office, Excel.

And then specifically in our scenario picker, we've got the match, the offset index, all of that business. Here, if you click on Scenarios, that's where you will find those functions and how they work if you're interested there, but lots of other Excel content as well.

So that is it from my side. If you have any questions, please do ask. I am going to stop the recording now, but I'm here for questions. I hope you found that useful.

Enjoy the rest of your day and your weekend, and do join next week.

We have something in our market series that you'll hopefully find interesting.

So thanks a lot, and let me know if there's any questions.