Create a KPI Dashboard in Excel

You go to any organization; it is important to analyze based on their Key Performance Indicators (KPI). A separate dedicated team will be responsible for investigating and showing the results with visual effects. This article will show you how to create a KPI dashboard of individual sales employees in Excel. So, download the workbook to create a KPI dashboard in Excel with us.

Different companies have different KPI dashboards. For this article, we are taking into consideration the sales-driven organization. Its core revenue generation department is its sales team in a sales-driven organization. To just how the company is performing, it is important to look at the performance of each individual in the department.

How to Create a KPI Dashboard in Excel?

We have applied a line graphLine GraphLine Graphs/Charts in Excels are visuals to track trends or show changes over a given period & they are pretty helpful for forecasting data. They may include 1 line for a single data set or multiple lines to compare different data sets. read more for “Target,” “Actual,” and “Average” numbers in the above chart. Therefore, this graph will represent the “Average” achieved and “Target” achieved against the “Target” numbers.

  • First, we need to create a “Target_Table” for each employee across 12 months. In the above table for each individual, we have created a target for each month. We have applied table format in excel and named it “Target_Table.” Similarly, we must create one more table called “Sales_Table,” which shows actual sales. Similarly, we must create two more tables to arrive at the salary and incentive numbers. Above is the table of individual salaries. So now, create a table for “Incentives.”We are done with all the data inputs required to show in the dashboard. Next, we must create one more sheet and name it “Dashboard – KPI.” Create the heading “Individual KPI Dashboard- 2019” in Excel. Now, create employee names and build a drop-down list in Excel of employees. Create a month-wise table to show “Target,” “Actual,” “Variance,” and “Average Sale.” And also to display “Salary” and “Incentive Paid.” We have divided the data to be shown for the first six months and the later six months. So that is why we have added H1 total and H2 total rows.H1 consists of the first six months total, and H2 consists of the second six months total. So, the whole is a combination of H1 + H2. For “Target” and “Actual,” we must apply VLOOKUP from the “Input Data” sheet and arrive at numbers based on the name selection from the drop-down list. To find “Actual” sales data, select “Sales_Table” in place of “Target_Table” in the VLOOKUP formula.To find the variance, subtract actual sales data from target sales data.To get an average of sales data, apply the below formula.Similarly, we must do the same for the “Salary Paid” and “Incentive Paid” columns.Then, find the “Total Earned” by adding “Salary Paid” and “Incentive Paid.”Now, based on the selection we make from the drop-down list of the name, VLOOKUP fetches the “Target,” “Actual,” “Salary Paid,” and “Incentive Paid” data from the respective table which we have earlier in the article. We need to create a cell for the “Efficiency Level.” Then, we need to divide the achieved amount by the target amount to arrive at the efficiency level. Now, arrive at the return on investment result. But, first, we must divide the “Actual” amount by the “Total Earned” amount. Now, a graphical representation of the data we have comes at. Below is the graph we have applied. We can create a chart based on the liking.

In the above table for each individual, we have created a target for each month. We have applied table format in excel and named it “Target_Table.”

Above is the table of individual salaries. So now, create a table for “Incentives.”We are done with all the data inputs required to show in the dashboard. Next, we must create one more sheet and name it “Dashboard – KPI.”

We have divided the data to be shown for the first six months and the later six months. So that is why we have added H1 total and H2 total rows.H1 consists of the first six months total, and H2 consists of the second six months total. So, the whole is a combination of H1 + H2.

To find “Actual” sales data, select “Sales_Table” in place of “Target_Table” in the VLOOKUP formula.To find the variance, subtract actual sales data from target sales data.To get an average of sales data, apply the below formula.Similarly, we must do the same for the “Salary Paid” and “Incentive Paid” columns.Then, find the “Total Earned” by adding “Salary Paid” and “Incentive Paid.”Now, based on the selection we make from the drop-down list of the name, VLOOKUP fetches the “Target,” “Actual,” “Salary Paid,” and “Incentive Paid” data from the respective table which we have earlier in the article.

We have applied a Clustered Column chart in excelClustered Column Chart In ExcelIn Excel, a clustered column chart depicts data in a series of vertical columns. Though these charts are simple to create, analyzing them becomes increasingly difficult as the number of categories increases from single to multiple. read more.

After all this, we need to insert a simple efficiency chart. Finally, we have applied the efficiency chart, and we can start using this KPI dashboard by downloading the Excel workbook from the link provided.

Based on the selection we make from the drop-down list, numbers will turn up accordingly, and graphs will show the differences.

Based on the requirement, we can increase the number of employees and fill the target data, actual data, and salary and incentive data accordingly.

This article is a guide to KPI Dashboard in Excel. We discuss creating a KPI dashboard in Excel, practical examples, and a downloadable Excel template. You may also learn more about Excel from the following articles: –

  • Excel Dot PlotsEdit Drop-Down List in ExcelConsolidate Data in Excel