Pivot Table Sum by Month: How to Group Dates

To sum values by month in a pivot table, group the date field by month and then add the value field to the Values area. This article explains the exact steps, common pitfalls, and advanced tips for grouping dates by month in Excel and Google Sheets.

Formula

The pivot table itself doesn’t use a single formula—it aggregates data. But the underlying calculation for a sum by month is simply:

Sum of Value for Month = Σ(Value for each row where the date falls in that month)

When you group by month, the pivot table automatically sums all values that share the same month and year. For example, if your data has dates in 2023 and 2024, the pivot table will create separate groups for January 2023 and January 2024.

Worked Example

Suppose you have a sales table with these rows:

• Date: 2024-01-15, Amount: 100
• Date: 2024-01-20, Amount: 150
• Date: 2024-02-05, Amount: 200
• Date: 2024-02-25, Amount: 250

If you create a pivot table and group the Date field by Month, you get:

• Jan 2024: 100 + 150 = 250
• Feb 2024: 200 + 250 = 450

The pivot table sums the Amount field for each month group. The result is exactly what you’d get by manually filtering and summing.

How to Group Dates by Month in Excel

1. Select any cell inside your pivot table.
2. Right-click on the date field in the Rows area (or the field header in the pivot table).
3. Choose Group from the context menu.
4. In the Grouping dialog, select Months (and optionally Years if your data spans multiple years).
5. Click OK. The pivot table now shows monthly sums.

If you don’t see the Group option, make sure the field is a real date (not text). If it’s text, you’ll need to convert it to a date format first.

How to Group Dates by Month in Google Sheets

1. Create a pivot table from your data (Data > Pivot table).
2. Add the date field to the Rows section.
3. In the row field settings, click the dropdown and choose Date > Month (or Year-Month).
4. Add the value field (e.g., Amount) to the Values section and ensure it’s set to SUM.

Google Sheets automatically groups dates by month when you select the Month option. You can also create a calculated field with a formula like =MONTH(A2) to extract the month number, but the built-in grouping is simpler.

Common Problems and Fixes

• Dates are not recognized as dates: If the Group option is greyed out, your date column may be text. Select the column, go to Data > Text to Columns (Excel) or use DATEVALUE() to convert.
• Blank or missing dates: Pivot tables may include a blank group. Filter it out by unchecking the blank row in the filter dropdown.
• Grouping by month only, not year: If your data spans multiple years, always group by both Years and Months to avoid mixing January 2023 with January 2024.
• Values are counting instead of summing: Right-click the value field in the Values area, choose Value Field Settings, and select Sum.

Alternatives to Grouping

If you can’t group (e.g., due to text dates), you can create a helper column with a formula to extract the month and then use that column in the pivot table. In Excel, use =TEXT(A2,"YYYY-MM") to get a sortable month-year text, or =MONTH(A2) for the month number. In Google Sheets, use =TEXT(A2,"YYYY-MM") or =EOMONTH(A2,0) to get the last day of the month. Then add this helper column to the pivot table’s Rows area.

Reading the Results

The monthly sum shows the total for each calendar month. If you grouped by year and month, you’ll see a hierarchy: each year expands to show its months. The order is chronological by default. You can sort by month name alphabetically, but that will lose the chronological order (e.g., April before August). To keep chronological order, group by year and month together.

Frequently Asked Questions

Why is my pivot table not grouping dates by month?

This usually happens when the date column contains text or when there are blank cells. Ensure all cells are actual date values. In Excel, you can test by using the ISDATE() function in a helper column. If they’re text, use Text to Columns or DATEVALUE to convert.

Can I group by month and year separately?

Yes, but you should group by both to avoid mixing years. In Excel, select both Months and Years in the Grouping dialog. In Google Sheets, choose the Year-Month option.

How do I show the month name instead of a number?

After grouping, the pivot table will display the month name (e.g., Jan) by default. If you see numbers, change the number format of the date field to a custom format like “mmm” or “mmmm”.

What if I want to sum by week or quarter?

In Excel, the Grouping dialog also offers Days, Quarters, and Years. For weeks, you can group by Days and set the number of days to 7. In Google Sheets, you can add a helper column with =WEEKNUM() or =QUOTIENT(DAY(A2),7)+1.

Can I use a pivot table to sum by month without grouping?

Yes, but you’d need to create a helper column with the month (e.g., =MONTH(A2)) and use that as the row field. This is less flexible than grouping but works when grouping is unavailable.

Why does my pivot table show “(blank)” as a month?

This means there are rows with no date or invalid dates. Check the source data for empty cells or text that looks like dates. Filter out the blank group by unchecking it in the row label filter.

Related

• Renewable Energy Environmental Trade-Offs by Project
• Mass Effect Crossover: Platform & Edition Guide
• Samsung Galaxy S21 Press Renders Leak?

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2