Hi everyone, Kevin here. Today I want to show you how you can create an interactive dashboard
in Microsoft Excel. With a dashboard you can showcase the most important information to
your organization, and best of all it's actually really easy to set up. You don't have to know any
VBA and you don't have to install any add-ins. All you need is the base version of Microsoft Excel
once you finish creating your dashboard as new data comes in it'll automatically update
to reflect the latest data also once you're ready to share it out with other people in
your organization you can very easily share it so they can get insights from your dashboard if you
want to follow along today i've included a sample spreadsheet in the description
all right let's check this out here i am in microsoft excel and i want to create a performance
dashboard for the kevin cookie company leadership is always asking me questions about things like
our profit unit sales and that type of business stuff now i figured i could create a dashboard to
answer all of those different questions that way i can continue focusing on my youtube videos as
you can see i don't have anything in this workbook yet but luckily i have data to build my dashboard
down below let's click on the data worksheet on the datasheet here you'll see sales data by market
for the kevin cookie company now keep in mind this is internal data but i figured it would serve as
a good example so just between you and me keep it on the download that i shared this sheet with you
to build our dashboard we're going to create a few pivot tables this table that you see here
is currently in a tabular format and this works really well for creating pivot tables
if you're new to pivot tables i've included an overview video in the description
also even if you're not new to pivot tables i've also included a link to a video that covers some
of the more advanced aspects of pivot tables and if you think you know pivot tables trust
me there's always something new to learn i'm learning new things about them all the time
as a first step we need to make sure that our data here is in a table format to turn it into
a table simply click anywhere in your data and then go to the top ribbon and click on insert
right here you have the option to turn it into a table you can also press the shortcut key ctrl t
let's click on this in this prompt it asks me for where my data is and here it automatically
identified all of the different data that i want to bring into this table
here it also asks me if my table has headers and this is my header right up here it does
so i'll make sure that i check this box next let's click on ok all of my data is now included
in a table and it has this nice table formatting next i want to insert a pivot table so once again
i'll click into my table and then up on the top ribbon let's click on insert and all the way over
on the left hand side there's the option to insert a pivot table when you click on that that opens up
a prompt and it asks us what table or range do we want to use i'll simply select this table 3.
this is the table that i just created i'll place my pivot table on a new worksheet and then i'll
click on ok this now drops me into a new worksheet sheet1 and i can now start building my pivot table
for my dashboard i want to have three different charts so i'm going to need three different pivot
tables and right now i only have one if i go down below over to all of my different sheets i can
press the ctrl key click on sheet 1 and then drag it over this will create another pivot table it's
just a copy of sheet 1. here i'll press the ctrl key again click on this sheet drag it over and
now i have three different pivot tables and i'll use these three different sheets to pull together
all of my views for my dashboard the first view i want to pull together is profit by country and
cookie here i'll right click on this tab and i'll select rename and let me call this just what it is
profit by country and cookie now that i've renamed the sheet i'm ready to pull together
my first pivot table and once again i want to know profit by country and cookie type over on
the right hand side i have all of my different pivot table fields first i'll select country and
i'll pull that down into rows here we could see all the different countries that we operate in
for the kevin cookie company also over on the right hand side i see a field called product here
i'll drag that down to columns and here you can see all of the different cookies that we make here
and trust me they are all extremely delicious over here i'll select profit bring it down into values
and here i can see all of our different countries all of the cookie types and the associated profit
now that i see all of my data here i want to make a few formatting changes right now you see
that it doesn't show up as a currency here i'll select all of these different values i'll click
up on home up above and here let me turn on the currency formatting type also i don't need all
of these different decimals i know every penny counts but for this analysis it's not necessary
here i'll remove the decimals before i insert a chart to visually show this data i want to make
one more tweak to the pivot table i want to make sure that i show all of the different markets in
order of most profitable to least profitable and i want to do the same for the different cookie types
here i'll zoom out just a little bit and i'll click into the grand total column
here i'll right click i'll go over to sort and i'll sort from largest to smallest so here you
see that india is our most profitable market and the united states is the least profitable
thank you to all the indian viewers of this channel we've been able to achieve record profits
we can now do the same for the different cookie types
over here i'll right click go down to sort and i'll sort this from largest to smallest as well
so now the markets are sorted from the most profitable to least and same with the cookies
now that my pivot table is all done i'm ready to insert a chart and we're going to use this
chart on our dashboard to insert a chart let's go to the top tabs and click on pivot table analyze
over on the right hand side there's the option to insert a pivot chart let's click on that
this opens up the insert chart dialog and right here i'm going to select the stacked column chart
next let's click on ok and here now you can see a pivot chart representing all of this data now when
we did the sorting here here you'll see all of the different markets are sorted from the largest over
on the left all the way down to the smallest on the right also with the stacked bar chart here
you'll notice that the largest items are at the bottom and the smallest items are at the top so it
makes it easier to consume the data overall this looks pretty good but i think i need a title on
this chart just so when people look at it they can understand what it is let's go up to the top tabs
click on design and all the way over on the left hand side click on add chart element right here
we'll go down to chart title and i'll add it above the chart for the chart title i'll simply describe
what it is it's profit by market and cookie type before i bring this chart back to my dashboard i
want to clean up a few things here i have all of these different field items included on my chart
here if i click on this i can filter my product but i don't want this cluttering up my chart
here i could right click on the item and i'll select to remove or hide
all field buttons on this chart and when i select that it really helps clean it up
i'm now ready to move this chart to my dashboard here i'll select the chart and i'll press ctrl c
next i'll click over to my dashboard and i'll paste it onto this sheet and check that out i now
have my first visual on my dashboard here i'll zoom out just a little bit so i can see how it
sits on my dashboard here i can select the chart item and if i press the alt key at the same time
it'll snap into different positions so this way i can organize my dashboard a little bit more easily
here i'll position this first item right here and i'll extend it down just a little
bit maybe right down to this point i think that looks pretty good next i want to create
two more pivot tables for my dashboard and i'll do these a little bit more quickly down below
i'll click on the next sheet and here i'll rename this and we'll call this units sold each month
to track units sold each month here i'll select the date and i'll make this my row and right up
here i'll select units sold as the values so here i can see by month how many units we sold
to help with the formatting here i'll highlight all of the numbers and i'll insert the comma style
and i'll also remove the decimal points just like we did before i'll click on pivot table analyze
and here i'll insert a pivot chart for unit sales over time i think a line chart will work
really well i'll select this one and then click on ok this now inserts my chart and here i'll
update the title to say units sold each month here i'll remove the legend and i want to remove
the fields just like i did before here i'll right click and all hide all field buttons on the chart
this now looks really good so i'll copy it and i'll bring it into my dashboard
in my dashboard i'll press ctrl v to paste and here i'll position it right near the top and here
i'll reduce the size just a little bit that looks pretty good lastly i want to create one more pivot
table for my dashboard i'll go down to the bottom tabs and let me rename this one to profit by month
to create a pivot table with profit by month it's going to be very similar to the last pivot table
that we created here i'll select months and i'll drag it down into rows next i'll select profit
and i'll pull it down into values and here we see our profit by month like i've always said there is
money in the cookie business next i want to set it so it appears as a currency i'll highlight
all of these values and here i'll select the currency view also i'll remove the decimal places
next i want to insert a chart so once again i'll click on the tab titled pivot table analyze and
right here i'll select a pivot chart to represent data over time line charts work very well
here i'll select line and then i'll click on ok this now inserts my chart and i don't know why
profit decreased in november and december those are the holidays we should be reaching records i'm
sure management will be interested in this right up here i'll click on the title of the chart and
i'll put in profit by month i'll click over on the legend that says total and here i'll delete that
and just like we did before i want to remove all the different fields i think that'll make
the dashboard look better once again i'll right click and select hide all field buttons on chart
next i want to bring this chart over to my dashboard so once again i'll select the chart
press ctrl c to copy and then i'll click into my dashboard here within the dashboard i'll
press ctrl v to paste and here all position and under units sold each month and i'll make
my best attempt at trying to make it just about the same size now just like we did before i can
press the alt key and then this item will snap to the cells on the page here i'll do the same down
here and now everything lines up nicely to make sure everything aligns very nicely on this page
i have a few different alignment tools here i'll select this chart i'll select this one and i'll go
to the top tabs called shape format over on the right hand side here i have my alignment tools
and here i'll make sure that i align them to the top and they were already aligned at the top but
if they weren't that would help me achieve that next i'll select these two different charts right
down here once again i'll go to shape format and let me align them to the left so these are also
in alignment with one another so everything looks nice and organized along with setting
alignment i can also specify the exact dimensions of one of these charts here i'll select this chart
and i'll go up to format up on top and over here i can see the precise height and width
so i could adjust these if i want different charts to be the exact same height and width so far we
just have a static dashboard and i want to make sure that people can interact with this dashboard
to make it dynamic let's select one of the items and then go up to pivot chart analyze
on this ribbon there's the option to insert slicers and also a timeline first let's
insert a timeline within the timeline prompt i'll select date and then click on ok this inserts a
timeline slicer here i'll select the item and i'll drag it over to the left hand side and here i'll
adjust the dimensions so it sits right alongside these different items next i want to insert some
additional slicers once again i'll select this pivot table i'll go up to pivot chart analyze
and over here i'll select insert slicer within this dialog i'll select both country and product
i want to quickly be able to filter my data by the country and the product next i'll click on ok
this now inserts two slicers but before i bring it over to the left hand side i want to clean them
up a little bit when you look at this list it's pretty obvious that these are all countries so
i don't need this header that says country here i'll right click on the item i'll select slicer
settings and i'll turn off the display header then i'll click on ok i'll do the same for product
now that neither of these items have a header here i'll resize it so it just
fits the different slicer items i'll do the same over here now i can drag this slicer over
onto the side i'll position it right here and i'll do the same with the product type
with the slicers over on the left hand side you'll notice that the width is not quite the same here i
can select this first item i'll click on timeline and here i can see the width i'll set it to two
and i'll select these other items and i'll set the same width for them as well here now you can see
that they all have the exact same width with my slicers now in place i can very quickly filter the
data on my dashboard let's say for example i just want to see data for india here i could select
india and you'll notice that my profit only shows india but my two other views didn't update how can
i make sure that these slicers are connected to all of the different pivot tables here i'll
right click on the slicer and let's go down to report connections within report connections i can
specify what pivot tables this slicer controls and currently it's only set to the profit by market
and cookie type i want to make sure it updates all of my different pivot tables so here i'll check
all these boxes and then click on ok i'll do the same for the other two slicers as well and check
this out when i click on my slicers now it updates all of my different pivot tables over on the side
so this dashboard is definitely becoming a lot more interactive now let's take a look at
chocolate chip cookies in the u.s look at that i can see how much profit for that cookie type
if i want to select multiple items with a slicer here i can simply click on india and then drag
my mouse down and that'll select all of these different markets and here i could do the same for
the product so it's very easy to look at my data how i want to view it this dashboard is working
really well but what if new data comes in and we want to make sure that our dashboard reflects it
well it's very easy to take care of down below let's click into the new data tab and
here you'll see that we have additional data for 2020. it's formatted in exactly the same format
here let's select all the data from this table and then press control c once you copy all of the data
let's click into the data sheet and go all the way to the bottom of the table this is the table that
we used to construct all of our pivot tables and here simply paste in all of the new data
because we turn this into a table it automatically incorporates this new data into that same table
next on the bottom let's click back into our dashboard and we want to make sure that the
dashboard now reflects all of the latest data to do that let's click into one of these pivot charts
and then go up to pivot chart analyze within the ribbon here there's the option to refresh let's
click on this and then select refresh all and there you'll see that your dashboard automatically
takes all of the latest data into account this dashboard now is starting to look pretty nice
but it still has the look and feel of excel and i want it to look more like a proper dashboard
to do that let's go up to the view tab up on top and with the sheet selected right here
you have the option to turn off the grid lines so there you don't have those typical cells in excel
also here i can turn off the headings so now it's really starting to feel more like a dashboard also
down below we have all of these separate sheets for all the different pivot tables and the data
here i could select all of those different sheets i could right click and then i could hide those
different sheets so now if you share this sheet out people will only see the dashboard that you
pulled together this dashboard is now starting to look really nice but maybe the color scheme
doesn't match your organization well that's no problem we can also change that up on top with
the top tabs click on the one titled page layout and over on the left hand side you can choose from
all of these different themes and that'll apply it to your dashboard also within themes you can even
browse for other themes or you could even save a theme that you create so you can customize this
to look exactly how you want it to look now that we've created the dashboard and it looks exactly
how you want it to look you probably want to share it out with other people in your organization
up in the top right hand corner you can click on the share icon this opens up the shared dialog
and here you could decide whether people can edit the sheet or if it's only view only back here you
can select people you want to share it with or you can simply copy a link and then share it out all
right well let me know down below in the comments were you able to successfully create a dashboard
to see more videos like this please consider subscribing and i'll see you next time you