Catalog

How to Sort a Pivot Table in Excel (Step-by-Step)

January 8, 2024 1.1K views

Sorting pivot tables is essential for effective data organization and analysis. This guide unlocks the power of sorting pivot tables, providing comprehensive explanations and guidance. Whether you're a beginner or experienced analyst, it equips you to make informed decisions and enhance data analysis. Join us as we unravel the intricacies, empowering you to maximize the potential of organized data insights.

How to Sort Data in a Pivot Table

Sorting data in a pivot table allows you to organize and analyze your data in a way that is meaningful and insightful. The sorting options in a pivot table can help you arrange the data based on specific criteria, such as alphabetical order, numerical order, or custom sorting rules. Here are the different sorting options available in most pivot table software:

1. Sorting by values

Step 1 First, choose any cell in the Grand Total Column and then click the Home tab.

Select column

Click Home

Step 2 Under Editing, click on Sort and Filter.

Select Sort and Filter

A drop-down list will display after clicking on the Sort & filter option.

Step 3 The needed option "Sort Largest to Smallest" may be found here. Sorting your data is as simple as a click away.

Click Sort Largest to Smallest

Finally, the information is sorted.

the result

2. Custom Sort a Pivot Table

Step 1 Click on any value in the row , except the Total, because we aren't sorting the  Total.

Select row

Step 2 Now choose Home > Sort & Filter.

Choose Home

Step 3 After clicking the drop-down arrow, select Custom Sort.

The "Sort by Value" window will be opened here.

Sort by value window

Step 4 Now, under Sort Option, choose Largest to Smallest.

Choose Largest to Smallest

Step 5 Likewise, choose the sort direction. In this case, we want to sort from left to right, therefore we'll choose Left to right.

Choose Left to Right

You can now simply determine which day had the biggest and lowest sales.

Result

How to Sort a Pivot Table in WPS

WPS Office is a feature-rich, free office suite with a high level of compatibility.  It is a well-known office and productivity suite that can manage texts in Writer, Presentation, Spreadsheet, and PDF files.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis

Steps

Step 1 Click Open your pivot table in WPS Office.

Open your spreadsheet

Begin by opening a spreadsheet with a pivot table.

Step 2 Position the cursor on the heading cell of the data to be sorted.

Step 3 Then, in the bottom right corner of the cell, click the small drop-down button.

click drop down button

Step 4 Depending on our demands, we may pick Ascending or Descending from the drop-down option.

pick depand on your demand

Result

Reasons for choosing WPS

  • User-friendly interface similar to Microsoft Office

  • Comprehensive support for pivot tables

  • Intuitive and built-in sorting functionality

  • Customization options for sorting by multiple columns or rows, ascending or descending order, and advanced sorting rules

  • Efficient performance, especially with large datasets

  • Compatibility with Microsoft Excel file formats

  • Cost-effective solution with a free version available

FAQs

What should I do if my pivot table is not sorting correctly?

If your pivot table is not sorting correctly, here are some troubleshooting steps:

  • Check data format: Ensure the data in the sorting column is formatted correctly, such as numbers or dates instead of text.

  • Verify sorting column: Double-check that you have selected the correct column or row to sort within the pivot table.

  • Refresh the pivot table: Update the pivot table by clicking the "Refresh" button in the "PivotTable Tools" or "Options" tab.

  • Clear filters: Remove any applied filters that might be affecting the sorting order.

  • Rebuild the pivot table: Try creating a new pivot table with the desired sorting settings.

  • Sort outside the pivot table: Sort the original data range before creating the pivot table and then refresh it.

  • Check for hidden columns/rows: Unhide any hidden columns or rows that might be impacting the sorting.

  • Verify field settings: Review the sorting settings for the field/column you want to sort.

Should I sort the data in ascending or descending order?

The decision of whether to sort the data in ascending or descending order depends on your specific requirements and the context of your data. Here are some factors to consider:

Ascending Order:

  • Ascending order organizes the data in a sequence from the smallest to the largest values or from A to Z.

  • It is suitable when you need to identify the lowest values, earliest dates, or arrange data alphabetically.

  • Ascending order can be beneficial when you want to highlight top performers or prioritize lower values.

Descending Order:

  • Descending order arranges the data in a sequence from the largest to the smallest values or from Z to A.

  • It is useful when you need to identify the highest values, latest dates, or arrange data in reverse alphabetical order.

  • Descending order can be valuable when you want to pinpoint bottom performers or prioritize higher values.

How can I preserve the original order of data while sorting specific columns or rows?

  • Add an extra column to your data source before sorting to indicate the original order or sequence of the data. Each row can be given a unique identification or a number that corresponds to the original order.

  • Refresh your pivot table to incorporate the newly updated data source column.

  • Select the column or row in the pivot table that you wish to sort while keeping the original order.

  • Locate and select the "Sort" button in the "PivotTable Tools" or "Options" tab (depending on your version of Excel).

  • Choose to sort by the newly added column indicating the original order in the sorting choices. Make sure to choose the correct sorting order (ascending or descending) based on your needs.

  • Sort the pivot table based on the specified column or row.

Conclusion

Sorting data in a pivot table is crucial for effective organization and analysis. WPS Office, a cost-effective office suite, offers comprehensive support for pivot tables and intuitive sorting functionality.

With WPS Office, you can easily sort pivot tables by values, customize sorting options, and efficiently organize your data. Its user-friendly interface and robust sorting capabilities make WPS Office a reliable choice for enhancing data insights and improving analytical capabilities.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.