Category: Sort and Filter

A common problem in an Excel pivot table is that new items are added at the end of a drop down list. For example, in the following list, the Paper Clips item is at the end of the list, instead of appearing in alphabetical order.

This problem can be solved by sorting the field as described in my previous article – New Items at End of Pivot Table Drop Down Lists.

Error Message For Hidden Items

Usually, sorting the list quickly solves the problem, but I recently had an email from Martin tePoele, who had received an error message while trying to sort a field in his pivot table.

The message said: “Too many items are hidden. Unhide some items to continue.”

The field that Martin was trying to sort had over a thousand items, and only about 40 were selected. All the rest were hidden.

Limits in Excel 2003

I did some testing in Excel 2003, and discovered that 512 items seems to be the limit. If more than 512 items are hidden, you’ll get the error message when you try to manually sort the field.

However, I was able to programmatically sort the field, with 1000+ items hidden, without any problems. You could try this if you have more that 512 items hidden, and you don’t want to unhide them in order to sort the pivot field.

For example, the following code will sort all the pivot fields in ascending order.

‘==========================

Sub PivotSort()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)

    For Each pf In pt.PivotFields
        pf.AutoSort xlAscending, pf.Name
    Next pf

End Sub

‘==========================

Hidden Items in Excel 2007

With the increased limits to pivot tables in Excel 2007, the problem seems to be fixed. I was able to hide 1000+ items in a pivot field, and could manually sort the field without getting the error message.

_____________________

 

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.

_____________________