Pivot table filter pane is not sorted. Why?
When you initially create a PivotTable, the default sort order is A to Z for text, or small to large for numeric:
When you add new data and refresh, the new data is not sorted in the PivotTable, but instead just gets added to the end of the older sorted data, in whatever order it appears in the source Table:
...and ditto for the order in the Filter dialog:
...but you can fix this by clicking the Sort Smallest to Largest or A to Z sort options in those previous screenshots, after which new items should be sorted appropriately:
Related videos on Youtube
Comments
-
ZygD over 1 year
In my pivot table filter, the column containing numeric values is not sorted, while the corresponding column in my data table is nicely sorted. Why?
Data:
Original pivot:
I recreated the same table in another sheet.
Recreated pivot:As can be seen, it is now sorted numerically. What caused the sort order in the filter pane of the original table to get distorted (the order is neither numerical, nor alphabetical)?
-
ZygD about 6 yearsPlease take a look at the last picture. It doesn't seem like alpha ordering either. 0.47, 0.03, 0.23, 0.74, 0.45...
-
teylyn about 6 yearsThe message here is that the filter pane will not use numeric sort order. The filter pane has previously shown some bugs, but they were fixed. Maybe this is a new bug. Can you share a sample file?
-
ZygD about 6 yearsBut also, this does not seem true that the filter pane does not use numeric sort order. I recreated the same pivot table from scratch. It is ordered numerically. Then why is my original table sorted differently stays a mystery.
-
ZygD about 6 yearsYou may have found the cause. I did add more data to my data table after I created my pivot tables. However, I cannot fix this by sorting the data. You give screenshots of "Row Labels", while I would like the filtering panes to be sorted, not the data in the worksheet, not pivot table data.
-
ZygD about 6 yearsThank you, now I know the reason! This leads to the following question: How to make filter panes in pivot table sorted?