How to Sort a Pivot Table in Excel

Excel's PivotTables are fantastic for summarizing massive datasets, but getting them to sort correctly can sometimes feel like a fight. Just when you think you have your data arranged perfectly, a refresh scrambles it all back to the default order. This guide will walk you through every method for sorting your PivotTable, from simple value-based sorting to advanced custom lists, so you can take control of your reports for good.

Why Standard Sorting Doesn't Work on PivotTables

If you're used to sorting standard data ranges in Excel, your first instinct is likely to click a column header and hit the A-Z sort button. In a PivotTable, this often does nothing or produces unexpected results. This is because a PivotTable is not a static range of cells, it's a dynamic summary report with a structured layout. All sorting actions must happen through the PivotTable's own tools, which tell it how to organize its internal structure, not just the visible cells.

Understanding this is the first step. Instead of fighting the normal sort buttons, you need to use the sorting options built directly into the PivotTable environment.

Method 1: Sorting by Values (Largest to Smallest or Vice Versa)

This is the most common sorting requirement. You have a list of sales reps, products, or regions, and you want to see which ones performed the best or worst. You need to sort your report based on a value field, like "Sum of Sales" or "Average Order Value."

The Quickest Way: Using the Right-Click Menu

The fastest way to sort by values is directly within the PivotTable itself. Let’s use an example where we have Product Categories in our rows and Sum of Revenue as our values.

1. Navigate to the column containing the numerical values you want to sort by (in our case, the Sum of Revenue column).
2. Right-click on any number within that column.
3. In the context menu that appears, hover over Sort.
4. You'll see two straightforward options: Sort Smallest to Largest and Sort Largest to Smallest.
5. Click Sort Largest to Smallest to see your top-performing product categories at the top.

That's it. Excel will immediately reorder the Product Categories based on their corresponding revenue, placing the highest earner at the top.

Method 2: Sorting by Labels (A-Z or Z-A)

Sometimes you don't care about the values, you just need to arrange your row or column labels alphabetically. For instance, you might want an alphabetical list of regions, employee names, or marketing campaigns to make them easier to find.

How to Sort Row or Column Labels

Sorting by labels is just as easy as sorting by values.

1. Click the filter dropdown arrow next to your Row Labels or Column Labels in the PivotTable.
2. You’ll see the options Sort A to Z and Sort Z to A.
3. Select Sort A to Z to arrange your labels alphabetically.

This directly sorts the labels themselves, ignoring the values in the rest of the PivotTable. It's perfect for creating organized, easy-to-scan lists.

Method 3: Manual Sorting (Drag-and-Drop for Custom Order)

What if you need a specific, non-alphabetical or non-numerical order? For example, you want to display business divisions in a certain order of importance ("North," "South," "West," "East") or arrange product tiers logically ("Standard," "Premium," "Enterprise"). In these cases, neither A-Z nor value-based sorting will work.

This is where manual sorting comes in. Excel allows you to literally drag and drop your PivotTable items into any order you want.

Steps for Manual Sorting:

1. Click on the cell containing the label you want to move. Make sure you select just the cell, not the entire row or column.
2. Hover your cursor over the border of the selected cell. Your cursor will change into a four-headed arrow (a "move" cursor).
3. Click and hold the left mouse button, then drag the item up or down (for rows) or left or right (for columns). A faint green or gray bar will show you where the item will be placed.
4. Release the mouse button when the item is in the desired position.

This method is incredibly flexible and intuitive for arranging items in a specific presentational order that reflects your business logic.

Important Note: Manual sorting must be enabled. It is by default, but if it's not working, click the filter arrow next to "Row Labels," select More Sort Options..., click Manual, and then click OK.

Method 4: Sorting with a Custom List

Manual drag-and-drop sorting is great for one-off reports, but what if you consistently use the same custom order every single time you create a PivotTable? For example, your reports always need to be ordered by day of the week (Sunday, Monday, Tuesday...) or by T-shirt sizes (Small, Medium, Large, XL). Setting this up manually every time is tedious.

The solution is a Custom List. You can teach Excel a specific order and then re-use that list to sort any PivotTable in the future.

Step 1: Create Your Custom List

First, you need to define your list within Excel's options.

1. Go to File > Options > Advanced.
2. Scroll down until you find the "General" section.
3. Click the Edit Custom Lists... button.
4. In the "Custom Lists" dialog box, select NEW LIST and type your list entries in the "List entries" box on the right. Press Enter after each item. For example: Small Medium Large XL
5. Click Add and then OK to save your list.

Step 2: Apply the Custom List to Your PivotTable

Now that Excel knows your custom order, you can apply it to your PivotTable.

1. Click the filter dropdown arrow next to Row Labels (or Column Labels).
2. Select More Sort Options....
3. In the Sort dialog box, under "Sort Options," uncheck the box that says Sort automatically every time the report is updated. This step is critical! If you don't uncheck this, Excel will default to alphabetical sorting every time you refresh.
4. Next, under "Sort by," select either Ascending (A to Z) by: or Descending (A to Z) by: and choose the field you want to sort (e.g., "Size").
5. Click the More Options... button.
6. In the "More Sort Options" dialog, uncheck Sort automatically every time the report is updated.
7. Then, under "First key sort order," click the dropdown and you will see the custom list you just created at the bottom. Select it.
8. Click OK twice to close the dialogs.

Your PivotTable will now be sorted according to the custom list you created. From now on, you can apply this list to any other report you create with a single click.

How to Stop Your PivotTable Sort from Resetting on Refresh

This is arguably the most common frustration with sorting PivotTables. You spend time arranging everything perfectly, you refresh your data source, and your custom sort order disappears, reverting back to alphabetical.

Fortunately, the fix is simple, although it's buried in the PivotTable settings.

1. Right-click anywhere inside your PivotTable.
2. Select PivotTable Options... from the menu.
3. Go to the Totals & Filters tab.
4. Near the bottom, under the "Sorting" section, find the checkbox for Use Custom Lists when sorting. Make sure it is checked.
5. Click OK.

This tells Excel to prioritize any custom list or manual sorting that you have applied, even after the data is refreshed. This is the official setting to "lock" your custom sort in place.

Final Thoughts

Sorting PivotTables in Excel gives you full control over how you present your data, once you know your way around its dedicated tools. Whether you need to quickly see top performers with a value sort, create an alphabetical index with a label sort, or arrange data according to business logic in a custom order, you now have the steps to do it confidently.

While mastering these skills in Excel is incredibly valuable, it also highlights the manual work involved in reporting, especially if you find yourself downloading new CSV files and rebuilding these reports every week. At Graphed target="_blank" rel="noopener"), we created an AI data analyst to automate this whole process. You can connect your marketing and sales platforms (like Google Analytics, Shopify, or Salesforce) in a few clicks, and then simply ask for the reports you need in plain English. We designed Graphed to skip the spreadsheet wrangling and deliver live, interactive dashboards in seconds, so you can focus on insights instead of manual configurations.