Pivot Table Issues

How to Sort Power Pivot Hour Groups in Chronological Order

Guest Writer Oct 7, 2026 868 views

Question details

The user needs to sort grouped hour values in a Power Pivot table in chronological order rather than text-based alphabetical order.

How to Sort Power Pivot Hour Groups in Chronological Order

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing time-based data in Excel using the Power Pivot Data Model and grouping records by hour.
Observed behavior: Hour values are being treated as text, causing them to sort alphabetically (e.g., 0, 1, 10, 11, 12, 2, 3) instead of chronologically (0, 1, 2, 3...).

Before you start

Ensure you have access to the Power Pivot Data Model and that your source data table contains the original time values before making changes.

Solution 1Recommended

Create and Apply a Numeric Hour Sorting Column

Convert the text-based hour values into numbers within the Data Model, and use the 'Sort by Column' feature to enforce chronological order.

Power Pivot automatically sorts text columns alphabetically from left to right. When numbers are stored as text, a '10' will appear before a '2'. To correct this, you must map your text-based hour display column to a strict numeric column within the Data Model.

Open the Power Pivot Data Model

Navigate to the 'Power Pivot' tab on the Excel ribbon and click on 'Manage' to open the Data Model window containing your table.

Add a Numeric Hour Column

Scroll to the far right of your table and click 'Add Column'. Create a DAX formula to extract or convert the hour into a number, such as =VALUE([HourText]) or =HOUR([TimeColumn]).

Verify the Data Type

Select your newly created numeric column. Go to the 'Home' tab in the Data Model window and ensure the 'Data Type' dropdown is set to 'Whole Number'.

Apply Sort by Column

Select the original text-based hour column that you use for your Pivot Table rows. Click on 'Sort by Column' on the Home tab, and choose your new numeric column as the 'Sort By' reference. Click 'OK'.

Create and Apply a Numeric Hour Sorting Column

Automatic Updates: Once applied, return to your Excel workbook. Your Pivot Table will retain the hour text labels but will instantly update to display them in perfect chronological order.

Free Microsoft Office alternative

Need a Simpler Way to Analyze Data? Try WPS Office

If managing Power Pivot data models and sorting rules feels overly complex, WPS Office offers a lightweight, highly compatible alternative. WPS Spreadsheet handles standard Pivot Tables intuitively, seamlessly grouping dates and times without requiring complex data model workarounds.

1. Download and Install: Get WPS Office for free from the official website and install it on your device.
2. Open Your Spreadsheet: Open your existing .xlsx file directly in WPS Spreadsheet with perfect format retention.
3. Create a Pivot Table: Highlight your data, insert a Pivot Table, and right-click time fields to group them chronologically with ease.

Free and lightweight office suite with rapid startupSeamless compatibility with Microsoft Excel (.xlsx) formatsIntuitive Pivot Table grouping for dates, hours, and valuesFamiliar user interface with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Pivot sort my numbers as 1, 10, 11, 2?

This occurs because the column is formatted as text rather than numbers. Text sorting looks at characters from left to right, so '10' alphabetically precedes '2' because the character '1' comes before '2'.

Can I group times by hour directly in a standard Pivot Table?

Yes. In standard Pivot Tables (without using the Power Pivot Data Model), you can simply right-click a time-based field, select 'Group', and choose 'Hours' to automatically group and sort chronologically.

Does the 'Sort by Column' feature alter my original data?

No, using 'Sort by Column' strictly changes the visual sorting order of the data inside the Data Model and Pivot Tables. It does not overwrite or modify the underlying data in your source table.

Excel & Data Problems