Pivot Table Issues

Sort Excel PivotTable Columns by a Hidden Source Field

Steven ParkerSteven Parker Nov 14, 2026 869 views

Question details

Sort PivotTable column labels using an order stored in another source-data field without displaying that field.

Sort Excel PivotTable Columns by a Hidden Source 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.
Before you start

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.

Solution 1Recommended

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.

1
Add the source to the Data Model

Select the source data, choose Insert > PivotTable, and select Add this data to the Data Model before creating the PivotTable.

2
Open Power Pivot

Choose Power Pivot > Manage and select the table containing the label and sort columns.

3
Set the sort column

Select the displayed label column, choose Home > Sort by Column, and select the source column that contains the desired order.

4
Sort the PivotTable labels

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.

Use Power Pivot to sort labels by a source column
Using an existing PivotTable: A PivotTable that was not created from the Data Model may need to be recreated using the model. If the sort values change, refresh the model and PivotTable.
Free Microsoft Office alternative

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.

Use spreadsheet sorting and PivotTable tools for data analysisOpen and edit common Microsoft Excel formats, including XLSX and XLSNavigate a familiar, easy-to-use spreadsheet interfaceMove everyday Office files to a free, lightweight suite
microsoft office alternative - wps office

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.