Group Dates by Month in an Excel PivotTable: Steps

How-to basics · Updated November 20, 2026

To group dates by month in an Excel PivotTable, put a real date field in the Rows area, right-click one of its dates, choose Group, then select Months in the Grouping dialog. If your data spans more than one year, select Years as well so January from different years is not combined into one total. This guide is for Excel for Microsoft 365 on Windows; the same grouping dialog is available in desktop Excel for Mac, although you may need to Control-click instead of right-clicking.

What You’ll Need

  • A source list with a column of valid Excel dates and a header such as Order Date. The cells need to contain date values, not text that only looks like dates.
  • A PivotTable based on that list. If you have not created one, select a cell in the data and choose Insert > PivotTable, confirm the range and destination, and select OK.
  • For reliable updates, keep the source data in an Excel table. Select a source cell and choose Insert > Table if it is not already a table. Adding rows to a table can make it easier for a PivotTable to include new records when refreshed.

Step-by-Step

  1. Check the date column. In the source data, confirm that every relevant value is a valid date and that the column has one clear header. Excel dates are stored as numbers, even when the cells are formatted to display a date. A value imported as text may look identical but will not behave as a date during grouping. Also check for blanks, error values, or words mixed into the date column; clean those up before proceeding.
  2. Open the PivotTable field list. Click anywhere inside the PivotTable. If the PivotTable Fields pane is not visible, use PivotTable Analyze > Field List on the ribbon. In Excel for Mac, click inside the PivotTable and show the field list from the PivotTable tab if it is hidden. The pane lets you place the date field and other fields into the report.
  3. Put the date field in Rows. In the field list, drag the date field, such as Order Date, to the Rows area. You can also select its checkbox and then move it to Rows if Excel places it somewhere else. The PivotTable should show individual dates down the left side. If the dates appear across the top, drag the field from Columns to Rows.
  4. Select a date item. Click one of the displayed dates in the PivotTable, not a label in the source data and not the date-field heading. You only need to select one date in the field you want to group. If the field currently shows a hierarchy such as years or quarters, expand or adjust the field so that you can select an individual date item.
  5. Open the grouping dialog. Right-click the selected date and choose Group from the context menu. In Excel for Mac, right-click or Control-click the date. You can also look for PivotTable Analyze > Group > Group Selection on the ribbon when the PivotTable is active. The Grouping dialog lists time units that can be used for the selected date field.
  6. Choose the month grouping. In the By section, select Months. To keep different calendar years separate, also select Years. For example, this produces separate entries such as January 2024 and January 2025 rather than one January total that combines both years. Click OK to apply the grouping. If all records are from a single year, Months alone may be sufficient.
  7. Check the resulting layout. Excel adds grouped date fields to the PivotTable, commonly showing Years and Months in the Rows area. Expand a year to see its months, or rearrange the year and month fields in the Rows area to change the nesting. Keep Years above Months when you want each year to contain its own January-through-December list. The underlying source dates remain unchanged.
  8. Add a value to summarize and refresh when needed. Drag a measure such as Sales or Quantity to Values if you want monthly totals. If the result shows Count instead of Sum, see the troubleshooting section below. When you add or correct source records later, click in the PivotTable and choose PivotTable Analyze > Refresh, or right-click and choose Refresh. Refreshing updates the PivotTable from its source; it does not fix invalid date entries.
  9. Undo the grouping if necessary. To return to individual dates, right-click a grouped date label and choose Ungroup. You can then select the date field and apply a different grouping. Ungrouping changes the PivotTable display, not the dates in the source list.

Common Mistakes

Selecting Months without Years across multiple years. This is a common source of misleading totals. If you select Months alone, Excel can combine every January into one January row, every February into one February row, and so on. That can be useful for comparing seasonal patterns, but it is not the same as reporting each month-year separately. Select both Months and Years when the report needs distinct calendar months.

Using text dates instead of date values. A column may display entries like 4/5/2025 while storing them as text. Excel may interpret that as April 5 or May 4 depending on regional settings, or it may not recognize it as a date at all. Do not assume the display format proves that the cell contains a real date. Check by changing a test cell’s number format to General; a valid date usually displays as a serial number. Restore the date format afterward.

Including blank or inconsistent entries. A blank cell, a text note such as Unknown, or an error in the date field can prevent grouping or leave an unexpected blank item. A single invalid value among thousands of good dates is enough to cause trouble. Clean the whole source column, not just the rows currently visible in the PivotTable.

Expecting a refresh to change the source range automatically. Refresh updates records within the PivotTable’s existing source. If new rows are outside a fixed source range, the refresh may not include them. Use an Excel table as the source or update the source through PivotTable Analyze > Change Data Source, then refresh.

Confusing calendar months with fiscal periods. Months groups dates into calendar months. It does not know that a company has a fiscal year starting in July, nor does it create custom four-week periods. Do not label calendar-year groupings as fiscal periods unless the boundaries truly match.

Tips

  • Pick the grouping that matches the question. Months and Years is best for a chronological monthly report; Months alone can help compare all Januaries, all Februaries, and so on across years. Quarters and Years can provide a higher-level view. In the Grouping dialog you can select more than one unit when the units are compatible.
  • Sort chronologically. Grouped date items are date-based, so they can be arranged in time order. If your month labels are instead plain text from a source column, they may sort alphabetically, putting April before February. Use the actual date field for grouping, or use a separate month number alongside text labels when designing a custom report.
  • Format the values for readability. Right-click a value in the PivotTable and choose Number Format to set currency, number of decimals, or another appropriate display. Applying number formatting through the PivotTable value field is generally more durable than formatting selected cells individually.
  • Use a clear layout for sharing. Put Years above Months in Rows, keep the measure in Values, and give the value field a useful name. If the report is long, expand the years relevant to the reader and collapse the rest. This keeps the monthly pattern visible without changing the source data.
  • Remember what grouping does not do. Grouping is a PivotTable presentation and calculation feature; it does not convert dates into month-start dates in your source list. If another report or formula needs a persistent month key, add a separate month-start or month-label column in the source data rather than relying on the PivotTable’s display alone.
  • Refresh after edits. PivotTables do not always recalculate as soon as source values change. Refresh after correcting date values, adding records, or changing the source range, then check the latest month and the grand total to confirm the report includes the expected data.

Troubleshooting

Group is unavailable or Excel says the selection cannot be grouped. First check the entire source date column for blanks, text, errors, or mixed data types, including rows that are filtered out of the PivotTable. Correct or remove invalid entries, refresh the PivotTable, and try grouping again. If dates were imported as text, convert them to genuine dates using a suitable import or conversion method, then verify a few cells and refresh. Be careful with ambiguous dates such as 03/04/2025: confirm the intended day and month under your regional settings before converting a full column.

The grouped result combines years. Right-click a month label and choose Group, then select Years as well as Months. If Excel does not let you change the existing selection as expected, choose Ungroup first, select a date item, and group again with both units checked. Confirm that the year field is above the month field in Rows so that the months appear beneath the correct year.

Some recent dates are missing. Click the PivotTable and choose PivotTable Analyze > Change Data Source to inspect the source range. Extend a fixed range to include the new records, or convert the source list to an Excel table and use that table as the PivotTable source. Then choose Refresh. Also check date filters, report filters, and slicers, which can hide records even when the source range is correct.

The Values area shows Count instead of Sum. This usually means the values being summarized include text or blanks, or Excel inferred the field as nonnumeric. Check the source measure column for text-formatted numbers and inconsistent entries. After correcting the source, refresh. If Count is intentional, use Value Field Settings to choose the desired summary, such as Sum, where the data supports it.

The Grouping dialog has no useful date options. Make sure you selected a date item in the PivotTable rather than a heading or a value from another field. If the PivotTable is based on the Data Model or an OLAP source, grouping options can be restricted. In that case, use a calendar or date table with month and year fields, or add appropriate month/year fields to the source or model and use those fields in the PivotTable. If you are working in Excel for the web and cannot find the grouping command, open the workbook in desktop Excel for Windows or Mac to apply the grouping.

New dates appear as ungrouped items or the layout looks stale. Refresh the PivotTable and check that the added dates are real dates within the source range. If the group boundaries no longer suit the data, ungroup and group the field again. A PivotTable can retain its prior structure after source edits, so inspect the Rows area and the visible year and month labels rather than assuming the report reorganized itself.

Frequently Asked Questions

Can I group dates by month without showing years?

Yes. Select Months without Years in the Grouping dialog. This combines the same named month across all years in the source, so use it only when that combined comparison is what you want. For month-by-month reporting across multiple years, include Years too.

Can I group dates by month and quarter at the same time?

Excel’s date grouping dialog offers units such as Months, Quarters, and Years, but the resulting layout depends on the units selected and the PivotTable field arrangement. For a clear year-quarter-month hierarchy, select the relevant units and arrange the generated fields in Rows. If the dialog does not allow the combination you need, use a calendar table with separate Year, Quarter, and Month fields.

Will grouping change my original date column?

No. Grouping changes how the PivotTable summarizes and displays the date field. It does not rewrite the original dates in the source data. If you need a month value in the source for another task, create a separate source field.

Why does my month order look wrong?

If the PivotTable is grouped from actual date values, months should follow calendar order. If month names came from a text field, Excel may sort them alphabetically or according to a manual sort. Use the original date field for grouping, or provide a calendar table that pairs each month label with a numeric month and year.

Does this work in Excel for Mac?

Yes, date grouping is available in desktop Excel for Mac. Select a date item in the PivotTable and use the Group command; Control-click can open the context menu if your mouse does not have a right button. Ribbon labels and their placement can differ slightly from Windows. Excel for the web may not expose the same grouping controls, so use desktop Excel if the command is missing.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.