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.

ListOrder01

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

  1. In the pivot table, click the dropdown arrow for Row Labels.
  2. 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)
    ListOrderB01
  3. Click on More Sort Options…
    ListOrderB02
  4. In the Sort dialog box, under Sort Options, click Ascending (A to Z), and select the field name from the drop down list.
    ListOrderB03
  5. 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.

  1. In the pivot table, right-click on any item in the field that you want to sort.
  2. In the pop-up menu that appears, click on Field Settings

    ListOrder02

  3. In the PivotTable Field dialog box, click the Advanced buttonListOrder03
  4. Under AutoSort Options, click AscendingListOrder05
  5. Click OK, to close the dialog boxes.

_____________________

 

11 thoughts on “New Items at End of Pivot Table Drop Down Lists”

  1. 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

  2. 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

  3. 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?

  4. 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

  5. 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

  6. @ 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

  7. Hi
    I am trying to sort the items in a PUll DOwn list that is in the Report FIlter area of my pivot table and I cannot find a Sort function for it. Where would it be?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.