[Music]
thank you for joining me today
I'm into Tracy and I'll be taking you
through how to build the interactive
Excel dashboard that you see behind me
I'm ready to go with my I simply Excel
t-shirt on and to quote Steven whew
a dashboard is a visual display of the
most important information needed to
achieve one or more objectives
consolidators and arranged on a single
screen so the information can be
monitored at a glance but with excel we
can take them one step further and make
them interactive as well before we start
a little background for those who don't
know me I started working with Excel 22
years ago when I worked as an accountant
in investment banks in London I now live
in Australia with my husband Phil and
our two boys Connor and Finn I gave up
accounting eight years ago to pursue my
passion for Excel and desire to help
people get more out of it which I do
through our website and blog at my
online training hub comm I run an online
excel desperate course and have trained
thousands of people from all over the
world and from a vast array of
industries in the last four years I've
received the Microsoft Excel most
valuable professional award for my
contribution to the Excel community okay
let's talk about what you can expect
from the webinar well it's going to be
at a fast pace you're not going to have
time to follow along step-by-step but
remember I'll be sending an email with
the links to the video replay and files
I'm going to teach you how to build a
dashboard obviously right but I want to
make the distinction between building a
dashboard versus dashboard design so in
this webinar I'm going to focus on the
mechanics of building a dashboard using
the built in tools there's no add-ins
required and no VBA by the way VBA is
the programming language for Excel I'll
cover the ideal data and workbook
structure and I'm going to build it fast
and smart so I can update it in an
instant I'll be using slicers to create
the interactivity and dynamic labels
that respond to selections made in the
slices so this is the data that
be using there's over 70,000 rows of
sales data for two australia-wide
fashion stores called fashions direct
and next look they're fictitious a data
spans January 2016 through to July 2017
you can see the data is in a tabular
format and it's classified by date
financial year the chain the location
information well so we've got suburbs
state postcode country we've also got
the manager the product category and
product buyer and it's more matted in an
Excel table and this is the first step
in having a fast and smart build because
any formulas charts or pivot tables that
reference the table automatically or
upon refresh will pick up any changes or
new data so the ideal workbook layout
has separate sheets for data analysis
preferably one sheet for each pivot
table if you're using pivot tables and a
sheet for the dashboard and we'll be
employing various techniques to ensure a
fast and smart bill namely Excel tables
pivot tables pivot charts
we'll be naming things like pivot tables
ranges and worksheets when we name
things it makes it quick to build and
easier to understand when you come back
to that file in three or six months I'm
going to be using Excel 2016 to build
this dashboard but what you see will
work in all versions of Excel from 2007
onwards with the exception of slicers
which are available in Excel 2010
onwards and the alternative to slices is
to use combo boxes and some VBA code
unfortunately I won't have time to cover
this today but I teach them in my online
Excel dashboard course we'll be using
map charts and they're a new feature
available to office 365 Excel 2016 users
again I won't have time to cover any
alternatives for those of you who don't
have office 365 but the map chart is a
really small part of the dashboard and
the reason I included it was to show you
this new feature as opposed to it being
mandatory for all dashboards okay let's
get started
so here is our data if I go to the end
you'll see there's about seventy two and
a half thousand rows and we also have a
sheet with some more data for August
another four and a half thousand rows
and we're going to use this so that I
can show you how quickly and easy it is
to update our dashboard once we've built
it so the first thing I need to do is
format this data in an Excel table and
the keyboard shortcut for that is ctrl T
and we need to decide where that our
table has headers well mine has headers
so I'm going to click OK and the first
thing we need to do is give our data a
name I'll table a name I'm going to call
it data very imaginative I know and now
we're ready to create our first pivot
table so let me just have a look at the
dashboard the first pivot table we want
is for this line chart here for the
sales by chain so let's go back and
we'll summarize with pivot table I'm
going to put it on a new worksheet and
it's correctly detected the table range
name as data so click OK and let's give
this sheet a name
let's call it line pivot and we'll give
our pivot table the name
so for this pivot table we need to know
the dates now I'm using Excel 2016 so it
automatically groups my dates I can
control Z to undo that or I can turn off
that default I want to show you how to
group them manually just in case you
don't have Excel 2016 so just select any
date right click group and then I want
it grouped by months and years so click
OK and now I have years and months in my
row labels the next thing I want other
chains and I want the sales amount so I
want to format these amounts because the
formatting that I apply here will carry
through to the pivot charts and I won't
have to mess about the formatting them
there so there's various ways to do this
I tend to right click and go into the
value field settings number format and
will apply those formats and I'll click
OK and ok so that's all I need for my
first chart let's insert a pivot chart
for it let me bring that down interview
so this will just be a line chart I'll
click OK now the first thing you'll
notice are all these filled buttons on
the chart and we can use these actually
to filter on the face of the chart but
I'm going to choose slicers for my
filtering so I actually want to
right-click and remove all filled
buttons the other thing I'm going to do
is just relocate my legend to the top
and we'll add a chart title so this
chart is called sales by tea
move it over to the left I'm going to
hold down shift while I move the chart
title and that just keeps it aligned and
I'm just going to move the legend up
here out of the way so we'll just do a
little bit of resizing here not a lot
look maybe that was too much okay
I'm not going to mess about with the
formatting too much at this point we'll
come to that at the very end and and
we'll do it all in one go so we're not
wasting time the next thing I need let's
go back to the dashboard is a chart for
my or a pivot table for my sales by
category so one way to ensure that all
of your pivot tables share the same
source data and pivot cache is just to
copy the sheet or the pivot table so I'm
going to copy the sheet holding down
control I'm gonna left click you can see
the little plus I get for the sheet that
tells me I'm copying it a left-click and
drag lets rename that to category pivot
and let's rename a pivot table
and I'll delete the pivotcharts we're
going to insert a different type of
chart anyway so for this one I don't
need my dates and I need the chain
I want the category in my row labels and
let's insert a pivot chart this one is
going to be a bar chart so bar charts
are really good when you have long data
labels and that just ensures that
they're horizontally formatted and you
don't have to turn your head on the side
to read them this makes it easier so
again we'll right-click and hide all the
field buttons and in this one I'm going
to actually delete the legend just click
on it and delete press the Delete key I
don't need it because all of my charts
are going to share a legend so I'm just
going to delete them as I go now we'll
create a manual legend towards the end
the other thing I want to do is I want
to see this data sorted in descending
order so I'm going to click on the grand
total because we're going to sort by the
grand total right click thought and this
one I actually want smallest to largest
and that would sort the chart largest to
smallest
I know it's counterintuitive so one
thing I want to point out is if you look
at the home series in orange for next
look it is actually less than the kids
series but home is positioned above kids
and that's because this is sort of based
on the total of these two columns
remember we're sorting on the grand
total so the total of home is greater
than the total of kids irrespective of
whether next look is bigger or smaller
so that's the bar chart for the
categories let's look at the next one
we need this sales by manager chart so
let's go and do that actually I should
give this one not a title first let's do
that I'll call it we'll call it
sales by category and moon for that
across there so we're consistent
okay let's copy that one hold down ctrl
and left click and drag and we'll rename
the sheet
we'll rename our pivot table
and let's change the data now instead of
category we want the manager and I also
want them grouped by their state so
let's pop that in now I want to change
the sorting so I actually want to sort
based on the state in ascending order so
that my chart is sorted in descending
order so we can make this a bit bigger
this one's going to be longer and we'll
change that
sales by manager okay the next chart we
want to create is the pie chart so let's
create the pivot table for that I'm just
going to copy the line chart it doesn't
really matter for this one
now if you know me you'll know I don't
really like pie charts attend they tend
to take up a lot of space and a little
bang for the buck if you like there's
not a lot to them so there's often
better charts for this but let's go with
it we'll create a pie chart because it's
going to support my dynamic labels then
I'm going to show you so for this one we
need to know the chain and then the sum
of sales because I want to create the
dynamic label I actually need the
percentage of sales as well so we're
going to pop the sales amount in again
and this time I'm going to right-click
and show values as percentage of grand
total so now I have my sales percent
let's make sure my chart has a name my
pivot table I mean and we've named the
sheet so let's insert our pie chart and
I'll get rid of all the filled buttons
I'll get rid of the legend and I'm
actually gonna get rid of the title as
well so look how big they are but two
pieces of information it's a quite a big
chart and I'll just resize it slightly
holding down ctrl makes the pie smaller
on all sides equally okay so that will
do for now for the pie the next thing we
need to do is create our pivot tables
for the sparklines
so let's go and do that I'll copy the
line sheet will call this sparkline
and I'm actually going to break my rule
here and instead of having one pivot
table per sheet I am going to have all
three pivot tables let me just show you
the dashboard again
I need a pivot table for the all chains
of the total and either pivot table for
the next look data and another one for
fashions direct and I'm going to put
them all on this sheet because I want to
demonstrate and show you with them all
on one sheet but if you were doing this
yourself then you wouldn't have them on
one sheet sheets cheap remember so I
want to get rid of all of my subtotals
and grand totals and I want to make sure
that the states here are first of all
sorted that doesn't show on the screen
so let's right-click short sort A to Z
which they are but I'm making sure they
always stay that way and the other thing
I wanted to do is some of the chains
don't have stores in every state let's
take a look so we can see here fashions
direct doesn't have any stores in South
Australia or Tasmania but I need to
allow for those states so what I want to
do is ensure that those states appear
even if they have no data so in the
field settings on the layout and print
tab I want to show items with no data
okay so now let me make sure this has a
name let's call it sparkline total pivot
because this is the one for both chains
and then I'm just going to copy it and
paste it below this one will be the one
for next look so let's give it a new
name
and we'll paste another one and this one
will be for sessions direct let's give
it a name
okay so we've named those three what's
the next one we need okay so we need a
pivot table for the map chart so let's
go and do that well copy the which one
shall we copy doesn't really matter
actually I'll copy the PI one and we'll
call it the map pivot
let's get rid of the pie chart and we
don't need that we don't need the chains
but we do need the states now for the
map we need to make sure that Excel
knows where these states are so we need
the country information as well so let's
pop that in now a lot of the time Excel
will figure out where these states are
based on the context but I prefer not to
leave any ambiguity in my data just in
case one day it gets it wrong so better
to be safe than sorry is my motto so
again let's get rid of the subtotals and
grand totals and the other thing I want
to do is I need this data in a tabular
format because map charts don't have a
pivot chart equivalent they're a regular
chart so I have to create my data in
such a way that it will support the map
chart and that needs to be tabular so
for that I need to right-click on
Australia and go into the field settings
and change the layout so that it shows
items in a tabular format and I also
need to repeat the item label so that
Australia is on every row so let's do
that okay so our data is in the correct
format let me make sure my states are
sorted and they are all right so this I
can't insert a map chart on this pivot
chart so what I need to do is copy the
data paste it as values and then I'm
going to insert a map chart based on
that so insert Maps filled map now that
I've got my map I can point it back at
the pivot table so let's select the data
so at the moment it's picking up columns
eg let's just change that to a to C and
click OK
so now you can see my map is picking up
the pivot table data I can get rid of
this data here and I'll get rid of the
chart title and the other thing I want
to do is because my map chart is showing
the total data I want to differentiate
the colors in here to distinguish them
from all of my other charts so all of my
other charts to use blue and our ends
but the same series so fashions direct
is blue and next look is orange so let's
make sure that this doesn't use blue or
orange because this isn't either of
those chains that's the total so in the
design we'll change the colors to a
monochromatic scheme and we'll leave it
at that now one thing I haven't done is
renamed my pivot table to map pivot okay
how are getting along let's see we've
got our data ready for our map and we're
ready to assemble our dashboard so back
in the file all I need now is a sheet to
put my dashboard on so to prove to you
that there's nothing special or magical
about these dash broadsheets I'm just
going to add a new sheet I'm going to
drag it to the front I like my dashboard
to be at the front of the workbook
and later on I'm going I can hide all of
these workings and my dashboard will be
separate from them if you like her at
the front at least I won't mix it up in
the middle of these so let's apply some
formatting
I'll make this row a little bit bigger
and we'll put in some fill and this is
called the big fashion group sales
dashboard
and let's change the font
and we'll make it bigger and we'll make
it white okay and let's Center it in
that section so we've got our head up
now all we need to do is move our charts
on to the dashboard so click on the
outer edge of the chart control X to cut
and ctrl V to paste let's just rinse and
repeat so each one control X this one
goes down here somewhere
I'm just roughly placing them on my
dashboard ctrl V to paste it in and the
pie charts come and pie will go in here
somewhere
roughly and sparklines I have to insert
them manually so all I need now is my
map and we'll paste the map in down here
somewhere okay so don't worry too much
about how everything is arranged so I
need to insert my spark lines and
they're going to go on row 12 so I'm
gonna cheat a little bit and I'm just
going to come back to this dashboard and
copy this text it's just text formatted
it's nothing special it's just gonna
save me some time so I'm gonna paste it
in there and we'll re-size those columns
so that it fits all right so in order to
build out this table I need to reference
some pivot tables and first of all I
need the state so I'm going to use the
if function - first of all test that
that state hasn't been filtered out
because if I've filtered it out with a
slicer then I don't want the formula to
return a 0 if that pivot table has an
empty state so first of all let's test
we'll go to our sparkline pivot if this
cell here equals blank let's go back
here it's not showing me the formula
just so you can see it let's close that
so if the sparklines pivot cell a6
equals blank then blank otherwise return
I'll just copy and paste it in there
otherwise return the value in that cell
and that gives me the first state or
copy that down for all the states so the
next thing we need to do is get the
value of sales for a CT for all chains
now I could create my own pivot table
for this but I'm gonna cheat a little
bit and try and save some time by using
one of the other pivot tables and the
map worm will do nicely because it has
the total for all chains and I'll just
show my formula bar that might help us
see what's going on
so that's reference the map pivot table
you can see get pivot data is instantly
inserted and I'll press Enter
now I want to do two things here I want
to allow for errors because we don't
want errors littering our dashboard so
wrap get pivot data and if error so all
it's saying is if get pivot data returns
an error then just return me with a
blank now the next thing I want to do is
I want to be able to copy this formula
down let me show you what happens if I
copy it down you can see I get the same
result in every cell and that's because
the state information is hard keyed and
that's one of the downsides of get pivot
data but we can automate that or make it
more dynamic by replacing that hard
keyed information with a reference to
this cell here and then as I copy it
down it will pick up the correct State
so let's do that and I'll copy it oops
copy it down so now we get the correct
data let's just format that in a
currency with no decimal places and we
need a little bit wiser okay the other
thing I want to put in here is a databar
just to help visually indicate the
length of those bars or the size of
those numbers and easily be able to
compare them to one another because
they're not sorted but by putting in the
bars that will help us identify which
states are bigger than others and by how
much it'll give us some context so on
the Home tab
we'll go conditional formatting data
bars let's choose a solid fill now I
need to edit that so go in the manage
rules and I
need to change the color because
remember this is all change so I need it
a shade of gray let's see if that one's
ok probably a little bit dark let's try
something slightly lighter we'll go with
that one and let's see it doesn't look a
lot different
alright we'll go with that for now
normally I wouldn't put the bar and the
value in the same field in the same cell
because you get that effect where the
bar can make the number difficult to
read normally I'd have them separate but
I haven't got a lot of room here so
we're sticking them on one column ok
we're ready to insert our spark lines so
first of all I'm going to select my
range and then on the insert tab I'm
going to use a line spark line line
charts are really good for showing the
trend of data over time so we're going
to tell it where the range is so on the
sparkline pivot table I need all the
data for the months by state now there's
a bit of a bug if you like with spark
lines in that normally or ideally we'd
put a dynamic named range in here so
that as our pivot table grew as we added
more data the spark line range would
automatically grow the problem is you
can't do that for a group of spark line
so let me just be clear this because I'm
selecting all of these at once this will
become a group of spark lines I can't
create a dynamic named range for a group
but I can create one for individual
spark lines so if I was to create an
individual dynamic named range for a CT
and then you South Wales and then
Northern Territory and so on I could do
that but that would be 8 times 3 so 20
for dynamic named ranges and I really
don't think it's the best use of your
time or mine to make you sit through me
creating all of those so what I'm going
to do instead is just insert the spark
line and I'm going to allow for
some growth by making the range extend
past the end of the pivot table so go
with that and you can see there's some
space at the end and you can tell that
my spark lines are grouped because when
I select one cell I get this blue box
around all of them now this is all
changed so we need to make the spark
line black that's doing next looked now
again the same thing insert a line chart
or go to the pivot find our pivot table
for next look allow it to go past the
end and we'll click OK now next look is
orange so let's change that to orange
and freshens direct rinse and repeat
Custer column Y and we'll click OK all
right let's just make sure that's the
correct shade of blue okay we are nearly
nearly ready to make it fancy the next
thing we want to insert are our slices
so I've got some space over here for
slices let's make this column a little
narrower now there's a few ways to
insert slices just like everything in
Excel there's a few ways to do
everything one way is to make sure you
either have a pivot chart or a pivot
table selected and then from the field
list if you have Excel 2013 onwards you
can right click and add a slicer or you
can go up to the insert tab and choose
slicer and this will give you a list of
all the fields and you can actually
insert multiple slices at the same time
so I can select state and category cuz
they're the other two that I need now
I've got my three slices category state
a financial year I can just move them
over into place let's roughly resize
them and if I hold down alt it will snap
them to the grid so
another shortcut you can use you can see
as I drag it across it's kind of
snapping to the cells below I don't
actually want it to do that so it won't
hold down alt but that's another tip for
you
if you want to align things to the cells
underneath and we'll resize that one a
little bit okay so our dashboard is
coming together one thing that will
really make it look better is if we turn
off the grid line so let's get rid of
those and that makes it look a lot more
polished and what's next
let's make our manual legends so instead
of having this legend up here let's put
one up here and the reason we can use a
manual legend is because all of our
charts share the same coding color
coding so we've created relationships
between our charts by keeping that color
coding consistent the last thing you
want to do is use different colors for
the same information and likewise you
don't want to use the same colors for
different information that's confusing
as well so you can see in every chart
fashions directors blue and next look is
orange and the same here and here so
let's create a manual legend I'm going
to use some shapes just a rectangle
we'll draw it in here and however big
you want we also need a text box for the
store name so this will be fashions
direct
and let's format that so that it's got
no fill and no outline and we'll make
the font white so that it stands out
better on the background all right let's
just pin that for now okay actually that
has a border so let's get rid of that
and we'll just roughly align them hold
down shift to select both hold down ctrl
and shift and copy them across and by
holding down ctrl you copy and by
holding down shift you make sure they
don't move off that horizontal line so
this one is orange and this is called
all right we need one more for our total
stores or all chains so let's just copy
this on again then see how at the moment
I haven't got shift held down as soon as
I hold shift it snaps back up to being
line with these ons so this is all
chains
and let's fix the color of this one so
actually it's got a gradient fill so
let's go and give it a gradient now it's
all washed out so let's fix that we'll
go into more gradients and over here we
can actually change this the gradient
colors so the first one let's make it
black this one will also make it
slightly darker and that's probably okay
so that gives us a bit more of an
indication so we've got our legend now
we can get rid of this one in the line
chart and the other thing I might do is
I'll set the axis so that the units are
in thousands and that will just give us
a bit more space and now all these task
planes are taking up loads of room so I
have to bring them back down to size
we'll get rid of the lines that just
clutters them and this poor line chart
doesn't quite have enough space does it
there we go
alright we need to repeat that
formatting for these axis the axis on
the other charts so that we're all
consistent let's double click to open
the task pane and also if you're using
Excel 2010 then double clicking will
open up the dialog box that you have in
your shop for your chart formatting in
Excel 2010
so let's squish you into there now
normally I would be using a much bigger
screen but I'm cramming it in so that
those of you who are watching this on a
tablet or a laptop can actually see what
I'm doing it's not too small so I'm
cramming it in when I normally wouldn't
cram it in this much anyway so the next
thing we need to do is create our
dynamic labels for the pie chart so
let's do that we'll do it on the pie
sheet so this label let's take a look at
it actually here it is here you can see
here I've got the name of the chain
the sales value and the percentage and
the same again for the other chain and
as I filter those labels update
accordingly so let's go and take a look
at how we do that so what we're doing
here is really just joining text
together concatenating text and the
first piece of information I need is the
store name so just simply select the
cell and then we'll use ampersand to
join the next piece of text
now this label wraps on two three lines
so I'm going to use the character
function and character ten is a line
break and that will wrap the text for me
and the next piece of information I want
is the sales amount and because I've
clicked on the values area of a pivot
table I get the get pivot data function
and you'll see that the store name is
hard keyed so I'm just going to select
that and instead of having a hard keyed
all into the cell reference and then we
want to join another line break and then
we want the sales percent and again the
store name is hard keyed so let's just
make that dynamic by selecting the cell
instead now when I press enter you'll
see all the pieces of information will
be there but these to get pivot data
functions are returning numbers and I
haven't told Excel how to format those
numbers it's converting this into text
and I haven't have told you how to
format that text whenever you join text
together whether it's a number or
letters it becomes text and let's see
what happens we get some ridiculously
long number that means it absolutely
nothing so what I need to do is wrap the
get pivot data functions in the text
function and that allows the get pivot
data part to be the value argument for
text and then I'm going to add a format
so we'll tell it to format this in
dollars and that will format the first
value the sum of sales and then we need
to repeat for the percent
so the format will be percents and now
we have our numbers formatted although
they're running on to one another and
that's just because I haven't wrapped
the tip the text yet so let's apply the
wrapping and now it fits much better
let's copy that down so we've got one
for fashions direct and one for next
look now we can reference these labels
in our pie chart labels and we're going
to manually insert labels and the way we
do that is by inserting a text box
we'll do fashions direct first so with
the text box active we can see the pull
handles are on the outside I'm going to
click in the formula bar type an equal
sign then click on my PI pivot sheet
select the cell containing my dynamic
label and press Enter
so you can see my labels in there I need
to format it and let's change the font
color to match the PI and our line left
and down let's make the font a bit
smaller now I see that I need to make
the PI much bigger it's one of the
reasons I don't really like pie charts
is because they take up a huge amount of
space and really return a very small
amount of information for the space that
they require now I need to format the
text box so that there's no fill and no
outlines and no outline no fill and now
it fits in there much better I'm going
to copy this text box so I'm going to
hold down ctrl you can see my mouse
cursor turns into a different symbol
with a plus and I can left-click and
drag to copy so now with its selected I
can edit the formula to pick up cell e2
and press Enter unfortunately it loses
all of the formatting which is rather
helpful not so let's go ahead and repeat
the formatting now one thing I like to
do when I'm inserting labels that are
color-coded to match the chart segments
is to maybe apply a shade darker so at
the moment these are the same colors the
segments in the pie and the next look
font but if I make it a shade darker it
makes it easier to read and it's
actually really difficult to tell that
that's a shade darker than the pie
segment so it just makes it slightly
easier to read for your users and as a
little trick I use sometimes if I if I
need to so now I've got my dynamic
labels but I haven't linked my slices to
all of the charts and pivot tables
at the moment they only control this
sales by change art so you can see as I
select different items this updates that
nothing else does so let's go ahead and
make those changes so first one is the
financial year I'll right click and go
to report connections this brings up a
list of all the pivot tables and you can
see why it was so important to give them
proper names because when you're trying
to connect slicers to pivot tables and
they just say pivot table one pivot
table two it gets very confusing very
quickly so I want this slicer to connect
to all of the pivot tables now here's a
tip if there is a pivot table missing
from this list so you you're expecting a
pivot table to be there and it's not
listed
there'll be to one of two reasons for
that for your slicer to control multiple
pivot tables all of those pivot tables
must use the same source data in this
example we only have one service source
data so that's a definite sometimes
though you can use the same source data
but inadvertently create a separate
pivot cache so every time you insert a
pivot table there is a copy of the data
held in Excel memory called the pivot
cache now if you're referencing the same
data then there should only be one pivot
cache but sometimes you can accidentally
create more than one pivot to cache from
the same source data and if that happens
then you wouldn't see a pivot table in
this list where it uses a different
pivot cache so the solution is for
example to copy one of the pivot tables
that is in the list and recreate that
pivot table that's missing so so this we
want all of these pivot tables to be
linked to the financial year slicer with
the state slicer we want to connect
everything but the map pivot and that's
because the map chart isn't a pivot
chart and the other reason is that
normally the workaround that I would use
is to link this map chart to
dynamic named range that picked up the
PivotTable
however map charts on you in Excel 2016
and they don't yet retain dynamic named
ranges so unfortunately I've got to work
within those limitations hopefully one
day they will but for now we're going to
just ignore the map pivot for the state
slicer let's rinse and repeat for the
category and for the category we can
apply it to every pivot table so now
we've connected our slices to all of the
pivottables and pivotcharts when i
select items in the slicer let me zoom
out a bit so you can see them in action
you can see everything is updating so it
all connected you need to do a little
bit of formatting to get it all looking
good but otherwise we're nearly there so
one of the benefits of building your
dashboards the smart way that I've shown
you where you link everything to a table
that contains your source data is that
updating them is super easy and fast so
let me show you down at the end of this
file I've got some August data
it's another four and a half thousand
rows or so so let's just copy that data
to the clipboard now normally I wouldn't
use copy and paste for my data I'd use
power query and go and get it
dynamically and have it all
automatically update but for the purpose
of the webinar and just to make this
process quicker I'm copying and we're
going to paste it in so this is my data
table this is what all of my pivot
tables are linked to and when you paste
data on the very next row under a table
the table range grows to incorporate
that new data so at the moment we can
see the end of my table is this icon
here is indicating the end of the table
when I paste the new data in you'll see
that shifts to the bottom of the new
data so ctrl V to paste it in you can
see the table formatting has expanded if
I go to the very last row you can see
there's the end of my table so now I
have 77 thousand rows of data or
thereabout
if I have any formulas linked to this
table they already include that new data
I just paste it in and for my
pivottables and pivotcharts
all i have to do is click refresh all so
let me show you back on my dashboard you
can see I only have data up to July 2017
all I need to do is go to the data tab
click refresh I'll keep an eye down here
boom it's all up to date I can't tell
you how exciting that is the first time
you update a report with one click and
then you go and get yourself a cup of
tea or coffee and put your feet up
because you deserve it I really hope
that if there's one thing you take away
today please take away how important it
is to set up your file and your source
data correctly because when you do you
can use these amazing built-in tools the
way they were intended to make your life
easy
so that's updating your dashboards the
next thing I want to do is fix the
colors so they use my company branding
and I quick way we can do this is using
themes and with themes we can format our
colors fonts and effects and we can even
set up our own custom theme that picks
up all of our company branding I can
also choose from some built-in themes
and as I hover over a theme you get a
preview of what it might look like cool
that's a bit bright so the one I
actually want to use is called parcel so
I'll select that you can see the fonts
have changed the colors and the layouts
a little more spacious so now that I
have everything looking the way I want
in terms of colors and fonts let's spend
a little bit of time formatting
I'll just minimize the form of the
ribbon and let me just get rid of the
formula bar so I have a bit more room
okay so alignment is really important
and aligning things helps you segment or
segregate your data sometimes you don't
even need borders and charts and tables
simple at alignment will do now one
thing
should do is group these objects
together so that when I want to move
them I can move them as one
so let's align the pie chart to the edge
of the table here I've got a bit more
room for my slices they're not aligned
so let me hold down shift and select all
of them and then we'll align them left
and I've got some more room so let's
move the state slicer up I'll hold down
shift just to keep it vertically aligned
let's move the category up and I can
actually make these columns a bit
narrower so that I've got more room over
on the right okay the pie chart and the
sales by chain chart aren't aligned to
the top and let's select the manager
sales by manager and we'll align these
three to the top say format align align
top my pie chart height is different to
my sales by chain so one thing I'll do
is go into format find out the height of
the chart I want to keep so that's five
point five four select my pie chart and
let's change that to five point five
four as well so now they're consistent
and we can probably move this up a
little bit now one way to move your
charts using your arrow keys on your
keyboard is to hold down control and
select the outer edge you can see that
pull handles have changed and now I can
just nudge that up a little bit okay
everything's looking pretty good I'm
still zoomed out again don't develop
your dashboards zoomed out always at a
hundred percent but I'm working with the
smaller screen here for the purpose of
the webinar so we'll just have to stick
with that limitation alright that's
looking pretty good I can make my
category slicer longer okay let's edit
the captions for the slices just to make
them fit in a bit better so this can
just be FY financial year and this one
can't do a lot with that maybe just call
it cats
three cats for sure okay you know what I
mean okay so one other thing we can do
to make this look this report look more
seamless and have less distraction is to
get rid of the column and row labels and
the sheet tabs and the scroll bars so on
the view tab we'll get rid of the
headings and then on the file tab under
options in advanced and then down in
display display options we can deselect
show horizontal scroll bar vertical
scroll bar and sheet tabs and click okay
so now I have something that almost
doesn't even look like Excel it looks
very clean there's not a lot to distract
the user they can interact with the
slicer you can see everything is
updating on my labels all of my charts
and we're ready to publish it and next
month when you come into work and you've
got the new data pop it into that source
data sheet refresh everything on the
data tab refresh all and you can get on
with some more exciting work now one
thing I have included in the file that
you can download if I go to the end
we've got date dashboard protection here
so there's some information here on how
to protect your dashboard when you start
to share it with your colleagues some
things you'll want to do with slices one
of the annoying things with slices is
when you click on them if you miss the
button and it's easy to miss so if I
miss the button I get these pull handles
around the slicer and it will still work
but it looks like it's in the edit mode
and I think it's not a really nice
effect for your users so you can disable
that you can also protect your worksheet
and you need to make sure you protect it
in such a way that the pivot tables will
still update when you interact with the
slices this talks about how to hide
worksheets and including how to really
really hide them so that even when you
right-click this unhide doesn't give
away that you've hidden sheets
and workbook protection things you might
want to consider here now we can also
embed this dashboard on a web page so if
you have an intranet you could embed it
on your intranet and all of the
functionality will work including the
slices and I actually cover how to do
that in my Excel dashboard course okay I
know I ran through building that
dashboard quite fast but my hope is that
you've picked up a few tips as well as
experience the power of Excel for
building dashboards and that you're able
to get started and have a go yourself
and to help with that you'll find a link
below this video to this page here you
can download the workbook and it
includes the complete Excel dashboard
that I built here plus step-by-step
instructions so you can rebuild it
yourself
it also contains links to tutorials on
pivot tables slicers sparklines dynamic
text labels and more so you can fill in
any gaps in your knowledge and that way
you can get up and running with building
your own dashboards right away now
further down this same page under the
ebooks and PDF heading you'll find a
link to my chart recipe ebook and this
is designed to help you decide which
chart to use for your data because if
you put your data in the wrong type of
chart then your message will be lost now
I've only touched on the surface of
what's possible with Excel dashboards in
this video because I wanted to keep this
under an hour but if you'd like to set
yourself apart from the crowd then
please take a moment to check out my
full online excel dashboard course where
I teach you a whole range of different
techniques including animations and you
get five sample dashboards thanks for
taking the time to watch this video
tutorial I hope you enjoyed it please
take a moment to like it and share it
with your friends and colleagues and why
not subscribe to my channel while you're
here