Interactive Excel KPI Dashboard

Here is a super slick Interactive Excel KPI Dashboard I prepared with some slicer magic. It will be a good practice of creative use of pivot table tools. Downloadable file is included.Interactive KPI Dashboard

Slicers is a very useful but underutilized tool in Excel. You can dynamically filter your pivot tables and pivot charts with animated buttons, aka slicers. For more information on slicers, you can check this short article.

Now let’s see how to build an Interactive Excel KPI Dashboard using pivot table tools:

I prepared a summary data for 4 departments; welding, cutting, assembly and packing. KPI’s for these 4 departments are selected as efficiency, productivity and scrap rate. Data set includes values for 12 months (4x3x12=144 rows). Here is how data set looks:Interactive Excel KPI Dashboard 1

Select all the data and insert a pivot table. Arrange fields as shown below (Don’t look for Rest field at this point. We are going to add it in next step):Interactive Excel KPI Dashboard 2

Click on the pivot table to activate it and add a calculated field from the menu shown below:Interactive Excel KPI Dashboard 3

Set calculated field parameters as shown below and we will have an additional field in our pivot table to use for our charting needs:Interactive Excel KPI Dashboard 4

Set KPI filter as “Productivity” and make 2 copies of the pivot table (by copy and paste). Set KPI filter for those tables as “Efficiency” and “Scrap Rate”. Here is how it is supposed to look (after all the slicers are in place):Interactive Excel KPI Dashboard 5

Insert a donut chart for each of these pivot tables. Cut them and paste them to another sheet. Format your charts to look like this:Interactive Excel KPI Dashboard 6

To do this, right-click on any gray button on the chart and select “Hide All Field Buttons on the Chart”. Delete chart title and legend. Then click on the donut and set shape outline to “No Fill”. Set shape color separately by clicking on them and selecting color.

Now make another copy of the first chart and arrange fields as shown below:Interactive Excel KPI Dashboard 7

When finished, make 2 copies of this pivot chart too:Interactive Excel KPI Dashboard 8

Now merge cells B36 to B39 and type “=B5” (without bracelets). B5 is the cell that contains result value of first pivot table (you can see from previous picture). Make sure to disable gridlines here. Then select your merged cell and copy it. Then paste it, as linked picture, to the sheet that has your donut charts. And drag it over related chart.Interactive Excel KPI Dashboard 9

We are finished our donut charts at this point. It is time for our column charts. We need a 3 column data table for each of our column charts. Here is how I do it:

Place a 3 column grid just under the second set of pivot tables. First row will contain column titles. First column will contain month names. Remaining 2 columns will be filled by formulas. Here are the formulas:

Result Column Formula:


This formula fetches result values based on department and KPI values selected on the pivot table above and month name in first column. You can see how to use this function from here SUMIFS FUNCTION.

Selection Column Formula:


This formula checks selected month on the pivot table above and shows result value from 2nd column if month matches. If it doesn’t match, it shows #N/A.

Here are the tables I prepared:Interactive Excel KPI Dashboard 10

Now select first table and insert a column chart. For formatting:

  • Double-click on the orange column and set is as secondary axis.
  • Set Series Overlap and Gap Width to 100%
  • Delete both axes, chart title, legend and grid lines.
  • Change column colors and set Shape Outline for the chart as “No Fill”

Here is how your chart should look like after all these make-up:Interactive Excel KPI Dashboard 11

Do the same for other tables (other KPI’s), you can also copy chart formatting to do this faster. Then place these chart next to your donut charts:Interactive Excel KPI Dashboard 12

Now comes the interactivity part. Click on your first donut chart and insert a slicer from INSERT ribbon. Select Month Name as field. Make sure settings are made as shown below:Interactive Excel KPI Dashboard 13

You should check whether your slicer works correctly, by clicking on its buttons. If it doesn’t change charts, it is most likely because of report connections. You can correct this issue from there. Insert slicer for other KPI’s (chart pairs) too.

Now customize you slicers as a 3×4 button grid with button height and width as 1,2cmx1,2cm. Change colors to match with your charts. You can see how to do this from Customize Slicers. Here is how it should look like:Interactive Excel KPI Dashboard 14

Add a final slicer for departments. You can do this by clicking on any of the charts and inserting a slicer. This slicer must be connected to all pivot tables.

Place it on top of your charts, add KPI texts over slicers and our dashboard is done.

You can download this Interactive Excel KPI Dashboard from the link below:

Download Interactive Excel KPI Dashboard

You may also like...

13 Responses

  1. Hisham says:

    There is no link to be downloaded

  2. Shanu Kulkarni says:

    Very Interesting and a very good Learning.

    Thank You

  3. Sofia Sá says:

    FABULOUS! Thank you so much for your sharing of knowledge 🙂

  4. Maryana says:

    I faced one issue
    after this step it shows wrong result
    “Set calculated field parameters as shown below and we will have an additional field in our pivot table to use for our charting needs:”
    KPI Productivity

    Sum of Result 40.66
    Sum of Rest -3966%
    I followed same steps I don’t know what is went wrong .

  5. Augustjat says:

    Manuscript is a collective name for texts

  6. Winona Azure says:

    There is no Rest field in the data. 🙁 So I got stuck on adding the calculated field called Rest.

  7. Winona Azure says:

    The percentages for Sun of Result and Sum of Rest are all messed up. What step did you leave out that makes them formatted like you show in the step after you created the calculated field Rest? I have, instead:
    Sum of Result 8258%
    Sum of Rest -81.58%

    • melih says:

      Since you didn’t select any filters at that point, it shows total of all values for all departments and months.
      It is going to become all right after you add all the slicers (this issue is occurred because I wrote the article after I constructed the chart).
      Thanks for the notice, I edited the post.

  8. Winona Azure says:

    “Now merge 3 cells” Um, what 3 cells? So many unclear directions here.

    • melih says:

      Random 3 empty cells is sufficient for this articles purpose. But I agree that I can be confusing, so I edited there too. Thank you.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.