Speaker 1: What's up everyone it's Kenji here and in this video we're going to create these visuals in excel that I've had to do either working in business or in finance as an intern or full-time employee at some of these companies over here and if you want to follow along you can download the data set down in the description below so let's get into it. So the first chart we'll look at is an area chart and this type of chart makes the most sense when you have both a general trend that you want to show as well as a breakdown. So let's look at the excel file that we have over here we've got Apple's revenue by product, so we have the breakdown which is essentially by each product as well as the overall trend which is going to be the total revenue per year right. So let's go ahead and select everything you can press the control shift down arrow, control shift right arrow, from there go to the insert tab, go under recommended charts and we're actually going to go to this fourth one in case you don't see it in recommended charts, you can always just go to all charts and from there you're going to find it under the area, second tab, go to the stacked area chart, press OK. Now you'll notice that the colors here don't look too great. So let's go ahead and change them. For that just select on a specific wave over here, right click on it and go under fill. Let's say we go for a darkest blue. Generally, I like to go from darker to lighter for instance. So let me go ahead and do that. I'm just gonna speed up this process so it doesn't take so much time on the video. All right, now we've got the chart looking the way that we like it. Let's go ahead and change all of the chart formatting. So firstly, we'll right-click on it from there You're gonna go to format chart area and we don't really want an outside border for the chart So we'll go under border and go to no line We'll also delete this title here because we're gonna do something a bit different and let's also delete the grid lines You can see here that they're a bit annoying relative to the chart. So for that just go to alt W V G That's gonna get rid of them. So let's type out the title here We're just gonna copy from here, Apple Revenue by Product, Ctrl C and then Ctrl V over here And we're gonna select this area by pressing the Shift right arrow And then we're gonna go to Alt H, MC Which is going to merge and center for us and then we're also gonna fill it in dark blue So go to Alt H, H and from there with the arrow keys go all the way to dark blue and Alt H, FC And from there we'll go to white color. So that's looking a bit better Now you can see that this is essentially going to be the title for the chart over here This way it looks a lot cleaner. Let's move the chart up a bit so it fits inside it Okay from there the legend we can actually bring it to the top so for that select the legend if you're not seeing this pop-up on the side just go to right-click and format legend and Let's put it to the top and while we're already here. We might as well reformat this table a bit So firstly, we're gonna do control space and then control plus What we want to do is essentially show a trend line over here of how the the revenues have evolved over time So type the trend here and what we're gonna do is select this area here So shift and then right arrow go to the insert tab and from there We're gonna go for the spark lines, which is essentially a trend line So go to the line and then the location range is where we want the output to be So in our case, we want it to be right here Press ok And now you can see that we've got a trend line of what's happened during these these five years specifically for the iPhone ctrl-c Shift down arrow ctrl V and that's going to give it to you for the whole All the different products go back to the sparkline tab over here Sparkline color we're gonna go with a dark blue like we have over here and click back on that and the weight This is essentially how thick it is. So we want to make it a bit thicker say to the 1.5 here Let's also add some markers. The markers are essentially the actual points, right? So go to marker color markers and then let's go for the dark and there you go that's essentially giving you a trend line which is somewhat similar to this the only difference is that this gives you the general trend of the revenue while this over here is giving you the specific trend of each product right. Then let's also format the table a bit more so firstly we're just gonna copy a title because we have the same title so ctrl C and then ctrl V over here then for all of these here we can actually put a bottom border so for that alt H B and then O and we're gonna put a right border here so alt H B for right so R. Then we can italicize these so shift control and right arrow to select them control I and let's also do control B which is going to bolden them. Same thing on this side control shift down arrow and then control B. Now that's looking a lot better it's looking pretty professional in my eyes so that's all for this side. And if you like what you're seeing we do have an Excel for business and finance course where we teach you everything we know about Excel specifically for people either looking to break into a business or a finance role or those just looking to level up their excel skills. Unlike most theoretical courses which might use apples and bananas as examples, in this one we try to make it as practical as possible, based on our real experiences working at companies like Tesla and Amazon. So aside from the typical lessons on formatting, formulas and charts, we do have case studies that replicate the type of work you might be assigned in your day to day ranging from financial modelling, to cleaning a dataset and presenting the visual insights. So if you're interested in checking it out, you can go to a link in the description below. Let's now look at a football field which is most common for representing a range of numbers. So for example in finance when you're valuing a company, you're not really sure what the share price is going to be in the future and so you want to give it a range which is a better estimate as opposed to one specific number which you might get wrong. So let's say we go to sheet 4 over here and we're given these ranges. So we've got the different types of valuation methods over here, we've got the 52 week trading range, which is essentially the highs and lows during this past year of that share price. The equity research report, this is what analysts say in Wall Street expect the share price to be. We've got the discounted cash flow, which is one of the main valuation methods. Comparables and percent transactions, which are three of the biggest valuation methods. I've made a video on this on YouTube as well, which you can check out somewhere up here. And here we've got the minimum, which is the minimum share price we expect and the maximum. So firstly, we're going to go control space and then control plus. And here what we want to calculate is the difference between the two. So we'll go to equals, the maximum minus the minimum, press enter, ctrl C, drag it down, shift down arrow, ctrl V. So now we've got the difference here, we'll just type it, difference. And now let's select everything that we have here, so ctrl shift down arrow, ctrl shift right arrow, and we're gonna insert. Then from there you can either go to recommended charts, and it's going to be the second one which is the clustered bar chart. ok and now you'll see that this doesn't really look like the football field that we're going for so it's going to take a bit of formatting to do. Firstly we're going to change the coloring here so we'll select this side over here so the left hand side of the columns the blue ones and from there if you're not seeing this on the on the right you can go to right click format data series and under the bucket here we're going to go to the fill color and we don't want the fill color so we're going to go for no fill there we go on the gray ones we'll select them and we're gonna do the same thing so no fill and the difference here is essentially what we're looking for this is going to be the range from the start to the end figure now it doesn't look too great because this this range on the bottom is just too big so we'll click on it right click and from there you can go to format axis and now we see that the minimum is at zero and the maximum is at 80 that's why it looks so spread out so instead we're gonna go to a minimum of say of 20 press the Tab key, Tab key again and a maximum of 40. And now we can see the estimates a lot better here. Let's also delete this legend which doesn't really say anything to us, so delete that. Select these figures over here, let's change the color to a dark blue like we have already. So for the fill here we're gonna go for the dark blue color and let's also add some labels so we know what the minimum and the maximum is here. So for that we'll firstly select on the left hand side, right click and go to add data labels. You'll notice that they are quite spread out so they're quite a bit to the left which we don't really want so we'll select them and from there you're going to go over here to this column one, go to the label options and under that we're going to go to the inside end which is going to put them essentially closest to the to the side that we want and we'll do the same on the other side so select this, go to right click, add data labels and from there we're going to want to format them so right click again and go to format data labels in our case in this case we want it on the inside base I think let's say select them by pressing elsewhere one final thing that we can do here is essentially put a dashed line from the top which is quite common for something like representing the current share price so what's the share price today or what's the average out of all of these different valuation methods so for that this is not very glamorous you just go to the insert tab and from there you go to shapes and we're actually just going to select a line here so while pressing the shift key so it stays straight we're gonna drag it down to something that looks good for us say over here for the color let's go for the same let's go for a yellow so it stands out a bit let's also increase the the width which is essentially the thickness of the line so we can see it better so we're actually going to increase it to say 1.75 or 1.5 actually and the dash type is essentially do we want it to be a continuous line or we want it to be dashed so let's put this fourth one here which looks a bit better and from there you know if the share price was over here today then you'd obviously change it accordingly. So that's the basic idea with the football field so let's delete this title for example and like I said with the previous one we're not gonna go ahead and show you how to do this title because we've already done that. And the last one we'll look at is a gauge chart and this is a fairly advanced Excel feature where you're essentially going to have a speedometer so it's gonna go say from 0 to 100 and you're probably wondering outside of say the cars where would you see something like this? It's common in something like a customer satisfaction score where you might have bad on one side, average and good customer satisfaction scores. So let's go ahead and do that on Excel. So go down to the sheet 5 here and we've got customer satisfaction. Here we've got the different ranges so the first 25% would be bad score from there the average would be at 50 and then for the other 25 so the 75 side it would be good and then have the maximum and the minimums. Next to that you have the point, the point is essentially what's the current score right, so the current score is a 48% which is not too good. Next to that we're actually going to have to type a 1, that 1 is going to represent the size of the actual ticker, so of the thing that's going to be moving. And then lastly here we're going to have to do a formula which is equals, then we're going to go 200% minus the 48 minus the 1, press enter. In case you're wondering why I put the 200 here, the reason for it is to equate to exactly this over here. So if you look at the sum of this by going down over here that equates to 200 percent so we want it to be at 200 as well so that the the ticker is actually accurate relative to the actual range. So firstly once you have all of this data selected here you're going to go to the insert tab, from there we're going to go to the pie and we're actually going to go for a donut. Once we have that selected you're going to see that this this is the area that we that we find relevant which is these three over here, so we're gonna have to rotate it such that they're on top. So select it, go to the three columns that you see here, and the angle of the first slice is what we want to change to 270, press enter. The donut hole size we're gonna change to something like 55, so this is essentially the the size of the donut going down a bit, so press enter. Now you can see that it is a bit thicker there. From there we're gonna want to remove all of these borders so for that go to the bucket here and we're gonna go to no border then we're also gonna select this just this blue one so make sure you double click it to select it and from there we're gonna go to no fill we're also gonna delete the legend here press the delete key okay so now that's looking slightly better and now we want to make it like essentially a traffic light where the colors go from red yellow to to green so firstly let's click on this one here from there you're gonna go to color under fill and let's go for something like this red here then for the middle one we're gonna go for a yellow color and then for the very last one we're gonna go for a green color nice so this is starting to look a bit more like it let's go ahead and put this a bit to the side and now we're gonna want to select the these areas over here which are gonna be the three points so for that we'll go to select data under select data we're gonna want to add a new series that's going to be the ticker for us so we'll go to add the series name we'll just call it a point and the series values which is the important stuff here we're actually gonna drag these three over here and then press ok press ok again now you'll notice that this is looking quite messy but we have to go ahead and change that first so we'll select it from there we're gonna go to right click and then we'll go to change series chart type and we don't want the second one to be a donut instead we just want it to be a regular pie chart, press OK and firstly we're gonna go for no border and then we're gonna select just this gray one here, make sure it's only the gray one that's selected, go to no fill, same thing with the blue one, make sure it's only the blue selected, go to no fill and now we're starting to see a ticker here but you'll notice that even though we have currently at 48% it's all the way over here which is essentially at 100. Now the reason it's it's currently like that is because we haven't changed the the angle of it so for the previous one we change it to 270 degrees and we need to do the same with this one so we'll go to right click from their format data point and here we're gonna want the angle of the first slice at 270, press enter so now the 48 looks like it is actually a 48. One final thing that you can do here is click on it it is a bit delicate to click it but once you get it we're gonna change the the color say to something like a dark color like a black and now that's looking a bit more like it so currently say if we change the point to say 55 that moves dynamically if we change it to 80 again you can see it moving which is looking quite good here you'll see now that the one here represents essentially the thickness of that ticker so if we put a 5% here you'll notice that this gets a lot thicker now let's leave it back to one let's now also add a label to it so know what's going on so we're gonna have to select just this this area over here i know it's a bit tricky to select but once you have it right click go to add data label and you'll notice that right now we have this one which isn't really what we want right at the end of the day we want the the actual point so the customer satisfaction score and not necessarily the one so what we're gonna do here is click inside the one then we'll go over here to the equals and go to equals and And we're going to select this point here, so the 80% right now, press Enter. And now when we move this, say we move this to a 50, that's going to move accordingly again to a 20. And now that moves dynamically. So that's more or less what we're looking for. And here's a few other things that we can do to the speedometer to make it look a bit nicer. So firstly, let's select the label and make it a bit bigger, say to size 11, for instance, Ctrl V to bolden it. Let's delete the chart title as we don't need one here. and here we can also add a bit of an instruction sheet so the people know what to do with this model so ctrl space, ctrl plus, ctrl plus, ctrl plus again and we can type something like insert score and we'll put some arrows here so say two dashes and then this sign here. Select this area so shift right arrow and then go to alt H MC which is emergent center shortcut, alt H BA which is all of the outside borders and then for the insert score we're actually gonna align it say to the right so for that go to Alt H A R and now what we want to do even if say we type 50 here it's obviously not gonna change because it's not dynamic at the moment so firstly we'll delete it and then we're gonna go to down over here on the point and we'll go to equals and link it up here press enter. So now if we put say 20% here, now that's dynamic, same thing goes if we put a 50% here. Now one thing to keep in mind is that you'll notice you have this this small gray thing here which is essentially like a leading line for the legend which we don't really want. So we'll select here on the label, go right click, format data labels and you see the show leader lines that's what we want to remove. So click on that once. Now that's looking a lot better and we have a fully dynamic speedometer. And to be honest if you feel like this type of chart is a bit tedious to do, I agree with you that is the case, it's just another excel skill to have in mind. If you're interested in learning financial formulas on excel check out this video over here, if you're interested in learning more about valuation check out this other video over here. That's all for this one, hit that like, hit that subscribe if you liked it and I'll catch you in the next one.