Wrong Item at Top of Sorted Pivot Table in Excel

After you sort an Excel pivot table, the wrong item might appear at the top of a column. For example, in the screen shot below, the list of Sales Rep names has Jan at the top. Below that name, all the other names are sorted in the correct alphabetical order.

See why that could happen, and the steps to fix that problem, so all items are listed A-Z

Video: Wrong Item at Top of Sorted Pivot Table

In this 4-minute video, I show how to fix the problem, if the wrong item appears at the top, after you sort an Excel pivot table.

Video Timeline
0:00 Pivot Sorting Problem
1:07 Why is Wrong Item at Top?
2:02 Stop the Problem
3:02 Global Default Settings

Why Is Wrong Item at the Top?

Most of the time, you’ll sort an Excel pivot table based on the words, or by the number values:

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

However, there’s another way that you can sort lists in Excel – based on a Custom List.

Excel has a few built-in Custom Lists that you can use. For example:

Weekday names — Sunday to Saturday, or Sun to Sat
Month names — January to December, or Jan to Dec

Using Custom Lists on Worksheet

On a worksheet, you can use any custom list for quick data entry. For example, instead of typing a list of weekday names, one by one, try this:

First, type any weekday name in a worksheet cell, where you want the list to start
Next, select that cell, and point to the fill handle, at the bottom right corner of the cell. In the screen shot below, I’ve circled the fill handle
Then, drag down, to the cells below, as far as you need to
- You can also drag right, left or up, to create the list
Excel automatically fills those cells with weekday names, from the custom list, repeating the list, if needed
- Tip: As you drag the fill handle, you can see the weekday names in a pop-up tip, beside the pointer

Video: Sort Worksheet List in Custom Order

You can also sort a worksheet list based on a custom list, instead of using an A-Z sort.

For example, when sorting the months of the year, you’d probably want January first (custom sort), instead of April (A-Z sort)

You can see the custom sort steps at the 1:25 mark in this video, where I show how to sort a list, based on a custom list of product names.

Custom Lists in Pivot Tables

Just like the Excel worksheet, Pivot tables also recognize custom lists, and can sort items in custom order, such as weekday name, or month name.

However, instead of waiting for you to choose that sort option, pivot tables do a custom sort automatically!

That’s why Jan is at the top – the pivot table thinks it’s the 3-letter name for January, the first month.

Turn Off Custom Sort Order

That custom sort order setting is turned on by default, in Excel pivot tables, which is a bit sneaky! If you’ve never run into the “wrong item at the top” problem before, it’s very confusing.

Fortunately, you can turn that custom sort setting off, if you don’t want it applied in your pivot table.

Note: This setting affects all pivot fields in the selected pivot table.

Here are the steps:

First, right-click any cell in the pivot table, and click PivotTable Options.
In the PivotTable Options dialog box, click the Totals & Filters tab.
In the Sorting section, remove the check mark from “Use Custom Lists When Sorting”
Click the OK button, to close the dialog box, and to apply the change.

Tip: In Excel 365, and Excel 2019 or later, you can change the default setting for all new pivot tables. I show the steps in the first video at the top of this post, at the 3:02 mark.

Get the Sample Excel File

To follow along with the video, you can get the Excel sample file, on the Pivot Table Sorting Tips page, on my Contextures site. There are more pivot table sorting tips there too!

The zipped file is in Excel xlsx format, and does not contain any macros.

‘___________________