How to Group a Pivot Table by Month in Excel

Key Takeaways

• You can group a pivot table by month by right-clicking any date cell in your PivotTable and selecting Group, then choosing Month from the dialog box.
• To see monthly data across multiple years, add both Month and Years in the Grouping dialog box so totals aren't combined into single rows.
• Excel doesn't have a built-in week option, but you can group by week by selecting Days and entering 7 in the Number of Days field.
• All dates in your source data must be formatted as valid Excel dates (not text) for grouping to work.

Why Group Pivot Table Data by Month

When a PivotTable contains a date field, Excel displays every individual date as its own row by default. If your dataset spans months or years, that can mean hundreds of rows, making it nearly impossible to spot patterns at a glance.

PivotTable date grouping solves this by consolidating individual dates into larger time periods such as months, quarters, years or weeks without changing your source data. By grouping within the PivotTable itself, you avoid constantly changing your source data and creating multiple PivotTables from the same data.

Common use cases for Excel pivot table date grouping include:

• Monthly sales trends across products or regions
• Seasonal analysis based on several years of historical data
• Budget tracking by month or quarter
• Headcount or attendance reporting over time

PivotTables have some useful "hidden" features that can make interpreting your data even easier—one reason grouping is a staple in any advanced Excel toolkit. The Group feature is one of the most powerful, and the steps below walk you through every option.

Before You Begin

Before you group dates in a pivot table, confirm the following prerequisites so the grouping feature works correctly:

• Every cell in your date column must contain a valid Excel date, not a text string that looks like a date. If dates were imported from another system, they may be stored as text even though they appear formatted correctly.
• There should be no blank cells in the date column. Even a single blank can prevent Excel from offering the Group option.
• The steps below apply to modern versions of Excel, including Excel for Microsoft 365. The interface may look slightly different depending on your version, but the grouping dialog box works the same way.

Download the example workbook: To follow along using our example data, download Group PivotTables by Month.xlsx

Step One: Group Your PivotTable by Month

Right-Click on any cell within the Dates column and select Group from the fly-out list. Then select Month in the dialog box. Using the Starting at: and Ending at: fields, you can even specify the range of dates that you want to group if you don't want to group the entire list.

Step Two: Group Your PivotTable by Month and Year

If your data spans multiple years, grouping by month alone combines the sales data for each month into one line, representing the sum of all years. Notice that in our example, there are three years' worth of data. We might use this information to see if there are seasonal sales trends throughout the year, based on several years of data.

However, if you wish to see linear trends over all three years, simply add Years to your choice in the Groupings dialog box. When you add more than one Group by, you can then collapse and expand the groups by clicking the + or  icon beside the group:

Step Three: Group Your PivotTable by Week

There is not a "Week" selection in the Grouping dialog box. To organize your data by week, select Days, then put 7 in the Number of days: field:

Hint: Set Your Week's Starting Date

In our example, you might notice that the first "week" is July 4-July 10, which was a Thursday-Wednesday. This happens because our first recorded date is July 4. To show a traditional Sunday-Saturday work week in your groups, manually set the Starting at field to a Sunday close to your first recorded date, such as June 30:

Step Four: Show Totals by Difference

Once you have grouped your data by month, you may want to learn something different about your data, such as how your sales totals change month by month. Your PivotTable can show your monthly sales numbers in terms of the difference from the previous month.

To show "Difference from" totals, click on any number in the column you want affected, in this case "Sum of Order Amount", then click Value field settings from the Active Field group on the Analyze tab.

On the Show Values As tab in the dialog box, choose Difference From in the Show values as dropdown menu, and (previous) from the Base item selection box. Your PivotTable will now show how each Salesperson performed over time by indicating the difference in sales from the previous month.

Make the Most of PivotTable Date Grouping

Grouping data within a PivotTable, and especially date-based data, allows you to combine the analysis power of the PivotTable with the familiar tools that make reading the results much more useful. In this guide you learned how to:

• Group dates by month to consolidate daily rows into monthly summaries
• Group by month and year to preserve year-over-year detail
• Group by week using the 7-day workaround
• Show month-over-month differences using the Show Values As feature

These techniques work with any date-based dataset, whether you're analyzing sales orders, expense reports, support tickets or project timelinesand they pair well with date or time charts for visual trend analysis.

Ready to sharpen your Excel skills further? Pryor Learning offers hands-on Excel training courses designed to take you from everyday tasks to advanced data analysis.

Troubleshooting: Why You Can't Group by Month in a PivotTable

If the Group option is grayed out or missing when you right-click a date cell, one of the following issues is usually the cause:

• Dates stored as text: Cells may look like dates but are actually text strings. Use the DATEVALUE function or the Text to Columns feature to convert them to true Excel dates.
• Blank cells in the date column: A single blank cell can prevent grouping from working. Fill or remove any blanks before refreshing your PivotTable.
• Mixed data types in the date column: If even one cell contains a text value, Excel disables grouping for the entire field. Sort the column and scan for inconsistencies conditional formatting can help highlight mismatched data types.
• Non-date values in the date column: Entries like "N/A," "TBD" or other placeholders break the grouping feature. Replace them with valid dates or remove the rows from your source data.
• PivotTable is based on a data model: If your PivotTable uses Power Pivot or the Excel Data Model, the standard grouping dialog is not available. You will need to create a date table in the data model and build relationships instead.

After correcting any of these issues, refresh your PivotTable and try right-clicking the date field again.