How to Sort Pivot Tables by Value in Excel

Go Ask Debbie

Pivot tables are powerful tools in Excel that allow users to analyze and summarize large datasets efficiently. However, to make the most out of pivot tables, sorting the data by value is crucial for better insights. In this tutorial, we’ll walk through the step-by-step procedure to sort pivot tables by value, including examples and screenshots.

Why Sort Pivot Tables by Value?

Sorting pivot tables by value helps in:

• Identifying top-performing products, sales, or other key metrics.

• Arranging data in an easy-to-read format for better decision-making.

• Highlighting trends and patterns in your dataset.

Step-by-Step Guide to Sorting Pivot Tables by Value

Let's assume we have a sales dataset that includes product names, sales representatives, and sales revenue. Our goal is to create a pivot table and sort the sales revenue in descending order.

Step 1: Create a Pivot Table

1. Select the dataset you want to analyze.

2. Click on the Insert tab in the Excel ribbon.

3. Choose PivotTable and select where you want the pivot table to be placed (New Worksheet or Existing Worksheet).

4. Click OK to create the pivot table.

Step 2: Add Fields to the Pivot Table

1. Drag the Product Name field to the Rows area.

2. Drag the Sales Revenue field to the Values area.

3. The pivot table will now display a summary of sales revenue for each product.

Step 3: Sort Pivot Table by Value

1. Click on any cell inside the Sum of Sales Revenue column.

2. Click the Row Labels drop-down and select the More Sort Options...
   

   

   
   
   
   

3. In the Sort Dialog box, select the sorting you prefer (Ascending A to Z) or (Descending Z to A) and select the field you wish to sort. Then click OK.
   

   

   
   
   

   

   
   

4. The pivot table will now be sorted by the selected value column.

Additional Tips

• If sorting does not work, ensure that your values are set as numbers and not text.

• Refresh your pivot table if new data is added by right-clicking the table and selecting Refresh.

• You can also sort by multiple columns by adding more fields to the Rows or Columns section.

Sorting pivot tables by value is a simple but essential step to make your data analysis more insightful. By following these easy steps, you can quickly arrange your data for better visualization and decision-making.

Try these steps with your dataset and see the difference!

Do you have any questions or need further assistance? Let us know in the comments!

 

For more training on Pivot Tables, read my "Mastering Excel Pivot Tables" here.