KPI dashboard 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 purpose.
The business world for some time now is heading in the direction that we weigh every possible thing. To quote a classic 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!
Table of Contents
- 1 Before Create your first KPI Dashboard in Excel
- 2 What are the most important KPI?
- 3 Build business performance kpi dashboards from scratch!
- 4 How to Create Excel KPI Dashboard? – Tutorial
- 5 General rules, advices and tips
- 6 Frequently Asked Question regarding Dashboards
- 7 Case Study: Sales KPI Dashboard using grid layout
- 8 Conclusion: Everything wants KPI
- 9 KPI Dashboard related tools and free resources
Before Create your first KPI 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 than it will be easy for us. If we do not than we have to find out every little detail by a preliminary survey.
How many performance indicators are needed? It is very important that you exactly know. If the goal is to track only 5-10 KPIs than it is sensible to use gauge chart. Most of the companies will need a lot more than that. The gauge chart is 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 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 would it lead if somebody opens the file, modifies it than forward it in e-mail. I don’t even dare to think…
Compatibility first! Remember that different version of Excel have different function. If the file was created with an Excel 2016 and someone wants to open it with an 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 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 which one is the 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 hurry.
Improtant characteristics of an effective KPI:
• Current value, namely where we stand now.
• Plan value, namely where we plan to stand now.
• Deviation value, 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 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 we can call it 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 are the most important KPI?
Let’s say that you are an IT designer. 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 be 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 to choose those KPIs is a hard and time consuming task.
Build business performance kpi dashboards from scratch!
There can be mapped multiple departments and significant related business process within a company. These days we can apply 50+ indicators at a moderate sized company. We would like to show everybody the world of performance measuring with the help of the articles of the KPI category.
Performance dashboard can be effective tools in reaching business goals. Was this enough reading for you? Excel dashboard templates allow 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 indicator 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 Excel KPI Dashboard? – Tutorial
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 tutorial, I will show you how to create kpi in Excel 2013 or above. The dashboard is fully compatible with Office365 for Excel. The template is an updated and improved version of a dashboard that I created in 2016. The template is compatible with Excel 2010 too.
Let us see how the final interactive sales dashboard looks like:
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 sales dashboard in Excel is a simple procedure, we’ll apply the good old offset function and conditional formatting.
This template allows user to select the dimension of data and shows sales efficiency right away. This dynamic template let us check sales by consumer type or location and examine values with averages too.
The OFFSET function helps to determine the ranking and grouping as soon as possible and with 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 the calculation area.
- We have the raw KPI data for 100 sales reps and for 4 products. The data looks as shown below.
2. First create a named ranges for the further calculations. Select the cell or range, in this example we’ll use both of them. Select the =data!$E$10:$E$109 range on the data sheet.
Go to Formulas tab! In the Defined Names group, click Define Name. Enter a Name (menu) for the range. Select cell E6 and add enter a name (sort_order)
See the results on the picture below!
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 salesrep from data table; 0 = same row; 1st position)
4. Use the =E10+$C10/1000000000 formula to create unique list!
5. To get the proper value in a range, you can use the CHOOSE function together with the LARGE and SMALL functions.
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.
6. To get the position of an item in an array apply the MATCH function.
7. Now use the OFFSET(data!F$9;$H10;0) to calculate the 1st position for 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 option button, click the Developer tab, click Insert. Finally under Form Controls, click option button icon.
9. To add linked cell, right-click the option 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 option button.
Use the same linked cell (E6 on the calculation sheet) for Product 1 to Product 4.
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.
11. The Macro is very simple. I think it’s not necessary to further explanation.
12. You can combine the conditional formatting tool with your KPI settings.
13. To manage all conditional formatting rules in this worksheet, use the Conditional Formatting Rules Manager.
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.
15. To insert a slider in Excel go to Developer Tab –> Insert –> Scroll Bar).
Click on Scroll Bar button and insert it in the worksheet.
16. Right click on the Scroll Bar and click on ‘Format Control’. This will open a Format Control dialogue box.
Go to ‘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!!!
General rules, advices 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. If you are interested, read our detailed article about main dashboard rules and fundamentals.
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 advices:
• 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 other also have to understand it besides you.
• 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 data bases 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
Q: How do I chose the appropriate chart?
A: 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.
Q: Which is the best solution for creating a KPI dashboard?
A: Good question! This depends on the number of KPIs necessary. If you only have to measure a few of them than gauge chart is the best. If there are more of them we recommend the bullet chart.
Q: I am not a dashboard designer, but would like to create splendid presentations. What should I do?
A: Read on! More of our free tools support beginner users also.
Q: Is it rewarding to use a BI or kpi dashboard?
A: If the classical excel dashboard is not enough than 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!
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 afterwards 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.
- How to create professional kpi dashboard in minutes? Check our gauge chart templates and professional chart add-in!
- Are you interested in human resource management? Visit the HR Scorecard Template!
- Quick KPI Charts in Excel? Or Waffle Charts? No problem!
- Advanced Healthcare Data visualization using performance indicators
Additional resources and Step by step Tutorials
Free Video Tutorials On Youtube
- Our main add-in is UDT. Check the video how it is works.
- How to use webapps to create performance reports (we’ve reached the 300!K video views)