Excel Question - Sorting Pivot Tables
Discussion
Just thought I'd throw this out there to see if anyone knows of a solution to getting Excel Pivot Tables to sort on the Values fields?
Example data looks like this:
This is the basic structure of the data, but the reality is it's made up of a load of tables, some with hundreds of thousands of rows, so the only practical approach is to add these tables into Power Pivot / Data Model, link them with relationships and use Pivot Tables to create the tables that my users are interested in. As such I can't just do it this way.
When I draw the Pivot Table it looks like this:
I've done this using the individual fields and with a hierarchy of those fields, the result is the same. The issue I have is that I can't sort the salary column because Excel can only sort within the hierarchy and that column is a Sum, so there'll only be a single value there for each combination within the hierarchy.
Not sure if that makes complete sense, but essentially it will sort the Regions, it will sort the Office within the Regions, it will sort the Employees within the Office within the regions and the numbers must follow that.
In this (and most other) cases the thing of interest is the values, the structure around it is artificial, though different people are interested in different summaries as the hierarchy is folded up.
The effect is that the only column that can be sorted in order across the entire dataset is the first one.
This is the view I want:
But I want Salary to be the last column, because that is what makes sense to my users and because I want to retain the option to sort on an element within the hierarchy of geography and then collapse the Pivot to summarise within the hierarchy.
There is no obvious reason why Excel can't do that depending on how the data is sorted. But it won't.
I'm starting down the barrel of a VBA solution to flick the pivot between the two layouts to give the choice of how the data is viewed, which is easy enough but it shouldn't be necessary. I'm processing a million rows of data that links up to two other tables so reconfiguring the pivot is much much slower than changing the sort order. I'm hobbled with 32bit Excel so I'm much closer to the limit than I should be with this, so stability is an issue as well.
I'll bodge it, but hopefully there's something hidden somewhere that I've missed!
Example data looks like this:
This is the basic structure of the data, but the reality is it's made up of a load of tables, some with hundreds of thousands of rows, so the only practical approach is to add these tables into Power Pivot / Data Model, link them with relationships and use Pivot Tables to create the tables that my users are interested in. As such I can't just do it this way.
When I draw the Pivot Table it looks like this:
I've done this using the individual fields and with a hierarchy of those fields, the result is the same. The issue I have is that I can't sort the salary column because Excel can only sort within the hierarchy and that column is a Sum, so there'll only be a single value there for each combination within the hierarchy.
Not sure if that makes complete sense, but essentially it will sort the Regions, it will sort the Office within the Regions, it will sort the Employees within the Office within the regions and the numbers must follow that.
In this (and most other) cases the thing of interest is the values, the structure around it is artificial, though different people are interested in different summaries as the hierarchy is folded up.
The effect is that the only column that can be sorted in order across the entire dataset is the first one.
This is the view I want:
But I want Salary to be the last column, because that is what makes sense to my users and because I want to retain the option to sort on an element within the hierarchy of geography and then collapse the Pivot to summarise within the hierarchy.
There is no obvious reason why Excel can't do that depending on how the data is sorted. But it won't.
I'm starting down the barrel of a VBA solution to flick the pivot between the two layouts to give the choice of how the data is viewed, which is easy enough but it shouldn't be necessary. I'm processing a million rows of data that links up to two other tables so reconfiguring the pivot is much much slower than changing the sort order. I'm hobbled with 32bit Excel so I'm much closer to the limit than I should be with this, so stability is an issue as well.
I'll bodge it, but hopefully there's something hidden somewhere that I've missed!
This is just a simple example of a common problem.
The users want to be able to see all of the hierarchy (so they can collapse it up to summarise) and to be able to sort the data using the fields that are in Values. Values can contain a number of fields and some times they do need to be summed up or counted, so putting those fields into Rows to enable the sort isn't really a solution.
I can create accompanying fields when I process the data to create the sort order in some cases and I can use VBA to change the Pivot Table contents to allow the sort to be changed, but this is a pain in the arse when there should just be an option to sort on the Values column.
The users want to be able to see all of the hierarchy (so they can collapse it up to summarise) and to be able to sort the data using the fields that are in Values. Values can contain a number of fields and some times they do need to be summed up or counted, so putting those fields into Rows to enable the sort isn't really a solution.
I can create accompanying fields when I process the data to create the sort order in some cases and I can use VBA to change the Pivot Table contents to allow the sort to be changed, but this is a pain in the arse when there should just be an option to sort on the Values column.
mmm-five said:
How about just putting the name first - as that's what the Salary relates to, and will make the sorting easier.
The salary relates to the name, which relates to the office, which relates to the region, they're in the right order and changing the order loses utility elsewhere.My question is whether there's something hidden away that gets around this issue or whether I just have to suck it up, bodge it and put more energy into getting corporate to let me use PowerBI instead.
OlonMusky said:
Just add the Salary as first field in Rows and keep it in Values as Sum then sort on the first column not on Values. That's the only way to do this.
That's what I've done, but it's rubbish and doesn't work when I have multiple values and people want to sort on several columns. As an example, I have some stuff with trend analysis that is ranked and people want to be able to work with both absolute changes and percentage changes because both are equally useful or relevant. I'm doing that by using VBA to change the column order, but its a bodge and we're not really allowed VBA anyway.
If it can't be done, it can't be done, I was just hoping there was a ninja out there and something I've missed.
paulrockliffe said:
That's what I've done, but it's rubbish and doesn't work when I have multiple values and people want to sort on several columns. As an example, I have some stuff with trend analysis that is ranked and people want to be able to work with both absolute changes and percentage changes because both are equally useful or relevant.
I'm doing that by using VBA to change the column order, but its a bodge and we're not really allowed VBA anyway.
If it can't be done, it can't be done, I was just hoping there was a ninja out there and something I've missed.
Open up a new sheet. Link to each cell in the pivot table. Sort to your hearts content. Not pretty but works.I'm doing that by using VBA to change the column order, but its a bodge and we're not really allowed VBA anyway.
If it can't be done, it can't be done, I was just hoping there was a ninja out there and something I've missed.
Jinx said:
paulrockliffe said:
That's what I've done, but it's rubbish and doesn't work when I have multiple values and people want to sort on several columns. As an example, I have some stuff with trend analysis that is ranked and people want to be able to work with both absolute changes and percentage changes because both are equally useful or relevant.
I'm doing that by using VBA to change the column order, but its a bodge and we're not really allowed VBA anyway.
If it can't be done, it can't be done, I was just hoping there was a ninja out there and something I've missed.
Open up a new sheet. Link to each cell in the pivot table. Sort to your hearts content. Not pretty but works.I'm doing that by using VBA to change the column order, but its a bodge and we're not really allowed VBA anyway.
If it can't be done, it can't be done, I was just hoping there was a ninja out there and something I've missed.
Excel won't work with a second copy of the data in the file anyway, as the usual on-screen whatevers it does in the back ground can't cope. Crash central. Even worse is you're running formulas and then asking them to recalculate.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff