Sort Pivot Table by Values (4 Smart Ways)

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 pivot table by values.

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.

Sort Pivot Table by Values

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.

Sort Pivot Table by Values

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.

Sort Pivot Table by Values

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.

Sort Pivot Table by Values

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.

Sort Pivot Table by Values

Now after pressing OK, you will see the below result in your pivot table.

Sort Pivot Table by Values

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.

Sort Pivot Table by Values

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.

Sort Pivot Table by Values

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.

Conclusion

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.


Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

2 Comments
  1. Reply
    Lakisha July 30, 2018 at 12:39 PM

    That takes us up to the next level. Great potnsig.

    • Reply
      Siam Hasan Khan July 30, 2018 at 2:53 PM

      Thanks Lakisha

    Leave a reply