in this video we'll create this awesome interactive Excel dashboard in just 15 minutes
first we will structure the dashboard and make it Dynamic then we'll create all of the charts
and visuals and finally we'll customize them to fit our look so let's get into it so here's the
Excel file that we'll be working with which you can download for free in the video description
let's suppose that we're working at McDonald's and we're tasked with creating a sales dashboard for
the South America region and for this we're given three different tabs the dashboard where we'll
have all our charts and visuals then the inputs tab where we have all of the data to create the
visuals and finally we've got contacts tab over here just to have the contacts of all the managers
so let's go back to the dashboard and the first thing here is to make this Dynamic so you can
see that we've got all of these icons here to the side we can actually hyperlink them
such that they navigate to different tabs so we'll go to right click on this one say
and from here towards the bottom you'll find the link and we want to
link it to a place in this document and more specifically to the inputs tab we'll hit OK
the third one we can link as well we'll go to link again this time for the contacts and the final one
here you can see the question mark let's say this is the support so it actually links to an email
so let's suppose this is to an email address and you can specifically type say info at support
.com that's just a made-up email but we'll hit OK there and then this first one should go to the to
this dashboard tab so we'll go to link again and Link it to a place in this document the dashboard
and we'll hit OK the idea here is that when you click on one it's gonna take you to the different
tab we need to add the same thing across all of the different sheets so we'll go to this
first one just select the column by clicking on the a here Ctrl C go to control page down Ctrl V
control page down again and Ctrl V and now we can dynamically navigate across the different sheets
now that we have the navigation in place let's work on the general structure so we're going to
add different shapes here we're just going to go to insert then under shapes let's go ahead and
create a rounded rectangle and we're just going to drag it more or less from here from column B
all the way to column m and here you can decide how how rounded you want it to be we don't want it
too rounded so I'm just gonna hover over there the shape fill we're gonna want it in a white color
with no outline so we're gonna go to no outline down over here one final touch here is actually
adding a shadow on the background of the shape kind of like so once we have the shape we can go
ahead and add a title here so let's call this a sales dashboard for now Ctrl a in case you don't
see it's probably because the text is currently in white so we'll change that to say a dark blue
Ctrl B to bold in to make it bigger you can just click on this bigger a here once we have this
first one you can just hit the Ctrl shift and then just drag it that's going to allow you to
drag it down over here so the idea is to have three different shapes here such that they're
going to be the three kpis so we'll go ahead and reformat this let me fast forward how I do that
then right below that we're gonna have one large chart over here and then one smaller one down
below here so let's do the shapes for that as well so Ctrl shift and just drag down let me reformat
that as well and finally we need to add one more shape over here which is going to represent the
map of South America nice now that we have all of the shape structures let me fast forward how
I rename them here's what the structure with the titles is going to be looking like so we've got
the title and what the figures are in right below that we have the three main kpis then we're gonna
have some kind of a line chart over here alongside the customer satisfaction scores and finally a
breakdown using a map chart to see the sales by country now that we've finished this first step
the second step is to work on the different charts and visuals so let's head over to the inputs tab
for this so control page down here the first thing we're going to do is go ahead and select all of
the customer satisfaction scores here just hit on Ctrl a for that and then we're gonna go over to
insert and we're gonna add what's known as a radar chart so we'll go to this drop down over here
under all charts we're gonna go towards the bottom where it says the radar one it was hit
on OK there and we don't really need this Legend here as we kind of have that already in the labels
so we'll delete we'll also delete the title here now you can more or less see how we're doing on
each of the factors and speed clearly isn't a very good one and nor is service we'll just go
ahead and move this over to the side and make it smaller and we'll work on reformatting it later
right next to that we've got the map chart over here so the country breakdown so control a again
this time under insert we're gonna head over to maps go under a field map and we can just remove
the title layer you can see that's looking fairly good already so we'll just make this a bit smaller
leave it here to the side then for these sales figures over here to compare them easily we can
use something like a line chart so we'll go Ctrl a insert and we're going to go under recommended
hopefully it shows up there and you can see that we've got the line chart so we'll hit OK on that
but you'll notice that this um axis over here has way too many labels so you actually can't
see what's going on within this range so from more or less 180 to 250 is what we care about
so first we'll delete this title here we'll change this axis by right clicking on it going to format
access that minimum amount is the one we want to change and we'll add something like 180 here
and hit enter and now you can see that discrepancy a little better
and again we'll just move that to the side for the time being
now let's work on these up over here basically here we're gonna get the different proportions
so the percentage of complete if we look at the formula is simply going to be the actual over
the target so we just want to get these two here so the complete and the remainder that's going to
be enough for us to create a donut chart so we'll go under insert under the pi over here we're gonna
select the donut down below great let's remove the title and we don't need the legend either and one
more thing that we can do here is actually add a text box inside which is going to give us the
percentage that's completed so in this case it would be 85. for this let's go to the insert tab
under shapes here we're gonna select the first one which is the text box
and then we're just gonna drag it over here but you can't simply put the 85 percent as that's not
going to be dynamic so we're actually going to go up over here to the formula bar go to equals
and we'll select that 85 percent ourselves and hit enter we can then reformat it so maybe put
it in the middle and let's also Center it like this Ctrl V to bold then we can make it a bit
bigger to something like size 16. and then we'll change the color to something like the dark blue
great from here let me just go ahead and resize it I'm gonna fast forward how I do the same thing for
all the other ones but one final thing is making the donut the width here a bit thicker so we can
see it a bit better so we'll go to right click there go under format data series you can see
that we have the donut hole size so this is what we want to change to something like a 65
and now you can see how it looks a lot thicker great let me fast forward how I do the same thing
for these other ones you can see here as I'm working on the second donut chart I just need
to go ahead inside the text box and change that from a d to a g I'm going to hit enter there but
you'll notice how the formatting all of a sudden changes if you want to bring it back to this
original formatting you just gotta select the text box go under the format painter and then just drop
it over here so click there you can see how it's reformatted it the same way let me fast forward
how I do the last one nice now we have all of the key visuals that we're gonna need if you like what
you're seeing and you want to learn more you can also check out our range of courses which include
Excel for business and finance PowerPoint Finance evaluation as well as a range of bundle packages
and what makes our courses different is that they're all applied to the real world
so aside from teaching the theory our lessons also offer case studies that simulate the type
of work you might be assigned in your day-to-day ranging from creating a financial model from
scratch on Excel to creating a professional slide deck presentation on PowerPoint
and if you get stuck along the way you can easily ask us questions in the course discussion forum so
if you're interested in checking it out you can go to the link in the description below alright
back to the video awesome now that we have the dashboard structure individuals we just need
to bring it all together so for this let's go back to the inputs tab first we'll select the map chart
and just hit on Ctrl X from here let's go back to the dashboard and Ctrl V we're just gonna drag
it over to the side and let me just reformat this one you can clearly tell it doesn't look too great
because the map is so small if you want to change that you just need to go to right click under the
legend first click on the legend right click go under format Legend and we want to click deselect
this button here so show The Legend one now if we look at how that looks you can see that it looks a
lot better because it's removed that spacing then let's also remove the fill here for the chart so
we'll go no fill and we don't want a border either so no border now it should all look a lot better
nice so that's the map chart done now let's focus on the other visuals starting with this
trend trend line over here so we'll go to the inputs tab select this trend line Ctrl X
let's go back to the dashboard here and Ctrl V and for this one we're going to want to change
both the colors and we're also going to change kind of how the markers are currently labeled
first let's select the entire chart under chart design let's go ahead and select the third option
here which looks a bit more elegant then we click on that specific line the orange one for now go to
right click format data series and here is where we want to change both the color and the markers
so we're gonna select a line that's going to be in red color and then we're gonna select the marker
right next to it select marker options and from here we want a built-in marker that's gonna be a
circle so we'll select the circle from here we're fine with a size five but we want that fill color
to be a solid fill that's going to be in white and we also want a border for it that's going to be
a solid line in red let's also increase the width of that to something like one point and now we can
kind of see what that looks like which hopefully looks a lot better same thing here with the blue
one let me fast forward that great so now you can see that we've got one in red and the other one in
dark blue let's also remove the fill here so we'll go for no fill and no borderline great now that's
looking a lot more like it now we'll work on the radar chart here for the customer satisfaction
so go back to the inputs let's control X again let's go back to the dashboard and Ctrl V
and again we'll just drag it over let's try to get the same format as this one over here so with this
um dots as markers so we'll go to right click go under format chart area
we want no fill no borderline and for these actual points we're just gonna select them
and we're gonna go for a solid line that's a dark blue color and we're gonna go for a marker that's
gonna be uh rounded again so built in we'll go for a rounded one and let's go for a dark blue color
solid fill white and we're gonna go for a solid line in dark blue and increase the thickness of
that to A1 as well and there you go great now we just have the three kpis left over here and for
these it would be nice to not just show The Donut chart but also have the actual number
here on the side so for this we're just gonna insert a text box so under shapes here we'll
go for text box and we're just gonna select it over here and again we want to make this
one Dynamic so we'll go equals and we're going to link it over to the inputs tab under the actual
amount for sales and same thing with profit customers later so once we have that we need
to reformat it like we've done before and then do the same thing for these let me fast forward that
now that I have this first one I just gotta go Ctrl shift and just drag it across like so and
then Ctrl shift again and drag this one as well now I just need to change the labels by changing
the link and if you get this issue again where it's not formatted properly just gonna select on
the first one go under form a painter just click on that second same thing over with this last one
like so great now we're ready to go under the inputs and select the three donut charts here
so just hitting the control key you can select all three Ctrl X let's go back to the dashboard
and Ctrl V now we just need to reformat all of these so let me show you how I do it for the
first one let's select it then go to right click under format chart area let's go for no fill and
we'll go for no line as well then we'll select the donut just select this this blue part only
let's say we go for a dark blue color here so like the dark blue we've currently been using
and we'll select the remainder so this area that we've still not fulfilled in Orange right
now so something like a lighter blue like so great now let me do these other ones as well
awesome now we have a really nicely designed dashboard which is also interactive using the
different hyperlinks over here and you can see that it tells us pretty much all of the key
information that we will need in just one sheet if you want to learn to make dashboards like this
even faster using power bi make sure you hit that subscribe Button as those videos are coming soon
for more on dashboards check out this link over here to make them with pivot tables or this link
over here to check out our course hit the like and that subscribe and I'll catch you in the next one