Excel Pivot Table Sort Columns by Date

Charts & pivot tables · Updated November 22, 2026

Yes, you can sort pivot table columns by date. The fastest way is to right-click a date column header, choose Sort, then select Sort Newest to Oldest or Sort Oldest to Newest. If your dates appear as text (like Jan-2023) instead of true dates, you must first group them or fix the source data—otherwise Excel sorts alphabetically.

Why Pivot Table Dates Sometimes Sort Wrong

Pivot tables sort by the underlying data type. If your source column contains real Excel dates (serial numbers with a date format), the pivot table treats them as chronological values. But if the column contains text that looks like dates (for example, imported from a CSV or typed with a leading apostrophe), Excel sorts them alphabetically: “10/2023” comes before “2/2023” because “1” is less than “2”.

Another common cause: the pivot table has grouped the dates into months, quarters, or years. Grouped fields sort by the underlying date value, but if you group by month only (without year), all Januarys from different years lump together and sort by month number, not chronologically across years.

How to Sort Columns by Date in a Pivot Table

Here are the exact steps for Excel for Microsoft 365 on Windows:

  1. Click anywhere inside the pivot table.
  2. Right-click the column header that contains the dates (e.g., “Order Date”).
  3. Select Sort from the context menu.
  4. Choose Sort Newest to Oldest or Sort Oldest to Newest.

If you don’t see the Sort option, make sure the field is actually a column field, not a value field. Also, if the pivot table has multiple row or column fields, the sort applies to the selected field only.

On a Mac, the steps are identical: right-click (or Control-click) the column header, choose Sort, then select the order. In Excel for the web, right-click the header, then choose Sort Newest to Oldest or Sort Oldest to Newest from the context menu.

Sorting by Date When Dates Are Grouped

If you have grouped dates (e.g., months or quarters), you can still sort them chronologically. Right-click any grouped date cell, choose Sort, and pick the order. Excel sorts grouped fields by their underlying date value, so months appear in calendar order (Jan, Feb, Mar) rather than alphabetical (Apr, Aug, Dec).

However, if you grouped by month only (not by year), all Januarys from different years will appear together, and the sort will be by month number, not by year. To sort by year then month, you must include the year in the grouping. Right-click a date cell, choose Group, and select both Years and Months.

Using a Manual Sort Order for Custom Date Sequences

Sometimes you want a custom order, like fiscal year quarters (Q1 starting in April). You can drag and drop columns manually, but that only works for the current layout. To make it permanent, create a custom list:

  1. Go to File > Options > Advanced (Windows) or Excel > Preferences > Custom Lists (Mac).
  2. Under General, click Edit Custom Lists.
  3. Type your custom sequence (e.g., “Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar”) in the List entries box, then click Add.
  4. Now right-click the date column in the pivot table, choose Sort, and select More Sort Options. Under Sort order, choose your custom list.

This works because Excel uses the custom list as the sort order for that field. Note that the custom list must match the display values in the pivot table (e.g., if months appear as “Jan”, use “Jan” in the list, not “January”).

Common Problems and Fixes

  • Dates show as numbers: The source column might be formatted as General. Select the source data, press Ctrl+1 (Mac: Cmd+1), choose a date format, and refresh the pivot table.
  • Sort option is grayed out: This happens when the pivot table has a filter on the date field. Clear the filter (click the filter icon on the column header, select Clear Filter) and try again.
  • New dates don’t appear: If you add new rows to the source data, the pivot table won’t include them until you refresh. Right-click the pivot table and choose Refresh.
  • Sorting by date also sorts the row labels: That’s expected if the date field is in the Rows area. To sort columns, the date field must be in the Columns area.
  • Text dates from external data: Use Text to Columns (Data tab) or the DATEVALUE function to convert text to real dates before creating the pivot table.

Sorting with a Calculated Field or Measure

If your pivot table uses a calculated field (like “Days to Ship”) that depends on dates, sorting by that calculated field works the same way. Right-click the calculated field header and choose Sort. However, if you’re using Power Pivot or a Data Model, you might need to sort by the underlying date column instead. In that case, add the date field to the row or column area and sort it, then the calculated measure will follow that order.

For example, to sort by “Days to Ship” descending, right-click the “Days to Ship” column header, choose Sort, then Sort Largest to Smallest. The pivot table will reorder the columns based on that value, but the date labels will move with them.

Alternative: Using the Sort Dialog Box

If right-click sorting doesn’t give you enough control, use the Sort dialog:

  1. Right-click the date column header.
  2. Select Sort > More Sort Options.
  3. In the dialog, choose Manual (to drag) or Ascending (A to Z) by and select the field you want to sort by.
  4. For date fields, you can also choose Newest to Oldest directly from the dropdown.

This dialog also lets you sort by a different field than the one you right-clicked, which is useful if you have multiple column fields.

Frequently Asked Questions

Why does my pivot table sort dates alphabetically instead of chronologically?

Because the date column is stored as text. Check the source data: select the column, press Ctrl+1, and see if the format is “Text” or “General”. If it’s text, convert it to real dates using Data > Text to Columns (choose Delimited, then Date) or use the DATEVALUE function in a helper column. Then refresh the pivot table.

How do I sort pivot table columns by month name in chronological order?

Right-click a month cell, choose Sort, then Sort Oldest to Newest. Excel sorts months by their calendar order (Jan, Feb, Mar) because it recognizes them as date groupings. If it doesn’t, ensure the source dates are real dates and that you grouped them (right-click a date cell, choose Group, and select Months).

Can I sort by date in a pivot table that has multiple row fields?

Yes. Right-click the date column header (or row label if the date is in rows) and choose Sort. The sort applies to that field only, independent of other fields. For example, if you have “Region” and “Order Date” as row fields, you can sort the dates within each region by right-clicking the date field and choosing sort.

Why does my pivot table sort dates by year then month, but I want month then year?

That’s the default chronological order. To sort by month then year, you need to create a custom list. Go to File > Options > Advanced > Edit Custom Lists (Windows) or Excel > Preferences > Custom Lists (Mac), add a list like “Jan, Feb, Mar, …” (without years), then right-click the date field, choose Sort > More Sort Options, and select that custom list. Note that this will group all Januarys together, even from different years.

Does sorting columns by date affect the pivot table’s calculations?

No. Sorting only changes the order of the columns; it does not change the values or the totals. The sum, average, count, etc., remain the same because they are calculated based on the underlying data, not the column order.

How do I sort by date in a pivot table on Excel for Mac?

Right-click (or Control-click) the date column header, choose Sort, then select Sort Newest to Oldest or Sort Oldest to Newest. The steps are identical to Windows. If you need the Sort dialog, choose More Sort Options from the same menu.

Related

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.