How to Sort a Pivot Table in Excel (Sorting Pivot Tables)

Contents hide

Steps to Sort Pivot Tables Data (Based on Values)

Sorting Pivot Tables in Alphabetical Order

Steps to Sort Pivot Tables Data (Based on Values)

Let’s sort a basic pivot table. Here, we want to sort the grand total column from smallest to largest (ascending sorting) to determine the most selling product.

First of all, select any cell in the Grand Total Column and Go to Home tab.
Further, click on Sort and Filter Under Editing.
After Clicking on the Sort & filter option, a drop-down list will appear.
Here you will find the required option” Sort Largest to Smallest”. Just a Click away to sort your data.
Finally, the data is sorted.

Similarly, you can sort the data from the grand total row.

To start with, again click on any cell in the total row.
Now, go to Home → Sort & Filter.
From here click on Largest to smallest.
The total row is sorted from largest to smallest.

In the above Pivot table, the grand total column and row are sorted from largest to smallest value. However, you can use the same steps to sort the total row or column in descending order.

Excel also provides an alternative way to sort data in Pivot Tables. Instead of using the ‘Sort & Filter’ group, you can right-click on a cell in the column you want to sort.

Sorting Data in Pivot Table Custom Sort

Imagine you must sort Product Cake from “Smallest to Largest” or vice versa. You can do this using a custom sort option.

Click on any value in the row Cake excluding the Grand Total as we are not sorting the Grand totals.
Now click on Home → Sort & Editing.
After you click on the drop-down arrow, click on Custom Sort.
Here you will get the window “Sort by Value” Opened.
Now select Largest to smallest under Sort Option.
Similarly, select the sort direction. Like here we want to sort from left to right, so we will select Left to right.
Here we go. Now you can easily find out which month had the highest and the lowest sale.

What if you need to make a minor change other than the selected pattern in your pivot?

For example, you have sales data from January to December. Let’s assume you need to bring the current month, say August, to the top. This does not fit into any set pattern.

Don’t worry, this is possible. In such cases, you can sort the pivot table manually. Here’s a simple example.

In the above table, the data is organized from Jan to Dec in the Columns, and the Countries are sorted in alphabetical order. Follow the steps below to bring “Aug” to the first column and “India” to the top row.

First of all, select the header of the column in which you want to move. Like here we want to move the month of Aug. So, we will select the cell Aug.
Remember we only need to select the header cell.
Now, once you move your cursor on the border of the selected cell, a move object cursor will appear.
After this click on the mouse and drag the column to the position where you want to move. Here we will drag it to the first column.
Similarly, repeat the steps with the country.
Select “India” and move the cursor on the border to get the move cursor and simply drag it to the first position.

Sorting Pivot Tables in Alphabetical Order

Now, let us sort the countries into alphabetical order for the convenience of our audience. We can do this in two different ways.

To begin with, click on the top cell “India” in this example.
Now click on the Home tab and go to the Sort & Filter button.
Here click on the option “Sort A To Z”.
The data is sorted. Now you see the countries are sorted in alphabetical order from A to Z.

You can also use the following steps to use another way of sorting in alphabetical order.

Select the Header cell i.e., “Country” in the pivot table.
Now click on the small drop-down arrow next to “Country”.
Now click on the first option i.e., “Sort A to Z”.
Here we go. The countries are sorted in alphabetical order from A to Z.

Manual Dragging to Sort a Pivot Table

Manual dragging is another method to sort data in a pivot table. This method offers a high degree of customization, allowing you to manually rearrange rows or columns to fit your specific needs.

To manually drag and sort data in a pivot table, click on the row or column header you wish to move. While holding the left mouse button, drag the header to the desired location in the pivot table.

Once you release the mouse button, the row or column will relocate to the chosen position. This method is proper when the desired order of data does not follow a standard ascending or descending pattern.

Note – If conditional formatting is applied on a pivot table, unfortunately, Excel won’t allow you to sort data based on the conditional formatting colors, icon sets, or data bars.

More Sort Options

In some specific situations, you can use the more sort options, which give you a few more options for sorting data.

There are two types of “More Sort Options”. When you select a value field and then open the more sort options, it shows you a dialog box like the following:

When you choose the right-to-left option in this dialog box, Excel allows you to sort data between the rows.

When you select a cell from the row label and the column label and open the more sort options, Excel shows a different dialog box, like the following:

When you click on the “More Option” button, this dialog box shows you a new dialog box where you can tick mark “Sort automatically every time the report is updated”. This activates an option for automatic sorting the data when you refresh the pivot table with new data.

More on Pivot Table