Blog Archive

Excel: Interactive KPI Dashboard

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 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 Procution 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 Procution KPI Dashboard 2
Click on the pivot table to activate it and add a calculated field from the menu shown below:Interactive Procution 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 Procution 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:Interactive Procution 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 Procution 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 Procution KPI Dashboard 7
When finished, make 2 copies of this pivot chart too:Interactive Procution KPI Dashboard 8
Now merge 3 cells 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 Procution 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:
=SUMIFS(Sheet1!$E:$E;Sheet1!$A:$A;Sheet2!$A$14;Sheet1!$B:$B;Sheet2!$B$11;Sheet1!$D:$D;Sheet2!$A20)
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:
=IF(A20=$A$15;B20;NA())
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 Procution 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 Procution 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 Procution 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 Procution 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 Procution 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 KPI Dashboard from the link below:

Download Interactive KPI Dashboard

 
 
 
Subcontractor Evaluation Checklist
Creating Spark-Line in MS Excel
Using VLOOKUP Tool for Microsoft Excel
Microsoft Excel: Top 10 Formulas for Aspiring Analysts
EXCEL: Using * in Excel Formulas
How to Improve Your Microsoft Excel Skills
Important Excel Keyboard Short Cuts
Excel Tip: Replace formulas with values Quickly
Creating Simple Check List in Excel
Learn Top 10 Excel Features