Excel Pivot Table by Month: Group Dates the Right Way

Charts & pivot tables · Updated November 18, 2026

To make an Excel PivotTable by month, put a genuine date field in the Rows area, put the measure you want to summarize in Values, then right-click a date in the PivotTable and choose Group > Months. Select Years as well if your data spans multiple years; otherwise, January from every year will be combined into one January total.

What a monthly PivotTable does

A monthly PivotTable summarizes detailed records into month-sized groups. For example, a sales list might contain one row for each order, with an order date, customer, and amount. Grouping the order date by month lets you see totals such as January sales and February sales without manually creating a separate formula for each month.

The source data stays at its original level of detail. Grouping changes how the PivotTable displays and summarizes the dates; it does not turn the source dates into month labels or remove individual transactions. You can expand a month to inspect its underlying dates, or change the layout to show another measure, such as order count or average sale.

A PivotTable is especially useful when you need to compare months, filter results by category, or update the summary as new records arrive. It is not the same as formatting dates to display only a month name: formatting changes what a cell looks like, while PivotTable grouping creates a summary category.

Prepare the source dates first

Excel can group a date field only when its entries are recognized as dates. Dates in Excel are stored as date values, even though they are displayed in familiar formats such as 4/15/2025. A cell containing the text “April 15, 2025” may look like a date but still be text, and a PivotTable cannot reliably group text as calendar months.

Before building the PivotTable, check the source column for blank cells, text dates, error values, and inconsistent entries. A single blank or non-date value in the date field can prevent date grouping or cause an error. If the data came from a CSV, report, or copied web page, confirm that Excel interpreted the dates correctly rather than assuming the appearance proves they are valid dates.

  • Use one header row with a clear field name, such as Order Date. Avoid merged cells and completely blank rows or columns inside the data.
  • Keep each record on its own row, and ensure the date field contains a date for every record you want included.
  • Store amounts or quantities as numbers if you want Excel to sum or average them.
  • Consider converting the source range to an Excel Table before creating the PivotTable. A Table can expand when new rows are added, although the PivotTable still needs to be refreshed to include those records.

Build the PivotTable and group dates

In Excel for Microsoft 365 on Windows, click a cell in the source data and choose Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK. In the PivotTable Fields pane, drag the date field to Rows and the numeric field you want to summarize to Values. Excel commonly puts a numeric field there as Sum; a text field is more likely to be counted.

  1. Click a date value in the PivotTable’s row labels, not a cell in the original source list.
  2. Right-click and choose Group. In the Grouping dialog, select Months. If the records cover more than one year, also select Years.
  3. Choose OK. The row labels should now show monthly groups, and the Values area should display the chosen calculation for each group.

For example, suppose the source has sales of $120 on January 5, 2025, and $80 on January 18, 2025. The January 2025 group totals $200. If February records are $150 and $50, February totals $200. If there is also a $200 sale in January 2026, selecting both Months and Years gives separate January 2025 and January 2026 rows. Selecting Months alone combines those January sales into $400.

On Excel for Mac, the core workflow is similar: create the PivotTable from the Insert tab, put the date field in Rows, and use the date field’s context menu to find Group. Ribbon and context-menu details can vary slightly by build. Excel for the web can display and work with PivotTables, but if the Group command is unavailable for your workbook, open it in the desktop app to create or change the date grouping.

Choose the right month and year layout

The key choice is whether the summary should distinguish years. If the source contains one year, grouping by Months is usually clear. If it contains several years, grouping by Months and Years avoids combining different periods that happen to share a month name. A report showing only Jan, Feb, and Mar can be misleading when it includes multiple years because each label may represent several years of data.

After grouping, Excel may show Year and Month as separate row fields. You can leave both fields in Rows for a hierarchy, or rearrange fields in the PivotTable Fields pane to change the report layout. You can also put Year in Filters or Columns when that better suits the report. Use the expand and collapse controls beside year labels to show or hide the months beneath them.

Month grouping uses calendar months. It does not automatically create a company’s fiscal periods when the fiscal year begins in a month other than January. For fiscal reporting, use a suitable fiscal-period field in the source data or a calendar table, then summarize by that field. Make sure the period labels sort in fiscal order; text labels alone can sort alphabetically rather than chronologically.

Make the monthly summary easier to read

Apply a number format to the values rather than changing the underlying data. For sales, select a value in the PivotTable and use the value field’s Number Format settings to show currency, decimal places, or thousands separators consistently. Formatting the field is generally preferable to formatting individual cells because PivotTables can change shape when refreshed.

Check the calculation shown in the Values area. A field containing numeric amounts should normally use Sum for total sales. If Excel displays Count of Amount instead, it may have detected text or blanks in the source field, or the field may not be consistently numeric. Open Value Field Settings to choose an available calculation, and correct the source data if a sum is intended but cannot be calculated correctly.

For a trend view, you can add a PivotChart after the summary is arranged. A line chart can make month-to-month movement easier to see, while a column chart is often useful for comparing monthly totals. Keep the year visible or filter to one year if multiple years would otherwise create confusing repeated month labels.

Use a helper column when grouping is unavailable

Manual grouping may be unavailable for some PivotTables, including certain PivotTables based on the Data Model or an OLAP source. It can also be useful to create an explicit month field when you want a predictable month-start date, need to combine data from different sources, or want to build a fiscal calendar. A helper column keeps the original transaction date intact while giving the PivotTable a separate field to summarize.

With transaction dates in A2:A100, enter this formula in the first row of a new Month Start column and fill it down: =DATE(YEAR(A2),MONTH(A2),1). For a valid date in A2, the result is the first day of that same month—for example, a date in April 2025 returns April 1, 2025. Format the helper column as mmm yyyy if you want labels such as Apr 2025, then refresh or create the PivotTable and place Month Start in Rows.

This produces actual dates, not text labels, so the month field can sort chronologically. It also keeps different years separate. If the desired fiscal periods do not follow calendar months, a simple month-start formula is not enough; add a fiscal year or fiscal period field that reflects the organization’s reporting calendar.

Common grouping problems and fixes

  • Group is unavailable or grouping fails: Inspect the entire source date column for blanks, text, errors, or values that are not dates. Correct the source, then refresh the PivotTable and try again.
  • All years’ January values appear together: Edit the grouping and select Years as well as Months, or use a month-start helper field. Month-only grouping intentionally combines the same named month across years.
  • New records do not appear: Refresh the PivotTable after changing its source data. If new rows are outside a fixed source range, expand the source or use an Excel Table as the PivotTable source, then refresh.
  • Amounts are counted instead of summed: Check that the source values are numeric and consistent, then use Value Field Settings to select Sum where available.
  • Months look out of order: Use date values or grouped date fields rather than text month names. Text labels can sort alphabetically; date-based fields sort by calendar order.
  • The displayed dates look wrong: Check the field’s number format and the source values. A date format changes display, not the stored date; it cannot convert an invalid text entry into a real date by itself.

Refresh and maintain the report

A PivotTable is a summary based on its source, not a live view that recalculates its structure every time a source row changes. After editing existing records or adding transactions, refresh it using PivotTable Analyze > Refresh in Excel for Windows. You can also right-click inside the PivotTable and choose Refresh. If the source range has not expanded to include new rows, refresh alone will not bring those rows in; update the source or use an Excel Table as the source.

For a recurring monthly report, keep the source data in a consistent, append-friendly layout and make the reporting period clear. Check that the latest date is included, confirm the year grouping, and verify that the total matches the source records for a selected period. If several PivotTables use the same source, refresh each relevant report or use the workbook’s refresh options as appropriate.

Frequently Asked Questions

Can I group a PivotTable by month without grouping by year?
Yes. Select Months without Years in the Grouping dialog. This combines January from every year, February from every year, and so on, which is useful for comparing typical monthly patterns but not for reporting separate annual results.

Why can’t I group dates in my PivotTable?
The source date field may contain blanks, text dates, errors, or mixed values. Grouping can also be restricted for PivotTables built from some Data Model or OLAP sources. Correct the source or use a separate month or calendar field.

How do I show Jan 2025 instead of a full date?
Group the field by Months and Years and apply a suitable date display format, or use a month-start helper date formatted as mmm yyyy. Keep the underlying field as a date so the labels remain chronologically meaningful.

Does a PivotTable update automatically when I add another month?
Not always. Refresh the PivotTable after adding records, and make sure its source includes the new rows. Using an Excel Table as the source helps the source range expand as rows are added, but the PivotTable still needs refreshing.

Can I group months in a fiscal year?
Calendar month grouping does not define a custom fiscal year. Add fiscal year and period fields to the source or use a calendar table that maps each date to the organization’s fiscal periods, then use those fields in the PivotTable.

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.