Pivot Table will not properly sort value="SEP" properly

RBo

New Member
Joined
Jan 4, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Whenever I create a Pivot Table over a worksheet or table that includes the value "SEP", and include the column containing the "SEP" value in my Pivot Table, the "SEP" value automatically sorts to the top. Even if I change the pivot table column to sort ascending or descending, SEP remains at the top of the Pivot Table.

Row Labels
SEP
AAG
AC
ACL
ADC


I have tried changing the source table or range column format from General to Text, and also did the same with the column in the Pivot, and those changes have no effect. This is not a new issue in Pivot Tables -it has been this way going back to Excel 97. I am relegated to manually moving "SEP" into the proper alphabetical sequence every time I create a Pivot Table containing this value...and since SEP is one of roughly 300 product line values, its in nearly half of the Pivot Tables I create.

Any workarounds for this?

Interestingly, the source table itself sorts SEP values correctly. Also, if I add my source table to a data model SEP sorts properly in a Power Pivot.

I assume Microsoft has something in their pivot table code that treats SEP as a short Month date value (MMM), but I am only making an assumption.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,292
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Right-click the pivot, choose Pivot table options, then on the Totals and Filters tab uncheck the option to use custom lists when sorting.
 

Forum statistics

Threads
1,175,527
Messages
5,897,942
Members
434,688
Latest member
vi28

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