Home > Pivot > Layout > Sorting

Pivot Table Sorting Fixes & Tips

Debra Dalgleish - Contextures

If you have Excel pivot table sorting problems, here are my tips for fixing them. For example, show new items in the correct order, instead of at the end of the list. There are tips for trouble-free sorting too - sort data by values, sort left to right, or sort in custom order.

Also see: Pivot Table Sorting Macros

Pivot Table Items

1) Pivot Table - Quick Sort By Values

To quickly sort a pivot field's items by a value field, follow these steps:

  • Click on any pivot item's value, to select that cell
  • On the Data tab of the Excel Ribbon, click the A-Z command or the Z-A command

In the screen shot below, the Product field is in the row area, and value in the Orders field is selected, in cell C4.

When the A-Z button is clicked, the Orders values are sorted in ascending order, smallest to largest.

Pivot Table Items

2) Pivot Table - Quick Sort By Labels

To quickly sort a pivot field's items alphabetically, follow these steps:

  • Click on any pivot item in the pivot field, to select that cell
  • On the Data tab of the Excel Ribbon, click the A-Z command or the Z-A command

In the screen shot below, the Product field is in the row area, and a product name is selected, in cell A5. When the Z-A button is clicked, the product names are sorted in descending order.

Pivot Table Items

3) Multiple Row Fields When Sorting

In the examples above, there is only one field in the Row area.

In that situation, all the pivot items are sorted together, so you can see the top and bottom values overall.

3a) Pivot Layout Hierarchy

If a pivot table has more than one field in the Row Labels or Column Labels area, the fields are in a parent/child hierarchy:

  • The first pivot field is at the top of the layout hierarchy
  • All the remaining row fields have a "parent" field in the layout hierarchy
  • Child field items are sorted within their parent group.
  • Sorting a parent field does not change the sort order of its child fields

For example, in the screen shot below:

  • Category is the first field - top of the Row hierarchy
  • Category is the parent of the Product field
  • Product Qty values are sorted in ascending order -- within each parent Category.

Pivot Table Items

3b) First Field in Row Area

In the Row area, the first field does not have a parent field -- there are no other fields to its left.

  • When you sort the items in that first Outer field, all of its items are put in order.
  • Sorting the first field does not change the sort order of its child fields

For example, in the screen shot below, the Category field was sorted by its labels, in Z to A order.

  • Cookies category moves to the top of the list
  • Product quantities within each Category are still sorted A-Z (smallest to largest)

Pivot Table Items

4) New Pivot Table Items Out of Order 

After you create a pivot table, new items might be added to the source data. When you refresh the pivot table, those new items might appear in the drop down lists, at the bottom of the list. Sometimes, the new items don't appear at all, if a filter is applied.

Watch this video to see how to solve problems with new items in a pivot table.

4a) New Excel Pivot Table Items Out of Order 

When you add new records to your pivot table source data, and refresh the pivot table, new items might appear in the drop down lists.

Sometimes, those new items appear at the end of the list, instead of in alphabetical order. For example, in the screen shot below, binders were just added to this pivot table's source data.

When the pivot table was refreshed, Binders appeared at the bottom of the Product list, instead of the top.

Pivot Table Items

4b) Fix the Sort Order go to top

Items in the pivot table drop down lists can get out of order, if the pivot field is set to Manual sort (the default setting). When a field is set for Manual sort, the pivot items are shown in alphabetical order at first, and you are able to drag the pivot items to a different position in the worksheet. Any new items will appear at the end of the drop-down list.

Follow these steps to sort pivot table field in ascending order:

  1. Right-click a cell in the Product field. For example, right-click the Envelopes cell.
  2. Click Sort, and then click Sort A to Z.

Sort A to Z

When you sort the Product field, its sort setting changes from Manual to Sort Ascending or Sort Descending.

This also sorts the drop-down list, and makes it easier for users to find the items they need. go to top

5) Video: Sort Pivot Table Report Filter 

After you refresh a pivot table, new items might appear in the Report Filter drop down lists. If the new items are not in alphabetical order, you can follow the steps in the short video, to sort them.

5a) Sort Report Filter Items 

If you add new records to fields that are in the pivot table's Report Filter, any new items might appear at the end of the list, instead of in alphabetical order.

You can sort the items in alphabetical order, either manually (as described below), or with programming (get the report filter sorting macro here).

5b) New Items at End of List

In this example, the Product field is in the Report Filter area, and Binders is a new product in the source data. When the pivot table was refreshed, Binders appeared at the bottom of the Product list, instead of the top.

report filter items out of order

5c) Fix the Report Filter Sort Order

If a field is set for Manual sort, new items will appear at the end of the drop-down list. You can't sort the fields that are in the Report Filter area, so move them temporarily to the Rows area.

Follow these steps to sort the Report Filter field in ascending order:

  1. Drag the Product field into the Rows area, above any other row fields.
  2. Right-click a cell in the Product field. For example, right-click the Envelopes cell.
  3. Click Sort, and then click Sort A to Z.
  4. Drag the Product field back to the Report Filter area.

Sort A to Z

When you sort the Product field, its sort setting changes from Manual to Sort Ascending or Sort Descending.

This also sorts the drop-down list, and makes it easier for users to find the items they need. go to top

6) Sort Pivot Table Field Left to Right

If there is a field in the pivot table Columns area, you can sort a pivot table row by its values, left to right. This moves the largest or smallest amounts to the left of the pivot table, so you can focus on them.

For example, in the pivot table shown below, the Category columns are in alphabetical order, with Bars at the left.

In the steps below, see how to:

  1. Sort Grand Total Row Left to Right
  2. Sort Column Heading Row Left to Right
  3. Sort a City Row Left to Right

categories sorted alphabetically

6a) Sort Grand Total Row

To sort the Grand Total row left to right, follow these steps:

  • Right-click on one of the values in the Grand Total row
  • In the pop-up menu, point to the Sort command
  • In the sub-menu, click on Sort Largest to Smallest, or Sort Smallest to Largest

The columns are rearranged, based on the amounts in the Grand Total row.

grand total row sorted largest to smallest

6b) Sort Column Heading Row Left to Right

After you sort the Grand Total row left to right, later, you might want to put the columns back into their original order, with category names sorted alphabetically

To sort the column heading row left to right, follow these steps:

  • Right-click on one of the labels in the Category heading row
  • In the pop-up menu, point to the Sort command
  • In the sub-menu, click on Sort A to Z, or Sort Z to A

The columns are rearranged, with the categories sorted alphabetically.

6c) Sort a City Row Left to Right

You can also sort the pivot table by a specific city's row, if needed.

For example, you could sort the Boston row, so its category quantities are sorted in descending order.

  • Note: You can't do a quick sort for a City row, like you can for the Heading Row and Grand Total. The column will sort, instead of the row.

6c1) Choose a Sort Option

To sort a pivot table item row, left to right, follow these steps (using Boston as the city example):

  1. First, in the pivot table, right-click a value cell in the Boston row.
  2. Click in the pop-up menu, point to Sort, and then click More Sort Options, to open a sort options dialog box
  1. In the Sort By Value dialog box, under Sort Options, select the Largest to Smallest sort option.
  2. Under Sort direction, select Left to Right. In the Summary section, you can see a description of the sort settings.
  3. Click OK, to close the dialog box

Sort by Value dialog box

6c2) Boston Row Sorted Left to Right

After you sort the Boston row, left to right:

  • Values in Boston row are sorted largest to smallest, left to right.
  • Category column order has changed, and Cookies, which has the highest Boston sales, is at the left.
  • NOTE: Rows for other cities may not be in descending order, because column order has been set by values in Boston row.

Boston categories sorted left to right

7) Sort Pivot Field in Custom Order

Most of the time, you'll sort a pivot table using one of these options:

  • alphabetically, based on the row labels, A-Z or Z-A
  • by numeric value - largest to smallest or smallest to largest

However, in Excel you can also sort items based on a Custom List, with a custom sort order.

7a) Custom Sort Order

Excel has a few built in Custom Lists that you can use, for custom sort options:

  • Full month names, January to December
  • Short month names, Jan to Dec
  • Full weekday names, Sunday to Saturday
  • Short weekday names, Sun to Sat

7b) Create a Custom List

You can also create your own custom lists, such as department names or cities, and specify a unique sort order for those lists.

To create a custom list, follow the instructions here: Create a Custom List

7c) Custom Lists Sort Order for Pivot Tables

In an Excel pivot table, you can control whether the Excel custom lists are used:

  • turn the setting on, and allow Excel to use those custom lists as the sort order, instead of sorting A-Z
  • turn off the setting, and prevent Excel from sorting based on a custom list

7d) Apply Custom Sort Order

After you create a custom list, follow these steps to apply a custom sort order to a pivot table field:

  1. Refresh the pivot table.
  2. If the field which has items from a custom list is set for Automatic sort, it should change to the custom list's sort order.
  3. If the field is currently set for manual sorting, it won't sort according to the custom list order. To change it to automatic sorting, right-click a city label, click Sort, and then click Sort A to Z.

7e) Prevent Custom Order Sorting in Pivot Table

In some cases, you might not want the custom list sort order used in a pivot table.

For example, if a salesperson's name is Jan or May, those names would appear at the top of a list, before other names, such as Ann or Bob.

To prevent the custom lists from taking precedence when sorting a pivot table, follow these steps to change the setting:

  1. Right-click a cell in the pivot table, and click PivotTable Options.
  2. In the PivotTable Options dialog box, click the Totals & Filters tab.
  3. In the Sorting section, remove the check mark from "Use Custom Lists When Sorting"
    

Note: This setting will apply to all the fields in the pivot table.

8) Show Values As - Sorting Problem

In a pivot table, you can apply a "Show Values As" setting to the numbers. These settings show custom calculations, such as Percent Of, or Difference From.

Some calculations, like the "Difference From" shown below, have two drop-down lists, Base Field and Base Item. In the Base Item drop-down list, there are two types of items:

  • Positional reference: (previous) or (next)
  • Specific reference: pivot items in the selected Base Field.

Show Values As dialog box with base field and base item

8a) Error Message for AutoSort

If you select a positional reference, you might see an error message like the one shown below.

Here is the text from that message:

  • Error Description: AutoSort and AutoShow can't be used with custom calculations that use positional references. Do you want to turn off AutoSort/AutoShow?
  • Buttons: Yes and No

error message when sorting

This error message is confusing, because it uses terms from older versions of Excel. Here are the old terms, and what they're currently called:

8b) Click Yes or No

Here's what happens when you click one of the error message buttons:

  • No: Excel will discard your Show Values As settings, instead of adding that change to the selected values column.
  • Yes: Excel will add your Show Values As setting to the column.
    • However, that error message will appear again, when you try to sort any of the value columns, using an A-Z command, or Sort Options in the right-click menu.
    • Instead, use the More Sort Options command, in base field's heading cell. There are steps in the next section

8c) Pivot Field Sort Options

To avoid the Excel error message, use the More Sort Options command, if your pivot table has values with positional references -- (previous) or (next).

Here are the steps, for a base field, Region, and value field, Diff Units:

  • In the Region field's heading cell, click the drop-down arrow
  • In the list of commands, click More Sort Options
    • The Sort (Region) dialog box opens
  • In the Sort options section, click the radio button for Ascending or Descending
  • In the drop down list, select the value field you want to sort - Diff Units in this example.
  • Next, click the More Options button

sort options dialog box

8d) More Sort Options

Follow these steps, when the More Sort Options (Region) dialog box opens:

  • At the top, in the AutoSort section remove the check mark for "Sort automatically every time the report is updated.
  • Next, in the Sort By section, click the radio button for Values in selected column
  • Click in the box, then go to the worksheet, and click a cell in the column that you want to sort
  • The Summary section shows the sort settings that you have selected.
  • Click OK, to close the More Sort Options dialog box.
  • Finally, click the OK button, to close the Sort dialog box, and to apply the sort settings

more sort options dialog box

9) Pivot Table Sorting Rules

In a pivot table, when you do an ascending sort, values are sorted in the following order:

  1. Numbers (including dates, which Excel stores as numbers).
  2. Text, in the following order:
    • 0 1 2 3 4 5 6 7 8 9 (space) ! “ # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
    • Note: Hyphens and apostrophes are ignored, except where two items are the same except for a hyphen. In that case, in an ascending sort, the item with the hyphen is sorted after the similar items without the hyphen. For example, Arrowroot would be listed before Arrow-root. (see screen shot below)
  3. Logical values (FALSE comes before TRUE).
  4. Error values, such as #N/A and #NAME?. Unlike a worksheet sort, where error values are treated equally, error values in a pivot table are sorted alphabetically.
  5. Blank cells.

9a) Hyphenated Words

In this screen shot, there are 5 variations of "Arrowroot".

  • Version without a hyphen is listed first.
  • Versions with a hyphen are listed in order of the hyphen's position.
  • More letters before the hyphen = higher in the list

pivot table sorting with hyphens in words

10) Get the Sample Files

  1. Food Sales: To test the pivot table sorting techniques, you can download the food sales sorting sample file. The zipped file is in xlsx format, and has no macros.
  2. New Items: To test the new items out of order problem, you can download the new product sorting sample file. The zipped file is in xlsx format, and has no macros.
  3. Macro - Sort Value: To test the Sort Based on a Value macros, download the Sort Based on Values file. The zipped file is in xlsm format, and contains macros from this page.
  4. Macro - Sort List: To test the Sort Based on a List macro, you can download the Sort Based on List file. The zipped file is in xlsm format, and contains macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Pivot Table Sorting Macros

Manually Move Pivot Items

Clear Old Items in Pivot Table

Report Filters

 

 

Last updated: June 2, 2024 2:54 PM