Is a short webinar and this webinar is on formatting Excel.

Alright, I'm gonna download this empty file.

Um, there's no slides as such for this webinar.

We're just gonna do everything in this Excel file and we're gonna go quite quickly, so lemme know if I'm going to fast.

Okay, so we've got an AI in here.

This is becoming, uh, quite common. Excellent.

Okay, so this is something I need to learn about.

Um, I'll be teaching AI quite a lot over the summer, I imagine, and this kind of note taker agents.

Um, productivity is something that a lot of our clients are interested in.

So I'm always interested. This one's called fireflies ai.

Okay? Right.

So we're gonna have a look at three things together.

Um, the first one is cell styles, okay? The second one is conditional formatting and the third one is data validation.

Okay? They'll take roughly 10 minutes each.

And if you wanted more detail, just let me know, uh, or we can talk at the end if we overrun a little bit.

Okay? Now let's get started then with styles.

Um, first thing about styles is, well what are they? Well, you can see on the info page here you can see three typical styles that tend to get used in models.

We've got input, which could be forward assumptions.

We've got hard code, which is usually historical, so gonna set in stone won't change.

And then you've got formulas which are calculated by Excel, usually by a combination of the above. Two, there are other formats which are common enough to warrant styles and if you're curious about that, you could hit alt HJ.

Take a look at the styles here.

Now the styles in this spreadsheet are actually a little bit messy, so we'll talk about that a bit later.

Um, I'll just jot that down so you've got it.

So alt HJ I'll just use your mouse, that's fine too.

And when you go in, you can then toggle around using your keyboard and it'll give you a nice little preview.

You can see that alt HJ moving around.

Um, some of them are kind of sensible.

And then some of the ones down here tend to get quite wacky.

These are the built in Excel styles and I don't feel like many people use them very often.

Okay? You can see these custom ones here are ones that Financial Edge have built, okay, they're our styles.

And I think ideally we would've cleared out these styles here by right clicking on them and saying delete or by using a plugin to bulk edit them.

Um, one of the frustrating things about Styles is that when you start a new spreadsheet, Okay, you realize that the changes you've made to styles elsewhere are not being made to your Excel, but being made to the spreadsheet.

So were I to send you that spreadsheet or you download it the way you have, you would get those styles, okay in here.

But were you to start a new spreadsheet, you would end up back with Excel's own style suite, which needs clearing out really.

Okay? So first things first or first learning point styles are baked into Excel at a worksheet level, not at a program level.

That means that you can send this stuff and it still works.

All right, let's apply some styles. Uh, let's go over here.

Okay, so let's say if I zoom in a bit.

And then, so this is like a table.

So ALT hj, give it a nice heading like that, okay, highlight this column.

ALT hj. That's percent all right over here.

Date Alt hj.

I mean it looks pretty good already, but let's see what happens when we change it to date.

Uh, you can see, uh oh, it sort of, there we go. That's a bit better. That way our American friends or if you are American are UK friends or vice versa won't get confused by months.

Okay? So usually pretty good idea.

Okay, now multiple, we want that to say like 10 x, but actually I don't think this hack has a multiple.

Okay? Now this is one of the benefits of styles, okay? First benefit is that we're exploring.

Now you can bake in, oops, banking text without Excel seeing it as text.

All right? Now if I want that one to look like a multiple, I could just put an X next to it manually.

But actually I realize, oh look, Excel wanted to work on that as a number.

Oh no. So me putting that X in has converted it into text.

Now what I could do with styles, if I look at this workout, which is, you know, just something I was working on before lunch, so I hit Alt HJ here, can you see I've got a style that says multiple, that turns that into a multiple with an X next to it.

But if I pointed it, XL is not seeing the X as such.

Okay? So you can bake text like stuff into Excel without actually putting text there, which is very useful.

For example, you could say if this is years, you could have it say one year, but it'd still be a one from the point of view of Excel.

That's very, very helpful.

Okay, while we're going, we might as well do this one so hard code.

Okay? Now the second benefit, uh, let's say over here I hard code some of these manually.

All right? So let's say I turn it into a kind of dark green and over here I do that again.

And then over here I do it with styles.

And then over here I press F four to do it again with styles.

And first thing you notice is that unless I'm very disciplined, I can actually mix up my blues and it looks a bit amateurish.

Okay? So styles give a unified style template that's difficult to deviate away from because it's just so clear, you know, all hj, there's hard coded, okay, I don't need to go hunting around here for which blue exactly matches all the other blues.

Which can be quite frustrating, especially if you're using custom blues, more colors, RGB codes, things like that.

Alright, so in terms of benefits, the second benefit is it helps standardize start throughout without having to select eg exact colors.

Okay? And while I'm here, the third benefit is bulk editing.

Is it easier and more consistent? All right, now let's say I'm taking my spreadsheet and I'm gonna move over to a new client and let's say a alt hj.

Now this new client, let's say hard code, I'm gonna modify it by right clicking on it.

I'm gonna hit the format there, which does it, how the style is working.

You can see there's a little programming language here. That's how I would might control things like that multiple that I mentioned.

What I'm interested here is, is just a color of the font.

So let's say I wanna turn it into this quite nice green here.

Now when I hit okay, just watch what happens. Okay? So everything that I put as hard code here has just moved, but here it's inconsistent.

And that's because I used the hard code style here and here, but I used crude formatting here and here.

So best practice would be to avoid crude formatting at all in models and spreadsheets.

Okay? Really all formatting should be done through styles to create much more standardization and consistent movements.

Okay? If the font's not right, you can change the entire font without having to hunt around for every instance of that font throughout, which can be very time consuming and error prone.

Alright? Alright, that's the first of our three areas.

I'm gonna pause now and see if anybody has any questions on styles.

Anything I haven't covered or maybe you've seen in the past or you've wondered about? Yeah, like duplicate style.

Um, there are plugins that do this, uh, like a bit easier.

Um, Julio, I don't think everybody else can see your question.

It doesn't matter. Um, he's asked how can you copy and paste the style? Say I wanted to work on that one, I could duplicate it and then call it, you know, hard code number, you know, uh, version two or something and then it's in there.

That's probably an unfortunate name 'cause it's long enough that it looks identical, but that one's hardcoded.

And then I could, uh, work on that if I wanted to change it slightly.

Like I say, there are plugins that allow you to do that. It'd be easier and allow you to clear out the, uh, unfortunate styles a bit easier as well.

Um, something some of you may have noticed is that when you pick up like a spreadsheet that's passed around quite a few, uh, people, you might find that you have this like style proliferation where there's all these junky styles where somebody's just added one without really thinking it's very common, so it's not best practice.

Okay, that's a good question. Anything else there? Did you mean that or did you mean take a style copy it and then special paste it as format onto another? It's possible that I've misinterpreted your question there.

Both the answers helped. That's useful. Useful. Okay, great.

All right, so if you're still typing a question, keep typing.

I'm gonna move on to conditional formatting, but I'm happy to backtrack if there are questions.

I'm just conscious of time. Now, conditional formatting's really powerful and I think in the last about 10, 20 years or so, it's really um, become a lot easier.

If we look at conditional formatting.

Say we wanted to highlight really good sales amounts for a bonus or something.

Um, in the old days you had to do everything through these managed rules and you had to like type formula and stuff and it was really awkward.

Um, recently they've really created a very strong and easy to use toolkit in the form of these kind of toggles.

So say you just wanted a a, a color chart like that, it would highlight the best and worst sales amounts automatically, which can be very powerful for drawing the eye in a dashboard.

You can do quite a lot of fancy stuff, but I, I think this stuff does get a bit overwhelming and can look a little cheap.

Okay? If you overdo it, okay, if you've got this everywhere.

So I'd use that sparingly.

Um, let's say we wanted to put something a bit more mathematically in where we have greater control.

These tests are also very powerful.

They build the formula for you.

So say we wanted to reward people with a really good sales, say over a hundred.

Okay? So you know, Halligan is getting a bonus for the Midwest but not for the south.

Okay? Ashley's gonna get a bonus for the south and the Midwest, you know, that kind of thing.

I'm making it up here. Now that can be really powerful.

But one limitation of that is that if you wanted to highlight like a record or a card, it's quite weak at that.

Um, that kind of base interaction that I've just shown you, if you say highlight cells above a hundred, it will take that quite seriously and it will make some quite big errors like misunderstanding text, okay? Or bring in information that you really didn't want.

So my intention there was to highlight everybody who has had a good year, but I wanna highlight the record like left to right, like the whole row or maybe column depending on how I structure it.

Let's do that over here.

Conditional formatting with formulas.

This is really fiddly and I think for ages I had to look up how to do this every time I did it.

So don't be shy of looking it up, okay? What you do is you highlight your whole data set, you get a new rule.

Now this contains all the logic that was in those dropdowns with more control.

So if you went wanted to do anything that was outside of those dropdowns, this is the place to come look in that we're interested in is a formula.

Now it's expecting a test.

The kind of thing you might type into an if this can get quite fussy.

So you may have to experiment with it.

Now if I say if units, excuse me, sales amount is greater than a hundred, that's probably really small for you. But notice it's slapped $2 signs there and that's gonna be important in a minute.

If I then tell it to fill with this nice green, my intention is for say, Hagan's record to get highlighted, but Hagan's second record not to get highlighted.

If they hit okay, you can see that's gone wrong.

The reason that's gone wrong is the double double signs.

Okay, let's talk it through again.

Do conditional formatting, manage rules, then I'm gonna double click on that one just to edit it.

What's happening is when excel is moving up and down, okay to a new cell, say this cell here, okay, when it's looking at this cell and seeing whether it should be green, it's only looking at H 55 locked and seeing if that's above a hundred.

And that's true of every cell that I've just highlighted.

So of course for every cell that I've highlighted, H 55 is above a hundred.

And what I need to do is start again and say right, you initially need to base it on H 55, it does need to be above a hundred.

Who, who is so fussy This okay, but now I'm sitting inside there and I'm gonna hit F four.

Now I've locked it to row, which I don't want because as I move down I want it to look at sales down and as I move left, I don't want it to move left to units.

The only thing I want it to assess is sales.

I'm gonna hit F four again and the dollar sign appears next to the H.

This is what I want now because as Excel looks down the list, it's always gonna look down the sales amount regardless of where I am left or right.

Say I'm here on cc, it will have looked down to CC sale amount because I freed the vertical axis of its assessment.

But when I say move left to transaction, 'cause I've locked the H, it's stuck in the sales amount, which is what I want.

If I hit okay twice, you can see that's working.

Now it's actually quite powerful.

So if I wanted to uh, improve on the power of it, what I could do is say the sales target up here, give it a hundred, edit the rule and instead of hard coding the hundred I could have the hundred be dynamic, okay, it appears to do nothing initially.

Let's say next year I change it to 50.

Okay, 200, 300.

Now nobody's going 300 now nobody's getting a bonus.

Two 50 just halligan.

So this can be really useful for drawing your eye to quite um, unified date cards.

It can also be really helpful for scenario pickers just to improve on the usability.

So we could do the exact same thing here and we could say, alright, let's highlight the whole lot.

Add a rule, use the formula which we use now that's like blue and what we're gonna do is we're gonna say if best locked to column, it's equal to base, then highlight blue.

And you can see that's true of this row here.

And when I change my mind that moves and it really draws the eye to where my scenario picker is.

I think this is quite strong, quite powerful.

It can make your presentations look really professional and smart without overdoing it and it's not really possible to do it with the default suite.

A very easy to use sort of conditional formatting that everybody's used to.

Okay, so that was topic two or three.

We're going to pause now and see if everybody's got any questions or wants to see anything again or has any experiences of their own, they wanna track in any use cases or anything like that here. I can't see any questions as per usual if you have them just keep typing and I'll come back but I'll move on now to data validation, which is actually quite straightforward.

Okay, it's the probably the easiest of the bunch.

Now here we've actually got something quite complicated.

What we've got is a match function.

What it does is it goes looking for best case from this array and then returns to positioning as a coordinator.

So if I type in worst case you would expect that to be a three because it's the third coordinator.

We've then got that being fed into a classic lookup via a choose.

So choose either this one, this one, this one based on what's in C five or an offset, which is move from an area before the date set and move the number of jumps that we've programmed in a cell somewhere or an index.

An index probably my favorite, okay, out of the index move vertically within the index to a coordinate which I've put somewhere.

Alright, so these are classic lookups.

Um, I think if you've done this before, you may have done it all at once.

So you might do something like index show at the index and rather than having the vertical coordinate as you know, some cell somewhere, you might just make the match in and say match that against these.

Exactly. You don't need a horizontal coordinate on the index.

So we leave that blank and it does exactly the same thing.

Now I can delete this and it still works. Okay? So it's just say I do best case, everything's flowing nicely.

This is how you might build a scenario picker, usually using index offset choose or perhaps X lookup.

Now if anybody's curious about X lookup, I can show you that's not really the focus of this.

It's supposed to be data validation, but we've got a bit of time so if anybody wants to talk about X lookup we can.

Or if anybody wants to talk through the pickers that you can see in front of you, that's fine too.

Otherwise I'll start moving on to actually data validation, which is what this page is supposed to be about.

Okay, I can't see any questions about the lookups again? Oh yeah, there we go. You going in there? So RA's asking for x lookup, um, X lookup is what all the cool kids are doing.

Uh, let's see if I can do it.

I always forget how X lookup works 'cause it's relatively new. So I tend to always hit this FX button.

She gives you a little tutorial, so it's looking for the lookup value.

Well that's gonna be best case from a lookup array.

Well that's like a match baked into the x lookup.

The return array needs to be a corresponding shape to the lookup array.

So it's sort of saying whatever coordinate is given by the match, you'll then go find from the return array.

If we can't find it, maybe we'll say not found. That's quite useful extra feature.

Um, the other lookups here can't do that.

And then we generally put a zero here for an exact match, especially with text.

That can be quite important.

Okay, make that 2% okay and then let's see if it's working.

Yeah, that's great. Okay, um, the thing about x lookup, you've just gotta take care on legacy, um, Excel.

And what I mean by that is some clients still use Excel from say nine seven, okay? They might have various reasons for that. Maybe security dunno.

Um, next lookup will not work, work on those machines.

Okay? So there's a very small chance that you might send your spreadsheet to your client and they say everything fell over what's happening? I think that is fading, but we still give advice saying, look, be careful of x look up.

Okay, sa Priya, is that working for you? Any other angles you're after Interesting that the format hasn't quite come out. There you are welcome. All right. Now validation's actually quite easy if we go up here.

Um, the point of this is that it's just absurdly easy to break this model.

Okay, I could type anything I want here and users have a kind of pesky habit of destroying parts of models that they can type stuff into.

Especially bits where you've got inputs like this, the scenario picker, they may get confused.

So I think what's easier to do here is do validation.

I've actually forgotten where validation is because I tend to find it now by doing alt queue, which brings up the search and then just start, start typing, started using that more and more.

And as I use it more and more, I'm sort of forgetting some of the more niche ribbon commands that I used to have a great command over.

If we go to validation, you can see it's quite complex.

We're just gonna keep it simple.

What we're gonna say is we will be defining the allowable contents of this cell, not because it's an integer or a decimal or a date or a time or maybe a range or something like that.

What we're gonna do is we're gonna give it a list that it can work from and that list is already here.

Whoops.

Okay? The disadvantage of this is that if our users go put things in that list or change the spelling, it's all gonna fall over.

But, uh, let's keep it like this for the moment.

Now that does two things.

The first thing it does is if I start typing nonsense, it just flat refuses to, doesn't matter how many times I press, you know, um, it, it would just go back to what I used to have.

It also gives me a dropdown, which is actually really nice from a usability point of view.

So this looks like more of a kind of program now than Excel looks quite ly like I've made something in VBO and so it can be helpful for making presentations or spreadsheets look more professional.

All right, good. So that was a whistle stop tour through three areas being validation, conditional formatting with formulas and styles.

If you wanted to go further with that, you can find that and more under professional skills.

Oh, they moved it, Microsoft Office, Excel.

And what you could find is a lot of the things we've done in say, advanced formatting with some workouts, you could go a bit further and start building a scenario manager using pickers.

You could learn about those index match, things like that using database analysis and data extraction.

And so you can start to spread your professional skills.

Okay? And all of this is very helpful.

Now I think I've got a question in the q and a, so I'm just gonna check Oh yeah.

Uh, so somebody's asked how do I get the cell next to the formula to show the formula? Yeah, so if I grab my data validation here, um, what you do is you say equals formula text and then you point it at that cell.

Um, the reason I was doing it so quick was because I've got a, uh, a macro setup for teaching purposes to do that.

So I've got a, a like a combination of keys that I put, but if you wanted to do it, you could say formula text like that.

All right, we've got a little bit of time now, um, at the end for questions.

Uh, we're onto our kind of announced finish time, so if you wanted to log off, feel free to and thank you very much for your, uh, work and attention.

Nice to meet you all and I hope you have a nice weekend.

If you wanna stay and ask questions, feel free to do that as well.

Okay, so that's the end of the formal session.

I'll stop recording. Okay? And if anybody has any questions, feel free to stick around. Otherwise, have a nice weekend.