Power Pivot Sort Data: Model and PivotTable Sorting

Yes. In Power Pivot, use Sort by Column to give labels a defined order in PivotTables, such as sorting month names by month number; to rearrange a PivotTable’s displayed items or values, sort the PivotTable itself. Sorting in Power Pivot does not permanently reorder the underlying rows of a table.

What sorting means in Power Pivot

The word “sort” can refer to several different actions in an Excel data model, and choosing the right one matters. Power Pivot stores tables and relationships for analysis; it does not treat the physical row sequence as a meaningful order for reports. As a result, sorting a table’s display in the Power Pivot window is not the same as telling every PivotTable how to arrange its labels.

For a model-wide label order, use Sort by Column. This sets a sort key for one column, so Excel can display its values in the order specified by another column. For a one-off change to a report, sort the PivotTable field or its values. If you need to physically arrange records in a worksheet, sort the source table in Excel instead; that is a separate operation.

Sort by Column: controls the order of a label field, such as a custom product tier or month name.
PivotTable sorting: changes the order of items or measure results in a particular report.
Worksheet sorting: rearranges rows in the source range or Excel table, not the model’s report-order rules.

Sort labels with Sort by Column

In Excel for Microsoft 365 on Windows, open the Power Pivot window from Power Pivot > Manage. In Data View, select the column whose displayed values need a special order. On the Power Pivot ribbon, choose Home > Sort by Column, then select the column containing the sort sequence. The exact ribbon controls can vary slightly with the Power Pivot window layout, but the command is named Sort by Column.

Make sure the table has both a label field and a sort-key field. For example, a Months table might have Month Name and Month Number.
Select Month Name, not Month Number.
Choose Home > Sort by Column and choose Month Number as the sort column.
Use Month Name in the PivotTable. Its items should follow the numeric month sequence rather than alphabetical order.

For example, alphabetical sorting puts April before August and January. If Month Name is sorted by Month Number, the report follows January, February, March, and so on. The sequence field is usually a whole number, but another sortable column can be used when it represents the intended order.

Build a reliable custom sort key

A custom order is useful for labels that have a business meaning but no natural alphabetical sequence. A status field might need to read New, In Progress, Waiting, Complete. Add a numeric Status Order column with values 1, 2, 3, and 4, then set Status to sort by Status Order. The key can be part of the imported source table or a calculated column in the model, provided it is available in the same table as the label.

The crucial rule is that each distinct label must map consistently to one sort value. If Status appears on many rows, every row labeled In Progress must have the same Status Order. A label linked to two different key values creates an ambiguous order and Power Pivot will reject the setting. The key does not need to be unique for every row: many rows can share the same label and the same rank.

For recurring calendar reports, a dedicated date or calendar table is often more dependable than trying to sort text dates. Store an actual date or numeric month key alongside the display label. If a report spans several years, use a Year-Month label and a sequential Year-Month key so that January 2025 does not get grouped into the same displayed category as January 2026.

Sort a PivotTable by label or value

If you only want to change the order in one PivotTable, use its own sorting commands rather than changing the model’s Sort by Column property. Click a field item in the PivotTable and use the available sort option, or right-click an item and choose Sort followed by an ascending or descending choice. This is suitable for arranging names alphabetically or reversing the order of a date field.

To rank categories by a measure, sort using a value cell in the PivotTable. For instance, to put products with the highest sales first, right-click a sales value for a product and choose the descending sort option. Check that the sort applies to the intended row field; a PivotTable with several row or column fields can make the target less obvious. Depending on the layout, use the field’s sort options to specify the measure used for ranking.

These are different goals: Sort by Column sets a consistent order for a label, while sorting by a measure ranks categories according to the current report values. A ranking may change when filters, slicers, or the measure values change. A custom label order such as New before Complete should not be based on sales values unless that is genuinely the desired behavior.

Sort or filter rows in the Power Pivot window

Data View is useful for inspecting imported rows and checking whether a sort key has the values you expect. Column-header controls can sort or filter the displayed data while you inspect it. This helps find blanks, unexpected labels, or inconsistent key values, but it should not be mistaken for a permanent instruction governing every report.

If the goal is to reorder source records for a person reading a worksheet, return to Excel and sort the source table there. If the goal is to make a PivotTable display custom label order, configure Sort by Column in the model. Sorting the visible rows in the Power Pivot window alone does not guarantee that a PivotTable, chart, or slicer will follow that same sequence.

What happens when the model refreshes

Refreshing a model reloads or recalculates its data according to the source and model design. The order in which source records happen to arrive should not be relied on as a reporting rule. A Sort by Column relationship is a model-level display setting, while the sort key values come from the data; if those values change or new labels arrive, review the result and confirm that each label still has one consistent key.

For example, if a source system adds a new status called Escalated, it also needs a rank if the report uses a custom status sequence. Otherwise, the new value may not appear in the intended position, or the model may be unable to use the defined sort arrangement. After refreshing, check representative PivotTables and slicers, especially when the source schema or category list has changed.

Common sorting problems and fixes

Sort by Column is unavailable or fails: Confirm that the selected label and sort key are columns in the same table. Check whether one label maps to multiple key values, including differences hidden by blanks or inconsistent source data.
Months still look alphabetical: Confirm that the PivotTable uses the exact month-name column configured with Sort by Column, and that the month-number key contains numeric values in the expected range.
Dates sort incorrectly: Check whether the source field contains genuine date values or text that merely looks like dates. Text dates may follow text order, while real dates have chronological values.
A measure sort changes after filtering: That is expected when categories are ranked by values. Filters change the measure results and can therefore change the ranking.
Sorting one report does not change another: A PivotTable sort is report-specific. Configure Sort by Column when the intended label sequence should be consistent wherever that model field is used.

When a custom sequence is important, inspect the key alongside the label before changing the setting. A small lookup table with one row per category can make the mapping easier to audit than a key repeated throughout a large fact table. It also makes it clearer where to add a rank when a new category is introduced.

Power Pivot sorting compared with ordinary Excel sorting

Excel’s worksheet sort rearranges rows in a range or table. Power Pivot’s Sort by Column defines how the model presents values from a particular field. A PivotTable sort changes the report’s item or value order. These operations can coexist, but one does not automatically substitute for the others.

For example, sorting a worksheet’s sales records by date can make that worksheet easier to read. It does not establish that a Product Category field should appear in a custom order in every PivotTable. Conversely, setting Product Category to sort by a category rank does not rearrange the rows in the source worksheet. Pick the operation based on what needs to change: records, model labels, or a specific report.

Availability on Mac and Excel for the web

Power Pivot model authoring and its Sort by Column command are features of Excel for Windows where the Power Pivot add-in is available. The feature set depends on the Excel edition and license. Excel for Mac does not provide the same Power Pivot window and model-authoring workflow, so there is no equivalent Mac ribbon path for setting this model property. Excel for the web can work with many workbooks, but it does not provide the full Power Pivot model-management interface described here. If a workbook was created on Windows, its model may still be used in supported scenarios, but use Windows desktop Excel to change the model’s sort-by-column setting.

Frequently Asked Questions

Can Power Pivot sort data alphabetically?
Yes. You can sort displayed rows while inspecting Data View, sort a field in a PivotTable, or configure a model field’s order. Use the PivotTable’s ascending or descending sort for a simple alphabetical report; use Sort by Column for a reusable label-order rule.

How do I sort month names chronologically?
Keep a Month Name label and a numeric Month Number in the same table. Select Month Name, choose Home > Sort by Column, and choose Month Number. For data covering multiple years, use a year-month label and a key that advances across years.

Can I sort a PivotTable from largest to smallest?
Yes. Right-click a value for the category you want to rank, choose Sort, and select the descending option. Verify that Excel is ranking the intended field by the intended measure, particularly when the PivotTable has nested row fields.

Why does Sort by Column give an error?
The sort key must be in the same table as the selected label, and each distinct label must consistently correspond to one sort value. Correct conflicting or blank mappings, then try the setting again.

Does sorting in Power Pivot change the source table?
No. Model sorting controls display order; it does not rearrange source worksheet records. Sort the Excel table separately if you need its rows physically reordered.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.