Sort by Value in Pivot Table in Excel [3 Methods]

To sort by value in the Pivot Table in Excel, go through the instructions below:

1. Click on a cell containing a value to sort inside the Pivot Table.
2. Right-click on that to access the Context Menu.
3. Now, choose the Sort option.
4. Then, select between Sort Smallest to Largest/Sort Largest to Smallest. Or, choose More Sort Options.

By following the mentioned steps, you can sort a Pivot Table by value in Excel.

Sort by Value in Pivot Table Using Context Menu

To sort the Pivot Table by value in Excel, use the Sort from the context menu. Now, follow the steps below:

1. Select a cell that contains a value inside the Pivot Table.
2. Then, right-click on the cell to open the Context Menu.
3. Next, go to the Sort option.
4. Choose between Sort Smallest to Largest or Sort Largest to Smallest.
   

Here I am sorting the column sales in the Sort Smallest to Largest order. As a result, Excel will sort the column from smallest to largest value.

Using More Sort Options to Sort by Value in Pivot Table in Excel

Access More Sort Options to sort by value in the Pivot Table in Excel. To do that, follow the instructions below:

1. Select a cell containing a value inside the Pivot Table and right-click on that.
2. Next, click on More Sort Options.
   
3. Then, choose the suitable option from Sort options & Sort direction and click OK.
   

Here, I have chosen Smallest to Largest and Top to Bottom. So, it will sort product names by sum of sales in Ascending order.

Sort by Value in Pivot Table with Sort & Filter Option

Use the Sort & Filter option to sort by value in the Pivot Table. Now, follow the procedure to use this option:

1. Click a cell that contains a value in the Pivot Table.
2. Then, go to the Sort & Filter option under the Editing group.
3. Next, choose the sorting option from the dropdown list of Sort & Filter. Or, you can click on Custom Sort.
   
4. Now, choose Sort options and Sort direction in the Sort By Value dialog box. Then, click OK.
   

Here, I have selected Custom Sort and chosen the Largest to Smallest and Left to Right for row Electronics.

Conclusion

In conclusion, sorting a Pivot Table in Excel by value allows users to arrange and analyze data in a meaningful way. By organizing information in ascending or descending order based on numerical or alphabetical values, users can quickly identify patterns, trends, or outliers within their dataset. In this article, I have discussed 3 methods for Excel Pivot Tables sorted by value in Excel. So, you will face no issues after going through this guideline for sorting by value in the Pivot Table.

Frequently Asked Questions

What is the shortcut for sorting in Pivot Table?

The shortcut key to sort in the Pivot Table is ALT+A+S. This key provides a quick and efficient way to sort data within a specific column in a Pivot Table in Excel.

How do I sort grouped data in a Pivot Table?

To sort grouped data in a Pivot Table in Excel without using a shortcut, follow these steps:

1. Click on any cell within the column that contains the grouped data in the Pivot Table.
2. Then, go to the Data tab on the Ribbon.
3. Next,  in the Sort & Filter group, click on the Sort button.
4. In the Sort dialog box, select the column you want to sort from the Sort by dropdown menu.
5. Then, choose the sort order (ascending or descending) for the grouped data. You can add additional levels of sorting or specify sorting options if your data has multiple columns and click OK.

These steps provide a non-shortcut method for sorting grouped data within a specific column in a Pivot Table in Excel.

How can data in a Pivot Table be sorted in alphabetical order or by values?

To sort data in a Pivot Table in Excel in alphabetical order or by values, follow these steps:

1. Click on any cell within the column you want to sort in the Pivot Table.
2. Go to the Data tab on the Ribbon.
3. In the Sort & Filter group, click on the Sort button in the Sort dialog box.
4. In the Sort dialog box, select the column you want to sort from the Sort by dropdown menu.
5. Choose the sort order (A to Z for alphabetical or smallest to largest for values) for the data and click OK.

These steps allow you to easily sort data in a Pivot Table either alphabetically or by values, providing flexibility in how you analyze and present your data in Excel.

Why is my Pivot Table not sorting?

If your PivotTable in Excel is not sorting as expected, consider the following troubleshooting steps:

• Check for Blank Cells: Ensure there are no blank cells in the column you’re trying to sort, as they can affect the sorting process.
• Verify Data Consistency: Confirm that the data in the column has a consistent data type. Mixed data types (numbers and text) may interfere with sorting.
• Select Correct Data Range: Double-check that you’ve selected the correct range of data in your Pivot Table. Ensure the entire column is included.
• Field Settings: Right-click on a cell within the column, go to Field Settings, and check the data type setting. Ensure it is set correctly (General, Number, Text, etc.).
• Refresh the PivotTable: If you’ve made changes to your source data, refresh the PivotTable to ensure it reflects the latest updates. Then, right-click on the PivotTable and select Refresh.
• Clear Existing Filters: Check for existing filters and clear them if needed. Go to the Data tab, select Clear, and choose Clear All.
• Multiple Fields: If you have multiple fields in the Rows or Columns area, ensure that the field you want to sort is the top-most one.
• Field Headers: Ensure your Pivot Table has field headers. Excel may not sort correctly if it interprets the first row as data rather than headers.

By addressing these potential issues, you can troubleshoot and resolve sorting problems in your PivotTable in Excel.