To assist with what initially should be in the dashboard, I've done a heatmap - fairly basic - but all I can think of at present.

Hi Sue,

You're having problems because your data isn't in a tabular format, so it's difficult to summarise and analyse.

First, you need your staff members to have a unique identifier. At the moment you have duplicate names in column A for different people. You need to be able to prevent Excel from aggregating these names into one person.

Then you can use Power Query to unpivot the data into a tabular format so you can analyse it in a PivotTable.

In the attached file I've unpivoted your data with Power Query (note there are errors in the data because some date cells don't contain dates, you'll have to clean the data), then used a PivotTable to analyse the data as an example of just one thing you can do.

I hope that points you in the right direction

Mynda

Thanks Mynda

I think my other problem is I don't fully understand the whole concept of dashboards, pivot tables, tabular formats and other Excel basics I need to know about. 

Thanks for the information and the unpivoted table. 

The reason there are duplicate values is that I removed staff last names to de-identify them.  For instance/example there are 2 Lynn's and two Denise's - with different last names.  I had not thought about that being an issue for this process, I did not go through to put other last names as it would have taken too long to do.  Even if I had removed the first names, there are still a number of staff with the same last name - so however, I had done that it would have given the same issue.

The reason there are date cells with no dates, is that the staff have not done the education, so not sure how I manage that - that is one aspect of the data that I want to capture - i.e. who has not attended which education and I'm not sure if that's something that this can do.

Thanks for the heading in the right direction.  Not sure if you can assist with the needing to get information about who has not attended which education?  Or do I use data sort - or am I expecting too much from Excel?

Any other tips you can provide about my rambling would be appreciated.

Regards

Sue

Hi Sue,

I should have been clearer, it's not the empty date cells that are the problem, rather that there are some cells that contain values other than dates. These must be cleared.

You can then filter the PivotTable for empty date cells to see who hasn't completed the training. 

Mynda