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 ctrl shift down arrow ctrl shift right arrow from there go
to the insert tab go on the recommended charts and we're actually gonna 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
gonna 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 going to 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 going to 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 going to 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 vg that's going to get rid of them
so let's type out the title here we're just going to copy from here apple revenue by product ctrl c
and then ctrl v over here and we're going to select this area by pressing the shift right
arrow and then we're going to go to alt h m c which is going to merge and center for us
and then we're also going to fill it in dark blue c so go to alt h and from there with the arrow
keys go all the way to the 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 going to do is select this area here so shift and then right arrow
go to the insert tab and from there we're going to 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 control c
shift down arrow ctrl v and that's going to give it to you for the whole um 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 on 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 going to copy the 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
voltage b and then o and we're gonna put a right border here so volt hp 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 bolt on them same thing on this side ctrl shift down arrow and then ctrl 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 data set 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 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 and 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 going to insert then from there you can either go to recommend the charts
and it's going to be the second one which is the clustered bar chart press 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 from a data series and under the bucket here
we're gonna go to the fill color and we don't want the fill color so we're gonna 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 going to go to a minimum of say of 20 press the top key top key again
and a maximum of 40. and now we can see the 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 there are 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 gonna go over here to this column one
go to the label options and under that we're gonna 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 the line here
so while pressing the shift key so it stays straight we're going to drag it down to something
that looks good for us say over here um for the color let's go for the say 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 gonna 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 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 going to 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 going to 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 by the one side average and good
customer satisfaction scores so let's go ahead and do that on excel so go down to the sheet five here
and we've got customer satisfaction here we've got the different ranges so the first 25 percent
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 you have the maximum and the minimums next 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 gonna have to type a one that one is going to represent the size of the
actual ticker so of the thing that's gonna be moving and then lastly here we're gonna have to
do a formula which is equals then we're gonna go 200 percent minus the 48 minus the one 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 pi 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 going to have to rotate
it such that they're on top so select it go to the three three columns that you see here
and the angle of the first slice is what we want to change to 270 press enter the doughnut hole
size we're going to 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 going to want to ch remove all of these borders so for that go to the bucket here
and we're going to go to no border then we're also going to select this just this blue one
so make sure you double click it to select it and from there we're going to go to no fill we're also
going to 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 uh
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 important stuff here we're actually gonna drag these three over here
and then press ok press ok again now you 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 going to 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 doughnut instead
we just want it to be a regular pie chart press ok and firstly we're going to go for no border and
then we're gonna select just this great 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 currently like that is
because we haven't changed the the angle of it so for the previous one we changed it to 270 degrees
and we need to do the same with this one so we'll go to right click from there 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 sheet to something like a
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 you put a five percent 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 we 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 we're gonna 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 make it a bit bigger say the size
11 for instance ctrl b to bolden it let's delete the chart title as we all need one here and here
we can also add a bit of an instruction sheet so that people know what to do with this model so
control space control plus control plus control plus again and we can type something like insert
score and we'll put some arrows here so let's say two dashes and then this sign here select
this area so shift right arrow and then go to alt hmc which is the merging center shortcut
alt hba which is all of the outside borders and then for the insert score
we're actually going to align it say to the right so for that go to alt h ar
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 going to 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 percent 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 that 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 the like hit that subscribe if you liked it and i'll catch you in the next one