New Pivot Items at End of List

Why do new pivot items appear at the end of the lists, when you add them to an Excel pivot table? It’s hard to find those new items, if they aren’t sorted alphabetically. Keep reading, to see why that happens, and how you can fix the problem of new pivot items at end of list.

Alphabetical Order

When you create a new pivot table in Excel, all the items are sorted in alphabetical order, by default. That makes it easy to find what you’re looking for, especially in long lists.

For example, here is a drop down list of product names, sorted A-Z, in a new pivot table.

You can read more about pivot table sorting on my Contextures website.

Add New Items to Source Data

However, if you add new items to the source data later, you might notice a problem with the pivot table lists. In the screen shot below, a new record has been added to the source data, and the product that was sold is a new one – Binder.

Refresh the Pivot Table

To see the new product in the pivot table, right-click any pivot table cell, and click Refresh.

New Pivot Items at End of List

Then, to see the product name, click the arrow in the Product field, in the pivot table’s Report Filter area.

Instead of appearing at the top of the list, in alphabetical order, the Binder pivot item is at the end of the list. In a long list, you might not even notice that the new item has been added!

Why New Items Are Out of Order

Why do new items appear out of order, at the end of the drop down list in a pivot table? In Excel, there are three pivot Sort settings

• A-Z
• Z-A
• Manual

When the pivot table is first created, the items are listed alphabetically, but the default sort setting is Manual.

So, after the pivot table is created, the alphabetical order is not automatically applied. As a result, Excel adds new pivot items at end of list, because they are the latest entries.

Manual Sort Order

There are advantages and disadvantages to keeping the Manual sort order:

• Advantage: You can drag items to a different position on the worksheet, so it’s easy to put important items at the top of the list.

• Disadvantage: Alphabetical order isn’t maintained for new items.

Change to Alphabetical Sort Order

If you want to change to alphabetical order, there is an easy fix:

• If the pivot field that you want to change is in the Report Filter area, temporarily move the field to the Row area
• Right-click one of the items in the pivot field that you want to change.
• In the popup menu, click Sort, then click A to Z

• (optional) Move the pivot field back to the Report Filter area
• Click the field’s drop down arrow, to see the items listed in alphabetical order

Return to Manual Sort Order

Later, if you want to go back to Manual sort, follow these steps:

• If the pivot field that you want to change is in the Report Filter area, temporarily move the field to the Row area
• Right-click one of the items in the pivot field that you want to change.
• In the popup menu, click Sort, then click More Sort Options
• Select the Manual sort option, and click OK
• (optional) Move the pivot field back to the Report Filter area

NOTE: Immediately after you change to Manual sort, you won’t see any change to the sort order of the pivot table lists. However, if the source data changes, Excel will show new pivot items at end of list, instead of alphabetically.

Get the Pivot Table Sorting Workbook

To test the problem with new items in a pivot table list, you can download the free pivot table sorting workbook. Go to the pivot table sorting page on my Contextures website, then scroll to the Download section, and click the link there.

______________

Save