Create KPI Dashboard

KPI dashboards in Excel – The all-time classic! A KPI can have a goal, which is the exact value that it should reach and ranges for tracing purposes. The business world for some time now is heading in the direction that we weigh every possible thing.

To quote a phrase: we weight it, it gets better. So we got the idea for the key performance indicator from here. And how true is the before quoted sentence!

Before Create your first Dashboard in Excel

Do we know the sources of the information? Where is the dashboard “feed” from? Automated updates are necessary, or is it enough to send daily or weekly reports? If we know the answer to all these questions, then it will be easy for us. If we do not, then we have to find out every little detail by a preliminary survey.

How many performance indicators are needed? It is very important that you know exactly. If the goal is to track only 5-10 KPIs than it is sensible to use a gauge chart, most of the companies will need a lot more than that. A gauge chart is an incredible tool, but it could be more efficient. If the goal is the tracking of more than 10 KPIs, than the best choice is the bullet chart.

What should be the format of the final dashboard? Will we have to share our information through the intranet or only share it with a few managers? We have to pay attention to set authorizations. The dashboard should always be a read-only! Just imagine where it would lead if somebody opens the file, modifies it then forward it in an e-mail. I don’t even dare to think…

Compatibility first! Remember that different versions of Excel have a different function. If the file was created with Excel 2016 and someone wants to open it with Excel 2007 will not come anything good out of it.

Data Cleansing: Last but not least clean raw data using built-in and macros and VBA solutions! It’s very important to convert tabular format data into an Excel table. All right, the task is given! But how should we start?

When an idea is born in our heads, there are two possible ways to go about it. In the first scenario, we immediately get to work and neglect the planning part. This never leads to nowhere. In the second case, we carefully plan the design and the necessary steps.

What do you think is more productive in the long run? In our opinion, the planning phase should take up 70% of the whole project. The remaining 30% is needed for the implementation. Mistakes are a lot less if we advance step by step.

Is it easy to create a key performance indicator?

From the technological view, yes. Check our data visualization and chart add-in if you are in a hurry.

create kpi dashboard

Important characteristics of an effective KPI:

  • Current value, namely where we stand now.
  • Plan value, namely where we plan to stand now.
  • Variance, namely by how much and what direction we differ from the plan.
  • And a trend value that shows the trend of fact values.

We have to give these values to create a KPI, and from then on, the tool is doing everything for us. If we want it displays the KPI on a speedometer, or on a diagram or a map. It does it all depending on the content, i.e., according to a given period, branch, product group, or other criteria.

But the challenge does not lie here but in the choosing of KPI. Could you tell which one is that 5-10 KPI, or can we call it a performance indicator by which you can monitor the achievements of your own field? Could you create 5 or 10 KPI with your field’s success criteria, your department’s value saving ability?

What is the most important KPI?

Let’s say that you are a CTO. You are a very busy man with not much time on your hand. Wouldn’t it be good to have a dashboard that you can look at every morning and could see the values that your department has added to the success of the company?

You can see that all IT procedure is running fine, where you can track the IT expenditure compared to plan, where you can see the levels of the IT projects and see how the goals and expectations of your department are realized, etc.

It would be good if there would be only a few indicators on your dashboard, and it could be clearly arranged. But what should these indicators be? And what would be those indicators you would put on your boss dashboard?

Today, thanks to the business intelligence solutions, not a great challenge to display the KPIs, but choosing those KPIs is a hard and time-consuming task.

Build business performance kpi dashboards from scratch!

There can be mapped multiple departments and significantly related business processes within a company. These days we can apply 50+ indicators at a moderately sized company. We would like to show everybody the world of performance measuring with the help of the articles of the KPI category.

A performance dashboard can be an effective tool in reaching business goals. Was this enough reading for you? KPI Dashboard allows you to see trends and key performance indicators. It’s an awesome tool for creating visual reports. The goal of this article is to show you how to build better reports.

As is usual key performance indicators can be linked to three different statuses.

If the indicator is red, we know at once that something is not going well with the examined process, and we have to intervene right away.
The yellow sign alerts us that – although we are not too far from the plan – the reviewed procedure, in our case, the marketing, needs increased focus.
The green light needs no explanation when we see this on the dashboard than all is in the best order.

How to Create an Excel Dashboard?

Our primary goal is to show the main key performance indicators and provide a comparative view of other KPIs or products using interactive visual elements.

In this guide, I will show you how to create kpi in Excel 2013 or above. The dashboard is fully compatible with Office365 for Excel. The Workbook is an updated and improved version of a dashboard that I created in 2016. The spreadsheet is compatible with Excel 2010 too.

Let us see how the final interactive sales dashboard looks like:

create-kpi-dashboard-MAIN-DASH

Our excel sales kpi dashboard is divided into four parts by products. It will be easy to compare the main indicators. To measuring performance and creating a sales dashboard is a simple procedure, we’ll apply the good old offset function and conditional formatting. This spreadsheet allows the user to select the dimension of data and shows sales efficiency right away.

The OFFSET function helps to determine the ranking and grouping as soon as possible, and with the help of formatting, we can analyze the high and low differences.

We should rank the highest and poorest performing products to determine which products are failing to boom with your customers.

The three units of our kpi dashboard:

• KPI is the main Excel worksheet; we summarize and visualize the results here.
• Now go to the ‘data’ sheet, here are the main data.
• On the calculation sheet can be found in the calculation area.

1. We have the raw KPI data for 100 sales reps and for 4 products. The data looks as shown below.

kpi dashboard data set

2. First, create named ranges for further calculations. Select the cell or range, in this example, we’ll use both of them. Select the =data!$E$10:$E$109 the range on the ‘data’ sheet.

Go to the Formulas tab! In the Defined Names group, click Define Name. Enter a Name (menu) for the range. Select cell E6 and add a name (sort_order)
See the results in the picture below!

formulas name manager

3. Use the OFFSET function to build a dynamic named range for further calculations.

On the calculations sheet type:=OFFSET(data!$E10;0;sort_order).

Explanation: How to get the correct name from the unsorted list? OFFSET(name of the sales rep from data table; 0 = same row; 1st position)

how to use offset

4. Use the =E10+$C10/1000000000 formula to create a unique list!

sorting data dashboard

5. To get the proper value in a range, you can use the CHOOSE function together with the LARGE and SMALL functions.

=CHOOSE($E$7;LARGE($F$10:$F$109;$C10);SMALL($F$10:$F$109;$C10))

The CHOOSE formula returns a value from a list using a given position or index. For example, CHOOSE(3,”KPI1″,”KPI2″,”KPI3″) returns “KPI3”, since KPI3 is the 3rd value listed after the index number.

The LARGE function retrieves numeric values based on their position in a list when sorted by value. The SMALL function retrieves numeric values based on their position in a list when sorted by value.

sort data

6. To get the position of an item in an array, apply the MATCH function.

=MATCH(G10;$F$10:$F$109;0)

use the match function

7. Now use the OFFSET(data!F$9;$H10;0) to calculate the 1st position for Product 1.

dashboard product 1

8. Jump to KPI sheet! You can insert option buttons to make data entry easier. Option buttons are perfect when you have just one choice.

To add the Option button, click the Developer tab, click Insert. Finally, under Form Controls, click the Option button icon.

kpi dashboard radio button

9. To add a linked cell, right-click the button, and then click Format Control. Jump to the Control tab. In the Cell link box, enter a cell reference that contains the current state of the button.

Use the same linked cell (E6 on the calculation sheet) for Product 1 to Product 4.

dashboard linked cell

10. To assign a macro to the inserted picture (icon), execute the following steps:

Right-click on the picture. Choose the Assign Macro command. Add a macro from the list.

assign a macro

11. The Macro is very simple. I think it’s not necessary to further explanation.

macro for dynamic dashboard

12. You can combine the conditional formatting tool with your KPI settings.

dashboard conditional formatting KPI

13. To manage all conditional formatting rules in this worksheet, use the Conditional Formatting Rules Manager.

format dashboard icons

14. On the Home tab, click Conditional Formatting, and then click Manage Rules. Select the ‘Format all cells based on their values’ rule type option. This is the easiest way to create kpi based conditional formatting.

advanced formatting

15. To insert a slider in Excel, go to Developer Tab –> Insert –> Scroll Bar).

Click on the Scroll Bar button and insert it in the worksheet.

insert a scroll bar

16. Right-click on the Scroll Bar and click on ‘Format Control’. This will open a Format Control dialogue box.

Go to the ‘Control’ tab, and make the following changes to create scrolling list:

  • Current Value: 1
  • Minimum Value: 1
  • Maximum Value: 76
  • Incremental Change: 1
  • Page Change: 10
  • Cell Link: $E$5 on the calculation sheet.

Finally, it’s done!!!

link cell to a value

General rules, advice, and tips

Focus on the essence! The basis of every dashboard is the one-page-layout. Keep it in mind that the user doesn’t always interested in the details. A CEO is usually always busy, having just enough time to look through some KPIs. While the business analyst has to see even the smallest details.

Before you effectively start creating a dashboard, here are some useful tips and advice:

KISS! Keep it simple, sparse. Very important is that we appropriately apply this rule! Don’t jam the dashboard full of unusable charts. This only distracts attention from the essence!
User-friendly interface. What we measure is not the only important thing. Representation is also important. When you create a dashboard, you have to think about others also have to understand it besides you.
Add an alert for KPIs. Apply varied tools! Some great technics are gauge charts, traffic lights, free widgets, bullet charts.
Ability to pull real-time data from several bases. The most frequent data sources are the following: online and offline database, OLAP cubes, pivot tables, text files, internal and external data sources.
Logical structure behind the scenes: most of the mistakes can be filtered out if we work with well-structured databases and data tables. Mostly we use three worksheets for the creation of all dashboards. The first one contains the raw data tables. On the second one, we make the calculations. Finally, on the third one, we display the final excel dashboard
Displays actual data, forecast, or key trends – key performance indicators. The KPI is such a huge subject! Therefore, we devote a complete chapter for the creation of KPI dashboards.

Frequently Asked Question regarding Dashboards

How do I choose the appropriate chart?

This always depends on the given project. Think about what you want to visualize. In the newer versions of Excel, you can use the Recommended charts function.

Which is the best solution for creating a KPI dashboard?

If you only have a few key performance indicators, then a gauge chart is the best. If there are more of them, we recommend the bullet chart.

How to track and visualize budget vs. actual performance?

By all means, use a variance chart, but as an alternative, the bullet chart is suitable.

I am not a dashboard designer, but would like to create splendid presentations. What should I do?

Read on! More of our free tools support beginner users also.

Is it rewarding to use a BI or kpi dashboard?

If the classical excel dashboard is not enough, then yes. The Excel Power BI was created specifically for cases like this!

Case Study: Sales KPI Dashboard using grid layout

Save your time and boost your productivity using a KPI Dashboard. If you want to keep your key performance indicators up to date, we recommend using the template. Multiple reports are not necessary anymore!

<sales funnel dashboard>

You can navigate easily between worksheets using the menu on the ribbon.

This free KPI dashboard template uses a grid layout and allows you to track the most important sales metrics. You can compare the most important indicators and check the monthly and YTD goals. The template demonstrates the business impact of the recommended actions. Decision making gets easier!

We use a sales funnel to get useful information to evaluate a company’s performance. The simplified flat dashboard design provides the best readability for end users. If you want to display a monthly breakdown, the best choice to create a drop-down list.

You can track these KPIs below:

  • Conversion Rate
  • Monthly Goal
  • YTD Goal

Conclusion: Everything wants KPI

Everything wants KPI! These days the business intelligence systems have kpi dashboard support. With their help, we can create performance dashboards in which we can place our most important indicators, the KPIs.

We can give a management tool by which they can oversee the fields under their responsibility; they can see all of its movement and notice right away if it is differ from the balance course.

This is a strong weapon what the Business Intelligence Solution providers do utilize.

They make people believe that if they buy their product, they will be able to create dashboards consisting of these KPIs, and afterward, they have nothing to do but look at the dashboard during the morning coffee and make sure everything is going well.

We, as in charge of business intelligence, help in the systematical gathering of existing KPIs, to text the requirements of the dashboards, unify the KPIs, but in the future, too, it is the task of the management to choose the most important performance indicators.

KPI Dashboard related tools and free resources

  1. How to create a professional kpi dashboard in minutes? Check our gauge chart tutorial and professional chart add-in!
  2. Are you interested in human resource management? Visit the HR Scorecard!
  3. Quick KPI Charts in Excel? Or Waffle Charts? No problem!
  4. Advanced Healthcare Data visualization using performance indicators
  5. Financial Report

Additional resources:

STAY TUNED.