How to summarize pivot table dates by month and year

Key points of this article

To summarize the date field by month or year, directly right-click in the pivot table and select date grouping. Usually, there is no need to add an auxiliary column first.
When both year and month are needed, the year and month must be checked together, with the year on the outer layer to avoid mixing months with the same name in different years.
Only real dates can be grouped. Dates in text format must be converted first, otherwise the grouping options will be grayed out or unavailable.
The pivot table must be refreshed after new rows are added to the source data, otherwise the new data will not appear in the monthly or yearly summary results.
You can cancel at any time after grouping. After cancellation, the dates will return to day-by-day display without affecting the source data.

When making sales statements, attendance records or reimbursement details, it is often necessary to summarize thousands of rows of daily data into "how many per month" or "how many per year". Many people will first add a column of "month" to the source table, and then use a formula to extract it. The operation goes full circle. In fact, the pivot table itself can do this: the date field can be grouped directly by month, quarter or year, and the source data does not need to be changed.

This article is written for people who often use Excel to do summarization, especially readers who encounter "how to summarize pivot table dates by month", "how to combine dates by year", or the date display format is incorrect. After reading, you can turn daily records into monthly or yearly summary tables in a few minutes, and you can also determine which situations require processing the source data first.

How to summarize pivot table dates by month? One sentence conclusion

In the pivot table, drag the date field to the row or column area, then right-click any date cell, click the date grouping function in the pop-up menu, and check the month in the pop-up grouping window (check the year if you need to group by year). Excel will combine all dates in the same month into one row, and numeric fields will automatically sum or count by month.

your situation	Recommended practices	reason
I just want to see the total for each month	Right-click the date field, select Date Grouping, and check only the month	Daily records are consolidated into months 1 to 12 with a simple structure
The data spans many years, so you need to look at both the year and the month.	Check both year and month	The year is in the outer layer and the month is in the inner layer. Months with the same name in different years will not be mixed together.
Want to look at it by quarter	Check the quarter, you can select the same as the year	Quarter is a common reporting format, so you don’t need to calculate the quarter yourself.
Dates are displayed as text, grouping options are not available	First convert the date column to the real date format	Text cannot participate in date grouping, this is a common cause of failure
Want to make statistics by week or every few days?	Select days in the step setting of the grouping window and fill in the number of days between intervals	There is no week in the default option, fill in 7 days to group by week

Before you start: Confirm these points first

Before starting to group, check the following items to avoid most rework.

Date columns must be real dates. Select the date cell in the source data and see if the edit bar or number format displays date. If the date is left-aligned, appears as text with an apostrophe, or cannot be used for sorting, it is text. Text dates cannot be grouped and need to be converted by column splitting or other methods, or rewritten with date functions and then refreshed.
No blank rows or incorrect values in the date column. Blank cells will form a separate blank group in the pivot table, and incorrect values may also cause the grouping to fail. Use filter to look at this column before summarizing.
The data area must be continuous. If the data source of the pivot table has an entire row of blank spaces, new data may not be included. After converting the data area into a table, the new rows will be automatically expanded into the data source.
Think clearly at which granularity you want to look at. When looking only by month, the same month in different years will be merged. For example, March 2023 and March 2024 will fall in the March row. Multi-year data should be grouped by year and month at the same time, or confirm the caliber you want first.
Confirm how numeric fields are summarized. After grouping, the amount field defaults to sum. If you want the average or count, you need to modify it in the value field settings, not on the date grouping.

Steps to summarize dates by month and year in Excel pivot table

The following steps work for data where the dates are already in true date format.

Select any cell of the source data and insert the pivot table. Switch to the insert related options in the top ribbon of Excel, start the pivot table function, confirm that the data area is correct, and create a new worksheet to store the results.
Drag the date field to the row area. If you want to calculate the amount by month, drag the amount field to the value area. At this point the pivot table will list the dates day by day.
Right-click any date in the row area. Click the date grouping function in the pop-up menu. Be careful to right-click on the date data itself, not the title or blank space.
Select the granularity in the pop-up grouping window. If you only want to see the month, check the month; if you need to look at the year, check the year; if you want both, check the year and month at the same time.
Check the step size and start and end dates. The window will usually automatically populate with early and late dates from the data. Make sure that the range covers all of your data and that no years are missing, then confirm that it is complete.
Check the grouping results. Row labels should show January, February, etc. under the year 2024, or just the month. The values should change with the grouping and no longer be displayed day by day.
Adjust number format if necessary. If the decimal places or currency symbols displayed in the value field do not meet the requirements, modify the number format in the value field settings and do not hard-change the value in the source data.
Refresh when the data is updated in the future. Right-click anywhere in the pivot table and select Refresh, or use the Refresh All function in Excel. Newly added records will be entered into the corresponding month or year according to the existing grouping rules.

Check each item after completion

After the grouping is completed, do not directly send the results to others, first use the form below to check item by item.

Check items	How to see	What to do if there is a problem
Does the sum of the months equal the total?	At the bottom of the pivot table, look at the total and compare it with the total calculated by the source data using the summation formula	Inconsistencies usually occur when the text date is not recognized. Go back to the source data to check the column, then refresh after the date
Check if there are blank lines	Check if the line tag contains "(Blank)"	If the source data date column contains empty cells, refresh after completing or deleting these records
Whether the year is complete	Check whether the year grouping covers all data years	If the start and end dates do not cover all data during grouping, ungroup and reset the data, and confirm the start and end range
Whether months with the same name are mixed together	When selecting only the month for cross-year data, check if different years are merged within the same month	At the same time, check the year and month to group the year as an outer layer
Whether the new data has already been included	Compare the number of newly added rows with the counts in the pivot table	Refresh the pivot table; if still missing, check the data area range or use the table as the data source

📊 Core Comparison

There are three common methods for summarizing dates, each suitable for different scenarios.

Method	Suitable situation	Main limitations
Date grouping within the perspective table	The data volume is large and requires quick monthly, quarterly, or annual viewing, and source data is difficult to modify	It can only be applied within pivot tables; grouping results cannot be directly referenced by other formulas
Added a month helper column to the source data	You need to treat the month as a regular field for multiple pivot tables or formulas	To maintain auxiliary column formulas, synchronize filling when data increases
Manually summarize using formulas	The summary caliber is quite special, such as by financial month or custom cycles	Too many formulas tend to cause errors, and large tables slow to calculate

Most daily summaries are grouped by date for convenience. Auxiliary columns or formulas should only be considered when grouping caliber is not a standard month, quarter, or year.

🎯 Which method suits you?

Only one-time statistics are conducted; data rarely changes. Simply group with a pivot chart, completing it in just a few steps, with results clear at a glance.
Regularly update the same report. Convert data areas to tables; thereafter, just refresh the pivot table each time new data is added.
Calculations must be linked with other tables. Add a month auxiliary column to the source data, generate month text or date using formulas, then use it to create pivot tables or reference calculations.
Dates are exported from the system, resulting in messy formatting. First, convert to the actual date, then group the data. If the formatting issue isn't fixed, the subsequent grouping will make mistakes.

❓ Frequently Asked Questions (FAQ)

1. Why is there no date grouping option on the right-click date field?

In most cases, the column is recognized as text, or you right-click not the date data itself. First, check whether the dates in the source data are aligned to the left and if they can be sorted, then convert them to the actual dates and refresh the pivot table.

2. What if the dates disappear after grouping and the data doesn't match?

Groups only change the way pivot tables are displayed and do not delete source data. First, check whether the start and end date range covers all data, and whether there are blank spaces or text values in the date column. After confirming, you can ungroup and try again.

3. How do you remove date grouping and restore daily display?

Right-click on the grouped row tab and click Ungroup. Source data is unaffected, and pivot tables restore to daily listings.

4. The date display format in the pivot table is incorrect, for example, if it turns into a number, how can I fix it?

First, set the date column in the source data to date format, then refresh the pivot table. If only the pivot table display needs adjustment, you can modify the numeric format in the field settings. Do not manually convert the source data to text, otherwise it will be impossible to group by date later.

📝 Summary

Pivot tables summarize dates by month and year, with two key points: the date column must be the actual date, and when grouping, select month, year, or both as needed. Before operating, check the data format, then verify with the total, blank row, and new data items. By mastering this process, daily cash flow can quickly be transformed into clear monthly or annual reports.

Open OmniDoc and process your table file