How to Sort Dates in Excel PivotTables (Oldest to Newest)

Formulas & functions · Updated November 20, 2026

To sort dates in an Excel PivotTable, click a date item in the PivotTable, right-click it, and choose Sort > Sort Oldest to Newest or Sort Newest to Oldest. For the order to be chronological, the source column must contain real Excel dates, not text that merely looks like dates. The steps below cover checking your source, sorting, and fixing common problems in Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

  • A workbook with source data that includes a date column and any related values or categories you want to summarize.
  • An existing PivotTable, or permission to create one from the data.
  • Access to the source data if you need to correct text dates or add records before refreshing the PivotTable.

It helps if each source column has a single, clear heading and there are no completely blank rows within the data. If you are making a new PivotTable, click a cell in the source range and use Insert > PivotTable. Add the date field to Rows or Columns, as appropriate, before sorting.

Step-by-Step

  1. Check that the source dates are actual dates. Go to the source table and inspect a few cells in the date column. Select a cell and look at the formula bar: a date stored by Excel usually appears as a date value, and changing its number format should change how it is displayed. A quick visual check is not conclusive, because text can be formatted to look like a date. If sorting the PivotTable later puts dates in an odd order, text dates are a common cause. Also check for blanks, errors, or mixed formats such as some dates entered as month/day/year and others as day/month/year.
  2. Locate the date field in the PivotTable. The date field normally needs to be in the PivotTable’s Rows area for a vertical list or its Columns area for a horizontal sequence. If the PivotTable Fields pane is closed, click inside the PivotTable and choose PivotTable Analyze > Field List. In the pane, confirm that the date field is in Rows or Columns. A date placed in Values is treated as a value to summarize, not as a list of date labels to sort in the same way.
  3. Choose one date item, not the heading. Click a visible date inside the PivotTable body. Avoid clicking the heading cell, a blank cell, or a value in the totals area. If the dates are arranged across columns, click one of the date labels at the top of those columns. Selecting an item in the field you want to sort helps Excel apply the sort to the correct PivotTable field.
  4. Sort from oldest to newest. Right-click the selected date item, point to Sort, and choose Sort Oldest to Newest. Excel orders the date labels from the earliest date to the latest. For example, a list containing March 12, April 2, and January 25 will be ordered January 25, March 12, then April 2, regardless of the original order of the source rows.
  5. Use the reverse order when needed. To put the latest date first, right-click a date item, choose Sort, and select Sort Newest to Oldest. This is useful for reports where the most recent activity should be easiest to find. The command changes the order of the date labels; it does not delete dates or change the underlying source data.
  6. Check the field’s sort options if the quick command is unavailable. Click a date item, open PivotTable Analyze > Sort, and review the available sort choices. Depending on the layout and Excel build, you can also open the field’s drop-down arrow beside its row or column label and look for the date sort commands. If you use More Sort Options, choose ascending or descending and make sure the sort is based on the date field rather than a value field. The wording and location can vary slightly between layouts.
  7. Decide how grouped dates should appear. If Excel has grouped the date field into Years, Quarters, or Months, the PivotTable may display a hierarchy rather than individual dates. Expand or collapse the relevant level using its plus/minus controls, then sort the date level you want to inspect. To change grouping, right-click a date item and choose Ungroup to return to individual dates, or choose Group to select available intervals. Grouping changes how labels are displayed; it is not a replacement for sorting the underlying date values.
  8. Refresh after changing the source data. If you corrected dates or added records, click inside the PivotTable and choose PivotTable Analyze > Refresh. If the source range itself has expanded, use PivotTable Analyze > Change Data Source to include the new rows, or use an Excel Table as the source so that added rows are included when refreshed. Then apply the desired sort again if the displayed order has changed.

On Excel for Mac, the same general approach applies: select a date label and use its contextual menu to find the sort options. If your mouse does not have a right button, Control-click the label. Ribbon and menu placement can differ slightly by Excel version. In Excel for the web, use the date label’s context menu or field menu where available; if a needed PivotTable command is missing, open the workbook in the desktop app.

Common Mistakes

  • Sorting the source range instead of the PivotTable. Sorting the source table changes the order of its records, but it does not always set the PivotTable’s label sort. Sort a date item inside the PivotTable for the report order you want.
  • Assuming date-looking text is a date. Values such as 04/05/2025 can be ambiguous, and text entries may sort alphabetically or in an unexpected sequence. Check and convert the source values using the correct regional date order before refreshing.
  • Sorting the wrong field. A PivotTable can contain dates, categories, and totals together. Clicking a value cell or a different label can sort a different field. Start by selecting a date label in the field you intend to reorder.
  • Expecting a sort to repair the source. Sorting only changes the order of labels in the PivotTable. It will not turn text into dates, remove invalid entries, or fix a source column with mixed meanings.
  • Forgetting grouping or filters. A filter may hide dates, and grouping may show years or months instead of individual days. The visible order can look incomplete even when the dates are sorted correctly. Check the field’s filter and expand the date hierarchy before judging the result.

Tips

  • Use consistent source data. Keep one date per cell, use a consistent interpretation of day and month, and avoid mixing notes such as “ongoing” into the date column. Put comments or status information in separate columns.
  • Format dates for readability after sorting. If labels are difficult to scan, change the date format in the source data or use the available field number-format settings in the PivotTable. Formatting changes how a date appears, not its chronological value. Avoid typing over individual PivotTable labels, because they are generated from the source.
  • Use a date hierarchy when the report needs summaries. Grouping by year, quarter, or month is often easier to read than hundreds of daily labels. Keep in mind that a month-only summary may combine the same month from different years; include Year too when the distinction matters.
  • Refresh deliberately. A PivotTable may use a saved cache of its source data. After correcting a source date, refresh so the PivotTable reads the updated value. If you add rows, verify that the source range includes them before refreshing.
  • Keep the source field intact. If you need both the exact transaction date and a month or year summary, retain the original date column. Use grouping for the summary instead of replacing the source dates with month-name text.

Troubleshooting

The dates still sort alphabetically or strangely. Return to the source and check whether the values are stored as text. One way to convert consistently formatted text dates is to select the source date cells and use Data > Text to Columns, follow the wizard, set the correct date order in the final step, and select Finish. The correct choice depends on the source, such as MDY or DMY; choosing incorrectly can swap month and day. Check a few converted cells, refresh the PivotTable, and sort again. If the column contains mixed formats, fix those entries individually or standardize them before conversion.

Some dates are missing. Open the date field’s filter menu and check whether items have been unchecked. Also inspect the source for blank dates, errors, or rows outside the PivotTable’s source range. If new rows were appended below the original range, choose PivotTable Analyze > Change Data Source and select the complete range, then refresh. Converting the source to an Excel Table before building the PivotTable can make future additions easier to include.

The sort commands are disabled or do not affect the expected labels. Click inside the PivotTable, select an actual date label, and confirm that the date field is in Rows or Columns. If the field is in Values, move it to Rows or Columns in the PivotTable Fields pane. Also check whether the report is protected or whether the selected cell belongs to a different field. Try the field drop-down or the Sort command on the PivotTable Analyze tab after selecting the correct label.

Grouped dates appear out of order or show unexpected levels. Check whether the displayed labels are years, quarters, or months rather than individual date values. Expand the hierarchy and sort the relevant level. If the grouping is not suitable, right-click a grouped date label and choose Ungroup, then apply the sort to the individual dates. If Excel will not group the field, look for blanks or non-date entries in the source column and correct them before refreshing.

The order changes after refresh. Refreshing updates the PivotTable from its source and can reveal new labels or changed values. Reapply the sort to the date label. If you rely on a specific reporting order, check the PivotTable’s sort options and ensure it is sorting by the date field, not by a calculated total or another value.

Frequently Asked Questions

Can I sort a PivotTable by date from oldest to newest?
Yes. Select a date label, right-click it, and choose Sort > Sort Oldest to Newest. Use Sort Newest to Oldest for the reverse order.

Why are my PivotTable dates sorted alphabetically?
The source values may be text rather than real dates, or the field may contain inconsistent date formats. Convert the source values using the correct date order, refresh the PivotTable, and sort again.

Can I sort dates from newest to oldest by default?
You can set the PivotTable’s visible label order to newest first using the descending date sort. After changing the source or refreshing, check that the order remains as intended and reapply the sort if necessary.

Can I sort dates by month while keeping the years separate?
Yes. Use the date field’s grouping options to group by Years and Months, then expand the levels you need. Including Year prevents January from different years from being treated as one undifferentiated month in the report.

Will sorting the PivotTable reorder my original data?
No. Sorting date labels in a PivotTable changes the report’s display order, not the sequence of rows in the source range. To change the source order, sort the source data separately.

Why do new dates not appear after I sort?
Sorting does not add source records. Check that the PivotTable source range includes the new rows, update the source range if needed, and choose PivotTable Analyze > Refresh.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.