Sometimes after making a Pivot table in Excel, the data can be seen placed in the wrong order. To sort it out, Excel has many different sorting options for the Pivot tables. In this article, we will see how we can sort a pivot table by values.
Table of Contents
Sorting Data Using the Pivot Table Sort Option
To sort data in the pivot table, select any cell and right-click on that cell to find the Sort option. In the Sort list, you will have two options, one is Sort Smallest to Largest and the other one is Sort Largest to Smallest.
Let`s say you want the sales amount of January sales to be sorted in the ascending order. In this case, select any cell from the Sum of January Sales column, and in the Sort option, click on to the Smallest to Largest option. You will get to see the below result after doing this.
Sorting by Values using the Sort & Filter Option
Excel has a built-in sort and filter option which works for both the normal table and Pivot table. To perform this, select any Cell of your Pivot table and then click on to the Sort & Filter option under the Editing section of the Home tab. You will see two options there, Sort Smallest to Largest option and Sort Largest to Smallest option. By clicking the Sort Largest to Smallest option, you will see that your table gets sorted from largest to smallest value.
After clicking the Sort Largest to Smallest option, you will see the below result in your pivot table.
Sorting Pivot Table Using a Condition
Let`s say you want your Pivot table to be sorted in which the value of January sales is greater than 800. In this case, select any cell from the Sum of January Sales column and in the Row Labels header, click on to the AutoSort option.
After selecting this, click on to the Value Filters and from there select the Greater Than option.
After doing this you will get a new dialogue box which tells you to insert a number. By writing 800 there, you are telling your pivot table to be sorted in which the cells which contain the value less than 800 will not be seen.
Now after pressing OK, you will see the below result in your pivot table.
As you can see, in this sorted pivot table the rows of the Electric Trimmer and Electric Kettle get disappear. Because in those rows the Sum of January Sales contains the value 800.
Using the More Sort Option
Usually, the sorting feature takes place in a column. There are more sort options where you can easily do the sorting for rows. For this, you need to open the Sort by Value dialogue box. To open the Sort by Value dialogue box, select any cell for which you want the sorting to happen. Now right click on that cell and choose the More Sort Options under the Sort dropdown list.
Now by clicking the More Sort Options. You will see a new dialogue box of Sort by Value will appear. Do the following in the dialogue box and press OK.
After this, you will see a sudden change in your table. The sorting takes place in the row. We selected the row Electric Kettle and there the lowest value was 700 which is the February Sales value for the Electric Kettle. After the sorting, the number 700 will come first as it is the lowest number for the row. We will see the February Sales column now comes first due to the sorting of smallest to largest in Electric Kettle row.
I hope this article will help you while working with Pivot tables. Here I explained different sorting procedure of Pivot Tables by values. Feel free to comment regarding this article in the comment section. Stay connected for more fascinating articles.