How to Disable Automatic Sorting in an Excel PivotTable
Question details
The user wants to prevent a PivotTable from automatically sorting data when the report is updated, while ensuring manual sorting options remain fully accessible.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Refreshing or updating data within an active PivotTable where a specific custom order needs to be maintained without automatic resorting.
- Observed behavior
- The PivotTable automatically resorts the data every time it is refreshed, which overrides the user's preferred custom layout.
Ensure you have an active PivotTable created in your worksheet and click any cell inside it to reveal the PivotTable sorting and filtering tools.
Turn Off Automatic Sorting in PivotTable Options
Access the advanced sort options to disable automatic sorting, which prevents your PivotTable from reordering data upon refresh.
When you manually arrange items in a PivotTable, refreshing the data can sometimes trigger default sorting rules, ruining your custom layout. Disabling the automatic sort setting guarantees that your items stay exactly where you placed them.
Click the drop-down arrow located on the Row Labels or Column Labels header within your PivotTable.
From the drop-down menu, select 'More Sort Options...' to open the Sort dialog box.
In the lower-left corner of the Sort dialog box, click the 'More Options...' button.
Under the AutoSort section, uncheck the box labeled 'Sort automatically every time the report is updated', then click 'OK' to apply your changes.
Easily Control PivotTable Sorting in WPS Spreadsheet
WPS Spreadsheet provides robust, easy-to-use tools for creating and managing PivotTables, allowing you to maintain full control over your data's sorting and formatting.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your Excel workbook containing the PivotTable.
- 2. Access the PivotTable tools: Click anywhere inside the PivotTable to activate the contextual PivotTable tabs on the top ribbon.
- 3. Adjust sort settings: Click the filter arrow on your row labels, select 'More Sort Options', and easily turn off automatic sorting to preserve your custom layouts.
Frequently Asked Questions
Why does my PivotTable automatically change order when I refresh?
By default, PivotTables may have automatic sorting enabled to keep newly added or updated data organized according to alphabetical or numerical rules. You can turn this off in the advanced sort settings to maintain a custom order.
Will turning off automatic sorting disable my ability to sort manually?
No, clearing the 'Sort automatically every time the report is updated' checkbox only prevents the table from resorting on its own during a data refresh. Standard manual sorting commands remain fully available.
Do regular Excel tables automatically sort when updated?
No, regular tables in Excel do not automatically sort when new data is added or updated, even if the AutoFilter feature is enabled. Manual sorting is always required for regular tables.
How do I create a custom sort order in a PivotTable?
First, disable the automatic sorting option. Then, you can simply click on a row or column label and drag it to your preferred position. The PivotTable will retain this specific layout during future data refreshes.
