New Items at End of Pivot Table Drop Down Lists
While working in the pivot table source data, you might add new items. For example, if you start selling a new product, that product will appear in the list of orders. If your pivot table is based on that list of orders, the new product will also appear in the pivot table, when you refresh it.
Sometimes the new items appear at the end of the drop down lists, instead of in alphabetical order. In this list you can see that Paper Clips are at the end, but should follow Paper.
To fix this problem, you can sort the field where the new items appear. In this example the new items are in the Product field, so we’ll sort that field.
Sort the List in Excel 2007
- In the pivot table, click the dropdown arrow for Row Labels.
- If there are two or more fields in the Row Labels area, click the drop down arrow at the top of the menu, and select the field that you want to sort. (The Select Field drop down doesn’t appear if only one field is in the Row Labels area)
- Click on More Sort Options…
- In the Sort dialog box, under Sort Options, click Ascending (A to Z), and select the field name from the drop down list.
- Click OK, to close the dialog box.
Sort the List in Excel 2003
To fix this problem, you can sort the field where the new items appear. In this example the new items are in the Product field, so we’ll sort that field.
- In the pivot table, right-click on any item in the field that you want to sort.
- In the pop-up menu that appears, click on Field Settings
- In the PivotTable Field dialog box, click the Advanced button
- Under AutoSort Options, click Ascending
- Click OK, to close the dialog boxes.
_____________________
For more information on Pivot Tables, please visit Pivot Table Topic Index on the Contextures Website.
_____________________
Thanks for a great tip. This has been bugging me for ever.
I searched everywhere for a solution to this issue bothering me for quite some time. Thanks a lot, this greatly saved my time.
In exchange, here is a short macro that automatically sorts in ascending order all the fields visible of all the pivot tables present in the active sheet.
Sub pivotFieldSort()
Dim customPivot As PivotTable
Dim pField As PivotField
On Error Resume Next
For Each customPivot In ActiveSheet.PivotTables
For Each pField In customPivot.PivotFields
If pField.ShowingInAxis Then
pField.AutoSort xlAscending, pField.Name
End If
Next pField
Next customPivot
End Sub
I searched everywhere for a solution to this issue bothering me for quite some time. Thanks a lot, this greatly saved my time.
In exchange, here is a short macro that automatically sorts in ascending order all the fields visible of all the pivot tables present in the active sheet.
Sub pivotFieldSort()
Dim customPivot As PivotTable
Dim pField As PivotField
On Error Resume Next
For Each customPivot In ActiveSheet.PivotTables
For Each pField In customPivot.PivotFields
If pField.ShowingInAxis Then
pField.AutoSort xlAscending, pField.Name
End If
Next pField
Next customPivot
End Sub
Can I do the same thing in Excel 2002??
Question 2… How can I remove old Products from the Product Field when I no longer want them to be listed in the Drop Down List?
Question,
when i refresh my data source that contains new data it appears in the pivot table as described above, however it appears unchecked and i have to manually check the new data.
That is ok if you only have one table, but i have lots producing different result
PLease help
How does one sort items in the drop down in the Report Filter when new data has been added? I have a Customer field in the Report Filter. My Row labels contain products.
Thanks
Lynn
@ lynn,
I couldn’t find a way either, move the field from “Report Filter” to “Row Label”, then right-click on the new row label and select the desired sort option. Then move the field back to the “Report Filter” area that seems to work OK
@Roger,
Thank you for your input!
How can I avoid having new items on the Scroll down list??