Sort Excel PivotTable Columns by a Hidden Source Field
Question details
Sort PivotTable column labels using an order stored in another source-data field without displaying that field.

- Product
- Microsoft Excel PivotTable and Power Pivot
- Device & OS
- not provided
- Scenario
- A PivotTable displays one field as column labels, while another source column contains the desired numeric or custom sort order.
- Observed behavior
- The column labels appear alphabetically rather than in the order specified by the source-data sort field.
Check that each displayed label has a consistent corresponding sort value in the source data. For a source-driven order, check that your Excel edition supports the Data Model and Power Pivot.
Use Power Pivot to sort labels by a source column
Use this method when the order should follow a numeric or other sort field in the source data.
The displayed label and its sort column must be in the same Data Model table, and each label must map consistently to a sort value. The sort column does not need to appear in the PivotTable.
Select the source data, choose Insert > PivotTable, and select Add this data to the Data Model before creating the PivotTable.
Choose Power Pivot > Manage and select the table containing the label and sort columns.
Select the displayed label column, choose Home > Sort by Column, and select the source column that contains the desired order.
Place the label field in the PivotTable Columns area, leave the sort field out of the layout, and use the column-label filter to sort in ascending order. Refresh the PivotTable after source data changes.

Apply a custom list to a fixed PivotTable order
Use a custom list when the desired label order is fixed and does not need to follow a changing source column.
Work with spreadsheets and PivotTables in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing Office files. WPS Spreadsheets supports common Excel formats, making it easy to move everyday spreadsheet work to WPS; it does not fix Excel's Power Pivot sort-by-column behavior.

Frequently Asked Questions
Can I sort PivotTable labels by a field without showing it?
Yes. In a Data Model PivotTable, use Power Pivot > Manage > Home > Sort by Column to assign the sort field to the displayed label, then leave the sort field out of the PivotTable layout.
Why won't Power Pivot accept my sort column?
Check that the label and sort columns are in the same Data Model table and that each label maps consistently to one sort value. Correct the source data, refresh the model, and try Sort by Column again.
Can I set a custom order without Power Pivot?
Yes. Create a list under File > Options > Advanced > Edit Custom Lists, enable Use Custom Lists when sorting in PivotTable Options, and sort the labels in ascending order. This does not dynamically read the order from a separate source field.
Why are extra columns still visible after I turn off grand totals?
They may be subtotals. Select the PivotTable and choose Design > Subtotals > Do Not Show Subtotals. Removing subtotals changes the layout but does not control label order.




