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

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)
   
3. Click on More Sort Options…
   
4. In the Sort dialog box, under Sort Options, click Ascending (A to Z), and select the field name from the drop down list.
   
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
   

   

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

_____________________