Excel Pivot Group Rows: Dates, Numbers, and Categories

Charts & pivot tables · Updated November 21, 2026

To group rows in an Excel PivotTable, right-click a row label or value and choose Group; for dates or numbers, set the grouping interval in the dialog box. You can also select several labels and group them manually, which makes it possible to create custom categories without changing the original data.

What grouping rows in a PivotTable does

A PivotTable summarizes records by fields, and grouping combines individual items in a row field into broader categories. For example, a sales PivotTable with one row per transaction can show sales by month instead of listing every transaction date. Grouping changes the PivotTable’s display and summary; it does not combine, delete, or rewrite the corresponding rows in the source table.

There are three common kinds of PivotTable grouping:

  • Dates: combine dates into months, quarters, or years.
  • Numbers: put numeric values into ranges, such as 0–99, 100–199, and 200–299.
  • Selected labels: combine chosen text or other individual items into custom groups, such as several product names under Outdoor.

This is different from grouping worksheet rows to hide or show detail. Worksheet outlining uses Data > Group and creates expand-and-collapse controls beside the sheet. PivotTable grouping instead changes how the PivotTable organizes its field items.

Group date rows by month, quarter, or year

In Excel for Microsoft 365 on Windows, click a date item in the PivotTable, right-click it, and choose Group. Alternatively, select a date and use PivotTable Analyze > Group > Group Field. In the dialog, select one or more time units, such as Months and Years, then choose OK. If the PivotTable has dates spanning several years, grouping by both month and year keeps, for example, January 2025 separate from January 2026.

  1. Make sure the date field is in the Rows area of the PivotTable Fields pane.
  2. Right-click one of the dates shown in the PivotTable and choose Group.
  3. Choose the units you need, such as Days, Months, Quarters, or Years. For a week-like interval, select Days and set the number of days in the By box.
  4. Review the starting and ending dates if Excel displays those options, then click OK.

Excel may create additional fields, such as Years or Quarters, and place them in the PivotTable. You can move those fields between Rows, Columns, and Filters to change the layout. For example, placing Years above Months in Rows produces a year-by-year hierarchy that can be expanded or collapsed. If you only want a monthly total across all years, group by Months alone, but remember that this combines January from every year.

Date grouping works best when the source column contains actual Excel dates consistently. A value that merely looks like a date but is stored as text may not group as expected. The displayed date format does not determine whether the value is a real date; its underlying cell value and type do.

Group numeric rows into ranges

Numeric grouping is useful when a PivotTable lists many distinct values, such as customer ages, order sizes, or prices. Click a numeric item in the row field, right-click, and choose Group. In the dialog, specify Starting at, Ending at, and By. The By value is the width of each interval. For example, grouping ages from 0 through 79 by 10 creates ranges such as 0–9, 10–19, and 20–29.

Choose boundaries that make sense for the data rather than accepting an unsuitable automatic range. If values include decimals, think about whether the interval boundaries and labels will be clear to readers. If a range begins or ends in the wrong place, ungroup the field and group it again with suitable limits. Review the final labels, especially when the field contains negative values or unusual minimum and maximum values.

Grouping is a presentation choice, not a substitute for checking the underlying figures. A grouped total can help show a distribution, but it may conceal important differences within a range. Keep the interval size appropriate to the question: a narrow interval reveals more detail, while a wider interval makes a long list easier to scan.

Create custom groups from selected labels

Manual grouping lets you choose which items belong together. Suppose a PivotTable lists individual products, and you want to show them as Core, Seasonal, and Clearance. Select the relevant product labels, right-click one of the selected items, and choose Group. Excel creates a new grouped field, often named something like Group1, and puts the selected items into a group. Repeat for other sets as needed, then rename the generated group items and field to meaningful names.

To select non-adjacent labels in the PivotTable, use Ctrl-click on Windows. For a consecutive block, click the first label and Shift-click the last. Then right-click a selected label and choose Group. The selection must be made among items in the same PivotTable field; grouping labels from different fields is not a way to combine unrelated categories.

Manual groups are convenient for a one-off report, but they can require maintenance. If the source later gains new product names, those items may not automatically join the intended custom group. For recurring reports, consider adding a category column to the source data instead. A source category is easier to audit, reuse in other reports, and update as classifications change.

Arrange, rename, or remove grouped rows

After grouping, use the PivotTable Fields pane to decide where the new field belongs. A group field above the original field in Rows creates a hierarchy: readers can expand a group to see its members. A group field used by itself gives a more compact summary. Drag fields to reorder them, and use the expand or collapse controls beside row labels when you want to show or hide detail.

Excel’s default names are often generic, so rename them before sharing the report. Click the group label or field heading and type a clearer name, such as Product family or Age band. Avoid giving a PivotTable field a name identical to an existing source field if Excel reports that the PivotTable field name already exists; choose a distinct label instead.

To undo grouping, right-click an item in the grouped field and choose Ungroup, or select an item and use PivotTable Analyze > Group > Ungroup. Ungrouping reverses the PivotTable grouping, not edits to the source records. If the PivotTable has several group levels, check the resulting field list and layout afterward, since you may need to remove or reposition generated fields.

Why the Group command may be unavailable

If Group is disabled or Excel reports that the selection cannot be grouped, check the field and the PivotTable source. Frequent causes include:

  • Blank or inconsistent date values: inspect the source column for empty cells, text entries, errors, or values that are not genuine dates. Clean the field so its entries have a consistent type, then refresh the PivotTable.
  • Mixed data types: a numeric field may contain text such as N/A or a number stored as text. Convert or correct the inconsistent entries when appropriate.
  • The wrong selection: click an item inside the row field you intend to group, rather than a total, heading, or item from another field.
  • PivotTable source limitations: PivotTables built from some external, OLAP, or Data Model sources may not support the same manual grouping controls as a PivotTable based on a regular worksheet range. In that case, create the category in the source or data model.

For date fields, inspect the source cells as well as the PivotTable. A column can display dates while containing text in some rows. Correcting the source and refreshing is generally more reliable than trying to work around mixed values in the report. Keep an unchanged copy of the source data before performing cleanup if the original values need to be preserved.

Refreshes and recurring reports

Grouping is attached to the PivotTable field, but a refresh can reveal new items or values that fall outside the ranges originally used. After adding source records, choose PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. Then check that new dates, numbers, or labels appear in the intended groups. Date or numeric boundaries may need adjusting when the dataset grows.

For reports that are refreshed regularly, keep the source data in a well-structured Excel table: one header row, one record per row, and consistent columns without merged cells or blank separator rows. An Excel table can expand as records are added, helping the PivotTable use the updated range when refreshed. If you add a separate category column, refresh the PivotTable and add that field to Rows instead of repeatedly maintaining manual groups.

Excel for Mac and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. Excel for Mac also supports PivotTable grouping, though the ribbon arrangement and contextual menus can differ by release. Try Control-clicking or right-clicking a PivotTable item and look for Group; if it is not in the menu, select the PivotTable and inspect its contextual PivotTable ribbon.

Excel for the web can open and work with many PivotTables, but available editing commands can vary. If the grouping command is missing or the desired grouping cannot be changed in the browser, open the workbook in the desktop app and group the field there. Avoid assuming that worksheet row outlining and PivotTable grouping are interchangeable: they serve different purposes in every version.

Frequently Asked Questions

Can I group PivotTable rows by month without combining different years?
Yes. Group the date field by both Months and Years. Excel creates a year level as well as a month level, so each year’s January remains distinct. Grouping by Months alone combines the same month across all years.

Can I group text labels in a PivotTable?
Yes. Select the labels in the same row field that should be together, then right-click and choose Group. For a classification that must stay accurate as new records arrive, a category column in the source data is usually easier to maintain.

Does grouping change or delete my source data?
No. PivotTable grouping changes how the summary displays its items. The original rows remain in the source range or table, although a refresh may be needed for new or edited source records to appear in the PivotTable.

Why can’t I group dates in my PivotTable?
Check for blanks, errors, text dates, or other values that are not dates in the source field. Also confirm that you clicked a date item and that the PivotTable’s data source supports grouping. Clean inconsistent source values, refresh, and try again.

How do I remove grouping from a PivotTable?
Right-click an item in the grouped field and select Ungroup, or use PivotTable Analyze > Group > Ungroup in desktop Excel. Then review the fields and layout to confirm the original detail is displayed as intended.

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.