To sort dates in an Excel PivotTable, click a date label, right-click it, and choose Sort > Sort Oldest to Newest or Sort > Sort Newest to Oldest. If those choices do not produce a chronological order, the field may contain text rather than real dates, or the dates may be grouped into months, quarters, or years.
Sort PivotTable Dates Chronologically
These steps apply to PivotTables in Excel for Microsoft 365 on Windows. They work whether the date field appears in the Rows area or the Columns area. Sorting the PivotTable changes how its items are displayed; it does not rearrange the rows in the original source data.
- Click a date label inside the PivotTable. Choose a date item, not a number in the values area.
- Right-click the selected label and point to Sort.
- Choose Sort Oldest to Newest for ascending order, or Sort Newest to Oldest for descending order.
Oldest to newest means the earliest calendar date appears first. For example, a list containing March 12, 2025; January 5, 2024; and October 9, 2024 will display January 5, 2024, October 9, 2024, then March 12, 2025. Newest to oldest reverses that sequence.
You can also use the field’s filter arrow when it is visible beside the row or column labels. Open the arrow and select the available ascending or descending date sort. The exact wording can vary with the field layout, but the right-click menu is a dependable way to sort a selected item. If the PivotTable has several fields in Rows, make sure you select an item from the date field you intend to sort.
What Excel Is Sorting
A PivotTable does not simply sort the visible text as though it were a list of words. A genuine Excel date is stored as a date value, and Excel can order those values by time. The cell format controls how a date is displayed—for example, 3/12/2025 or Mar 12, 2025—but changing the display format does not change the underlying date or its chronological position.
That distinction matters when a column looks like dates but contains text. Text values can sort alphabetically or in an order that appears inconsistent with the calendar. For example, text dates written as 1/10/2025 and 12/2/2024 may not sort by their intended dates, because Excel may be comparing characters rather than date values. A PivotTable generally reflects the underlying values and types in its source field, not just what the cells look like.
Also distinguish sorting labels from sorting results. Sorting the date field orders dates or date groups. Sorting a sales total from largest to smallest orders the date labels according to their associated sales values instead. If the goal is to see the highest-revenue month first, sort by the value field; if the goal is a timeline, sort the date labels chronologically.
Group Dates by Month, Quarter, or Year
Grouping is useful when a PivotTable has one row per transaction but you want a summary by month, quarter, or year. In many current Excel versions, adding a date field to the Rows or Columns area may cause Excel to group it automatically. If the PivotTable displays separate fields such as Years and Quarters, Excel is showing grouped date levels rather than a simple list of individual dates.
To choose grouping yourself, right-click a date item in the PivotTable and select Group. In the Grouping dialog, select the intervals you need, such as Months, Quarters, or Years, and confirm. The available choices depend on the date field and the data. To return to individual dates, right-click a grouped label and choose Ungroup.
Grouping by month alone can hide an important distinction: January 2024 and January 2025 may be combined into a single January category. For a multi-year report, include Years as well as Months, or keep the year as a separate level in the PivotTable. Then the report can show January under each year instead of merging all Januaries together. A month-level report can still be sorted chronologically when its year context is preserved.
When a grouped field looks out of order, check which level you clicked. Sorting the year level will order years, while sorting the month level affects months within the grouping. Grouping is not the same as changing the date format: a format such as “mmm” only changes how each date appears, whereas grouping changes the categories displayed in the PivotTable.
Sort Labels or Sort by a Value
Chronological sorting and value sorting answer different questions. Suppose a PivotTable has months in the rows and total sales in the values. Sorting the month labels oldest to newest creates a timeline. Sorting by total sales from largest to smallest ranks the months by performance, so the sequence will no longer be chronological.
To sort by a measure, select a value in the measure’s column, right-click, and use the sort options for that value, choosing largest-to-smallest or smallest-to-largest as appropriate. Check the result and the field labels: the intended operation should rearrange the date categories based on the selected measure. This is especially useful for questions such as “Which month had the most orders?” For a trend chart or time series, chronological order is usually easier to interpret.
For nested row fields—for example, Year above Month—sorting one field may not reorder every level in the way you expect. Keep the hierarchy in mind and select a label at the level you want to change. If you want each month nested within its year, preserve the year field and sort months within that structure rather than trying to create one flat month-name list.
Fix Dates Stored as Text
If Excel sorts date-looking labels alphabetically, inspect the source column before changing the PivotTable. A source field can contain a mixture of real dates, text dates, blanks, and other entries. This often happens after importing a CSV, combining files from different regions, or pasting values from a web page. Different regional conventions can also make a string such as 04/05/2025 ambiguous: it might represent April 5 or May 4.
- In the source data, select several entries and check whether Excel recognizes them as dates. Changing the number format to a date style can help reveal real date values, but formatting text does not convert it into a date.
- Correct the source values using an appropriate conversion method for the file and its date convention. Confirm a few converted dates against the original records before relying on the summary.
- Check for blanks, error values, or text mixed into the date column. These entries may appear as separate PivotTable items or prevent date grouping.
- Refresh the PivotTable after fixing the source, then apply the chronological sort again.
Do not assume that a date display proves the cell contains a date value. A text string can be formatted to look similar, but sorting and grouping may still behave differently. If the original dates are ambiguous, find out whether the source uses month/day/year or day/month/year before converting anything; guessing can silently switch dates and produce a plausible but incorrect report.
Refresh the PivotTable and Check Its Source
A PivotTable uses a cached summary of its source data. If you add transactions, correct dates, or expand the source range, the PivotTable may not reflect the change until you refresh it. Right-click inside the PivotTable and select Refresh. To refresh all PivotTables and other supported connections in the workbook, use Data > Refresh All.
If newly added dates still do not appear, check whether the PivotTable’s source includes the added rows. A source range that was selected manually may stop at the original last row. Converting the source data to an Excel table with Insert > Table is often a practical way to make a growing dataset easier to manage; after adding rows, refresh the PivotTable. Verify the source in the PivotTable’s data-source settings if the range remains uncertain.
Refresh can update the PivotTable’s available items, but it does not repair text dates or decide how ambiguous strings should be interpreted. Fix the source types first, then refresh and sort. If old labels remain after a refresh, inspect the field’s item-retention settings or confirm that the old entries are not still present in the source.
Common Sorting Problems
- Dates appear alphabetically: The source may contain text. Convert and verify the source values, refresh, and sort again.
- Months are in an unexpected order: The field may be month names treated as text, or the PivotTable may have a grouped month level without a year. Use a true date field and include the year when the report spans multiple years.
- Grouping is unavailable: Look for blanks, errors, or non-date values in the source date field. A mixed or invalid field can prevent Excel from grouping the entries as dates.
- The sort changes only part of the report: The field may be nested with other row or column fields. Select a label at the correct hierarchy level and check the resulting order.
- New dates are missing: Refresh the PivotTable and confirm that its source range includes the new records.
Another common misconception is that sorting the source worksheet automatically sorts the PivotTable. The PivotTable has its own layout and sort order, so sort the PivotTable labels directly. Conversely, sorting the PivotTable does not reorder the underlying transaction list. Keep the original data in a useful order for data entry or review, and set the summary’s order separately.
Excel for Mac and Excel for the Web
The basic approach is similar in Excel for Mac: select a date label in the PivotTable and use the context menu’s sort options, or use the field dropdown when available. Menu wording and the position of PivotTable commands can differ slightly between Windows and Mac releases. On a Mac, use a two-finger click or Control-click if needed to open the context menu. The Windows right-click steps above should not be interpreted as Windows-only PivotTable behavior.
Excel for the web supports working with PivotTables, but available commands can differ from the desktop app and may change as the web version is updated. If a grouping or source-management command is not offered in the browser, open the workbook in Excel for Microsoft 365 on Windows or Mac and make the change there. In all versions, the key checks are the field type, the selected hierarchy level, and whether the PivotTable has been refreshed.
Frequently Asked Questions
How do I sort a PivotTable from oldest to newest?
Click a date label in the PivotTable, right-click it, and choose Sort > Sort Oldest to Newest. If the result is not chronological, check whether the source dates are stored as text.
How do I sort a PivotTable from newest to oldest?
Right-click a date item and choose Sort > Sort Newest to Oldest. This puts the latest date or date group first.
Why are my PivotTable dates sorting alphabetically?
The source field may contain text strings that look like dates, or it may mix text and real date values. Convert the values consistently, verify ambiguous regional date formats, refresh the PivotTable, and sort again.
Can I sort PivotTable dates by month?
Yes. Group a genuine date field by Months, and include Years when the source spans more than one year. Otherwise, January from different years can be combined into one category.
Does sorting the PivotTable sort my original data?
No. Sorting PivotTable labels changes the summary’s display order. It does not rearrange the rows in the source worksheet.
Related
- Password Protect an Excel File from Being Opened
- Password Protect Excel to Prevent Opening
- Password Protect an Excel File Before Opening It Safely
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sort Entire Row by One Column Without Splitting DataYes. To sort entire rows by one column in Excel for Microsoft 365 on Windows, select the full…
- Excel Time Between Two Timestamps: Formulas and TipsIf the start timestamp is in A2 and the end timestamp is in B2, enter =B2-A2 to calculate…
- Excel Weekdays Between Dates: Formulas, Holidays, and TipsTo count weekdays between two dates in Excel, use =NETWORKDAYS(A2,B2), with the start date in A2 and the…
- Sort Excel Workbook Tabs Alphabetically in ExcelExcel does not have a built-in command that automatically sorts workbook tabs alphabetically. In Microsoft Excel for Microsoft…
- How to Sort Dates in Excel PivotTables (Oldest to Newest)To sort dates in an Excel PivotTable, click a date item in the PivotTable, right-click it, and choose…
- VLOOKUP in Excel: Practical Tips for Reliable ResultsFor a reliable VLOOKUP in Excel, put the value you want to find in the first column of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.