So welcome everybody.

My name is Alistair Matchett and um, I was a banker myself at JP Morgan and then worked in private equity at three I and we have got half an hour together to go through some advanced formatting in Excel.

I did have a few slides, but frankly I've given you the slides and they didn't really add very much.

I think it's easier if we just jump into Excel.

Also, the session's being recorded and we will post the recording after the session.

So I think we should just get straight into it.

If you have questions as we go through, there is a q and a box which we will keep our eyes on if you have any questions as we go through.

So the first thing I'm going to do is just share my screen so you can see my excel there.

And let me just make sure I've got the q and a box open, um, and see the participants.

That's great. Okay, so let's get started.

Now the first thing we're going to do is we're gonna take a look at styles because styles are a very efficient way of formatting in Excel.

If I go to a blank Excel spreadsheet and I just type some numbers in, so let's say I type 0.001 or a thousand or minus 100, you can see that there's no consistency to the formatting.

Different numbers at different decal places.

Negative, negative numbers are not in parentheses, there's no comma in the thousand separator.

So what we're going to do is we're going to change the formatting for every spell, every cell in this workbook.

And once you've done this, you can save it as a template.

So you can always pick it up, but it means that about, literally about I'd say probably 50% of your formatting is done, you don't need to worry about formatting the numbers.

So there are four in the, as you are gonna see in a moment, but I think I'm just gonna write it out first.

That are four um, codes in Excel formatting.

Okay? There's the positive number, there's the negative number, there's the number which is zero and there is text.

Okay? So these are four different types of formatting and I'll open show you the code in just a moment.

The positive number, we're going to use some convention here in the coding before I get into it.

The hash is a wild card, okay? And the reason you need the wild card is that the comma is going to be a thousand separator, okay? And then we are going to have a 0.0 that will be the um, that will be um, the, the number where the format rather where the number is less than um, 0.0 something, okay? In other words, it's less than that decimal place.

Um, then what we've got is an underscore right hand parenthesis and the underscore tells Excel to give me a space next to um, oh the space the size of the next character.

Okay? Now I'm doing this because when I did it in the system, you'll then understand it.

So the positive number code we're going to use is gonna be hash comma hash hash 0.0.

What that means is that positive numbers will be one decimal place and they'll have a thousand separator.

But what we also need to do is underscore right hand parentis because we want to have a space the size of a right hand parent parenthesis between the positive number and the cell wall.

A negative number is gonna be almost identical.

It's gonna BEA 0.0 but it's just going to be in parentheses.

Then the zero is just going to be the um, 0.0, okay, 0.0, there we go.

And we're also going to have to put an underscore righthand parentheses there because it needs again to line up. So that's if the number is just completely zero.

And then if it's text, we are going to put a placeholder for text, which is the at sign and then again underscore righthand parenthesis to make sure that the texts or text lines up with the numbers as well.

So if you put NA for not available, the NA will line up with a number.

So those are the three coding.

We are actually gonna combine these together into one long format code.

And the format code I'm gonna use is gonna be hash comma 0.0 semicolon, 'cause that divides the type formatting type to the next to the number.

So hash comma hash 0.0, right parent the next one which is zero, which is 0.0.

And then finally the Text. So that's the formatting code that we are going to use and I'm just gonna copy that.

And now what I'm going to do is I'm going to change every single cell format in this file.

So this is extreme formatting power going on.

I'm gonna do that by going to the home menu alt H and then J for cell styles.

Now I've got lots of garbage in this spreadsheet and you can build a macro through chat GBT to take out styles, but I'm gonna go down to click that and frankly that's the easiest thing to do is right click it.

'cause once you've done this, you shouldn't need to do this again then choose modify.

What you then get is a little dialogue box that's asking you what do you want to actually adjust in this style.

Now when you're doing this type of style, these type of style, particularly in the normal style, you want to make sure that you're not too specific because this means otherwise it will overwrite any other formatting like borders, fills, everything like that.

So make sure you only select number and then click on the format button or Alt O to open that up.

And it comes up to what looks like a format sell box.

But it's much more powerful because this is gonna change every single cell in this workbook.

So I'm gonna go down to the category called custom, which is a little, this little secret um, formatting type.

And you see where it says type at the top there.

I'm just gonna paste in that formatting code that I've just explained to you.

So the positive number code first, semicolon, negative number, code second semicolon, zero number code third semicolon text code last and Excel is kind of just, that's kind of what we are formatting.

So then I'm just going to hit enter and then I'm gonna go down and click on, okay and just watch my screen when I hit.

Okay, so look at the numbers in rows 3, 4, 5, 6 when I hit, okay, come boom, did you see that everything got beautifully formatted and I can go to any cell in this workbook and type a thousand got a common separator minus 100 in parentheses 0.001, just one decimal place.

So the formatting is completely consistent.

Now if you create a template like this, you can press F 12 and if where it says save as type, you actually go down and choose an Excel template.

Can you see that Excel template? You'll notice what happens is that the location of the file changes.

Now you mustn't change that and you can save it.

So if I just call this, I'm gonna call this test 'cause I do want to delete this afterwards.

I've saved this as a template.

Then if I do file new alt fn, what will happen? Can you see I've got those um, styles at the very top there and my, the one I've just created hasn't come up.

I guess that's 'cause I've just created it. Let me just take a look at personal something there.

Um, test. There we go, it's there.

So that means I can always get a carbon copy and if I choose that, what, let me just show, show you if I just choose that or it may not allow me to open two files with the same name.

I think that's why that's getting upset.

But you can actually open a complete carbon copy of this file.

So that's the first thing I wanted to show you.

If you reformat the normal style, it means every single cell in the workbook will change.

Not every single workbook you have open, but just the workbook you are working on. And if you send so it's template, you can um, rebuild it.

The other thing that you can, well let me, let me just um, go onto my next thing before I do the come to mama maneuver.

So if I get to the next sheet, I'm going to zoom in a bit 'cause there's a few other styles I want to go through.

There's a percent style and the percent style.

I'm actually gonna do this on the screen because um, I just want to show you the coding 'cause it's actually easier for you to see on the screen.

So the coding I'm gonna use for the percent style is going to be 0.0% right hand parenthesis, that's the positive number, semicolon 0.0%.

So that is the percent style.

I don't need to worry about the text or the zero the date style.

What I'm going to do is you can really go to town with this.

But if I just type in a date, let me show, show you. If I type in a date, 31, 12 23 and hit enter, be prepared to be shocked no date.

My gosh, what's happened? Well I've changed the number, the normal style in this workbook.

So this means I won't get by default to kind of guesstimate date what happens.

I get an index number and that's the number of days between January 1st, 1900 and December 31st, 2023.

So probably 5,291 days.

So I need to apply a date format to that.

The date format I'm gonna do, if you do D dd, that's two digits for the day.

So that will be a number between one and 31.

Then dash mm MM will give me the month in three letters.

If I do four, it will gimme the whole word.

I'm just gonna do three dash YY that will gimme me two digits for years.

I can do four digits for years as well, but I'm actually going to adjust this 'cause what I'm going to use is rather than the date, just the month and year, most people like that.

Then for the multiple, what I'm gonna do here is I'm gonna use a very similar format to the one I created here.

Except I'm not going to worry about the text and the zero cells.

What I'm going to do, you can, if you want just put X next to the zeros and that will work.

But a lot of people when they look at how that, you'll see when I do this how it looks on the screen.

People don't really like that.

And so instead they want a space between the number and the X.

If you want a space, you've gotta put it in quotes.

So let me show you. So to have a space, what you need to do is you need to put open quotes, space X close quotes and then the same SpaceX quotes.

So let me just go and do this now.

So I'm gonna do the percent style first, so alt hj, but this time what I'm going to do is down at the bottom I'm gonna do alt N for new cell style and I'm gonna call this P for percent and then I'm going to deselect everything 'cause you want to have your styles as flexible as possible and then go to format.

And then in the type box I'm gonna type that 0.0%, right? Right hand hit enter, don't hit enter twice, go down and click on the, okay.

And now what I can do is I can apply that style.

Now I have the styles on my little shortcut toolbar right at the top.

Can you see here this little shortcut? That's my cell styles there.

Um, so if I do alt, um, so just alt, sorry.

And then type number five, can you see I've got this, uh, the styles and if I just go down, um, I'm gonna find my P and then I can apply that style.

Now the benefit of using styles is that if you use them in one place, you'll be able to change them and they will change in every single area that you have used that style in the whole of the workbook.

So they're very powerful if you want to kind of keep these styles working.

Now if I go to the date, I'm gonna reformat these dates.

So I'll go alt hj and I'll go and create a new style and I'll call this D for date.

Deselect everything format, go to the custom category and then do under type the MMM dash yy hit enter, then go and click on.

Okay, and then I'm gonna apply the style.

I'm just gonna do alt hj.

And if you go to the D style, can you see that? That gives you a nice little date format.

Then let's do the multiple alt HJ new style.

I'm gonna call this M for multiple Deselect everything Go to format.

And then the type box.

I'm just gonna put open quotes, SpaceX, close quotes after the first zero.

And then open quotes, SpaceX, close quotes and you can see at the top it gives you a little example and I'll hit enter tab down to, okay, and then I'm going to do alt five.

And then, oh I've got the normals M there.

It's not a little bit tricky. Oh I have to go.

There we go M and you can see it's given me the X format.

Then I'm gonna do a hard code.

So I'm gonna do alt hj new cell style and this time I'm gonna call it B for blue.

And I'm going to deselect everything except the font color here.

So I'm deselecting everything apart from the font color.

Then go to format and go to the font tab, not the number tab, the font tab and then go to color.

Now there's kind of really rubbish colors in Excel. So I'm gonna go down to the bottom and choose more colors.

And then I'm going to go to the custom color.

'cause then you can get really specific about the colors.

So if you have a client that's a very distinct color set, awesome for the RGB coating.

And then I know that for a really rich blue that's 2 5 5 in the coding, you see that really lovely blue, that's what most people like to use hard numbers.

Then I'll click on, okay and then tab down to, okay and now I've got a new cell style alt hj and I can go up and choose B and it gives me the blue style.

Now I know you can have macros here, but this also means you can undo, you can do control Z and it will undo that or um, control y redo.

So unlike macros where you can't undo, you can undo with styles.

And if you have created a style, so if I go and change the style, for example, let's say for some kind of crazy reason, I wanted to make that blue style a different color.

So I modify it and I choose let's say a different, let's say red.

Um, and then okay, can you see everywhere I've used that style, it changes.

So it's a very, very powerful thing styles, let me just undo that 'cause otherwise it'll be kind of irritating if you get this as a template.

Now it gets better because what you can do is you can do a come to mama maneuver.

So let's say you've got a file from a client and it's just got garbage formatting in.

So you can see that it's got no parentheses for negative numbers.

It's got no standardization of decimal places.

Um, it doesn't have thousand separator. What can you do? You can do the come to mama maneuver.

So if I do alt HJ and then I go down to the bottom where it says merge styles ALT MI can merge styles from the other file that I have got open, which is the test file.

And if I hit okay, what it will do is say look you've got a normal style in this sheet, in this sheet and a normal style in the other sheet.

Do you want to overwrite it? I do and just watch my screen.

Can you see all that change? And if I want to apply a format, I can do alt hj and you can see you've got all those other styles and the garbage styles from the other sheet. Okay, so you can see that's a really powerful feature.

Let me go back to my other sheet because now we're gonna move on to some conditional formatting.

So there's three things I'm just show you here first some really basic stuff which is inbuilt to Excel.

I've got a range of numbers here.

And often what you want to be able to do is you want to be able to kind of color code like the highest and the lowest number. This is really great for data visualization.

So I'm going to the home menu and then I'm gonna go to elf conditional formatting.

And I'm gonna start just by doing icon sets because there's one thing that you may recognize and that is these little ratings down here.

Can you see that? Oh my gosh, it's got that little thing you all see in the kind of bank presentations and I heard it here enter.

So this is kind of ranking them according to their size.

And then if I do sale amount amount, let me just show you another option here and I'm going to go to color scales and you can kind of do like a heat map from the lowest or to the highest.

Okay? So it's really a nice way of playing around to visualize data.

So in this case red is the lowest and then clear is the highest.

You can play around with that but that's not particularly sophisticated.

What is sophisticated is coming your way right now here, excuse me here what we have is we have got three different scenarios. We've got scenario one capital structures, this could be LBO scenario two and scenario three.

Now what I want to be able to do is build a little model here.

So let's say I put in my assumptions and the assumptions I'm gonna use in the model I'm gonna use in offset function.

And I'll use the starting point here and it's gonna ask me how many rows I want to go down, which is gonna be zero.

Then it's asking me how many columns I want to go across.

And that's going to be the scenario choice.

'cause it'll either be the first scenario, the second scenario or the third scenario and we'll go across each column then close parent and head enter and I'll just copy that down.

So you can see here we have got a little assumption set using these three options and a five type number two, number three, it will change.

Where does formatting coming in? Well formatting comes in because often what you want to be able to do is highlight the scenario set visually that the model is currently using.

You can build a little formula that says nex is the scenario choice.

It is scenario number three or you could name it.

But actually what I want to be able to do is kind of put some shading in the background so it shades the area that is being used by the model because that means when you change the assumptions, you know that that's a set of assumptions that you need to change.

So I'm gonna go to the home menu and then I'm going to conditional formatting.

But now what we're going to do is we're going to go to the rules and I'm gonna choose a new rule and um, the new rule I'm going to use is a formula rule.

Now the way this works is this is very like an if statement.

So the three parts, the if statement, the statement, what to do, if it's true, what to do if it's false.

Well this is kind of like a partial if statement 'cause it's saying gimme a statement and I will apply formatting if that statement's true.

So what I'm gonna do is, and you must have the selection of the area you want to format first.

So what I'm gonna do is that I'm not going to use n statement, I'm just gonna put the statement in.

So it's gonna be equals if the um, scenario that cell there.

But I don't want to fix both the row and column 'cause I want to be able to copy this, right? So I want to fix just the row, okay? So I want to just fix the row.

So I press F for that which means releases the column so I can copy the formatting, right? If that equals the scenario choice.

Now the scenario choice, I do want absolute reference.

So you, the way you wanna kind of read this is if C eight equals C four then apply the formatting.

So then I'm going to go down to the format you can choose border.

The problem with border is that you often find that actually the border is too skinny so it doesn't really give you a kind of juicy, um, level of border.

So instead I'm gonna choose a fill, let me choose a kind of like a pastel fill there and you can have some raw effects, you know, two colors, all sorts of things.

Um, and what I'm going to do is then choose, okay, so what we're saying is that if it's a current scenario then apply this background formatting and now I'm gonna click on, okay and you can see here it doesn't apply 'cause it's three.

If I choose two, does it apply? No if I choose one, oh can you see that? Look at that beautiful background.

Once you've done that, I'm gonna select these cells and do control C to copy and then shift right arrow and we're gonna paste the formats as space peal, just the format.

So alt e es is the old shortcut for pay special T performances.

Kaboom hit enter and then choose.

If I choose number two or number three, can you see it shifting? And this means you can always tell what section of the assumption block the model is using visually. And that's actually very helpful if you are changing things in a model.

So that's conditional formatting using that little formula code.

Very, very helpful.

Now the last little squeak that I'm going to get in for you is data validation.

So here what we have is a little model.

We've got a scenario pickup, best case and base case and worst case.

And you've got a little match function that's matching the word best case with those and giving you one, two or three.

And that's driving the scenario.

And you can see that three different ways of building that you can either use choose offset or index.

But what I'm really want to do here is show you how you can control what gets put into cells. Currently it's best case I can choose worst case here that works base case that works.

But let's say I make a typo.

Oh, you get a whole load of ref outs and that's really annoying if you send something to a client.

So what we're going to do is we're going to validate the cell.

So if I go to the, excuse me, the data menu and when I'm on the data menu type V for validation.

And then the second V for validation, what it's doing, it's saying okay, if I go to settings the first tab, it's currently allowing any value in the cell, but I want to prevent that you can for example, just make sure it's only a whole number that's sometimes used of if you have like a scenario choice one, two, or three and the other sheets you could do a whole number between one and three.

And it would only allow you to type in one, two or three, nothing else.

In this case I want to choose a um, list.

Okay? So rather than the whole number A list and it's asking me what's the source of the list, well it's the name of my scenario, it's these three scenarios.

So this means it will only allow me to type in one of those three scenarios.

And then in the input message I'm going to put um, um, um, scenario choice, I should call it scenario.

And the input message is hold down, hold alt and down arrow to choose the scenario.

And then the last tab, the area alert, um, know, choose, uh, an existing scenario and then I'm gonna choose click on.

Okay? And now can you see that little drop down? Now it's not visually on the sheet when you click away from it, but when I go to it, it says snow hold, alt and down arrow.

So hold down alt, hit down arrow and I get the three choices and if I type in something that's wrong, I'll get an error message, choose an existing scenario.

So this is a really great way of you controlling what people are putting into your scenario assumption cells.

Now it is half past, I'm really sorry but we've ran out Of um, but please don't hesitate to give me a shout if um, you have any questions.

We are running the sessions every Friday, three times next Friday we have got, um, data extraction from Excel or database functions in Excel.

Um, you will be able to see if I come into Felix quickly, you will be able to see the recordings.

If I go down and open this o up, you can see right down to the bottom you've got the recordings of the prior sessions.

We will build, build them as into a playlist over time.

But we've got database analysis in Excel next Friday.

Hope to see you there. I hope you have a great weekend.

Thank you for joining.