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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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