Simple KPI Dashboard using Excel

Any Tom, Dick and Sally can make things complex. It takes guts and clarity to simplify things. That is why I was pleasantly surprised to see this dashboard prepared by Michigan State. You can see it below:

Linda, one of My Excel School students shared this dashboard link with me and asked if I can show how to construct something like this. Here is my version of the dashboard.

While it does not seem so, it is the Step 1 that takes a lot of time and hard-work.

Step 1: Defining the dashboard metrics, layout & vision

This is the most time consuming part of any dashboard. There is no one way to do this step. So I am going list a set of guidelines for you to follow.

Speak with your audience & define what they want: For any dashboard, you will have some audience. So speak with them, understand what their information needs are. List down everything they want to know. Some parameters you want to consider are,

Understand the sources of data: Another tricky part of a dashboard development is to get right data. In corporate environments, your data sources may be spread across and follow their own formats. So you need to plan ahead for all these differences, otherwise, you will end up doing lots of extra work.

Prioritize the information: Once you have listed down various metrics, KPIs, information pieces to be used in the dashboard, list them down in the order of priority. Metrics or KPIs that are most important and indicate the overall health of the system (or project, initiative or company) should be on top.

Remove ruthlessly: Now comes the tricky part. You must remove items, metrics and information that is low on value from the end dashboard. This is where your persuasion, negotiation skills come in handy.

Make a rough sketch of the dashboard: Even before you make something in Excel, just make a rough sketch using pen and paper (or MS Paint or PowerPoint). This way, you can validate the design with end users and get buy-in early. (related: use excel for screen prototyping)

There are more ideas and tactics you can follow. But if you follow the above guidelines, then 80% of your work is done.

Step 2: Designing Excel Dashboard

This step becomes easier once you have clarity of vision and listed down what you want (and what you dont want). And if you find this difficult, there is always help.

In this, let us learn how to construct the particular dashboard you see above.

Arrange the data: For a simple dashboard like this, you can arrange the data in this fashion.

Create Dashboard Layout and Load data: Once the data is in-place, create a blank layout. You can follow any template. I liked the Michigan State Dashboard template and created something like that.
Once the layout is ready, link to the source data (using Copy & Paste as links).

Download Simple KPI Dashboard Workbook

Special thanks to Michigan State website for the inspiration & Linda for sharing the link.

Do you like this dashboard?

I really liked the simplicity of this dashboard. The Michigan state government folks have done fine job of listing down the metrics and carefully capturing them and presenting the outlook in a crisp fashion.

What about you? Do you like the ideas shared in this article? How would you approach a dashboard project? Please share your tips & ideas using comments.

Want to Learn Dashboards? Go thru these resources:

If you want to learn dashboards, then you have come to the right place. Click thru below links to access a ton of information, ideas & material.

20 Responses to “Simple KPI Dashboard using Excel”

i think that it would be so easy to build in additional information to allow someone to make sense of the numbers... and that in the rebuilt Excel dashboard you should have. for example, the 24.7 to 24.6 change is that real or just statistical vagary? Why does it get a down arrow, why not unchanged arrow? Under quality of life, population growth of the indicated age demographic... what triggeres up/down arrow designation - change between periods or the fact that both are negative? maybe it would be good to have less of this demographic group. the "rank" makes no sense at all... why is it applied to some but not all measures? what does rank mean? what are the time periods?

A good dashboard is about telling a story. It is about accurately communicating what has happened, and maybe even provide a reason for why. A dashboard should prompt more questions than it answers, it should have enough detail to provoke discussion, yet not too much to put people off. A tall ask I here you say? Definately doable, and Chandoo my man, I think this is a really good example of a simple, yet effective dashboard. I have always been a fan of KISS (Keep It Super Simple) and this applies every so much to the world of dashboards. Like you say, it is imperative to understand what you're audience expects and wants to see, and that means talking to them! Mind you, we have just learnt about the range.speak method in VBA classes, so maybe I can talk to my dashboard recepients via VBA....!!!!

Dave - The special characters can be created using the Character Map in Windows or by learning the keyboard shortcuts (displayed in the corner of the Character Map). Alt+0241 is the keyboard code for an "ñ".

@Dave... I just inserted these symbols in to a cell, then selected the cell contents (F2, select all) and copied them. Then I went to the formula cell and pasted them and moved around.

@Vicki.. good tip on ALT code 🙂

@Bill.. They do have detailed views of each of these sections on the Michigan State website (here: http://www.michigan.gov/midashboard ). We can easily implement such drill-down functionality in this dashboard and redirect viewers to other sheet tabs.

Whenever I am given what seems to be an impossible task in Excel, the first thing I do is go to Chandoo.org. Again! and Again, you have provided just what I needed! You are so right that this template is beautifully "Simple".

Since the requirement for our Executive Staff is by Group Location, I added a Drop Down List Validation Box with VLOOKUPs to view the data by Group. It was easy to also add Conditional Formatting to the Progress Indicators.

I can't thank you and the originator of this Dashboard enough for sharing and helping me to continue to build my Excel Skills.

Hi really nice work.
but correct me if I am wrong here. is Prior and Current flipped?
because if I have 10% in Prior and 50% in Current then it is showing down arrow instead of up.
even the arrow indication showing wrong down says Performance Staying about the same

Zuber - you need to understand the measure to know whether up is good or bad. Personal Income going up is good, crime rate going up is bad. The arrows are an indicator of good and bad or as the column heading says "Progress".

This article is simply amazing. I love your ideas on weight loss. typically it just takes some effort and the right diet schedule/ know how. i'd be interested in link to your article from my FB page as i think it will serve my audience well. I would not be alble to induce my purpose across as well as you most definitely did here. Thanks again.