Power Pivot Sort Order: Sort Labels and Months

To control label order in Power Pivot, select the label column in the Power Pivot window, choose Home > Sort by Column, and select the column that contains the intended order. For example, sort Month Name by Month Number so a PivotTable displays January through December instead of alphabetically.

What Power Pivot sort order means

Power Pivot has a model-level setting called Sort by Column. It tells Excel which column to use when ordering the distinct values of another column. A common example is a text label paired with a numeric sort key: the labels remain readable, while the key supplies their sequence. The setting is stored in the data model, so it is useful for consistently ordering fields in PivotTables and other reports built from that model.

This setting does not rearrange rows in the source worksheet or physically sort the table in the Data Model. It defines the order for values in the selected display column. That distinction matters: changing the worksheet table’s sort order is not a substitute for setting the model’s Sort by Column property. A PivotTable can still have its own sorting choices, which may affect the arrangement in that particular report.

Use Sort by Column when the order is meaningful but does not follow ordinary alphabetical or numerical sorting. Examples include fiscal periods, product stages, priority labels, and weekdays. For an ordinary numeric measure, such as sales, you usually sort the PivotTable by the measure rather than using this model property.

Why month names often need a sort key

Text values are normally sorted alphabetically. A field containing January, February, and March may therefore appear as April, August, December, February, and so on. A month number provides the chronological sequence without changing the readable labels. In a date dimension, the useful pairing is typically Month Name and Month Number, with the name sorted by the number.

For example, a calendar table might contain the following pairs: January and 1, February and 2, March and 3, continuing through December and 12. In the Power Pivot window, select Month Name and set its Sort by Column value to Month Number. A PivotTable using Month Name can then show the months in calendar order. The number column is a sorting aid; it does not need to be displayed in the report.

For a label that includes both month and year, such as Jan 2025, the key must distinguish each year-month combination. A chronological date key or sequential year-month key can do that. Sorting Jan 2025 by month number alone would not produce the full sequence across years: January from several years would share the same month number. The key must represent the order required for the entire label field.

Set Sort by Column in the Power Pivot window

In Excel for Microsoft 365 on Windows, open the model and set the property on the column whose displayed values need ordering. The exact ribbon availability depends on the workbook and whether Power Pivot is enabled.

Open the Power Pivot window from the Power Pivot tab by selecting Manage. If the tab is not visible, check Excel’s COM add-in settings to see whether Microsoft Power Pivot for Excel is available and enabled.
In Data View, select the display column, such as Month Name. Confirm that the selected column is the label users will place in the PivotTable, not the key column.
On the Home tab, choose Sort by Column, then choose the key column, such as Month Number.
Return to the workbook and refresh the PivotTable if needed. Check that the field is using the model column you changed and that the resulting order matches the intended sequence.

The sort key can remain in the model even if report users do not need it. If you want to keep it out of the PivotTable field list, use the model’s Hide from Client Tools option for that key column, where available. Do not delete the key: the display column depends on it for ordering.

Rules the label and sort key must meet

The mapping between the displayed value and the key has to be consistent. Every distinct label being sorted must correspond to one appropriate sort-key value. If the same label is associated with different keys, Power Pivot cannot determine a single position for that label, and the operation may be rejected or the result may not represent the intended order.

Use one key per label. Month Name can map to Month Number because every January maps to 1, every February maps to 2, and so forth.
Choose the right level of detail. Month Name alone is suitable for ordering the twelve month names. A Month-Year label needs a key that orders individual months across years.
Check data types and blanks. Ensure the key contains usable values and that blanks or unexpected text do not create an unintended position.
Make the key sequence meaningful. A numeric key such as 1, 2, 3 is generally easier to inspect than arbitrary values, though the key can be any consistent column that expresses the required order.

Sorting does not create a missing key or repair inconsistent source data. If labels come from multiple tables, review the table that supplies the field used in the PivotTable. A clean date or category dimension with one row per relevant label is often easier to maintain than trying to sort a repeated fact-table field with inconsistent keys.

Sort by Column versus sorting a PivotTable

These controls solve different problems. Sort by Column defines the model’s order for a field’s labels. PivotTable sorting arranges items in a specific report, for example alphabetically, ascending or descending by a value, or by a manual arrangement. If a report needs products ranked by revenue, use the PivotTable’s value-based sort. If a report needs status labels in the sequence Not Started, In Progress, Complete, define a status sort key and use Sort by Column.

A report-level sort can change what you see even when the model has a sort-by-column setting. For instance, sorting a PivotTable by a measure can put categories in highest-to-lowest order rather than in the model’s category sequence. If the displayed result seems wrong, inspect the field’s PivotTable sort options as well as the model property. Also confirm that the report uses the intended field; a similarly named column from another table may have a different sort configuration.

A manual order entered for a particular PivotTable is not the same as a reusable model sort definition. The model setting is more appropriate when the same label order should be applied consistently across reports. A report-specific ranking is more appropriate when the order depends on a measure or on the needs of one particular view.

Other useful sorting examples

The same approach works for more than months. A weekday label can be sorted by a weekday number, provided the number follows the chosen convention—for example, Monday as 1 through Sunday as 7. A fiscal quarter label such as Q1, Q2, Q3, and Q4 can be sorted by a quarter sequence. A workflow status can use a custom integer key to put stages in business order rather than alphabetical order.

For category labels such as Low, Medium, and High, use a key that reflects the desired progression, such as 1, 2, and 3. The label column must still map consistently to that key. If the desired order is different in different reports, one fixed model-level sort may not suit every report; use separate label fields or a report-specific arrangement instead of expecting one model property to express several conflicting sequences.

Real date fields are usually preferable when the data represents dates. A genuine date column can be sorted chronologically and can support date grouping in a PivotTable. Text that merely looks like a date may sort alphabetically or inconsistently. Use a separate label and key when you specifically need a formatted text label, such as a month name, while preserving a meaningful chronological order.

Common problems and how to diagnose them

If Sort by Column is unavailable or an attempted setting fails, first verify that the selected field is the display column and that the key column exists in the same model table as required by the Power Pivot interface. Then inspect the values: repeated labels must not point to different key values. For example, if a category called Standard appears with sort keys 2 and 5, decide which position is correct and fix the source mapping.

If the setting appears to work but the PivotTable remains alphabetical, check whether the field in the PivotTable is the same model column you changed. Refresh the PivotTable and examine any report-level sort or manual ordering. Also make sure the field is coming from the expected table; duplicate field names can make this easy to miss.

If month labels are ordered correctly within a year but incorrectly across years, the key is probably too coarse. Month Number orders the twelve month names, but it does not distinguish January 2024 from January 2025. Use a year-month key for a combined year-month label. Conversely, if the field contains only month names and you want the same January-to-December sequence regardless of year, Month Number is the appropriate key.

If a source refresh introduces new labels, verify that the refresh also supplies their sort keys. A new status with no key cannot be placed reliably in a custom sequence. Maintaining the label-to-key mapping in the source or in a dedicated dimension table makes future refreshes more predictable.

Excel version and platform considerations

Power Pivot model authoring and its Sort by Column control are features of desktop Excel for Windows when the Power Pivot add-in is available. Excel for Mac and Excel for the web do not provide the same Power Pivot window for configuring this model property. If a workbook relies on a model sort order, use Excel for Windows to change or troubleshoot that setting. A PivotTable may still be viewable in another Excel edition, but viewing a report is different from editing the model metadata that defines the order.

The command is a model setting, not a worksheet formula or a standard Data tab sort. It does not require rewriting the source table into a new physical order. Keep a clear key column in the model and document unusual sequences, especially for fiscal calendars or business-specific stages, so another workbook maintainer can understand why the labels appear in that order.

Frequently Asked Questions

How do I sort months chronologically in Power Pivot?
In the Power Pivot window, select the Month Name column and choose Home > Sort by Column > Month Number. Make sure each month name maps consistently to its number, then refresh and review the PivotTable.

Can I sort text labels by a numeric column?
Yes. That is a standard use of Sort by Column. Each distinct text label needs a consistent numeric key that represents the order you want, such as status labels paired with sequence numbers.

Why does Power Pivot reject my sort column?
A common cause is that one displayed label corresponds to more than one sort-key value. Check for inconsistent mappings, blanks, duplicate category spellings, or a key that is too detailed or too coarse for the label.

Does Sort by Column reorder my source data?
No. It sets the order for values of a model column; it does not move rows in the worksheet or sort the underlying table physically. Use a worksheet or query sort when you need to rearrange source rows.

Can I still sort a PivotTable by sales after setting a label order?
Yes. A PivotTable can be sorted by its values for a particular analysis. That report-level ranking can differ from the model’s label sequence, so check the PivotTable’s sort setting if the displayed order changes.

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.