Getting the data right for a dashboard

Dashboards will only work when the requirement is understood and the underlying data is as right and clean as can be. This is a lot harder to achieve than it should be.

Why is this such a tough process? Our reasons are:

  • Lack of initial planning
  • Mixing up of data (will explain later)
  • Allowing users to enter anything they want
  • Users ignoring the rules of the spreadsheet
  • Sharing of spreadsheets

Whether your data is

  • Out of a formal system, or
  • Created by you

You need to follow/ implement some basic rules

Step 1- Plan FIRST

This is the most important.

A meaningful dashboard is hard to create. You can’t just take a database and summarise it. You need to think about what you are trying to show.

More importantly it is what your boss wants to see.

Sit down with the parties involved and ask them what they want. Go even further and make them draw the charts. It is easy to say you want a chart of actual versus budget. But make them draw it. Then you will see that what they mean is actual vs budget, in months, on a year to date basis.

There is nothing like making the boss draw some dashboards for them to realise that it is not a simple process.

Step 2- Get the Data

Once you have the plan, and only then, do you actually know what data you need.

If the boss asked for a revenue report, but when they drew it they showed volumes AND sales amounts on the same chart, you now know you need financial and non financial information.

The plan must come first.

Step 3- Investigate and clean up the data.

When you get the data, you will discover that there are problems with it. Ideally you correct in the source system, but sometimes this can’t be done so you need to correct it.

As a rule, the following typical issues should be enforced/ corrected:

  • Columns should contain one type of data i.e. date, number or text. Having a column that looks like below will make it very hard to create reports. In ┬áthe same column we have both whether we got the job and how much it is worth.

Get the Data Right

 

  • As much as possible limit the number of choices available within a column. Below we have columns where the same names are spelled differently or a simple Yes/ No is answered in lots of different ways. When you try and create the dashboard, the viewer will have to further summarise and know the the Yes’s and the Y’s are the same thing

Get the Data Right

  • Avoid building in subtotals within the data
  • Make sure that dates are seen by Excel as actual dates. Most dashboards have a date element to them. If Excel is given valid dates it can work wonders. But if the date is not Excel acceptable then you have lots of work coming. Below we have the delivery dates entered in many different ways and in one cell we even have 2 dates in it.

Get the Data Right

  • If you have too much data to be meaningful, categorize the data in some way to make it visually more presentable. So below if we showed the exact age of everyone in the work force the report would not add value. However, if you categorize the ages into useful bands you can now get a clearer picture of the workforce. If you want the detail go look at the report.

Get the Data Right

Back to: Excel Dashboards and Management Reporting > Getting the data right for a dashboard