Excel 365 - Keep Pivot Table data in same order as data source

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
297
Office Version
  1. 365
Platform
  1. Windows
Say I have some data that looks like that in the first image (simplified here of course!)

If I create a pivot table, it automatically sorts the region into alphabetical order (second image) even though the pivot table setting is set to "sort in data source order" (third image).

I know that I can drag the rows into the required order and for something as simple as this, that's what I'd do, but my actual data has a lot more rows, and the values in those rows could change depending on the data being extracted for the system (same overall layout, but differing values - so one extract might have data for North/South/East/West, but another might have Alpha/Bravo/Charlie in the "region" column)

Is there any way to stop a Pivot Table from sorting data and to return it in the order that it appears within the source data?
 

Attachments

  • img01 - data.jpg
    img01 - data.jpg
    33.8 KB · Views: 23
  • img02 - pivtable.jpg
    img02 - pivtable.jpg
    17 KB · Views: 22
  • img03 - pivsettings.jpg
    img03 - pivsettings.jpg
    34.1 KB · Views: 22

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,223,923
Messages
6,175,404
Members
452,640
Latest member
steveridge

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
Back
Top