Power Pivot Sort by Column: How It Works

In Excel for Microsoft 365 on Windows, set a Power Pivot sort column by opening the model in the Power Pivot window, selecting the column whose displayed values need a custom order, then choosing Home > Sort by Column and selecting the column that contains the sort values. For example, sort a Month Name column by Month Number so PivotTables show January through December instead of alphabetical order.

What Power Pivot’s Sort by Column Does

Sort by Column is a model setting that tells Excel which other column should determine the order of values in a selected column. The displayed labels stay the same; the sort column supplies their sequence. This is useful when the natural order of labels differs from alphabetical or numerical order, such as weekdays, fiscal periods, product stages, or severity labels.

Without a custom sort, text values are commonly shown alphabetically. That makes labels such as April, August, December appear in an order that is unhelpful for a calendar. A separate numeric column can express the intended sequence while users continue to see readable month names. The setting belongs to the Data Model, so it can affect that field wherever the model is used, rather than being only a one-time manual rearrangement in a single report.

It is not a command to sort an entire worksheet or reorder rows in the Power Pivot data grid. It also does not rank measures, sort products by sales, or change the underlying data. Those tasks use other sorting controls. Sort by Column governs how distinct values of the selected model column are ordered when the field is presented.

Set a Sort Column in Excel for Windows

Open the workbook in Excel for Microsoft 365 on Windows and choose Power Pivot > Manage. If the Power Pivot tab is not visible, the Power Pivot add-in may need to be enabled in Excel’s COM Add-ins settings.
In the Power Pivot window, use Data View and select the table that contains the label column and its intended sort column.
Select the label column itself, such as Month Name. Make sure you have not selected the numeric sort column by mistake.
Choose Home > Sort by Column, then choose the matching sort column, such as Month Number.
Return to Excel and refresh or inspect a PivotTable that uses the field. If an existing PivotTable has a manual sort applied, check its sort settings as well.

The two columns need to be in the same model table. The sort column may be hidden from client tools if users do not need to see it, but it must remain in the model for the sort property to use. Consider giving it a clear name, such as Month Order or Stage Rank, so its purpose is understandable to whoever maintains the workbook.

Example: Put Month Names in Calendar Order

Suppose a Dates table contains Month Name and Month Number. Month Name contains text values such as January and February; Month Number contains 1 through 12. Select Month Name and set its Sort by Column property to Month Number. A PivotTable using Month Name can then display the months in calendar sequence while retaining the friendly names.

The important feature is the relationship between the labels and their order values. Every January row should have the same Month Number, 1, and every February row should have 2. The date table can have many rows for each month; that is fine because the same month label maps consistently to the same month number. The sort setting is about distinct label values, not about requiring one row per month.

The same approach works for weekday names: use a weekday label and a weekday index from 1 to 7. If the week should start on Sunday, assign Sunday 1; if the reporting convention starts on Monday, assign Monday 1. The numeric values encode the business or reporting convention, so confirm which convention the report requires before setting the sort.

For workflow labels such as New, In Progress, Waiting, and Complete, use a rank such as 1, 2, 3, and 4.
For risk labels such as Low, Medium, High, and Critical, use an integer ranking that reflects the intended reading order.
For fiscal periods, use a period index that follows the organization’s fiscal calendar rather than assuming the calendar year starts in January.

Rules for Choosing the Sort Column

The sort column must give each distinct value in the displayed column one consistent sort value. If a label has more than one possible order value, Power Pivot cannot infer which one should control its position. For example, a label called Q1 might occur in several years and be associated with different sequential period indexes. Sorting Q1 alone by a year-and-quarter sequence is ambiguous because the same displayed label would need to occupy multiple positions.

When the displayed label is not unique across the full reporting context, make the label more specific. Instead of displaying Q1 for every year, use labels such as 2024 Q1 and 2025 Q1, and provide each label with its own numeric sequence. Likewise, a month label that is intended to distinguish year-month periods should include the year, such as Jan 2025, rather than using Month Name alone. If the same word genuinely represents the same category and same position everywhere, a repeated sort value is appropriate.

Use a sort column with values that express the intended order clearly. Numbers are a common choice because they are straightforward to audit, but the key requirement is a valid, consistent mapping. Be cautious about sorting numeric-looking text: values such as 1, 10, and 2 may sort alphabetically as text rather than numerically. Check the data type and the actual values in the model, especially if the source column was imported as text.

How the Setting Affects PivotTables

Sort by Column establishes the model’s order for the selected field; it does not guarantee that every report will always preserve that order in every circumstance. A PivotTable can also have its own sort choice, and a user can sort items manually or by a measure such as sales. Those report-level choices can produce an order that differs from the model’s category order.

For example, a Month Name field may be set to follow Month Number, but a user can still ask a PivotTable to sort months by total revenue. That is a different request: the first uses a fixed calendar sequence, while the second ranks categories according to values in the report. If the result appears unexpected, distinguish between the model’s label order and the PivotTable’s current sort mode before changing the model.

Sort by Column does not change the values in the source table, alter relationships, or create a new calculated column. It is metadata attached to a model column. Refreshing the source data should not be treated as a way to define a custom order; the helper values and their mapping need to remain valid in the refreshed data.

Common Problems and How to Fix Them

The Sort by Column command is unavailable. Confirm that the Power Pivot window is open, that you are in Data View, and that a model column is selected. Also check that the selected column is not a measure or an object that cannot receive this setting.
Power Pivot reports an ambiguous sort. Inspect the distinct label values and their corresponding sort values. A single label must not point to different positions. Make the label unique enough for the intended sequence, or redesign the helper column to give every label one stable rank.
The PivotTable still looks alphabetical. Verify that the property was set on the displayed label column, not the order column. Then check whether the PivotTable has a manual sort or a sort-by-value choice that is overriding the expected category order. Refreshing the PivotTable can help it reflect model changes.
Some items appear missing. The sort property does not add categories that have no data to a PivotTable. Check the PivotTable field settings, filters, and whether the desired category exists in the model. A sort order controls sequence, not whether an item is included.
The order changes after a refresh. Check whether the helper ranks are populated consistently in newly imported rows. If the source uses text ranks or inconsistent labels, correct the source or model preparation so that the mapping remains stable.

For troubleshooting, compare a small set of distinct label and sort-value pairs first. A compact check often reveals duplicate labels with different ranks, blank order values, text-versus-number mismatches, or a typo in a category name. Correcting the mapping is more reliable than trying to compensate with repeated manual sorting in individual PivotTables.

Power Pivot on Mac and in Excel for the Web

Power Pivot model authoring and its Sort by Column control are features of Excel for Windows. Excel for Mac does not provide the Power Pivot add-in interface for creating or editing these model settings, and Excel for the web does not provide the same Power Pivot model-authoring controls. If a workbook depends on a Power Pivot model and this property needs to be changed, use Excel for Windows with the relevant Power Pivot functionality available.

Excel for Mac or the web may still let someone work with workbook content in other ways, depending on the workbook and available features, but that should not be confused with editing the model’s sort property. If the workbook is shared across platforms, document the intended order and make model changes in the Windows environment where the feature is supported.

Alternatives and Related Sorting Options

When Power Pivot Sort by Column is not available, a manual PivotTable sort can be a short-term option for one report. It is less suitable when the order must be consistent across multiple reports, because manual choices may need to be repeated and can be changed by users. A helper rank in the model is usually more maintainable for a recurring category sequence.

Another option is to include the desired order in the displayed label, for example 01 – January and 02 – February. This can make text sort correctly without a separate model property, but the prefixes become part of what users see and may be inconvenient in charts, slicers, exports, or other reports. A separate numeric order column keeps the presentation label clean.

Do not confuse this feature with sorting source data before loading it, sorting rows in a worksheet table, or sorting a PivotTable by a measure. Those operations act at different levels. Choose the model sort property when the goal is a stable custom order for the values of a particular field, and use report sorting when the order should depend on the current PivotTable layout or its calculated results.

Frequently Asked Questions

Can I sort month names chronologically in Power Pivot?
Yes. In the same table, create or use a Month Number column with values 1 through 12, select Month Name, and choose Home > Sort by Column > Month Number. The month label must map consistently to its number.

Does the sort column have to be in the same table?
Yes. The label column and its sort-by column need to be columns in the same Power Pivot table. If the order is currently elsewhere, bring or create the appropriate helper column in the table that contains the labels.

Can one label have two different sort numbers?
No, not for a single Sort by Column mapping. Each distinct displayed value needs one consistent order value. Make labels unique, such as adding the year to a quarter or month, if the intended sequence differs by context.

Does Sort by Column sort the whole PivotTable by sales?
No. It sets the order of values for one model column. To rank PivotTable items by sales or another measure, use the PivotTable’s sort-by-value controls instead.

Will a PivotTable always use the model’s custom order?
It uses the model order unless a report-level sort or manual arrangement changes the displayed sequence. If the order looks wrong, inspect the PivotTable’s sort settings as well as the model property.

Can I change this setting in Excel for Mac?
Excel for Mac does not include the Power Pivot model-authoring interface used for this setting. Edit the model in Excel for Windows where Power Pivot is available.

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.