KPI Dashboard in Excel [Part 1 of 3] – Dynamic Scatter Chart

Watch Video – Creating KPI Dashboard in Excel – Part 1/3

Learn the exact steps I take to create World Class Dashboards in Excel. Join the Excel Dashboard Course.

A Key Performance Indictors (KPI) dashboard is one of the most used dashboards in business.

Its primary objective is to show the performance of key KPIs and provide a comparative view of other KPIs or companies.

In this tutorial, I will show you how to create a KPI dashboard in Excel.

This is an improved version of a dashboard that I created last year in my previous job role as a financial/data analyst.

I have broken down this Excel KPI Dashboard tutorial into three parts:

Objective: We have the KPI data of 100 companies, and the objective is to create a dashboard that would help in identifying key accounts based on the performance. Apart from a comparative view, it should also enable the user to drill down on individual companies.

KPI Dashboard in Excel – Part 1/3

Let me first show you how the final KPI dashboard looks like:

KPI Dashboard in Excel - Description

In this Key Performance Indicator (KPI) dashboard, a user can select the KPIs that he/she needs to compare. It would instantly update the scatter chart with a spread of all the 100 companies across the four quadrants. It is a great way to segment companies based on the performance.

At the top-right (of the dashboard), a user has the option-buttons to select a quadrant and get a list of all the companies in that quadrant.

Below it, there is a bullet chart that shows the KPIs Vs. Peer Average comparison for the selected company.

I have broken down this dashboard creation process into 3 parts. In today's article, I will show you how to create the dynamic scatter chart.

Click here to download the example file

KPI Dashboard in Excel – Dynamic Scatter Chart

I have the KPI data for 100 companies. For the purpose of this dashboard, let's call these companies Com 1, Com 2, and so on.. and the KPIs are KPI 1, KPI 2, KPI 3, and KPI 4. The data looks as shown below:

KPI Dashboard in Excel - Data

  • The dashboard comprises 3 worksheets – ‘Data', ‘Calculation', and ‘Dashboard'.
  • It is almost always a good idea to convert raw data into an Excel Table. In this case, I have named this table KPIData.
  • In the Dashboard sheet, insert 2 ActiveX Combo Box. The input to these Combo Box would be the name of the KPIs (KPI 1, KPI 2…) and each Combo Box is linked to a cell in the Calculation sheet.KPI Dashboard in Excel - ComboBox Cell Links
  • We now need to create a dataset for the chart (this data resides in the Calculation worksheet). Since the chart updates when the drop-down selection is changed, the data needs to be dependent on the selection. We can do this using a combination of INDEX and ROWS formula. Here it is:
    • X Axis: =INDEX(KPIData,ROWS($A$15:A15),$B$8+1)
    • Y Axis: =INDEX(KPIData,ROWS($A$15:B15),$B$9+1)
      KPI Dashboard in Excel - Dynamic Chart Data
  • Now this data is fed into a scatter chart. Since the data is dependent on the Combo Box drop downs, as soon as the drop-down selection is changed, the chart instantly updates.
    KPI Dashboard in Excel - Scatter Chart

Download the Example File

This simple dynamic scatter chart is really helpful when you want to down-select a handful of companies based on their KPI performance.

In the next article of this series, I will show you how to spot a company in this chart, and how to get the dynamic interpretation of the chart (in the text box below the chart).

Let me know what you think? Leave your thoughts in the comments section.

Stay Tuned.. More awesome excel stuff coming your way in the next article (KPI Dashboard in Excel – Part 2) 🙂

Other Excel Dashboard Tutorials:

You May Also Like the Following Excel Tutorials:

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)