How To Set Up Kpi In Excel

Watch Video – Creating KPI Dashboard in Excel – Part one/iii

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

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

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

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

This is an improved version of a dashboard that I created last year in my previous chore part equally a financial/information analyst.

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

KPI Dashboard in Excel – Function one: Dynamic Scatter Nautical chart.
KPI Dashboard in Excel – Office 2: Dynamic Interpretation.
KPI Dashboard in Excel – Role 3: Dynamic Data Extraction + Bullet Chart.

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

KPI Dashboard in Excel – Part one/3

Let me get-go show you how the terminal KPI dashboard looks like:

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

At the tiptop-correct (of the dashboard), a user has the option-buttons to select a quadrant and get a listing of all the companies in that quadrant.

Below it, at that place is a bullet chart that shows the KPIs Vs. Peer Average comparing for the selected company.

I have broken downwardly this dashboard creation procedure into 3 parts. In today'south article, I will bear witness you how to create a dynamic besprinkle chart.

Click hither to download the example file

KPI Dashboard in Excel – Dynamic Scatter Chart

I have the KPI information for 100 companies. For the purpose of this dashboard, let'southward call these companies Com one, Com 2, so on.. and the KPIs are KPI 1, KPI two, KPI 3, and KPI iv. The information looks as shown beneath:

The dashboard comprises 3 worksheets – 'Data', 'Calculation', and 'Dashboard'.
It is almost e'er a skillful idea to convert raw data into an Excel Tabular array. In this case, I have named this tabular array KPIData.
In the Dashboard sheet, insert ii ActiveX Combo Box. The input to these Combo Box would exist the proper noun of the KPIs (KPI 1, KPI two…) and each Combo Box is linked to a jail cell in the Adding sheet.
We now need to create a dataset for the chart (this information resides in the Calculation worksheet). Since the chart updates when the drop-downwards selection is changed, the data needs to be dependent on the choice. Nosotros can do this using a combination of INDEX and ROWS formula. Here it is:
- X Centrality:=INDEX(KPIData,ROWS($A$xv:A15),$B$8+one)
- Y Centrality:=Index(KPIData,ROWS($A$15:B15),$B$nine+1)
Now this data is fed into a scatter chart. Since the information is dependent on the Combo Box drop downs, equally presently as the drib-downwards choice is changed, the nautical chart instantly updates.

Download the Instance File

This uncomplicated dynamic besprinkle chart is actually helpful when you want to down-select a handful of companies based on their KPI performance.

In the next article of this serial, I volition show yous how to spot a company in this chart, and how to get the dynamic estimation of the chart (in the text box beneath the nautical chart).

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

Stay Tuned… More awesome excel stuff coming your way in the adjacent commodity (KPI Dashboard in Excel – Part 2) 🙂

Other Excel Dashboard Tutorials:

Excel Dashboards – Examples, Best Practices & Resource.
Game of Thrones Dashboard in Excel.
Excel Dashboard: Premier League Season 2022-15 visualized.
KPI Dashboard by Chandoo – A 6 Part Serial.

You May Also Similar the Following Excel Tutorials:

How to Create a Drop-down in Excel.
Adding and Using Checkboxes in Excel.
Adding and Using Radio Buttons in Excel.
Creating Heat Maps in Excel.