How to Sort a Pivot Table in Excel

Contents hide

Sort Based on Values

Let’s sort a very basic pivot table. Here we have wanted to sort the Grand Total from smallest to largest to know 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 smallest to largest value by following the same steps.

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.

Here in the above Pivot table, both the Grand Total columns and rows are sorted from Largest to Smallest value.

Custom Sort a Pivot Table

Imagine, now you need to 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.

Now, what if you need to make a minor change other than the selected pattern in your pivot.

For example, you have sales data from Jan till Dec. Let’s assume you need to bring the current month let’s 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.

Now in the above table, we have the data organized from Jan till Dec in the Columns and the Countries sorted in alphabetical order. Follow the below steps to bring “Aug” in the first column and “India” in 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.

Sort in Alphabetical Order

Now let us sort the countries into alphabetical order for the convenience of our audience. This can be done 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.

More on Pivot Table