AWESOME Excel Charts & Visuals | For Business & Finance Professionals

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

test attribution text

Add Comment