Group Data into useful Bands

Free Preview

Another common dashboard problem is that there is too much information. How can we summarise the data into a format that can be charted?

To follow along go to 3.10

In the example below we are looking at the ages of employees. But everyone is different. If we charted this, it would be too much and meaningless.

It might be a good idea to create some groupings within the data. As shown below we can use a small table where we group certain ages together. Now with a VLOOKUP (true version) we can create a column that can be analysed and visualised.

Grouping Data into bands

The other benefit of this method is if you want to change the groupings, you just change the lookup table (red square above) and Excel will do the rest.

You can now create a dashboard based on the age CATEGORY instead of the actual age.

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