In Excel for Microsoft 365 on Windows, group PivotTable items by selecting a date, number, or label in the table, then choose PivotTable Analyze > Group > Group Selection, or right-click the selection and choose Group. Excel can create date periods such as months and quarters, numeric ranges such as price bands, or custom groups of text items.
What PivotTable grouping does
A PivotTable summarizes records by the fields in its source data. Grouping lets you combine values from one field into broader categories without changing the underlying records. For example, a transaction list with individual dates can be summarized by month, while a list of product prices can be summarized in ranges.
Grouping changes the way a field is displayed and summarized in the PivotTable; it does not rewrite the original data. Excel adds grouped fields to the PivotTable field list. Depending on the group type, you may see new fields such as Years and Months, or a single field containing ranges or your custom labels. You can use those fields in Rows, Columns, or Filters just like other PivotTable fields.
Grouping is useful when the detail in a report is too fine-grained to reveal a pattern. It can reduce dozens of dates to a handful of months, or turn many individual products into a few meaningful categories. The original detail remains available in the source data and can still be included in the PivotTable.
Group dates by month, quarter, or year
To group dates, first place the date field in the PivotTable, usually in Rows. Click one of the displayed dates, then choose PivotTable Analyze > Group > Group Selection. In the Grouping dialog, select the time units you need. Excel offers units including seconds, minutes, hours, days, months, quarters, and years. You can select more than one unit, such as Months and Years, to separate the same month across different years.
- Check that the source column contains actual Excel dates, not text that merely looks like dates.
- Click a date value in the PivotTable and open the Grouping dialog.
- Select the desired units. If grouping by days, set the number of days per group when you want periods such as 7-day intervals.
- Choose OK, then move the resulting date fields among Rows, Columns, and Filters to shape the report.
For example, a sales table covering January 2025 through December 2026 can be grouped by Years and Months. Put Years above Months in Rows to show a separate month list under each year. If you group only by Months, Excel can combine all Januaries together, which may hide year-to-year differences. Add Years when the source spans multiple years and that comparison matters.
Date groups are reporting categories, not new dates in the source. A month group represents all records whose dates fall in that month. If your dates are daily but your report is quarterly, grouping by Quarters and Years produces a compact summary while preserving the daily records beneath the PivotTable’s detail.
Group numbers into ranges
Numeric grouping is useful for fields such as age, order value, quantity, or response time. Put the numeric field in Rows or Columns, select one of its displayed values, and choose PivotTable Analyze > Group > Group Selection. In the dialog, set Starting at, Ending at, and By. Starting at and Ending at define the range to cover; By sets the width of each interval.
Suppose a survey contains ages from 18 through 67. Setting Starting at to 18, Ending at to 67, and By to 10 creates decade-sized bands. Excel displays ranges such as 18–27, 28–37, and so on. The exact labels and boundary display can depend on the chosen start and interval, so check the resulting PivotTable before sharing it. A By value of 5 would create narrower age bands instead.
Choose interval boundaries that make sense for the question. For order values, ranges of 0–49, 50–99, and 100–149 may be easier to explain than arbitrary widths. Grouping is not a substitute for checking the data: an unexpected value far outside the usual range can affect the proposed ending point or produce an extra group. Review the source values and the output after grouping.
Create custom groups from text labels
Text grouping lets you combine selected items into categories you name. For instance, a PivotTable listing individual products can group several related products as “Accessories,” leaving other products in their own groups. This is a manual categorization: Excel does not infer that labels are related based on their wording.
In the PivotTable, select the labels you want to combine. To select nonadjacent items in Windows, hold Ctrl while clicking each item; on Mac, use Command-click. Right-click the selection and choose Group. Excel creates a group, commonly with a default name such as Group1. Select that label in the PivotTable and type a clearer name. Repeat for other categories as needed.
Use custom groups when the category is appropriate for this particular report and the list is manageable. If the same categorization must be reused across many reports, it is usually better to add a category column to the source data or to a maintained lookup table. That makes the classification visible, reusable, and easier to audit than a set of report-specific manual groups.
Choose the right grouping method
The best method depends on the field and on whether the categories should be automatic or controlled. A useful rule is to group dates by calendar periods, numeric measures by meaningful intervals, and text items only when you have a clear, deliberate category scheme.
- Dates: Group by months, quarters, or years for time-based reporting. Include Years if a period such as January occurs in multiple years.
- Numbers: Use numeric ranges when a banded distribution is more useful than a list of every value. Set boundaries that match the business question.
- Text: Use manual grouping for a small, report-specific set of related labels. Use a source-data category field for repeatable classifications.
- Large or changing datasets: Prefer a category column or a data model dimension when groups must stay consistent as records are added.
A common misconception is that grouping changes the source records or permanently combines them. It does neither: it changes how the PivotTable organizes values. Another misconception is that a month-only grouping always means a calendar sequence. If data spans several years, grouping only by month may combine records from the same month across those years, so use a year field as well when the distinction is important.
Fix common grouping errors
If Excel reports that it cannot group the selection, the most common cause is inconsistent source data. For date grouping, inspect the entire source date column for blanks, text entries, errors, or values stored as text. A cell displaying 3/4/2026 might still be text rather than an Excel date serial. Convert or correct the underlying entries, refresh the PivotTable, and try grouping again.
For numeric grouping, check that the field contains numbers throughout, with no text labels, errors, or blanks that interfere with grouping. A value such as “Unknown” mixed into an otherwise numeric age column is a likely problem. Correct the data or represent missing values consistently, then refresh the PivotTable. Do not replace missing data with a number unless that number has a valid meaning for the analysis.
Also check that you selected an item from the field you intend to group, rather than a subtotal or a value in the Values area. If the source data has been corrected but the PivotTable still shows old items, use PivotTable Analyze > Refresh to update it. If the PivotTable uses the Data Model or an OLAP source, ordinary manual grouping may be unavailable; create the categories in the source or model instead.
When grouping dates, confirm that the source values are genuine dates and that the range contains the periods you expect. When grouping numbers, review the Starting at, Ending at, and By values. A technically valid interval can still create misleading bands if it does not match the question being asked.
Rename, adjust, or remove groups
After creating a group, click its displayed name and type a more informative one. For numeric and date groups, use the field’s grouping settings to adjust the interval or units when available: select a grouped item and open Group Selection again. The PivotTable field list may show multiple fields created by date grouping, so check which field you are moving or filtering.
To remove grouping, select an item in the grouped field and choose PivotTable Analyze > Group > Ungroup, or right-click and choose Ungroup. For a date field, ungrouping returns the field to its individual date values; it does not delete the source dates. For manual text groups, ungrouping removes the custom grouping and restores the separate labels.
If you want to start over, ungroup first and then create a new group with the intended units or boundaries. Refreshing a PivotTable updates its connection to the source data, but it does not replace careful review of grouping choices. After adding source records, check that the PivotTable’s source range includes them and confirm that the resulting groups still make sense.
Grouping versus source categories and other options
PivotTable grouping is convenient for quick analysis, but it is stored as part of the PivotTable’s layout rather than as a classification on each source record. That distinction matters when several PivotTables need the same categories, when categories change over time, or when another person needs to understand how a group was assigned.
For stable categories, add a field such as Product Category or Age Band to the source data, using a documented rule. Each record then carries its category, and multiple PivotTables can use the same field. For date reporting, a source table can also include separate year, month, or quarter fields if those are useful beyond one PivotTable. In Excel for Microsoft 365, Power Query is another option for shaping and categorizing data before it reaches a PivotTable, especially when the transformation needs to be refreshed repeatedly.
On Windows, the ribbon route is PivotTable Analyze > Group > Group Selection or Ungroup. Excel for Mac provides PivotTable grouping as well, though tab names and ribbon layout can differ by release; right-clicking, or Control-clicking on a trackpad, is another way to find the grouping command. Excel for the web has a more limited feature set than the desktop app, so use desktop Excel if the grouping command is unavailable in your browser version.
Frequently Asked Questions
Why can’t I group dates in an Excel PivotTable?
The date field may contain blanks, text values, errors, or dates stored as text. Check every source row, correct inconsistent entries, refresh the PivotTable, and try again. A PivotTable based on the Data Model or an OLAP source may also restrict ordinary grouping.
How do I group dates by month and year?
Select a date in the PivotTable, choose PivotTable Analyze > Group > Group Selection, then select both Months and Years in the Grouping dialog. Keeping both fields prevents January records from different years from being combined into one month category.
Can I group numbers into custom ranges?
Yes. Select a numeric item, choose Group Selection, and enter the Starting at, Ending at, and By values. For example, a width of 10 creates intervals ten units wide; choose boundaries that fit your data and reporting purpose.
Can I group text items manually?
Yes. Select the labels to combine, right-click, and choose Group, then rename the resulting group. For categories that must remain consistent across reports, add a category field to the source data instead.
Does grouping change my original data?
No. Grouping changes the PivotTable’s organization, not the source records. You can remove a group with Ungroup, and the individual source values remain available for analysis.
Related
- Free Personal Budget Spreadsheet: Plan Monthly Spending
- PowerPoint Notes: How to See Them While Presenting
- Excel Not Responding When Deleting Rows? Causes and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Donut Chart: Change Colors by Slice or ThemeTo change colors in an Excel donut chart, select the chart and choose Chart Design > Change Colors…
- How to Make a Proper Histogram in Excel: Step by StepTo make a proper histogram in Excel, put one set of numeric measurements in a column, select the…
- Multiple Doughnut Charts in Excel: How to Make ThemTo make a multiple-ring doughnut chart in Excel, arrange each data series in its own column (or row),…
- Excel Mac Chart Creation: Make and Format ChartsTo create a chart in Excel for Mac, select your data, choose Insert > Recommended Charts, select a…
- Excel Stacked Column Chart with Two SeriesYes. In Excel for Microsoft 365 on Windows, put your category labels in the first column and the…
- Excel Pivot Chart: Remove “Sum of” From LabelsTo remove “Sum of” from a PivotChart legend label in Excel for Microsoft 365 on Windows, rename the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.