How to Sort Excel PivotTable Data After Custom Calculations
Question details
The user needs to sort data in an Excel PivotTable after applying custom calculations, which disables the automatic sorting feature.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and sorting data within a PivotTable that contains calculated items utilizing positional references.
- Observed behavior
- Automatic sorting and filtering become disabled when custom calculations in the PivotTable use positional references, forcing the need for a manual workaround.
Ensure your PivotTable is refreshed so you are working with the most recent source data before modifying sort settings.
Turn Off AutoSort and Sort Manually
Since positional references disable automatic sorting, you need to turn off AutoSort and manually specify the sort order for your PivotTable items.
When Excel detects positional references in calculated items, it locks the automatic sorting to preserve data integrity. Bypassing this requires switching to manual sorting.
Click the drop-down arrow next to the Row or Column Labels in your PivotTable, select 'More Sort Options', and choose 'Manual (you can drag items to rearrange them)' to disable AutoSort.
Right-click the specific PivotTable item you want to sort, select 'Sort' from the context menu, and choose your preferred sorting order (such as Sort A to Z or Sort Z to A).

Pre-calculate Source Data
To retain the automatic sorting functionality in your PivotTable, avoid using positional references by moving the calculations to your original source data.
Use WPS Spreadsheet for Advanced PivotTable Management
WPS Spreadsheet provides a seamless and intuitive interface for creating, calculating, and sorting PivotTables. It is fully compatible with Microsoft Excel formats and offers powerful data analysis tools for free.
- 1. Open your data in WPS: Launch WPS Spreadsheet and open your Excel workbook containing the source data.
- 2. Insert a PivotTable: Go to the 'Insert' tab, click 'PivotTable', select your data range, and generate the table.
- 3. Add calculated fields: Under the 'PivotTable Tools' tab, select 'Fields, Items, & Sets' to create calculated items easily.
- 4. Sort the data: Click the drop-down arrow on the row or column labels to apply ascending or descending sort directly to your data.

Frequently Asked Questions
Why does my PivotTable sorting get disabled after adding a calculated item?
Excel disables the AutoSort and AutoFilter features when custom calculations within the PivotTable use positional references (such as referencing a specific row or column index instead of a stable field name). This is designed to prevent calculation errors when data shifts.
Can I sort a PivotTable by the values in a calculated field?
Yes, you can sort by a calculated field as long as it doesn't break reference rules. Simply right-click a value in the calculated column's grand total or subtotal, choose 'Sort', and select 'Sort Smallest to Largest' or 'Sort Largest to Smallest'.
How do I turn off AutoSort in a PivotTable?
Click the filter drop-down arrow next to your Row or Column label, select 'More Sort Options', and change the sort order to 'Manual (you can drag items to rearrange them)'. This turns off AutoSort and lets you manage the order yourself.




