in this video we'll create this interactive excel dashboard that updates based on your timeline and
slicer preferences and you can download the excel file in the description so let's get into it
suppose we're analysts at coca-cola and this is the data set we're working with
based on this we want to create a visual dashboard that's going to tell us how the
company is currently performing and what are some of the customer trends so firstly let's
take a look at the data set and we've got the key american coca-cola retailers so we've got that
over here alongside some relevant figures like the region the beverage runs so within coca-cola
they own fanta sprite etc and then we've got the different financials right over here and to see
how long this data set is go to control control down arrow that should get you to the bottom and
you can see here that it's over 3000 rows long now to start the analysis let's convert this into
a table so go to control up arrow all the way to the top here and we'll select everything by
going ctrl shift down and then ctrl shift right from there you can go to the insert and table
here or you can press ctrl t that's a shortcut for that that's going to get us the range and hit ok
now we have all of this in table format which is what we're looking for once we have this
looking like a table let's take a look at the dashboard tab go to control page zone and here's
what we currently have we have the coca-cola logo alongside the key american retailers so
up on top over here it'd be nice to have a few of the summary the key financial figures essentially
so the total revenue the quantity sold etc so for this go to control page down under the data
what we're going to do is we're going to insert a pivot table click on that from there hit ok and
so we'll create a new sheet there and we want to get the key financial figures there so essentially
we're going to have the total sales we'll put that under values that's going to give us the
sum of total sales that's the total revenue for us similarly we want to get the units sold so
how much how much coca-cola or how many bottles that we sell then we've got the operating profit
and lastly we'll get the operating margin but because this is actually a percentage the margin
we're gonna change that so go and click on it and then we'll go to value field settings and we'll
change that to an average hit okay and now we've got all of these different financials that we
think are going to be quite useful now that we've calculated these figures we can put them in the
dashboard by linking them so go to control page down over here up until you get to the dashboard
and the first thing that we're going to have is the total sales over here and then we're
going to put the total units sold and let me fast forward all of this so you don't have to watch it
once we have all of them listed we're just gonna go ahead and link them so we'll go
equals for total sales it's gonna be the sum of total sales over here and then just
next to that for unit sold we're gonna go back up it's gonna be this one here
nice now whenever they open the excel file you're gonna have all of these
summary financials which are gonna be the most important ones so that's quite handy
now that we have the relevant financials for the year it would be nice to see a monthly breakdown
maybe in the summer months there is more sales because people are going out more for instance
for this we'll create another pivot table so go ahead and go under the data here and then we'll
go insert pivot table and this time we want to put it in an existing worksheet and that location
we want to click over here it's going to be let's say we put it just below where the current one is
and we'll select that there and hit ok so we said we wanted the dates on one side and for that we
can just select the invoice date so we'll put that under the row and that should give us the months
as you can see over here and then we also want to have the total sales as the values which is going
to be the sum of them that you can see over here once we have this information let's go ahead and
reformat it so select all of it by going to shift up up arrow and then ctrl 1 there and we're going
to change this to a currency format we don't want any decimal so we'll put zero decimals there and
the dollar sign works for us and we'll hit ok nice now let's insert a pivot chart as we have
both the months and the relevant financials that we need so we'll go under pivot table analyze and
select pivot chart hit okay and that's the pivot chart that we want from there we'll copy it to
the dashboard tab so control c control page down arrow control page down arrow and go over here
and this is the one that we want to do now let's reformat this one so firstly we'll remove these
different labels that we've got for this we'll go under the pivot chart analyze under field buttons
we want to go to hide all that should hide it for us that's the first step then for the legend we
can delete that so just press the delete key also we don't really need these these lines
here so we'll delete them as well and we want to change this name to something like monthly sales
from there we also want to make this a bit thicker so it looks a bit nicer and what it's currently
looking like for this we'll right click on it go to format data series and let's change this
gap width to something like um 40 here hit enter now that's looking slightly more like it let's
open this up a bit and let's also remove these borders so for this just select it go under border
and we're going to go no line nice that's looking a bit more like it let's bold in this and for the
fill color here we also want to change that so go under home and we can go ahead and select it
from here let's say we go for a light blue like that nice now we can see the monthly breakdown
one thing that would be nice though is to make it dynamic so maybe you only want to see the first
half of the year and so you want to be able to change that graph dynamically for this firstly
let's click on it go under pivot chart analyze and we'll go insert timeline for the invoice state and
hit ok now we have this thing over here let's go ahead and drag that out so we'll drag it all the
way all the way to the end here and so now if we start to look at it you can see that we've got all
of the periods here and if we drag them suppose i go only to october you can see that the chart
updates dynamically with it as well and there you go even further and so on so that's basically the
idea with this type of chart let's go ahead and reformat it to make it look like everything else
firstly we'll change the invoice they title here which you can do up over here and we can
put something like sales period hit enter there and then we're gonna change the coloring here
for this go under this drop down and we'll go new timeline style and we want to change the color of
the selected time block which is this light blue area we'll go under format there and for the fill
we want to change the fill color go to more colors so we can customize it the same color as up here
that's going to be the 2a 3e68 hit it hit ok there and then hit ok again and hit ok and that should
do it for us then we want to change that to that one so we'll go ahead and click on it here under
the customize options that we've got and now that's looking a bit more like it we also want to
get rid of that border so we'll go ahead and click on it right click go to modify and from there go
to the whole timeline go under format go under border here and we'll change that to none hit ok
and then hit okay again if we extend this back out to the whole year so just go ahead and drag that
across the hole here you can see that there's a bit of a trend where in the month of the summer
period it seems like sales pick up there and then again towards the end of the year they do as well
so there's some sort of seasonality going on there and if you like what you're seeing we do have an
excel for business and finance course where we teach everything we know about excel specifically
for people either looking to break into a business or a finance role or those in it trying to level
up their excel game unlike most theoretical courses we try to make this one as practical
as possible based on our real experiences working at companies like tesla or amazon
so aside from the typical lessons on formatting formulas and charts we also have case studies that
replicate 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 so if you're interested in checking it
out go to the link in the description below one thing that the visuals we've made so far don't
consider are the regions so how well do we sell in california what about in new york all of that
stuff we don't really know right now so let's create a visual for that to showcase this we
can insert a dynamic map of the us that's going to tell us which region is doing best kind of like a
heat map for this we'll go back to the data tab so control page down here and what we want to do is
create another pivot table so we'll go to insert pivot table and we want to put this in an existing
sheet let's say we go back to that sheet 2 that we've got here and we'll put it down over here
okay there and then hit ok nice and here what we want to do is on the row side we want to have the
state and then on the values instead of having the total revenue let's go by the units sold so
the quantity we'll go ahead and put that and it should be the sum of units sold now if you
scroll down over here you can see that we've got the breakdown for all the states now to create a
map chart we're actually gonna have to not do it on a pivot table as that doesn't work so we'll go
ahead and link them here equals alabama and then we can just drag them drag that down and across
all the way till we get to the bottom which is over here and there we go once we have this we
can select it so ctrl ctrl shift down arrow ctrl shift right and from there we're going to go to
insert and what we want to insert is a maps you can see there and we're gonna go on that one
click on it now you can see that we've got a map of the us which is somewhat like a heat map where
depending on if it's selling more it's gonna be a bit of a denser color we'll go ahead and copy them
up so ctrl c and we'll edit it on the dashboard tab that we got over here and then just paste
it so ctrl v and let's say we put it over here to the side let me just scroll over there so we can
see it a bit better for a chart title we're going to change this to something like map of unit sold
and over here under series one we also want to change this we'll go right click on it
and we'll go to select data and i'm gonna change that so go to edit there and we'll put units
sold and hit okay hit okay again now we have that looking good let's go ahead and make this
slightly bigger there and now let me change the color of these titles make them bold as
well and put them to a blue color for this i'm just going to fast forward it as it's quite easy
i also want to add some commas here to the unit so it's easier to tell so we'll go under the
data tab so i go to the sheet 2 here and instead we're going to go control control up here then
ctrl shift down arrow so we select all of them press ctrl 1 and we're gonna go under number we
want to use the separator and we don't want decimals we'll put those at zero and hit ok
then go to dashboard and that should be updated for us as you can see over here
taking a closer look at this map we can really start to see that california over here
texas and some other states are really the main drivers of the quantity sold here
then that makes sense after all that's probably where the biggest populations are within the us
one thing you might have noticed is that we don't really have much information on specific retailers
or specific brands so for example how how much in revenue has fanta made how about sprite etc
so let's go ahead and work on that for this we'll need to make a few slicers so firstly go
ahead and click on that chart then we'll go under pivot chart analyze and we'll go to insert slicer
from here we want to do one for the retailers say then let's also do another one for the region why
not and lastly let's do one by beverage brand and hit ok there now you can see
that we've got these three different slicers let me fast forward me uh reformatting them
to show you what these slicers do so if you go ahead and click on any of these say dreamco
you'll notice that the monthly sales start to change same thing if you go to coca-cola
but everything else isn't changing so the total sales or the map
the heat map right here is not going to change that's because it's currently not linked
so let's go ahead and link it as well we'll go under sheet 2 over here
and under the total sums that we've got so that's the very top part of the dashboard we'll go
under pivot table analyze and we'll go to filter connections here we want to tick on all of these
for this one it's already going to be linked because that's the first one we link to
and then down over here we want to actually put the state name
so we're just going to put state up on top and over here we're going to put units sold
and we want to link this over here which is the actual pivot table as this is just being linked
go to pivot table analyze again go to filter connections and we're going to tick on all of them
and now if we go back to the dashboard let's say we change the coca co from coca-cola to the sunny
water and we put this first retailer you'll notice that everything starts to change dynamically so
the totals over here as well as the map as you can see down below you'll notice that there's a sign
over here under the map which if you click inside it it's just gonna say something like hey um this
is high confidence or not so don't really worry too much about that one final thing we could do
here with the slicers is reformat them to look like everything else so in this dark blue color
so let's go ahead and select on all of them so go to control and then click on them so keep
holding the control key from there we'll go to slicer and we want to go on the drop
down and go to new slicer style for the whole slicer what we want to do here first we get the
selected item with data we'll go to format there then for the fill color we want to change that
color to the dark blue so that's going to be down over here under hex value we'll go 2a3e6a
hit enter and then we'll go to ok from there we also want to format go to format again and this
time for the font color so if it's dark in the background we need a white color for the actual
text so we'll go ahead and click on white hit ok hit ok again and now we've got this new format
that should be this one over here so if you click on it you can see what it's currently looking like
and so that's looking a lot more clean i think now we have a fully dynamic dashboard so for example
here if i'm focused specifically on the south to see how that's performing you can see that the map
will automatically update just the south area and so will all of the figures for that go press the x
on the filter there so it defilters everything similarly if we want to see how fanta is
performing because maybe we're in charge of that you can see all of the breakdown for that here as
well and you can even change the timeline over here to say um up until september for instance
for more on dynamic dashboards on excel check out this link over here and if you want to learn more
about excel specifically for business and finance check out this link over here that's all for this
video hit that like hit that subscribe if you liked it and i'll catch you in the next one