Excel Pivot Table Group by Date: Step-by-Step

Charts & pivot tables · Updated November 22, 2026

To group a pivot table by date in Excel, right-click any date cell inside the pivot table, choose Group, then select the date periods you want (like Months, Quarters, or Years) and click OK. This instantly collapses daily dates into the larger time buckets you pick, and you can group by multiple periods at once, such as Months and Years, to avoid mixing dates from different years together.

Why Grouping Dates in a Pivot Table Matters

Raw date data is almost always stored at the day level—every transaction, sale, or event has its own date. If you build a pivot table directly from that data, you get one row per day, which is rarely useful for spotting trends. Grouping lets you summarize by week, month, quarter, or year without writing a single formula or adding helper columns. It also keeps your source data intact, so you can change the grouping later without altering the original spreadsheet.

For example, a sales log with 1,000 daily rows can become a clean monthly summary with 12 rows in seconds. The grouping feature is built into Excel’s pivot table engine, so it recalculates automatically when you refresh the pivot or add new dates.

How to Group Dates in a Pivot Table (Step-by-Step)

  1. Select any cell inside the pivot table that contains a date field (usually in the Rows or Columns area).
  2. Right-click the selected cell and choose Group from the context menu. Alternatively, go to the PivotTable Analyze tab (or Analyze on Mac) and click Group Field in the Group group.
  3. In the Grouping dialog box, you’ll see a list of date periods: Seconds, Minutes, Hours, Days, Months, Quarters, and Years. Select the ones you want—for example, Months and Years.
  4. If you want to limit the grouping to a specific date range, set the Starting at and Ending at boxes. Leave them blank to include all dates.
  5. Click OK. The pivot table now shows grouped date fields, often with an extra field like “Years” appearing above “Months” in the Rows area.

On Excel for Mac, the steps are the same, but the ribbon tab is called Analyze instead of PivotTable Analyze. In Excel for the web, right-click a date cell and look for Group—it works, but the dialog box is simpler and may not offer all periods like Seconds or Minutes.

Choosing the Right Date Grouping for Your Data

Excel lets you group by multiple periods simultaneously, which is powerful but easy to misuse. The most common combination is Months + Years because it prevents January 2023 and January 2024 from appearing in the same “Jan” bucket. If you group only by Month, Excel merges all Januarys across years into one row, which is usually not what you want.

  • Years only – best for multi-year trend overviews.
  • Quarters + Years – good for quarterly business reviews.
  • Months + Years – the default for most monthly reporting.
  • Days – useful if you want to group by a specific number of days (like 7-day weeks) using the “Number of days” box.
  • Hours – only available if your date field includes time values; great for shift-based analysis.

To change the grouping later, right-click a grouped date cell and choose Group again, or choose Ungroup to revert to daily dates. You can also drag the generated fields (like “Years” or “Months”) out of the Rows area to remove that level of grouping.

Common Problems and How to Fix Them

The most frequent issue is the Group option being grayed out. This almost always means the field in the pivot table is not recognized as a true date/time value. Check the source data: if the column is formatted as text (often because dates were imported from another system), Excel can’t group it. Fix by converting text to dates using the DATEVALUE function or the Text to Columns wizard (Data > Text to Columns > Delimited > Next > Date > MDY).

Another common problem is grouping by Month but seeing “Jan” and “January” or seeing dates that don’t sort chronologically. This happens when your source data contains inconsistent date formats. Ensure all dates are actual Excel date serial numbers, not text strings that look like dates.

If you group by Months and Years but the pivot table still shows individual days, you likely have a field in the Rows area that is separate from the date field—for example, a “Product” field placed above the date. Grouping only affects the date field itself; you may need to remove other row fields temporarily to see the grouped dates clearly.

Grouping by Week or Custom Day Intervals

Excel’s default grouping doesn’t have a “Week” option, but you can group by a custom number of days. Right-click a date cell, choose Group, then select Days and set the Number of days to 7. Excel will start the first group from the earliest date in your data, which may not be a Monday. To force weeks to start on a specific weekday, you’ll need to add a helper column in your source data with a formula like =A2-WEEKDAY(A2,2)+1 to get the Monday of each week, then group by that calculated field.

For fiscal years that don’t align with calendar years, you can’t change the built-in grouping. Instead, create a calculated column in your source data (e.g., =YEAR(A2) for calendar year, or a custom formula for fiscal year) and add that field to the pivot table. Grouping works on that calculated field just like any other date field.

Grouping Dates in Pivot Tables vs. Using Formulas

Many users wonder whether to use pivot table grouping or write formulas like =TEXT(A2,"mmm") or =EOMONTH(A2,0) to extract month names. The pivot table method is faster and more flexible because you can change the grouping period without editing formulas. However, formulas give you more control—for example, you can create custom fiscal periods or ISO week numbers that pivot grouping doesn’t support.

A good rule of thumb: if you need standard periods (days, months, quarters, years) and you’re already using a pivot table, use the built-in grouping. If you need non-standard periods (like 4-4-5 retail calendars) or you want to keep the source data as a flat table for other analyses, add helper columns with formulas and then use those columns in the pivot table.

Frequently Asked Questions

Why is the Group option grayed out in my pivot table?

The Group option is grayed out when the field is not recognized as a date. This usually means the source column contains text values, blank cells, or mixed formats. Convert the column to real dates (using DATEVALUE or Text to Columns) and refresh the pivot table. Also, make sure the field is in the Rows or Columns area, not just in Values or Filters.

Can I group by week in a pivot table?

Yes, but not with a dedicated “Week” button. Right-click a date cell, choose Group, select Days, and set Number of days to 7. The grouping will start from the earliest date in your data. To align weeks to Mondays, add a helper column in your source data that calculates the Monday of each week, then group by that column.

How do I ungroup dates in a pivot table?

Right-click any grouped date cell (like a month name) and choose Ungroup. This reverts the field to its original daily granularity. You can also remove the generated fields (like “Years” or “Months”) from the Rows area to effectively ungroup that level.

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

Yes. In the Grouping dialog box, select both Quarters and Years. Excel will create a hierarchy with Years above Quarters, so each quarter is shown within its correct year. This prevents Q1 2023 and Q1 2024 from being lumped together.

Why does my pivot table show “Jan” and “January” as separate groups?

That happens when your source data has inconsistent date formats—some cells are real dates and others are text. Excel treats text like “Jan” differently from a date formatted as “Jan”. Clean the source data so all entries are actual dates, then refresh the pivot table.

Does grouping dates affect my source data?

No. Grouping is a display and summarization feature within the pivot table only. Your original data remains unchanged. However, if you refresh the pivot table after adding new dates, the grouping will automatically include them (unless you set a fixed Starting at/Ending at range).

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.