Pivot table won't sort how I want when more than one field appears on the 'Row'.

Rhino_Dance

Board Regular
Joined
Jul 23, 2008
Messages
74
'Exhibit 1' below is taken from a pivot table. I have two fields in the 'Row' section of the pivot table which are 'Region' and 'Sales_Rep'. My value is 'Revenue' and it appears in the column marked 'Total'. (The numbers on the left are row numbers which can be ignored.) I want to sort the pivot table by descending Revenue. However, when I do this sort, I get a separate sort for each region. I don't want this. I simply want all the rows in the pivot table to appear in descending order by revenue irregardless of Region. What I want is shown in Exhibit 2 below.

After many attempts I have concluded that there is no way to do a simple sort like this when there is more than one field on the 'Row'. Is this true? If so, what are my options in generating the sort that I want while still maintaining my report as a pivot table? Thanks.

Exhibit 1: THIS IS WHAT I DON'T WANT. Sorting within each Region.

BCD
12RegionSales_RepTotal
13ASara$169,856.00
14Rachel$167,989.00
15Chris$161,945.00
16Will$156,770.00
17Jessica$154,323.00
18Rick$152,894.00
19Kevin$152,387.00
20BKevin$175,686.00
21Jessica$164,684.00
22Will$159,550.00
23Rachel$157,616.00
24Chris$153,524.00
25Sara$149,359.00
26Rick$144,315.00
27CChris$191,361.00
28Rachel$188,697.00
29Jessica$179,164.00
30Kevin$169,003.00
31Rick$167,765.00
32Sara$160,515.00
33Will$141,774.00

<tbody>
</tbody>
Pivot2 (2)





Exhibit 2: THIS IS WHAT I WANT. All records sorted by Revenue, irregardless of region:

Excel 2010
IJK
12RegionSales_RepTotal
13CChris$191,361.00
14CRachel$188,697.00
15CJessica$179,164.00
16BKevin$175,686.00
17ASara$169,856.00
18CKevin$169,003.00
19ARachel$167,989.00
20CRick$167,765.00
21BJessica$164,684.00
22AChris$161,945.00
23CSara$160,515.00
24BWill$159,550.00
25BRachel$157,616.00
26AWill$156,770.00
27AJessica$154,323.00
28BChris$153,524.00
29ARick$152,894.00
30AKevin$152,387.00
31BSara$149,359.00
32BRick$144,315.00
33CWill$141,774.00

<tbody>
</tbody>
Pivot2 (2)
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
I don't think one can do exactly what you describe with a PivotTable.

A barrier is that the PivotItems in the top level of the Row Labels (in your example, the top level is "Region") need to remain continguous. Their order can be rearanged, but all the "A's" have to stay together along with the "B's" and "C's".

One workaround would be to create a top level above region that had only one row per PivotItem. An easy way to do that would be to add a PivotField to your SourceData that has the Region and Names concatenated with a unique delimeter.
Excel Workbook
ABCD
1Top LevelRegionSales_RepTotal
2A|SaraASara$169,856.00
3A|RachelARachel$167,989.00
4A|ChrisAChris$161,945.00
5A|WillAWill$156,770.00
6A|JessicaAJessica$154,323.00
7A|RickARick$152,894.00
8A|KevinAKevin$152,387.00
9B|KevinBKevin$175,686.00
10B|JessicaBJessica$164,684.00
11B|WillBWill$159,550.00
Sheet


By making the "Top Level" field the first field in the Row Labels Area, it will allow you to sort by the Total field.

The extra field can be hidden. Not an ideal solution, but I'm not aware of a better way to sort a Pivot Table as you describe.

Another alternative would be to copy-paste parts of the PivotTable and sort as a standard worksheet range..which has a different downside.
 

Rhino_Dance

Board Regular
Joined
Jul 23, 2008
Messages
74
Jerry,
Thanks very much for your help. The idea of sorting on a hidden concatenated field is a great workaround which I will use. The one downside is that since the concatenated field is on the top level of the row, it shares the same column as the filters, so the filters will always be hidden with this method as well. I appreciate your assistance. -John
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
John,

The hidden column should align with the cells that display Report Filter's field names, but not the dropdown for the filter.
You can copy the field names to the right of the dropdown, or even use formulas to reference the hidden cells that have the field names.
 

Rhino_Dance

Board Regular
Joined
Jul 23, 2008
Messages
74
Jerry,
Thanks, that's a great idea. I might also build in a button that allows the user (or me) to hide and unhide the columns while doing this. I've also thought of a macro that takes the data from the pivot table, copies it to a new sheet and performs the desired sort. I appreciate your help.-John
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,719
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top