Chart & Visualization Issues

How to Fix Incorrect mm:ss Sorting on Excel Pivot Chart Y-Axis

Adam Davis Sep 30, 2026 869 views

Question details

Correct the sorting order of average turnaround times formatted as mm:ss on a Pivot Chart Y-axis when durations exceed 60 minutes.

How to Fix Incorrect mm:ss Sorting on an Excel Pivot Chart Y-Axis

Product: Microsoft Excel
Device & OS: not provided
Scenario: Displaying and sorting average turnaround times that sometimes exceed 60 minutes within an Excel Pivot Chart.
Observed behavior: Values over 60 minutes roll over (e.g., 72 minutes displays as 12:00), causing the Pivot Chart Y-axis to sort the durations in the wrong order.

Before you start

Verify that your time data represents elapsed duration rather than a specific time of day, as this dictates how the spreadsheet should calculate the values.

Solution 1Recommended

Apply the [mm]:ss Elapsed Time Format

Use the bracketed minute format to force Excel to calculate total elapsed minutes, preventing values over 60 minutes from resetting like a standard clock.

By default, the mm:ss format is treated as a clock time. This means that once 59 minutes and 59 seconds pass, the timer rolls over to 00:00 for the next hour. Wrapping the minutes in square brackets tells the spreadsheet to continuously count elapsed minutes.

Select the Pivot Table Data

Locate the 'Average of Turn Time' column in your underlying Pivot Table.

Open the Format Cells Dialog

Right-click the selected data and choose 'Format Cells', or press Ctrl+1 on your keyboard.

Apply Custom Format

Navigate to the 'Custom' category. In the Type box, enter [mm]:ss and click OK.

Format the Chart Y-Axis

Double-click the Y-axis on your Pivot Chart to open the Format Axis pane. Go to the Number section and apply the same [mm]:ss custom format code.

Refresh the Pivot Table

Right-click anywhere inside the Pivot Table and select 'Refresh' to update the chart sorting.

Format Preserved: A duration like 72 minutes will now correctly display as 72:00, ensuring the Y-axis scales and sorts in perfect numerical order.

Solution 2

Use the [h]:mm:ss Format for Longer Durations

If your turnaround times frequently span multiple hours, format the data to explicitly show hours alongside minutes and seconds.

Data Visualization Solution

Fix Pivot Chart Sorting Issues Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced custom time formatting, including [mm]:ss, allowing you to accurately track, sort, and visualize elapsed turnaround times without axis glitches.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your Pivot Chart.
2. Select the Time Data: Highlight the turnaround time values in your Pivot Table.
3. Format as Elapsed Time: Press Ctrl+1 to open Format Cells, select Custom, and input [mm]:ss.
4. Refresh the Data: Right-click the Pivot Table and select Refresh to instantly correct the Y-axis sorting.

Fully compatible with Microsoft Excel file formats (.xlsx)Advanced Pivot Table and Chart visualization toolsSupports exact custom time and date formatting seamlesslyFree, lightweight, and easy-to-use alternative

microsoft office alternative - wps office

Frequently Asked Questions

Why does 72 minutes show up as 12:00 on my chart?

By default, the mm:ss format represents the minutes and seconds of a standard clock, which resets every 60 minutes. Therefore, 72 minutes is calculated as 1 hour and 12 minutes, displaying only the 12 minutes.

What do the square brackets in [mm]:ss mean?

Square brackets instruct the spreadsheet software to calculate total elapsed time rather than time of day. This allows the minutes value to exceed 59 without rolling over into a new hour.

Does this sorting fix apply to other spreadsheet tools like WPS Office?

Yes. The custom format codes [mm]:ss and [h]:mm:ss for elapsed time are standard across major spreadsheet applications, including WPS Spreadsheet, ensuring full cross-compatibility.

Excel & Data Problems