Purpose of a good dashboard and an announcement

Here comes a big announcement:

So you don't want to buy a pig in a poke? Okay, that's pretty understandable. Below you will find a preview of the file as well as the final dashboard for you to download, if you want to have sneak into it. Here comes the picture (click to enlarge):

I have to say a big thank you to Chandoo, blog master of Pointy Haired Dilbert, one of the best MS Excel blogs out there. The data and structure came from his Visualization challenge #2 (see the response from the participants here and the winners here) and he allowed me to use it for this example. Again, a big THANK YOU for this excellent blog (subscribe to his updates if you want to watch over the shoulder of a visualization and Excel guru - hint, hint ).

Before we come to the contents of the series and the general overview what makes up a good dashboard, the result will be explained a little bit more in detail. It consists of four areas altogether:

The header area

This includes the corporate information (like the company name and the logo) and field, where you can set your focus to any range of the data you would like to have a closer look on. You can determine the revenue by any of the five structural elements that are provided in the raw data: Region, product, customer type, sales person and timeframe.

The management requirements

It has been assumed, that the management of Palpatine Inc. (yes, an empire needs to be managed as well ) had some special requirements: The wanted to see the share of products and customer types per region and overall. This is what is displayed in the left area of the dashboard:

Sales person overview

This area of the dashboard integrates and interactive interview of product sales in $ per sales person. You can select any (or all) of the four sales persons and the corresponding part of the chart is highlighted.

Regional sales per timeframe

This section of the dashboard allows you to choose any of the four regions (East, South, West and North) and display the values for the selected year (2008 and 2009):

 So what will be the contents of the 14 days to dashboard mastery? Well, it consists actually of three parts:

• Part 1 - Things that you need to know (day 1 - day X): In this part you will get an overview what you can expect with a detailed explanation of what will be covered in each single day. You will also get a review of the rS1-method and why it has been chosen to create the dashboard. We will also together analyse a PDF explaining the rS1-method in detail (Thanks to Reinhold Scheck, the author of this method, for providing us with this document). The last two days will cover the functions we will use over and over in the calculation model.
• Part 2 - The basic approach (day x to day x): In this part, we will begin to set up our document based on the rules provided by the rS1-method. We will also enter functions and combine things we have learned in part 1 to get the first results.
• Part 3 - Finalizing the complex calculation model (dayx to day x): In this part we will start to build our final chart, enhance it with functionalities and put everything from the firs two parts together. We will also talk about necessary steps to protect your spreadsheet file from unwanted changes so it could be used as a presentation tool for our management.

After reviewing what will be in the 14 days to dashboard mastery "package" let us head over to the seconde part of this article:

What makes a good dashboard?

From my experience there are currently three factors that are essential for good dashboards. The picture below shows these factors in an easy to understand buzzword:

This stands for

• Flexibility = easy to update
• Usability = easy to use
• Readability = easy to read / understand

Of course this is no order, but it makes a better buzzword  From most of the users perspective, the readability comes first, usability next and flexibility last. Let us get into a little more detail what is meant by these three guidelines. Let us start in reverse order.

Readability or why your dashboard should be easy to understand

Let us have a look on a dashboard design, which has been reproduced from a software product claiming to produce nice web based dashboards. To b honest, this is not the best way to display data in a dashboard. We will analyze this piece and then you will be shown a better way to display this data. Please note, that this of course is all thing of personal likings. It may be, that you are asked to produce exact this kind of dashboard. Nevertheless you should try to convince them with a clearer design. So here we go, this is the dasboard, we want to improve (I will not explain the steps but analyze what is wrong with the current design and why the updated design is maybe better):

What do you think? My personal opinion is, that this chart is simply overcrowded with elements that a) do not belong into a chart and b) could be displayed clearer with a different approach. Here comes the improvement:

With this approach we liberated the chart of all these bubbles and text boxes. Usually the chart itself should (more or less) stay on its own, texts and breakdown of the result should be placed somewhere outside the chart. Another improvement for this chart would be to use even less colors Utilizing markes, that are reused in the analysis section below the chart helps to recognize to which point the statement refers. Of course this is only one chart and not a complete dashboard but in the original display it made up two thirds of the dashboard space.

This of course also means that you should not put too much data or information in a dashboard. Referring to the above mentioned example this would mean that if your boss asked you to display the average call handling time including best and worst values the three events should be erased from the chart. Why displaying information that you have not been asked for? 

Usability or why even dummies should be able to use your dashboard

Although the word "dummies" in the heading above is a little bit rude, it is essential that if you decide to offer interactivity to your dashboard (which we will cover in the article series in detail) everyone should be able to use this interactivity without reading endless explanations or introductions. Just remember that - especially when it comes to dashboarding - you may have an audience that only has basic skills in Excel. Not everyone is a data analyst or controller able to push the spreadsheet software to its max. Their focus simply is different. E. g. a manager usually have limited amount of time because of the pure amount of tasks they have to handle. Most of the time they are not willing to read the introductory sheet to learn how to use this checkbox, the slider etc. The creation of a dashboard is not the end in itself. It is made for others to give them a brief overview over KPIs they decided they wanted to see.

Let's have look on an example:

Well, it's nice to have this dropdown box but there are some questions that immediately occur in my mind:

• What will those colors tell me?
• What should I do with the dropdown? Will I have to find myself? 
• What if I reopen this file in four weeks? Will I ask the same questions again?

The solution is so easy and really easy to implement:

You have spent some time to create the file to get this nice interactivity into the chart so why not spend to minutes more to write some explanatory text?

Flexibility or why it is good to think about the future

The song for this third and last rule of good dashboarding could be Keane's "Crystal Ball" (if you don't know Keane, go and have websearch, it's an amazing british band!). It's all about planning your dashboard in a way that you can understand it when you reopen it after several months. The above mentioned rS1-method is a very good approach for this. The method works a lot with names for ranges which make the references more "speaking". Which of the following two functions is easier to read?

=Sheet1!$B$5-Sheet3!$C$24

or

=rB1.Revenue-rB1.costs

Assuming that you are not a masochist I bet you chose the second one, right? Within the rS1-method we will also have a sheet called "names" to which we always can refer if we do not know, what range is selected by a name.

Another approach also explained in the rS1-method which increases flexibility is the iintesive usage of functions like INDEX and VLOOKUP/HLOOKUP. This allows the system to return data that has met several conditions and makes it quite easy to return values. This is much easier to only once change the condition later on instead of replacing formula over formula.

After all these discussion about the article series and what makes up a good dashboard here is the final document we will create for you.

Download the final dashboard file (Excel 2007 only at the moment, ZIP, 131 kB)

So this concludes the outlook to the things to come and also my personal approach in creating dashboards. If you want to stay up to date, don't forget to sign up to my newsletter (and receive my VLOOKUP mini series PDF for FREE).

Discuss this blog post in the forums (click the blue button)