Creating a dashboard over a multiple data set

[njw499]

I am trying to work out how to create a dashboard that allows me to show me trends over time, but enable me to select a subject to just see on the graph, rather than all 14. So for example i want to be able to select maths, and then see a trend line for school1, for school 2, for school 3, and so on, across the 3 assessment points. Have attached the sample data, but not really sure where to begin.

[dflak]

First, you need to normalize the data. The general theme is "go deep rather than long." I did this for you on the Normalized sheet. I also made this into an Excel Table because they have many benefits, including "knowing" how big they are so that when a pivot table uses them as a data source, all you have to do is refresh the pivot table whenever the data changes.

Then I made a pivot table from the normalized data. It looks ugly and I could have put it on a separate sheet. You could CUT and paste it to another sheet if you wish.

I made a chart from the pivot table and added a couple of slicers. Slicers are fancy filters. Click in the pivot table and select Insert > Slicer - the rest is intuitive.

[plot-ai]

Would something like this be useful? This is built using an addin though. Attachment 814503sample dashboard.png