Hello everybody and uh, welcome to your short webinar on Excel formatting.

My name is Ollie, I'm uh, one of the tutors here at FE and I'm pleased to meet you.

And uh, this is a short half hour webinar on three topics.

Now we're gonna not gonna look, you look at the case, we're going to look at the advanced formatting workout.

So if we could open that one up, that'd be great.

I'm not gonna use any notes as such.

We are just gonna use the workout.

Like I say, it's a fairly swift webinar and so the best idea is just to get on with it, okay? And start using the skills.

Now you can ask me whatever you like and there might be a bit of time at the end to ask different questions.

Okay? We're gonna get started and what we're gonna do is we're gonna look at styles, we're gonna look at conditional formatting.

Probably the trickiest bit of the webinar is with formulas.

And then we're gonna look at validation.

And while we look at validation, we'll have a great look at index match as well.

Okay? So let's get started with styles.

I think the way I will explain styles is with this cover sheet here, you can see that we have the three major styles that we tend to see in models being assumptions, historical, kind of our work.

Now, if I was to want to change some things about this uh, cover sheet, you could say that the company name, I suppose you could argue it's an assumption.

So let's turn that into an assumption.

And what I did was I opened up styles.

Okay, so these are called styles and I used alt HJ to do that.

Okay? So if I did it again, let's say I decide that this is historical for some reason doesn't quite work, but I'm just gonna use it.

Oh, hj hardcoded, there we go.

And if I wanted to, I could bulk do that by pressing F four and repeating, okay? Now these styles are a way of bulk editing.

Broadly speaking in terms of formatting, there are two choices.

One is styles, which I've just shown you and that's recommended.

And I'll talk about why it's recommended in just a second.

Second way we could do it is by doing what I might call crude formatting.

Now, if I wanted to turn this into hard code, I could, if I wanted to just go to the font, easiest way is with my mouse.

I could do it with a keyboard, but I wanna show you with my mouse and I could show you, right? I want to turn that into a blue.

I want it to be roughly the same blue as that hard code.

That's actually quite hard to find the exact same blue as a hard code, that one all do.

Okay? And then if I wanted to turn that one into it as well, I could hit F four, F four, F four like that.

So what I've just done is I've used two methods to achieve the same thing.

The first method is styles, alt hj.

Those styles needed to be set up by somebody, and I'll talk about that in a minute.

Okay? And then the second way of governing formatting is crew formatting, which is just using the format or font.

Now let's say I'll go to a new client and I want to change all of the hard code for some reason, okay? Into another color.

What I can do is I can go alt hj and that one did not work for some reason.

So lemme do that again. And then I could right click on the style, modify it, go into the format, and then change what I want about it.

So for example, this style, what it does is it governs the, uh, the font by changing it into blue.

If I wanted, I could make that a nice purple.

One of my former employees, everything was purple so they would've wanted purple.

Now if I hit, okay, so I'm taking my spreadsheet to my former employer, okay? Now notice it's quite hard to see actually that all of the uh, wands here have gone purple.

Now that's so hard to see, I'm just gonna change it again and I don't think I'd ever make it this shrieky, but I'll just change it to a bright red.

Okay? Now notice everything that I've used styles on has flowed.

And what I mean is it's easy to bulk edit.

If I wanted to edit the crude formatting that I've done earlier, I would've to go through every single area where I've done it crudely and change the format.

And that's easy with the data set that I'm working with now.

But imagine a model that's thousands of lines long and with multiple tabs on the whole, whenever we're doing formatting, we recommend styles.

Styles are tricky as you've gotta get used to how they work up here.

If you set up a new spreadsheet, you might have already noticed this when you played around with this yourselves, you'll notice that a lot of the useful styles that I was using are not their hard code Assumptions and the rest we would need to set those up new or we would need to merge them from another spreadsheet.

Or more likely what we'd need to do is take another spreadsheet like this one, check out that it's got the styles that we want, maybe clear out some of these styles that we don't want.

And there are plugins that help you do that quicker if you're interested.

And then use this spreadsheet for our own means.

Maybe clearing out and starting again with it or maybe using the template that our company gives us in terms of day-to-day use of styles.

Let's say we were here, what we'd do is we'd use it pretty much as formatting.

So here we might do a heading.

This column wants to be a percent that's down This column wants to be a date and we've got our date set up to be American friendly, okay? This column wants to be a hard code, okay? And all of these, look, my hard code's still red.

Um, all of these are now set up in a user-friendly way that would flow and be easy to bulk. Get it, everybody can understand what's happening.

Alright, that's our first of three topics in our whistle Stop tour through formatting.

Now's a good chance to ask you questions about styles.

Feel free to ask anything you like. We're ready.

We're gonna move on to conditional formatting.

Our n neighbor is having some dawning work done.

That's loud. Let me know.

Just wander to have the window open.

Okay, I don't see any questions.

If you are typing a question, then please just keep going.

Okay? And I'll interrupt what I'm doing.

We're gonna move on to conditional format for next.

Now, conditional formatting has really improved, is that bothering me? Okay, so conditional formatting has really improved over the last 10 years, let's say over here in units.

And I usually just do this with my mouse.

Let's say we wanted it to show good to bad and let's say we wanted the same with sales.

Okay, good to bad. Then you can see they're kind of in alignment.

So fair enough, they're telling one story if they're out of alignment with each other, okay, it would reveal other information.

Like this has sold a lot of units but they weren't selling for very much money.

So it really draws your eye into things in a very, very helpful way.

We can also go quite wacky with it, okay? You can do data bars, even do things like icon sets.

I think your spreadsheets can get very busy with this stuff and I, I wouldn't necessarily recommend all that fancy stuff unless your company has a kind of an approach which is inclusive of that.

I think the modern approach is quite, um, professional, quite sparse.

Um, so I'd probably cut that stuff out unless you feel it adds a lot.

Um, what's quite helpful there we go, is we can create tests with conditional formatting.

And so we can use conditional formatting a little bit like a lookup.

So let's say we've got units, and I'll put that up Peter, sale amount.

We could say we're interested in units above 50 and we're interested in sale amount.

That's say above 200.

What we can do now is we can highlight and we can apply rules and these rules really intuitive.

So we could say we want this stuff to highlight if it's greater than 50, okay? And then we'd probably want it green 'cause that's a good thing.

And then we want this stuff to highlight if it's above, and again, this is gonna be dynamic, so we point it to that and we say green.

Now you can see that on the whole they're in alignment with each other, but there's one outlier by just make another outlier. So this is a low order and say this is a high order with, you know, a very low sales price, something like that.

Um, you can see the conditional formatting means good, but it, it can't that easily do hold table attention grabbing.

Um, operations. It also can't easily highlight areas outside the test.

So if I wanted to highlight a whole row for example, that's quite hard to achieve.

Using those simple, the simple tests, what they do when you click on them is they work on wherever you've highlighted.

Okay? So if I were to extend it to there, none of these would fulfill the test because none of them have a numerical value.

These would probably all pass the test because they're actually behind the scenes numbers in the 10 thousands.

Okay? That highlights another problem, which is if I try and do kind of bulk conditional formatting, so color scale here, you can see that what it's doing is interpreting all these numbers as low and all these numbers as high.

So it's not smart enough to say I want tests on either of these columns.

So a little bit of care needs to be taken.

Now if you wanted to do more sophisticated highlighting tests, old school, nah again, ah, I did it again.

Here we go. Old school formula based tests can be very powerful.

Now if I say sales again units 50 and sales 200, what I could do is I could highlight the whole table and then I can do conditional formatting and I can make a new rule and I can click this area here which says formula.

Now this is the way that they used to all work and I still get a bit worried about doing this even now years after I learned how to do it because it's quite fiddly.

Now what this is gonna do is it's gonna cast this eye into any of these cells and then apply a test.

So if I tell it, and so this is a two-way test, both things need to be true.

Now units needs to be greater than 50 and sales map needs to be greater than 200.

Now if I then give it a nice friendly green back, back, uh, background, what I found is that the whole table highlights because I've programmed it wrong.

What I did there is I said, look, regardless of where you're on the table, say I'm here, okay, look at that cell there and compare it to that and look at that cell there and compare it to that.

And it stands to reason then that anywhere in the table that would be true because I've locked the test cell.

So what I need to do is really carefully go back and manage the rule and look at it again.

And this is probably quite small for you, so I'm hoping you can just about read it from your screen.

Can you see here, I've got a G eight but I've got it locked.

That means that wherever the assessment is taking place, it will always just look at G eight. It doesn't matter how low I go or how much to the right or to the left.

So what I need to do is hit F four and then F four again.

And if you are very eagle eyed, you can see that the dollar sign is next to the G but not the eight.

And that means as I'm moving up and down with my assessment, so is the cell that's being looked at if I'm moving left and right with my assessment? It's not, and then I'm gonna do that again over here.

And what that's gonna do is it's gonna say right as I move up and down, move the cell that you're assessing up and down and as I move left and right, don't do that.

Now if I've done my job properly and I have, you can see that what it does is it really nicely highlights the areas where both tests are being passed.

So if I turn this into a low sales amount, the test is failed.

If I turn this into a high units, the test is still failed, but if I give it a high sales amount, the test is passed and regardless of where I am left or right, it will always pass or fail the test based on these two, okay? Wherever they are in terms of height.

And this is why formula based is so fiddly because you've gotta really conceptualize how the test is working and how it'll be passed or failed, okay? That's formula based, conditional formatting, uh, just pausing that, see if there's any questions, anything you wanna discuss.

I think my neighbor's done with uh, angle grinder. So I'm gonna, again, if you are typing out a question and you're still typing and get, keep going, I'll interrupt otherwise, uh, well I'm gonna move on, but I'll interrupt what I'm doing in case you've got a question.

The last thing we're gonna do is data validation.

Now what's going on here is we have a scenario picker, we've got this as an input, but I could say worst case this is actually a match.

What it's doing is it's saying, right, go look at what this set of stuff, see if it matches against what's in red, make the match an exact one.

And what match does is it, it it returns a number depending on where you are in the lookup.

So worst case is third in the lookup base case is second in the lookup.

Now what's happening then is we're gonna use that and I'm gonna get rid of choose an offset because I just think they're confusing at this point and they're being fed into an index.

Now the way the index works is it says, alright, here's a list of possible things, our index, then here's the location C five in that list that I want you to return.

So the second coordinator, so in a way it's like the reverse of the match.

Now that returns a six and that's because that says two, if I turn that into a three, it moves things around.

But I don't wanna have to manually type that in because I want to type in worst case for example.

Now in case you're wondering, it's actually quite uncommon to see the match split out this way.

It's much more common to see the index having a match embedded inside it.

So you would say match this set of characters against these possible and do so Exactly please.

And what we've just created is an index match. Okay? It's an index with a match embedded in it, which is a classic lookup tool.

And what we've done is we've cut out the middleman and we've said now best case matches to number one, which then returns, excuse me, matches to number one out of this lock, which then returns the first in that index.

And you might be wondering what are we doing? I thought we were doing data validation and we've ended up doing index match.

Yeah, fair enough. We needed to learn index match to understand the scenario function.

We're gonna go on to data validation next, but you may be curious about index match or have heard about it or just want to me to repeat anything I've said it's quite complicated and so feel free to ask now.

Okay, again, if you're still typing, do continue. Okay.

And I'll answer your question as I see it in the chat box.

Um, just gonna check the q and a. I haven't got anything in it. No, good.

Some people prefer q and a for some reason right now.

Let's get on to data validation then.

Now this situation unfortunately is absurdly easy to break back.

Oh David, that's a good question.

Um, so David has asked, and I think it's invisible to most of you, um, what's an index or how does it differ with an index match? Match? I, I don't know what an index match match is.

So, uh, I I'm not sure.

I think you might be, oh wait, wait, wait. Now I've sort it through. Okay, uh, let me think, let do this in Okay, uh, One Three, Oops Oliver, Bob.

Okay, So let's say I've got a date set where I've got a number and then a name and I want number two from the Bob Row.

Okay? Um, what you are talking about is your classic index match and what you're gonna do is you're gonna feed it an index.

Now that's vertical and horizontal.

You're then gonna say the vertical will be defined by where we are in the vertical and then the horizontal is gonna be defined by where we are in the horizontal matched perfectly.

Okay? And that's a classic index match further then apply from earlier, say conditional formatting, highlight where we're equal to that, that you can see that if I move around now, okay, what I'm doing is I'm effectively changing the coordinates here to one and one or one and two or three and two and that's being picked up by the coordinate machine here.

It's the basic way an index works is it says or similar answers and then vertical coordinate horizontal cord.

That's basically how it works. Ah, yes.

Um, so David, hopefully that made sense to you.

Um, Desmond has just said, and again, I don't think many people can see it, but it says, um, I've come to admire X lookup.

Yeah, I, I like X lookup too, but I find it, um, I guess 'cause I learn in XM match is just natural to me and so I think young people probably include yourself compared to me, like X lookup.

Um, I don't love it. Okay.

And probably the biggest reason I don't love it is because Legacy Excel, uh, won't run it.

Um, I can't remember exactly when it was introduced something in the two thousands.

Um, and I think if you send it to a client who's still running Excel 97, which does sometimes happen, if you've got kind of really locked down machines, it can cause problems.

And so, uh, I'm, I'm not a big fan but I know it's becoming more and more and more popular and uh, Desmond's just said he agrees with me, so that feels good.

Okay. Or do or do you agree I'm old? Is that what you're agreeing with? Because that's fine too.

Okay. So a nice little aside there on um, data lookups just in case you're interested in that and you're thinking, gosh, I I need some help with that.

You can find those lookups in much more detail on the Microsoft office Excel and then I think you can find them under data extraction.

Yeah. Okay. So if you're interested in those, yeah, David's saying they've been instructed not to use X lookup 'cause of the compatibility issue.

So definitely I can hear some echoes what I'm saying.

I I wouldn't use it but I know so some companies love it.

Okay, let's finish up then by looking at data validation.

Now this thing is absurdly easy to destroy and that's because um, I can type anything I like in here and it won't match against these unless it's exact.

What I need to do to protect my worksheet is data validation.

I tend to use the search bar these days if I can't remember where something is and uh, you do alt queue validation and there it is.

And now probably the simplest thing to do is feed it a list.

You can either type out a list of acceptable answers or you can point it to a list that already exists.

Excel's gonna look at that list and say anything in this cell has to conform to the things I've been fed.

And you give you a nice little dropdown as well.

If you then try and type in anything, right, it'll either auto complete it like it did just now or if it can't auto complete it, it just tells me no and it will not do it okay? Until I put something like worst case base case or best case in.

So when you're doing lookups, um, which are based on lists or scenario planning, it's a really good idea to bake in some validation.

Alright, that's about all we've got time for.

Um, we'll stop there. I hope that's been useful.

Um, I'll stick around in case people have questions, but the advertis length of the uh, webinar is half an hour, so I'm not gonna keep going with any kind of full teaching now.

Okay, so if you're off, uh, have a lovely morning, afternoon, evening, wherever you are.

Um, if you're sticking around and asking questions, feel free to, um, I'll stick around for a while and have a nice weekend.

Thanks Desmond. Thanks for the comments. It's always nice. Have to chat with people.

Okay, it's going pretty quiet so I'm gonna close things off.

Have a nice evening, afternoon, morning, wherever you are.