Group Rows in a Pivot Table: Excel Steps and Fixes

To group rows in an Excel PivotTable, select one or more row-label items, right-click, and choose Group. For date or number fields, Excel can group items into useful periods or ranges; for selected text items, it can create a custom category. Grouping changes how the PivotTable organizes its report, not the underlying source records.

Quick steps to group PivotTable rows

These instructions apply to Excel for Microsoft 365 on Windows. Click a cell inside the PivotTable first so its contextual ribbon tabs appear. Then select the item or items you want to group. Right-click the selection and choose Group. Excel adds a grouped field to the report, and you can rename its labels to make the result easier to read.

Click inside the PivotTable and locate the field in the Rows area.
Click a row label or select several labels belonging to the same field.
Right-click the selection and choose Group.
If Excel opens a dialog, choose the appropriate settings and select OK.
Rename the new group or grouped field if its default name is not descriptive.

You can also use the ribbon after selecting a PivotTable item: open PivotTable Analyze > Group > Group Selection. The ribbon option may be unavailable until a suitable item is selected. On a Mac, the same idea applies, but modifier keys and some ribbon labels can differ; use Command-click rather than Ctrl-click to select non-adjacent items. If the desktop ribbon differs in your version, the right-click menu is often the easiest route.

What grouping does—and what it does not do

A PivotTable summarizes records into fields and items. Grouping creates a higher-level category from existing items, so you can report on a useful unit such as month, decade, or region cluster instead of listing every original value. The original source rows remain intact, and grouping does not rewrite or combine the source data.

Grouping is different from hiding detail or collapsing a hierarchy. A grouped item represents several underlying values under a shared label; collapsing a field merely hides lower-level detail that is already in the PivotTable. It is also different from grouping worksheet rows with Data > Group, which creates an outline on the worksheet rather than changing PivotTable fields. If you want to combine report categories, group inside the PivotTable.

Group dates by month, quarter, or year

Date grouping is useful when the source contains individual transaction dates but the report should summarize a longer period. Select a date item in the PivotTable, right-click, and choose Group. In the Grouping dialog, select one or more time units, such as Months, Quarters, or Years, then choose OK. Depending on the PivotTable and Excel settings, dates may already appear grouped when the table is created.

For example, a sales PivotTable with one row per order date can be grouped by Months and Years. The report can then show monthly sales while retaining a separate year level. Grouping by Months alone can combine January from multiple years into a single January total. If the report covers more than one year, include Years as well, or keep a year field in the row hierarchy, so readers can distinguish January 2025 from January 2026.

The date dialog can also offer starting and ending dates. These determine the range represented in the grouped report; they do not change the dates in the source. Grouping by days allows a specified number of days per interval, which can be helpful for weekly-style periods, though it does not automatically create calendar weeks beginning on a particular weekday. For a calendar or fiscal calendar that has special rules, a dedicated date or fiscal-period column in the source data may give you more control.

Group numbers into ranges

Number grouping turns individual numeric items into intervals. Select a numeric row label, right-click, choose Group, and set Starting at, Ending at, and By. Starting at and Ending at set the range covered; By sets the interval width. For example, if a PivotTable lists ages from 3 through 67, setting the start to 0, the end to 69, and By to 10 creates decade bands such as 0–9, 10–19, and 20–29.

Choose interval boundaries that answer the question your report is meant to address. A sales report might use bands of 100 currency units, while a response-time report might use 5-minute bands. If a value lies outside the range you specify, check how the resulting groups cover the full set of source values before relying on the totals. Clear, consistently sized intervals make comparisons easier; irregular intervals may be appropriate when they match meaningful thresholds, but label them carefully.

Create custom groups from selected labels

For text categories, you can combine selected items into a custom group. In a PivotTable listing product names, for instance, select several products and group them as a category such as Accessories. Hold Ctrl while clicking separate labels on Windows, then right-click and choose Group. On a Mac, use Command-click for non-adjacent selections. If the labels are next to one another, you can select the range of cells containing them instead.

Excel assigns a generic group name, which you can replace by editing the group label in the PivotTable. A custom grouping is convenient for a one-off analysis, such as bundling a few products for a presentation. If the same classification is needed in multiple reports, consider maintaining a category column in the source data instead. A source field is easier to audit, reuse, and update when product membership changes.

Prepare the source data before grouping

Grouping relies on the values in a PivotTable field being compatible with the kind of grouping you want. A date field should contain actual Excel date values, not a mixture of dates and text that merely looks like dates. A number field should contain numbers rather than entries such as Unknown or a dash. Blank cells, error values, inconsistent formats, or mixed types can prevent grouping or make the result confusing.

Before creating the PivotTable, use one clear header per source column and keep each column’s data type consistent. If the source is an Excel Table, adding records to the table is generally easier to manage than using a fixed range; refresh the PivotTable after changes so it can incorporate updated source data. If you correct values in the source, refresh the report and try grouping again. Grouping itself is not a substitute for cleaning inconsistent records.

For dates, check a sample cell by changing its number format or inspecting the formula bar; a displayed date can still be stored as text. Convert text dates and numbers in the source using a method appropriate to the data, then refresh. Avoid deleting records just to make grouping work unless those records are genuinely outside the intended analysis.

Common grouping problems and solutions

Group is unavailable: Make sure you selected an item inside the PivotTable, not a worksheet label outside it. Some selections, such as a subtotal or total, are not groupable. Select an individual item in the field instead.
Excel says the selection cannot be grouped: Inspect the entire source field for blanks, text mixed with dates or numbers, and errors. Correct the source values and refresh the PivotTable.
Months from different years are combined: Add the Years grouping as well as Months, or place a year field above the month field in the Rows area.
New source items do not appear: Refresh the PivotTable and verify that its source range includes the new records. A fixed source range may need to be expanded; an Excel Table can make additions easier to include.
Grouping appears automatically: Excel may automatically group date fields in a new PivotTable. If that behavior is not wanted, open File > Options > Data and select Disable automatic grouping of Date/Time columns in PivotTables, where that setting is available.

If you are using Excel for the web and a grouping command or dialog is not available in your current interface, open the workbook in the desktop app to use the full PivotTable controls. The exact display can vary by platform and update, but the grouping concept and source-data checks are the same.

Rename, rearrange, or remove groups

Default labels such as Group1 are rarely useful in a finished report. Click the grouped label in the PivotTable and type a clearer name, such as Small, Medium, and Large or Accessories. You can also rename the grouped field through the PivotTable field layout, then drag fields in the Rows area to change the order of the hierarchy. For example, placing Year above Month makes annual sections easier to scan.

To reverse a grouping, right-click an item in the grouped field and choose Ungroup. For date groups, this returns the date detail to the PivotTable; for custom groups, it separates the combined labels again. If you only want to hide a level temporarily, collapsing or removing a field from the Rows area may be more suitable than ungrouping. Refreshing updates the PivotTable from its source, but it is not a replacement for checking that a grouping still represents the categories you intend.

Frequently Asked Questions

Can I group rows in a PivotTable without changing the source data?
Yes. Grouping changes the PivotTable’s organization and adds grouped categories to the report; it does not combine or edit the underlying source records.

Why can’t I group dates in my PivotTable?
The date field may contain blanks, errors, or text values mixed with real dates. Check the full source column, make its values consistent, refresh the PivotTable, and try again.

Can I group months while keeping years separate?
Yes. In the date Grouping dialog, select both Months and Years. This creates a year-and-month hierarchy so January in one year is not added to January in another.

Can I group text labels such as products or regions?
Yes. Select one or more items from the same PivotTable field, right-click, and choose Group. Rename the resulting group so its meaning is clear.

How do I undo a PivotTable grouping?
Right-click an item in the grouped field and choose Ungroup. The original values return as separate PivotTable items; the source data is not altered.

Is PivotTable grouping the same as grouping worksheet rows?
No. PivotTable grouping creates categories within a PivotTable field. Worksheet row grouping, found under Data > Group, creates an outline to expand or collapse rows on the sheet.

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.