How to Sort a Pivot Table in Excel: Step-by-Step (2024)

When you deal with large amounts of data in your Pivot table, knowing how to sort data in it is really helpful πŸ˜€

Excel offers you a lot of sorting options for you to easily sort data in your Pivot Tables. And with a sorted pivot table, you can find and analyze the presented data quickly πŸ“ˆ

In this lesson, you will learn how to sort a pivot table by Pivot table values, by text, and by custom order in Excel.

Download the attached Excel workbook to practice this lesson, and let’s start πŸ’ͺ

Sort a pivot table by values

The below Pivot Table shows the sales data of a hotel. The column labels show the day of the week. The row labels show the package.

Pivot Table - Sales Data

You can sort the values of the above pivot table, vertically as well as horizontally β¬†οΈβž‘οΈ

Sort pivot table vertically

Assume that you want to get the highest sales contributing package to the top of the list.

Then, you have to sort the Pivot Table vertically in descending order.

Follow the below steps to sort Pivot Table data.

  1. Click the small drop-down arrow in row labels.
Click the small drop down arrow in row labels in the Pivot Table.
  1. Select more sort options from the drop-down list.
Select more sort options from the drop down list
  1. To sort descending order select “Descending (Z to A) by:”.
Select descending order from sort options.
  1. Click the down arrow button below that and select the sum of sales column.
Click the down arrow below that and select the sum of sales column.
  1. Click the “OK” button to sort data.

Then, packages will be sorted based on the value field.

Sorted Pivot Table vertically.

Sort pivot table horizontally

Imagine that you want to sort pivot table days based on sales value. You can arrange it from the smallest value to the highest value.

So, you must sort the Pivot Table horizontally in ascending order.

You can follow the below steps for that.

  1. Select any value in the Pivot table’s grand total row except the cell in the grand total column. So, you can select any cell in the range of B6 to H6.
Select any value in the Pivot table's grand total row except the cell in the grand total column
  1. Go to the Data tab.
Go to the Data tab.
  1. Click the custom sort icon of the sort and filter group in the data tab.
Click the custom sort icon
  1. Then the sort-by-value dialog box appears. To sort in ascending order, you have to select smallest to largest from the sort options dialog box. Select the largest to the smallest sorting option, if you want to sort data in ascending order.
Select one of the sort option
  1. Select left to right as the sort direction as you want to sort horizontally.
Select one of the sort direction
  1. Click OK.
Click OK

Your Pivot Table is sorted horizontally in ascending order.

Sorted Pivot Table horizontally

Sort a pivot table by text (from A-Z)

In Pivot Tables, you can sort text as well. A Pivot table field that contains text can be sorted in alphabetical order.

You cannot sort Pivot Tables by following β›”

  • Case-sensitive text entries
  • Conditional formatting indicators/ icon sets
  • Cell color
  • Font color
Kasper Langmann, Microsoft Office Specialist

The packages in below Pivot Table are not in alphabetical order.

A Pivot table to sort alphabetical order

If you want to arrange packages in alphabetical order, you can follow the below steps.

  1. Right-click on any cell that contains a package name. So you can select any Row label in cells A3 to A5.
Right click on text value field
  1. Go to the “sort” option in the menu.
Go to the Sort option.
  1. Select “Sort A to Z” to sort in alphabetical order.

Now the Packages are sorted in alphabetical order as follows.

Packages are sorted in alphabetical order.

Pro Tip:

If there are leading spaces, it will distort PivotTable data sorting 😫

So, before you sort text items, make sure to remove leading spaces.

Custom pivot table sorting

Pivot Table sort options are not limited to numeric values or alphabetical order πŸ€”

You can sort Pivot Table based on a custom order or own custom lists πŸ₯³

In the below Pivot Table, days are not sorted alphabetically order.

Days of the Pivot Table are sorted alphabetical order.

If you want to sort days as per the order of the days of the week, you have to do the following.

  1. Select any cell in the days row.

So, you can select any cell in the range of B2 to H2.

Select a cell in the days row.
  1. Go to the Home Tab.
Go to the Home Tab
  1. Click the sort and filter button in the editing group.
Click sort and filter button in the editing group
  1. Select custom sort options.
Select custom sort options.
  1. Select the “Ascending (A to Z) by” sort option and click the “More options…” button.
  1. Uncheck the automatic sorting option. Then, your table will not sort automatically.
Uncheck the automatic sorting option
  1. Expand the drop-down list of “First key sort order” to see the inbuilt custom lists.
  1. Select “Sun, Mon, Tue, …” as the custom list and click “OK” twice.

Now your Pivot Table will sort as below 😍

Pivot Table with correct order for days

Pro Tip:

Sometimes you add new items to the source table.

Those new items are coming at the end of the Pivot table’s report filter.

Even a manual sort is not possible for report filters.

The solution is to move the report filter to rows or columns and apply the Excel sort option in the same way you did earlier.

Then move that Pivot item again to the report filter area πŸ€—

That’s it – Now what?

Well done! πŸ‘πŸ» Now you know all the methods to sort a pivot table field. Whether it’s by values, by text, or by your own custom list.

Excel makes sorting data in Pivot tables so easy! With a few clicks, you can find and analyze the data in your Pivot tables.

If you want to do a lot of tasks with just a single click of your mouse, then it’s time for you to know more about Excel macros. You can automate your workflow (and a whole lot more) using Excel macros 🀯

Join my Excel Advanced user training where you’ll learn the basics of Excel Macros with me.

Other resources

If you want to learn everything you need to know about sorting data in Excel, don’t forget to read our How to Sort in Excel: Data and Columns guide.

Dive deep into Pivot tables and how to create them 🀿

You can apply all your Pivot Table skills to create amazing dashboards in Excel (+free templates here).