welcome to the one video that will take you from excel charts newbie to excel charts pro
and for this we'll be going over five charts and ranking them in terms of difficulty
from one star all the way to five stars so let's get into it first up in level one we've got a
donut chart and this can make sense to track something like a customer satisfaction score
out of a hundred so let's look into the excel file which you can download in the description below
so over here you can see that we already have two of these donut charts made one for europe
which as you can see they're dynamic so if the satisfaction score drops to 50 percent
then that's going to change accordingly over here press ctrl z to go back let's do the same thing
same thing with age over here let's say that their satisfaction score is 75 percent so if
that's the case the unsatisfied is the remainder so we'll go 100 percent equals 100 minus the
satisfied proportion that's gonna be that so from here to go ahead and do the donut chart
we're just gonna select it first ctrl shift down ctrl shift right we're going to go under insert
from here you're going to go under the pie chart that you can see and then just
below that towards the bottom you're going to find the donut chart that we're interested in
let's go ahead and put it over here to the side let me resize it a bit and so first things we
got to do here is we're going to have to make it a bit thicker so we'll go ahead and select it go to
right click we're going to go form a data series and over here the doughnut hole size is what we
want to decrease so we'll just make it more or less kind of like that then for the colors this
orange part is going to be the remainder for us so let's say we put it in a light lightish color
so go under format under shape fill we're going to put this light light orange here and then for this
other area go ahead and select it we're gonna put the darker orange color like this one over here
we can delete the legend so click on it and press the lead there and then for this text
box that actually updates automatically it's a bit more complex so let's go about that firstly
let me close this area we'll select the chart and then from there you wanna go under insert
here we're gonna insert a text box so to do that just go under text text box and we're just gonna
put it more or less right in the center kind of like so nice now this we want it to be dynamic
so we want to want it to link essentially for this we're just going to have to go under the data bar
so once we have this this area here selected we're going to go to the formula bar sorry
go to the equal sign from here we're gonna select the satisfied area that's gonna autofill for you
there and hit enter great now we just need to format this so we'll put it in the center
both top and from the sides as well we'll put in the center there say ctrl b to bolden it and
let's just make it a bit bigger there so we'll select something like just keep pressing the a
a bit so it looks a bit better so that seems about right then for the color you simply have to change
it from here let's say we go for this dark orange over here and that's the basic idea with the donut
chart obviously now it's all gonna be dynamic so if i change this to a 60 then that's gonna change
accordingly all right moving on to level two and over here we're gonna have a comparison between
our actual expenses and our budgetary expenses for our company so let's take a look at that over here
as you can see you have all of the different line items so the employees the office space etc etc
we can start to see what the difference is going to be like here so let's go ahead and select the
whole data set you can just press ctrl a for that then we're gonna go under insert again
and in this case we're just gonna go to the column chart drop down here
we're gonna select this very first one which is the most standard type great now from here what
we want to do is essentially have the actuals go inside of the budget area so we can see how far
in we are within the budget region now to do so we'll just select that orange side but make sure
you select all of them from the right click and here we're gonna go to format data point
so here we want it to be the secondary axis go ahead and click on that and now you can see that
one is basically inside of the other we just gotta make it a bit skinnier so we can actually see it
so on the gap width here let's say we go for something like a 400 hit enter there now you
can clearly start to see where it's looking what it's looking like so for this one because these
are expenses let's just put them in red so we're gonna put them go under format here and the shape
fill we're just gonna select a red color like this one over here and then for the background
side let's go with the shape outline of that same red so that's gonna be the the border area
and for the shape fill we're gonna go with a lighter lighter reddish color like this one over
here now you can clearly see that for marketing this is sort of gone out of out of line over here
and over here for the office space it's looking a lot better one final note here is that the
proportions don't seem to be exactly the same on both sides so that's something we need to correct
so for this area over here let's go ahead and correct that go under these columns here then
we'll go to access options for the maximum value we want that to be at 600 so it's
it's accurate to compare with the other one so we'll go at 600 now they're in gaps of 100 as
we want them here so it makes a lot more sense then we can go ahead and delete that one side
just by clicking the lid there and there you go and thank you to morningbrew for sponsoring
this video instead of aimlessly scrolling through social media you can start your day with knowledge
from morningbro a free daily newsletter to get you up to speed on business finance and tech
in just 5 minutes while reading traditional news can sometimes feel a bit dry and dense
morning brew is witty relevant and informative for example i recently learned about the labor
shortages that are causing airline chaos and how amazon is shifting its strategies by slowing down
its expansion under its new ceo andy jassy who's reducing the number of warehouses and brick and
mortar stores so if you're interested in learning about all of this and more go to the link in the
description below it's completely free and it only takes 15 seconds to sign up alright back to the
video next up in level 3 we've got the waterfall chart and this makes the most sense when you have
a starting value something that happened along the way and an ending value so for example it could be
with employee headcount if we look at this example over here you can see that we've got the 2021
total employee headcount then we hired some people we demoted some we laid some off and eventually we
reached a 2022 figure and so if you want to see the whole progress a waterfall chart makes a lot
of sense let's go about that by pressing ctrl shift down ctrl shift right we'll go to insert
from here we're just going to go to this one over here double click on that and then we're gonna go
under waterfall which is the first one so this is currently what we've got but it doesn't quite make
sense in the the waterfall doesn't quite fall it just keeps going up for some reason so this last
figure is the one that is probably off so we'll click on it just double click it to isolate it
right click we're gonna set it as total and so now that's gonna make a lot more sense for us
and over here in orange we've got the motions let's change this to something like a red color
so we're just going to isolate the decrease sign there just by clicking on it again for the format
we don't want an outline so no border basically no outline and for the shape fill let's go with a
red color and so for the increase we should have we should have them in this blue color now for
this total one for the two totals let's say we put them in a darker blue so they seem a bit separate
let's go with this one and this total figure as well so we'll just click inside the legend for it
click on the shape fill and we'll change it to a dark blue so as you can see here even though
we hired a good number of people 50 in this case it seems like quite a few left the company which
probably isn't too good a sign you can get rid of the gridlines as well just by clicking on it on
them and pressing delete coming in at level four we've got the forecast chart and this obviously
makes the most sense when you want to forecast the trend into the future so looking at the excel file
that we've got down over here under the forecast tab you can see that we've got um netflix which
is the thicker the thicker symbol is this one over here so netflix shares with their date and
their closing price let's say that this is the share price that we invested in about a year ago
and this is how it's been progressing for us and want to determine whether it's worth
continuing to own the share or whether we should sell it if you go to control down arrow you'll see
that we own this share pretty much up till today and so we've got the share prices for every monday
over here now to forecast it into the future we're just gonna go under the data tab then you're gonna
go to forecast sheet which is this tab over here click on that now you can start to see the pop-up
of excel's forecast of what it's gonna do based on the previous data so this whole year's worth
of data from the past and so here you can see when the forecast is ending in this case it's
around september and this is this is the estimate of what it's going to do you can go under options
to see more of the different variables that you can tweak from here so for example in this case
you can change the confidence level and other things like that that are related to statistics
this case we'll just hit create for now so as you can see excel created a new sheet for us and what
this is saying these curved lines are saying is that within 95 percent of the time the share price
should fall within this range so it's saying that it's probably not going to be any higher
than around 300 which isn't too great a sign if you want to keep investing in it now you can see
that excel also made this table this is basically how we calculated all the data as you can see over
here these are the calculations that it's made um going into the future you can look into them
if you're interested on a serious note though i don't recommend using excel to try and forecast
whether you should buy or sell a share this is just an example so don't use it in real life
lastly in level five we've got a column chart with percentage changes and i'll be very impressed if
you already knew how to do this one so let's go to control page down all the way to the bottom here
and this is the data set that we'll be working with so over here we've got the monthly breakdown
of the revenue and how that compares versus the next month if you click inside the next month it's
basically the following month here so you can see the comparison and i've gone ahead and made some
things like the variance variance percentage press the f2 key to look inside that which is basically
the percentage change from one to the other the variance which is just one minus the other and
over here i've got a table where it basically says hey if the figure is positive then you've
got to put it on this side and if it's negative you're gonna put it on this other side as you can
see i put the positive changes as a negative figure based on this figure right over here
the condition here just says that if the variance over here is greater than if it's greater than
zero then i want it to put a negative sign in front now the reason i'm doing this you'll be
able to see after in the chart that's an overview of the data set so let's get into actual charting
we'll go ahead and select this this top area over here we just select these first three columns then
we'll go under insert and for now we're just going to go with a simple 2d clustered column
like this one over here let me just drag that down a bit more so we can see it we'll just put
it down over here we're going to remove the legend so go ahead and click on it and hit the lead there
we don't really need the title for now just because i'm going to show you how to make the
the chart more generally and so for this this orange oranges area which is basically the next
month figures we want to make this transparent so we got we're going to go under format
then for the shape fill we're gonna go to no fill same thing with the outline we want no outline
great now we're gonna go to chart design and here's where we're gonna start putting
the percentage changes basically so go to add chart element go to error bars then more error
bar options i know this is a bit daunting here and towards the bottom you're going to reach a
custom area click on that then under specify value the positive error value is going to be this whole
range that we set up earlier that i set up earlier it's going to be this area here then the negative
one is going to be this other area over here click that again and hit ok there now you can start to
see that you've got this these lines which are going to give you the range of that difference
then we'll click inside that that transparent um data bar so just click inside it then go to right
click and actually we already have it up here go to format data series if you don't see it it's
going to be this button over here now for this the series gap we're just going to put at zero
zero percent and same thing goes for the gap width we're going to put it at zero percent
and now we want to add a few labels which are going to be the percentage change for us so right
click go to add data labels now for now you can see that we just get the standard numbers which is
not quite what we want so we'll click and click on them then go to this column area go to that then
we're going to go value from cells which allows us to select specific values within the range so
go to value from cells and the select data label range is what's gonna pop up for us in our case
we're interested in the percentage changes which are these figures here hit okay there and we don't
want the the values anymore because that's kind of too much info we'll deselect that values area and
now that's looking more like it so let's go ahead and change these by pressing ctrl b to bolden them
first and let's also go to home and for the text here let's say we go for a red color let me go
inside there and select actually let's put a green for the positives and then for the negatives which
is once this one over here and the other one's this one which you can see they're not very well
very well placed we'll work on that in a bit we'll just put them as red first and then i'm just gonna
put them to the inside end that's gonna be on the bottom here same thing with this one
we're just gonna make it red and then put it under the inside end let me scroll down so you can we
can see it a bit better there let's stretch the graph out a bit like so and let's change the color
colors of these columns to something like a darker blue so go to format shape fill let's go
with the dark blue let's remove the grid lines as they're a bit distracting and now that's looking
a lot more like it and that's how you go about creating a column chart with percentage changes
let me know down in the comments below out of the five charts how many you knew
check out this video over here for excel formulas or this other link over here to learn more about
our courses hit that like and the subscribe button if you liked it and i'll catch you in the next one