I have a huge amount of data (200k+ rows and 10+ columns), compiled in this format:
Month Date Time Value1 Value2 Value3
Jan-18 1/1/2018 12:00:00 AM 10 20 30
Jan-18 1/1/2018 12:01:00 AM 13 23 33
etc...
I've used PivotTables and PivotCharts with slicers to allow my charts to sort the data according to months. However, I want to be able to choose which column to show, for example: slicers which allow me to show Value1 or Value2 depending on my choice.
A method which worked for me was to compile the data in this manner:
Month Date Time Type Value
Jan-18 1/1/2018 12:00:00 AM 1 10
Jan-18 1/1/2018 12:01:00 AM 1 13
Jan-18 1/1/2018 12:00:00 AM 2 20
Jan-18 1/1/2018 12:01:00 AM 2 23
This allowed me to sort with slicers according to type. This is not a very viable option as data is constantly added and is just a pain to do so.
Is there a better way to do this? I've searched around for this but could not find a solution, would appreciate any input.
Cheers!
Yup, you can use the VBA I've previously posted at Converting multiple variables into values with excel pivot tables or power pivot to do that. It requires you to make up a 'harvester' PivotTable containing just the columns you want to swap out, so that you can then create a slicer from which to trigger the code. But I'm pretty sure this will let you do exactly what you want.