📊 How to Build Excel Interactive Dashboards - YouTube

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 data sheet, 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  down low 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 up 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 Control + 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, sheet  1, 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 control key,  

click on sheet 1, and then drag it over.

This will create another pivot table.

It's just a copy of sheet one.

Here I'll press the control  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 can see all of 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're 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 of 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 this 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 elements.

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 Control + 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 could 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 I’ll  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 control + 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 of 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 control + C to copy, and  then I'll click into my dashboard.

Here within the dashboard,  I'll press control + V to paste  

and here all position it 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 up  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 could also specify the  exact dimensions of one of these charts.

Here also like 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 a 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 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 this 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 all select these other items, and  all 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 of 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 this side, so this dashboard is  definitely becoming a lot more interactive now.  

Let's take a look at chocolate chip cookies in the  US. 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 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 turned 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 sheets selected right here, you have the  

option to turn off the gridlines, 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 share 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.