Pivot Table Issues

How to Keep Excel Pivot Table Custom Sorts After Refreshing Data

Khadija Khan Sep 29, 2026 868 views

Question details

The user needs a way to preserve their manual or custom sorting order in an Excel PivotTable so that it does not reset when the underlying data source is refreshed.

How to Keep Excel Pivot Table Custom Sorts After Refreshing Data

Product: Microsoft Excel
Device & OS: not provided
Scenario: Updating the underlying data source and refreshing a PivotTable to reflect the latest data.
Observed behavior: Excel automatically resets the manual sorting of PivotTable fields back to the default alphabetical or automatic order when the data is refreshed.

Before you start

Ensure your PivotTable is currently sorted in the exact custom order you wish to preserve before modifying the background sort settings.

Solution 1Recommended

Disable Automatic Sorting in PivotTable Options

The most direct way to keep your manual sort order is to uncheck the automatic sort setting within the PivotTable's advanced sort options.

By default, Excel enables an automatic sorting feature that prioritizes default alphabetical or numerical sorting upon refresh. Disabling this allows your manual drag-and-drop sort orders to stick.

Access Field Sort Options

Right-click the specific PivotTable field header that you have manually sorted and wish to lock in place.

Open More Sort Options

Select 'More Sort Options' from the right-click context menu to open the Sort dialog box for that specific field.

Navigate to Advanced Settings

Click on the 'More Options' button located at the bottom left of the Sort dialog box.

Disable Automatic Update Sorting

Locate the checkbox labeled 'Sort automatically every time the report is updated' under the AutoSort options and uncheck it.

Apply and Verify

Click 'OK' twice to apply the changes. Refresh your PivotTable to verify that your custom sort order remains intact.

Disable Automatic Sorting in PivotTable Options

Custom Sort Preserved: Any new data items introduced during future refreshes will typically appear at the end of your list, allowing your custom arrangement to remain undisturbed.

Solution 2

Use a VBA Macro for Complex Custom Sorts

For highly customized or multi-field sorting conditions that standard settings struggle to preserve, a simple VBA macro can reapply the exact sort order automatically.

Data Analysis Made Easy

Easily Create and Sort Pivot Tables with WPS Spreadsheet

WPS Spreadsheet provides a robust and user-friendly interface for managing large datasets, creating PivotTables, and maintaining custom sorting configurations without frustrating resets.

1. Open Your Data: Launch WPS Spreadsheet and open the .xlsx file containing your raw data.
2. Insert a PivotTable: Navigate to the 'Insert' tab and click 'PivotTable' to generate a dynamic report for your dataset.
3. Configure Fields and Sort: Drag your required fields into the Rows and Values areas. Click on individual row items and drag them to arrange your preferred custom sort order.
4. Lock Sort Settings: Right-click the sorted field, navigate to the field settings, and adjust the sorting preferences so your manual adjustments are retained during future data updates.

Fully compatible with Microsoft Excel (.xlsx) file formats and PivotTable structures.Advanced custom sort options to organize your data exactly how you need it.Intuitive drag-and-drop PivotTable builder designed for quick data analysis.Lightweight and free software that processes large data sources quickly and efficiently.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel PivotTable sorting keep changing when I hit refresh?

By default, Excel PivotTables are configured to sort automatically every time the report is updated to ensure new data is placed logically (e.g., A-Z). This background setting overrides any manual drag-and-drop sorting you may have applied.

Can I save a specific custom list to sort my PivotTable automatically?

Yes. You can create a Custom List by going to File > Options > Advanced > Edit Custom Lists. Once created, you can access the 'More Sort Options' menu in your PivotTable to sort by this specific custom list instead of a standard alphabetical order.

Will disabling automatic sorting affect newly added data items?

Yes. If you disable automatic sorting to preserve a manual sort, any newly added data items from a fresh data source refresh will typically appear at the very end of your manually sorted list, meaning you may need to manually drag the new items into their proper place.

Excel & Data Problems