Search code examples
excelpivot-tabledashboardslicers

Excel Slicers based on Column headers

I'm trying to make pivot table slicers based on the following table

enter image description here

it has around 3000 row. When I create a pivot table and then use the slicers. The slicers come up like this enter image description here

while I want to be like this enter image description here

is the a way you can help me accomplish that?

with or without codes, but I prefer without codes.

Thanks

Solution

  • PivotTables don't like Crosstabs (i.e. where column headers run across the top). You need to "unpivot" your data first, which means changing it from a CrossTab to what's known as a Flat File.

    If you've got the PowerQuery add-in installed (or have Excel 2016 or Excel/Office 365 subscription) then you can use PowerQuery to do this. Google "PowerQuery" and "Unpivot" and you'll turn up a whole heap of videos.

    Otherwise you can use VBA such as my Unpivot routine I've previously blogged about at http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/