Slicer that will select a column

By "subject" you mean you want to be able to select Art, Business Studies, Citizenship, etc. I presume. Your data is not set up to support this. The data needs to be "normalized" into a structure that looks like:

School | Gender | PP | Subject | Number

You'll need a program to translate the Master Sheet into this format. I've done this before, but each case is slightly different (depending on how many "fixed" things like School, Gender, PP there are. I can find one of these and adapt the code perhaps.

Since you don't have a quantity I will assume that the information in columns A:H are "fixed" - you don't need to split them out like subjects (Columns H:AJ).

Here is as far as I can take it at the moment. There is a macro called "Normalize Data." It probably takes 5-10 min to run. I did not run it all the way, I only ran about 1/4 of the data and then had to tweak the schools to get a distribution.


I've built pivot tables off this table. Since I have the data organized differently, I can't get the last two pivot tables (% @ each stanine
and Cumulative %) to work. If you can give me a definition of what these figures are supposed to represent, I can either add helper columns to the source data or revise the pivot table to provide the figures.

I thought differently on this on the drive home.

I added a helper sheet called Parameters. It has a list of all the subjects in Column A. I overlaid this with a named dynamic range that is used for data validation in Cell L2 on the Subject worksheet.

When you make a selection in this field, it computes the offset in cell D2 on the parameters sheet.

This offset value is used in a helper column (the very last column on the Master sheet – currently AK) to get the value associated with the selected subject. The column header name for this is “Selected.” I use it in place of “Art” in the pivot table.

When you select a new subject, the offset changes and the values in the Selected column changes.

I had to add a change event on Cell L2 on the parameters page to “force” a pivot table refresh.

The data on the Master sheet is now an Excel table and the pivot tables use it as a data source. If you insert a new subject, the pivot table will adjust to the new range. Make sure you also add the subject to the list of subjects on the Parameters sheet.

There are some minor issues with the pivot tables. I seem to be picking up blanks. Perhaps these can be filtered out. Otherwise the data seem to match perfectly.

Also the HML has blanks instead of “O.”

I’d be interested in your explanation of how you cleaned up these two items.

Let me know if this meets your needs. Unlike a true slicer, you can only select one subject at a time, but I think that’s what you want. It certainly sticks closer to the original functionality and eliminates the need to renormalize the data whenever anything changes.