Pivot Table Issues

How to Sort Excel PivotTable Data After Custom Calculations

Elise WilliamsElise Williams Nov 3, 2026 869 views

Question details

The user needs to sort data in an Excel PivotTable after applying custom calculations, which disables the automatic sorting feature.

How to Sort Excel PivotTable Data After Custom Calculations
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.
Before you start

Ensure your PivotTable is refreshed so you are working with the most recent source data before modifying sort settings.

Solution 1Recommended

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.

1
Turn off AutoSort

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.

2
Apply a manual sort

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).

Turn Off AutoSort and Sort Manually
Manual Sorting Capability: When set to Manual, you can also click and drag the border of a selected cell in the PivotTable to physically move an item up or down the list.
Sort PivotTables Easily in WPS Spreadsheet

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. 1. Open your data in WPS: Launch WPS Spreadsheet and open your Excel workbook containing the source data.
  2. 2. Insert a PivotTable: Go to the 'Insert' tab, click 'PivotTable', select your data range, and generate the table.
  3. 3. Add calculated fields: Under the 'PivotTable Tools' tab, select 'Fields, Items, & Sets' to create calculated items easily.
  4. 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.
Fully compatible with Microsoft Excel (.xlsx) PivotTable formatsIntuitive interface for adding custom calculations without breaking sort functionsFree, lightweight, and fast alternative for heavy data analysisFamiliar user interface with zero learning curve
microsoft office alternative - wps office

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.