Excel KPI Dashboards – Adding Micro Charts [Part 4 of 6]

This is 4th part of Creating Management Dashboards in Microsoft Excel 6 post series by Robert.

This 6 Part Tutorial on Management Dashboards Teaches YOU:

Creating a Scrollable List View in Dashboard
Add Ability to Sort on Any KPI to the Dashboard
Highlight KPIs Based on Percentile
Add Microcharts to KPI Dashboards
Compare 2 KPIs in the Dashboards Using Form Controls
Show the Distribution of a KPI using Box Plots

The Challenge – Adding Visualization to the KPI Dashboard

In this final post on KPI dashboards with Microsoft Excel, we will show you how to add meaningful graphical visualization directly into our dashboard table. With scrolling, sorting and highlighting the dash-board already offers some interesting analytical features (see previous posts). But it is still displaying the data as pure numbers. That makes it difficult for the user to recognize the relative sizes of the values at a glance. Furthermore it is often necessary to communicate the relative position of the data compared to one or several other calculated or given values like the total average or a target.

The solution

[click here to view larger size]

Inserting conditionally formatted bar-line-combination-charts directly into the dashboard table visualizes the shown data and enables the user to get an overview at a glance. The bars show the relative sizes of the corresponding values, the conditional formatting let us immediately identify which values are below target (red color) or larger than target (grey color) and the line makes it easy to see whether a value is above or below the total average.

Download the Excel file – KPI Dashboards with visualization

The Implementation

To implement the charts, we need some knowledge about creating and formatting special charts with Microsoft Excel. In my humble opinion, the by far best resource on charts with Microsoft Excel is Jon Peltier’s excel charts pages. All you have to know for our dashboard charts is brilliantly described on Jon’s website (follow the links below).

1. Prepare the workbook for the new features (5 extra columns on the dashboard for the bar charts, additional rows on the data worksheet to define the targets and new columns on the calculation sheet).
2. Insert 5 conditional formatted bar charts. Read Jon’s method to create a conditional formatted chart.

   Use the table on the dashboard as the data source for the chart and use the targets defined on the sheet “data” as the threshold whether a value is formatted red (below target) or grey (larger than or equal target).

3. Calculate the total average on the calculation sheet for each KPI and add an average line to each of the bar charts by using an XY-scatter chart type. Read more on Bar line combo.The necessary calculations for the steps 2 and 3 can be found in columns Q to AQ of the sheet “calculation”.
4. Format the charts to make only the bars and the average line visible (no axes, no grid lines, no data labels, no caption, no border or fill color of chart area and plot area). Like Albert Einstein said: “as simple as possible, but not any simpler.“
5. Adjust the charts on the dashboard to make them fit exactly to the corresponding cell ranges. One tip for this: Holding the ALT key pressed when resizing a chart will make the chart size auto-fit to the size of the cell range beneath it. That makes it easier to position the charts correctly.The bar charts already look exactly the way we want them to. But there is one undesirable effect: when scrolling up or down the table, the maximum scale of the horizontal axis changes and the bars seem to “jump” up or down.

   To avoid this, add two additional XY-scatter-series to the chart, representing the minimum and the maximum of the total data and assign them to the secondary axis. Furthermore add 2 additional bar series to the chart, again representing the minimum and the maximum of the total data and assign them to the primary axis. We thereby “force” both horizontal axes to be identical and stay the same when scrolling up or down. Since we do not want to display these dummy-series, format them with no line and invisible markers (XY-scatters) respectively with no fill color and no border.

6. Finally update the caption beneath the table to explain the meaning of the line and the bar colors.

What is next?

• Download the excel KPI dashboard final workbook
• Bookmark Dashboards using Excel pages for future reference.
• Drop a lovely note of thanks to Robert if you have benefited from this series.

Read the next part: Part 5: Compare 2 Decision Parameters in the Dashboards Using Form Controls

Also, Checkout our Excel Dashboards Page for more examples and resources.