welcome to the one video that will take you from Excel visuals newbie to season professional for
this we'll go from having raw data with no visuals to creating an awesome dashboard to impress anyone
in 5 Steps let's go alright so here's the scenario suppose we're hired as a consultancy to help out
this company and try improve their performance now for this for us as a junior analyst we simply have
to make a dashboard that's going to show some of the key metrics and visuals to assess how
the company has been performing if we look at the Excel file some of these metrics are going
to include the project roadmap so some kind of a timeline alongside the sales for this company some
of their satisfaction scores staff turnover Etc and you can download this Excel file that we'll be
working with for free in the video description as well so step one for us is going to be to create
a project timeline and one of the better visuals for that is going to be a gun chart so let's take
a look at the file we'll go up over here here and this is where we want to create the timeline and
right next to it next to it over here under raw data we can see all of the data that we'll need
so for this we'll have the the start date for the for the project alongside the end date for
each of this individual tasks so it might be the client onboarding Gathering the data developing a
solution and so on and the duration here is going to be how long each task is going to be for so to
create a visual for this first thing we're just going to select all of the start dates so go to
control down arrow from here what we're gonna do is go under insert and we'll go down over
here and we want a 2d we want to get the the Stacked bar chart which is the second one over
here so just click on that and right now we're missing all of the duration which is going to be
this part to the side now to add that we're just gonna click right click here go to select data
and from here we want to add a new series so let's go to add and the series name for us it's going to
be the duration days and the values here just click on that we're just going to select all of
these so Ctrl shift down all the way to the bottom there and just hit OK then for this horizontal
part these numbers we actually want them to be the names of the tasks so we'll go to edit again
Ctrl shift down hit okay there and OK again and just like that we have all of these to the side
now let's work on some of the formatting for this so firstly these these lines over here we
actually don't want we only want these orange ones which actually represent the days for us
so let's go ahead and right click on that under fill we're just gonna go with no fill there great
now we're just gonna have to invert the order of this over here as you can see client onboarding
should be the top thing as that's the first thing we would do so go to right click format access
and towards the bottom here you're gonna find the categories in reverse order so let's click on that
and just like so that's looking better we'll also remove the chart title and for these dates
what we're gonna do is go to right click go to format access here let's let's basically remove
this cup and this cup over here to the side as well so it's the minimum and maximum bounce that
we need to change but you can see that they're here in number format they're not in date format
which makes it a bit tricky for us so what we're going to do instead is just first we're gonna get
out of that so we're just gonna select the actual dates so we want the very first date and the very
last one so we're just gonna go equals and select our first start date and again equals and select
a very last date hit enter we're just gonna change these two so first we'll select them
we're gonna change them to number format so Ctrl 1 is a shortcut there we're gonna go under number
and we're okay with that hit okay there and so these are the numbers that we want
so now we can go up here again and for the minimum bound we know we now know that it's
gonna be four four eight oh five and for the maximum it's the four four eight five
five hit enter there and now you can see how that range has nicely changed for us
let's now go ahead and resize this chart over to the dashboard here so I'm just gonna drag it over
and you basically need to stretch it out so let me fast forward that now we can very clearly see
how long each of the tasks is going to take we're developing a solution is probably going
to take the longest one more thing we can do is add labels so that's going to show the actual
days so this is going to take 20 days five days Etc next up in step two we want to create a table
with a trend line so if we scroll down over here you can see the sales by country where we've got
all of the dates alongside each country and we want to create a trend line over here now to
do so we're gonna go to the insert Tab and from here under sparklines we'll go to click online
you can see that the date range pop-up shows up and we want it to be all of these here so we just
select these four and the location is fine where it says Trend so we'll hit OK there now we have
that Trend and we can see how it looks we can even go further and say change the color or actually
increase the weight so we can see it a bit better let's say I go for one and a half for example
then we're just gonna press the shift down arrow and hit Ctrl D that's gonna drag it down all
across these also within that table we have the growth rates which maybe we want to identify a
bit better using conditional formatting so that way we'll find out better when it's negative
maybe we have a red sign or something like that so go to Ctrl shift down to select all of them
then we're just gonna go under conditional formatting up here we're gonna go for Icon
sets and we really we have quite a big range here we go with the flags so just click on those
and now what doesn't really make sense is that we have a 20 growth rate which is quite a good
growth rate and yet it's still in yellow so let's go ahead and change the rules here a bit so under
conditional formatting again we'll go to manage rules and then we'll go to edit those rules
so let's take a look here and as you can see it's going by percentage and that's why it looks
a bit off there so we're gonna go by number instead where hey if it's greater than zero
then we should want it in green because that's a positive thing then we want it when it's zero
we want it to be in yellow so we're just going to change that to a number as well and finally
when it's less than zero it should be in red which makes sense to us so we'll go okay
and we're just gonna go okay again now that seems to make more sense if I artificially change this
to a zero then it should go to Yellow like so press Ctrl Z to go back there and if you're
liking this Excel video you can also consider it taking our Excel for business and finance course
where we teach everything we know about Excel and what makes this course different is that it's all
applied to the real world while we still cover the theoretical lessons like formatting formulas and
charts we also offer case studies that simulate the type of work you might be assigned in your
day-to-day ranging from Financial modeling to cleaning a data set and presenting some visual
insights and if you get stuck along the way you can easily ask us questions in the discussions
forum we also offer several other courses including PowerPoint Finance evaluation and
more so if you're interested in checking it out you can get 20 off up until the 31st of December
2022 so go to the link in the description below in step 3 we think it would be useful to find out
the satisfaction score for our employees and our customers to see if the company is doing okay now
for this it's probably best to measure it with a gauge chart so let's take a look so here's where
the satisfaction scores will be with the gauge charts and for this we have this data over here so
we have the customer satisfaction and the employee satisfaction so the first thing you'll find are
the ranges so this is basically what's going to measure what's good or bad and then next to that
we have the point which is basically the actual score and don't worry about these two we'll get
to them later so first things first we're gonna select these ranges so Ctrl shift down there
and then we're gonna go under insert go to this these pie charts here and towards the
bottom you'll find the donut chart which is the one that we want so click on that now I know this
doesn't look much like a gauge chart yet but just bear with me first we'll remove the title
let's also remove the legend below and from here we'll click on it then we'll go to right click
format data series and we're just going to rotate this so the angle of First Slice we want it to be
at 270. hit enter there and now you can see that this is looking a bit more like it where this is
the bad area the OK area and the good area then for for the donut hole size let's go ahead and
Shrink this so we'll put put it at 55 hit enter there and then let's also remove the fill of this
blue area so just select the blue one then we're gonna go towards the bucket and under
fill we're gonna go to no fill here there you go then for each of these we're gonna make them
um red yellow and green just like a traffic light so we're gonna select the first one
let's go ahead on the color and make that one red then the middle one will make yellow
let's go with this yellow up over here and then this last one will make it in green so
go under colors again let's change it to Green like so all right now this is starting to look
more like it but we're missing the line that's gonna show us where exactly we stand here so for
this first let's move the chart down a bit so we're just gonna drag it down over here
and from here we're just going to right click go to select data and we need to add more areas so
hit on ADD there and the name is gonna be the point for us and the values that we want to
add are going to be these three over here then we're just gonna hit OK there and hit OK again
now what we want to do is this whole outside area we actually want to change so we'll go right click
then we're gonna go to change series chart type and just bear with me I know this isn't
looking too good right now but we'll get there and for this bottom area where it says point
we're gonna change that to a regular pie chart hit OK there great now for this gray area just
click on it make sure you only have the gray area selected and we're gonna go with no fill
same thing goes with this Blue Area we're gonna go with no fill and so that orange area should
represent the line but the problem is that it's all the way down there and that's because we
actually change the angle of this one remember we change the angle to 270 so we need to do the same
here so we'll go right click format data point and then we're gonna change this over here to 270
hit enter there then we're just going to select the chart more generally the pie chart there and
go to no border as well so we can see it better same thing with this side go to no more there
okay now that's looking a lot more like it for this orange we'll change it to something
like a black or a dark dark color like so and one final thing we can do here is add
some labels so go to right click there and go to add label now you can see that it's showing this
too which represents this number here and it's not what we want so we'll double click on it
up over here under the formula bar we'll go to equals and select that sixty percent hit enter
and Ctrl B to bold and if you want now that's looking more like it so now when you change the
score to say 55 that's going to move accordingly same thing if I go with 20 and if you're wondering
what the second point is this is actually the thickness so if I change this to a 10 you'll
see how that Arrow gets so much thicker press Ctrl Z there to go back now that we have this
gauge chart let's go ahead and remove the border so no border let me fast forward how I resize it
awesome now that we've done the customer satisfaction score we just need to do the
same thing with the employee side so we would basically Ctrl C and then paste that graph over
here and just change the ranges by going to right click and then selecting data but for that I'm
just going to fast forward it as you probably already understand the steps great now we have
two gauge charts for the customer satisfaction and the employee satisfaction now step four here is to
create a actuals versus budget for our expenses to see how the company is expenditures have been
relative to their budget this chart is going to be over here towards the bottom where it says
budget versus actual and if we scroll over to the side we can see all of the data that we'll
need which is this one here so we have all of the typical expenses alongside the budget and
the actual amounts so we're gonna select all of this so Ctrl shift down control shift right there
from here go to insert and what we're going to want under the column charts here is this one
the more the first one there just click on that and we'll delete the title layer because we don't
need it and next up all we need to do is just select this whole Orange area right click and
go to format data series and we want this as a secondary axis so you can see one's going to
be on top of the other as of now you can't see it too well but the reason for this is so that
the the actual amount is going to be on top of the budget so we want to change the Gap width here so
something like um say 400 hit enter there now you can see what that's more or less looking like same
thing with with these outside ones the blue ones let's go ahead and reduce the Gap width there to
say 159 say and just like so that's looking a bit better then we'll change the colors here so the
budget ones let's say that we want these actually let's start with the actuals we're gonna change
the color of these to something like a red because it's an expense so we'll go with the red there
like so then for the blue ones in the back what we're gonna do is go under format up on top
then we're just gonna put the outline as the red but for the shape fill let's go with something
like a light orange like so now that's looking a lot more like it one final thing here you might
notice is that the sides here the axis aren't exactly the same so this one's up to 500 well
this one is to 450 so let's correct that we'll go to right click we'll go to format access then the
maximum here we want it at 500 hit enter there and now that's looking more equal we can just delete
one side because they both say the same thing and now let me fast forward how I move that over
great now we can clearly see that the marketing team here has actually spent way too much money so
we might need to control that and finally in step 5 we want to see the company's head count and how
that's evolved and a good way to visualize that is using a waterfall chart so over here towards
the side and this is where we'll add the staff turnover you can see that we've got all of the
stuff um data that we need which is the previous year's total alongside and the new hires layoffs
and departures that's gonna reach the 2023 total so we'll select all of that by going to control a
then under insert we're gonna go over here towards the side click on that and we want the waterfall
so just click on that one we'll remove the title layer now you can see that it actually looks a bit
odd in that it doesn't actually fall back down so this last one that we want as a total we're
actually gonna have to right click on it and go to set as total now that makes a lot more sense where
we started with this amount in 2022 these were the changes and it went down to this other amount
for the decreases here just click on that in the legend let's change them to a red color so under
fill we're gonna change them to Red say for the increases let's change them to say we're fine
with the blue say but this one will also put as a total right click on it and let's say that we put
it in a gray color as well so under fill we're gonna go with this gray now you can clearly see
the trend then for the legend we can also put it down towards the bottom so under format Legend
we're gonna say put it on the bottom there and we also don't want to fill more generally we
don't want a border there so we're gonna go no border and no film now let me fast forward
how I move that great so that's our waterfall chart done and just like that we've created an
awesome dashboard where we can see the project roadmap using a gun chart alongside the table
seeing more or less or historic historical Siege triggers our satisfaction scores with the gauge
charts and these column charts to see our budget versus actuals and finally the evolution of our
staff across the whole year now sometimes creating these charts is a bit tedious and there are more
powerful softwares like power bi so let me know in the comments if you want a tutorial on that
for more on Excel check out this video over here to learn how to make a dashboard or this link over
here to take our Excel course hit the like and that subscribe and I'll catch you in the next one