How to Sort a Pivot Table in Excel : A Complete Guide (original) (raw)

Last Updated : 23 Dec, 2024

Sorting a Pivot Table in Excel is a powerful way to organize and analyze data effectively. Whether you want to sort alphabetically, numerically, or apply a **custom sort in Excel, mastering this feature allows you to extract meaningful insights quickly. This guide walks you through various Pivot Table sorting tutorials, including advanced options and practical examples, ensuring you can manage your data with precision and ease.

Table of Content

• Types of Sorting in Pivot Tables
• How to Sort a Pivot Table in Excel: Step-by-Step Guide
• Advanced Sorting Options in Pivot Table
• Practical Examples of Sorting in Pivot Tables
• Common Issues and Troubleshooting
• Tips for Effective Sorting in Pivot Tables

Types of Sorting in Pivot Tables

1. **Label Sorting: Sort row or column labels alphabetically or in reverse alphabetical order.
2. **Value Sorting: Sort data based on numerical values, such as totals or averages, in ascending or descending order.
3. **Custom Sorting: Arrange data in a specific order, such as months, days of the week, or a custom-defined sequence.

How to Sort a Pivot Table in Excel: Step-by-Step Guide

Follow the below steps to learn how to sort a Pivot Table in Excel:

Step 1: Open MS Excel Sheet

Open an Excel spreadsheet containing a Pivot Table. Ensure the Pivot Table is correctly created from a clean and organized dataset.

Open MS Excel in which you have Pivot Table

Step 2: Sorting by Row or Column Labels

• Click on the drop-down arrow next to the Row Labels or Column Labels heading in the Pivot Table.
• From the drop-down menu, choose:
  • Sort A to Z for ascending alphabetical order.
  • Sort Z to A for descending alphabetical order.

Sort Row Labels

Step 3: **Sort Values

• Right-click on any value within the Pivot Table.
• Select **Sort, and then choose:
  • **Sort Smallest to Largest for ascending order.
  • **Sort Largest to Smallest for descending order.

Right- Click and Select Sort Option

Step 4: **Sort Using the Field List

• Open the **PivotTable Field List by clicking anywhere inside the Pivot Table.
• Rearrange the fields in the Rows or Columns area to change the sorting order.

Rearrange the Field in Rows and Columns

Step 5: **Custom Sorting

• Highlight the Pivot Table or relevant section. (Here we have highlighted Column D).
• Go to the **Data tab on the ribbon and select **Sort.

**Note: Pivot Tables are linked to the source data, so modifications should always be done through the PivotTable Field List or by updating the source data.

Highlight the Column >>Go to the Data Tab>> Click on Sort Option

Set the Field to Sort By

**In the Sort dialog box:

• From the **Sort by dropdown menu, choose the **field or **column that you want to sort. **For example, if you’re sorting by months, select the "**Month" field.
• Choose **Custom List for Sorting Order
• Under the **Order section, click the dropdown menu and select Custom List.

A **new window, Custom Lists, will appear. This allows you to define the sequence in which data should be arranged.

Under the Order Select "Custom List"

Define Your Custom Sequence

If the sequence you want is already available in the predefined lists (e.g., "**Sunday, Monday, Tuesday, ..." or "**January, February, March, ..."), select it. Here we have Selected "January, February, March.."

If your desired sequence is not listed:

Enter your sequence manually, separated by commas. For example:

• High, Medium, Low (for priority levels).
• Silver, Gold, Platinum (for membership tiers).
• Q1, Q2, Q3, Q4 (for fiscal quarters).

Click **OK to save the custom sequence. Click **Add in the Custom Lists window.

Select your Sequence >> Press OK

**Apply the Custom Sort Order

• Once your custom list is selected or created, click **OK in the Custom Lists window to return to the **Sort dialog box.
• Click **OK again in the Sort dialog box to apply the custom sorting to your Pivot Table.

Custom Sorting Applied

**Note: Sometimes you might get error message, "**We can't make this change for the selected cells because it will affect a PivotTable", occurs because Excel restricts direct modifications to the data layout or structure of a Pivot Table. Sorting, filtering, or modifying the Pivot Table should be done using the built-in PivotTable Field List or the associated menus, not by directly editing cells.

Advanced Sorting Options in Pivot Table

1. **Sort by a Specific Field

To sort rows or columns based on a specific value field:

• Click the drop-down menu in the Row or Column Labels.
• Select **More Sort Options.
• Choose **Ascending (A to Z) or **Descending (Z to A).
• Select the value field to sort by, such as "**Total Sales" or "**Average Profit."

2. **Sort Manually

• **Drag and drop row or **column labels directly within the Pivot Table to rearrange them manually.

3. **Using Slicers for Sorting

• Add a slicer to filter and sort data dynamically.
• Insert a slicer by clicking on the Pivot Table, going to the **Insert tab, and selecting **Slicer.
• Use the slicer buttons to sort or filter data interactively.

Common Issues and Troubleshooting

Incorrect Sorting

• Ensure no blank rows or inconsistent data in the source dataset.
• Check if the field used for sorting is numeric or text-based, as Excel treats these differently.

Dynamic Data Updates

• If the source data changes, refresh the Pivot Table to reflect updated sorting.

Custom Lists Not Working

• Verify that the custom list is correctly defined in Excel’s options.
• Go to **File > Options > Advanced > General > Edit Custom Lists to manage sequences

Tips for Effective Sorting in Pivot Tables

• **Plan the Layout: Design your Pivot Table structure before sorting to avoid repetitive adjustments.
• **Combine Sorting with Filtering: Use filters to focus on specific subsets of data while keeping it sorted.
• **Visualize Sorted Data: Pair your sorted Pivot Table with a Pivot Chart for a clearer representation of patterns.
• **Save Custom Views: Save different sorted views using Excel’s Custom Views feature to quickly switch between analyses.

Conclusion

Sorting a Pivot Table in Excel is essential for efficient data organization and analysis. By learning the basics and exploring advanced techniques, such as **custom sort in Excel, you can enhance your ability to present data in a structured and meaningful way. Use these methods to optimize your workflow and resolve common issues, making your Pivot Table insights more impactful.

Also Read

• [How to Refresh a Pivot Table ](Refresh Pivot Table Data in Excel)
• How to Remove Pivot Table But Keep Data in Excel
• Pivot Table Conditional Formatting in Excel