Hide the helper field for sorting in a pivot table

user_702977 0
Viewed 275

I have a table in B4:D8. I would like to create a pivot table on it, and sort the people by descending order of NoteA then descending order of NoteB.

As we can only apply one sort condition in a pivot table (correct me if i'm wrong), I need to define a field SortIndex=NoteA*1000+NoteB, then sort the pivot table by descending order of SortIndex.

The data is indeed sorted, but one disadvantage of this approach is that it seems that we have to show Sum of SortIndex in the pivot table.

Is it possible to not show it?

enter image description here

pivot-table excel
2 Answers
user_69082820

Maybe this workaround can help you out.

First, instead of using a calculated field on your pivot table, add it to your data source.

I added a column named RANK with this formula:

=VALUE(RANK(C5;$C$5:$C$8;0)&RANK(D5;$D$5:$D$8;0))

enter image description here

Second step would be creating your Pivot Table, which will order from A-Z by default, so you need to take fields RANK and NAME into fields section and then NOTEA, NOTEB into values section:

enter image description here

Now just hide column rank. It will be there and Pivot Table will be sorted properly because it's the first column and you can hide it and work easier.

I know hidding is dirty but could help you out in this case because you are hidding the FIRST column of your data so it not should be a problem to work with it:

enter image description here

Please, nothe this will work only if there is no name with same NoteA and NoteB. In case there could be duplicates, you need to update the formula at RANK field. You could add the name, or other criteria as you wish.

user_91998280
Related