To group data in an Excel PivotTable, select a date, number, or label in the relevant row or column area, then right-click and choose Group; you can also use PivotTable Analyze > Group Selection. Excel can combine dates into periods, numbers into intervals, or selected text labels into custom categories, without changing the original source data.
What grouping does in a PivotTable
Grouping changes how a PivotTable summarizes its fields. Instead of listing every individual date, amount, or label, Excel can show broader categories. For example, a sales report with one row per transaction could show revenue by quarter, by $500 sales bands, or by a few manually defined product groups.
The grouping is part of the PivotTable layout, not a rewrite of the source table. The original transaction dates and amounts remain as they were. You can usually undo a group with Ungroup, or refresh the PivotTable to bring in source changes and then review whether the groups still cover the data you need.
Grouping is different from filtering. A filter hides some items from view; grouping combines items under a shared heading. It is also different from changing the source data: if you need a new category to be used consistently in multiple reports, a helper column in the source table may be a better choice.
Prepare the source data first
A PivotTable works best when its source is a clean, rectangular list. Each column should have one unique header, each row should represent one record, and there should be no completely blank rows or columns splitting the data. Convert a frequently updated range to an Excel Table with Insert > Table so the source can expand when records are added.
Before grouping, check that the field contains consistent values of the intended type:
- Dates: Excel must recognize the entries as dates, not text that merely looks like a date. A date stored as text may sort strangely or refuse to group.
- Numbers: Values must be numeric. A mixture of numbers and text, such as a number with a typed word in the same field, can stop numeric grouping.
- Labels: Text grouping is based on the distinct items displayed in the PivotTable. Check for unwanted spelling differences, extra spaces, and inconsistent abbreviations.
Create the PivotTable through Insert > PivotTable, choose the source range or table, and place the date, number, or label field in Rows or Columns. Grouping is generally performed on a field in those areas, rather than on a value displayed in the Values area.
Group dates by month, quarter, or year
Date grouping is useful when a source contains detailed dates but the report needs a time-based summary. In a sales PivotTable, for instance, grouping transaction dates by months can reduce hundreds of daily entries to a manageable monthly trend. Excel can group dates by units such as seconds, minutes, hours, days, months, quarters, and years, with the available choices depending on the field and version.
- Place the date field in the PivotTable Rows or Columns area.
- Right-click one of the dates in the PivotTable and choose Group. Alternatively, select a date and choose PivotTable Analyze > Group Selection.
- In the Grouping dialog box, select the time units you need. For monthly reporting across several years, select both Months and Years.
- Set the starting and ending dates if you need a defined reporting span, then select OK.
Selecting both months and years matters when a report spans multiple years. Grouping only by month can place every January together, combining January 2025 with January 2026. A year-and-month breakdown preserves the chronological context. Grouping by quarters can also be paired with years for the same reason.
If the PivotTable already displays separate date levels or a date hierarchy, inspect the fields in the Rows area before adding another group. Depending on how the PivotTable was created, Excel may have automatically organized a date field into levels. You can keep the levels that serve the report and remove or rearrange those that do not.
Group numbers into ranges
Numeric grouping converts many distinct values into intervals. It can help answer questions such as how many orders fall between $0 and $499, $500 and $999, or $1,000 and $1,499. Put the numeric field in Rows or Columns, right-click one of its displayed values, and choose Group.
In the Grouping dialog box, set Starting at, Ending at, and By. For example, if an Amount field runs from $80 to $1,420 and you choose a start of 0, an end of 1,500, and an interval of 500, Excel creates ranges spanning 0–499, 500–999, and 1,000–1,499. Check the displayed labels and boundaries in the resulting PivotTable, especially if values include decimals or sit exactly on a boundary.
Choose an interval that makes the comparison useful rather than simply convenient. Narrow bands show more detail but can create a long report; wide bands are easier to scan but may hide meaningful variation. If the ranges need different widths or business-specific names, use a source-data category column instead of relying on equal-sized PivotTable bins.
Group text labels into custom categories
Excel can group selected text items manually. This is handy when a field has a small number of labels that you want to combine for one report. For example, a PivotTable might list products as Basic, Standard, Premium, and Enterprise, while a particular analysis needs Basic and Standard together as Core.
- Place the text field in Rows or Columns and select the items to combine. To select multiple nonadjacent items in the PivotTable, use Ctrl-click on Windows.
- Right-click one of the selected items and choose Group, or use PivotTable Analyze > Group Selection.
- Excel creates a grouped field, often with a default name such as Group1. Rename the group or field to make the report clear, and confirm that the intended items are included.
Manual groups are specific to that PivotTable. They are not a durable replacement for a category in the source data. If product classifications change often, or the same classification must be used in several PivotTables, add a category column to the source table and refresh the reports. That makes the rule visible, easier to audit, and less likely to differ from one report to another.
Rename, edit, or remove a group
After grouping, review the new headings. A default name such as Group1 does not tell a reader what the category means. Select the heading and type a descriptive label, such as Core Products or 500-dollar ranges. Avoid names that could be mistaken for source-field values.
For a manual text group, you can adjust which items belong together by selecting the relevant PivotTable items and grouping or ungrouping them as needed. To remove a group, right-click an item in it and choose Ungroup, or select the item and use PivotTable Analyze > Ungroup. Ungrouping restores the individual items in the PivotTable; it does not delete source records.
After a refresh, check the result if the source has new dates, numbers, or labels. New records may introduce values outside the original numeric boundaries or text items that have not been assigned to the intended manual category. Refreshing updates the PivotTable from its source, but it does not decide what a new business category should mean.
Common grouping problems and fixes
If Excel reports that it cannot group the selection, first inspect the source field rather than repeatedly trying the command. A single blank, text entry, or error among values intended to be dates or numbers can prevent grouping. In date columns, also check for dates stored as text and for cells that look blank but contain spaces or formulas returning an empty string.
- The Group command is unavailable: Confirm that the field is in the PivotTable’s Rows or Columns area and that you selected an item in that field, not a value in the Values area.
- Date grouping fails: Look for blanks, errors, and text dates in the source. Standardize the column as real Excel dates, refresh the PivotTable, and try again.
- Numeric grouping fails: Check for text mixed into the number field, including labels such as N/A. Correct or separate those entries before refreshing.
- Unexpected date totals appear: Group by both month and year when the source spans more than one year, and verify the starting and ending dates.
- New items are not in the expected group: Refresh, inspect the grouped field, and revise the group or use a source category column for a repeatable rule.
Do not remove a legitimate missing value blindly just to make grouping work. Decide whether it represents an unknown date, an unrecorded amount, or an input mistake. Correct the source appropriately or report the missing records separately; otherwise, the PivotTable may become easier to group but less accurate.
Choose between PivotTable grouping and source categories
PivotTable grouping is a good fit for quick exploration and report-specific views. It lets you try monthly, quarterly, or numeric summaries without adding columns to the underlying data. Manual text grouping is also convenient when a small set of labels needs a temporary combined view.
A source-data category is usually better when the rule is part of the organization’s standard reporting, needs to be reused, or must be understood by other people. For example, a RegionGroup column can explicitly map several sales territories to East, Central, or West. A source column also makes the mapping easier to review and maintain when new territories are added.
For date summaries, grouping in the PivotTable is often sufficient. If several reports need the same fiscal periods, however, a calendar table or explicit fiscal-period fields can provide more control than ordinary calendar quarters. Fiscal years do not always match January through December, so confirm the organization’s definition before building a report around calendar grouping.
On Excel for Microsoft 365 for Windows, use the contextual PivotTable Analyze tab or the right-click menu. On Mac, select a PivotTable item and use the contextual PivotTable ribbon or right-click menu; names and ribbon placement can vary slightly by release. Excel for the web has a more limited set of PivotTable editing features than desktop Excel, so if Group is unavailable there, open the workbook in the desktop app to perform the grouping.
Frequently Asked Questions
Why can’t I group dates in my PivotTable?
Most often, the source date field contains a blank, an error, or a value stored as text instead of a real Excel date. Check the entire source column, correct inconsistent entries, refresh the PivotTable, and try grouping again.
How do I group months without combining different years?
Right-click a date, choose Group, and select both Months and Years. The PivotTable will distinguish, for example, January 2025 from January 2026 instead of combining them into one January total.
Can I group PivotTable values into custom ranges?
Numeric grouping uses a starting value, ending value, and interval, so it is designed for regular bands. For irregular ranges with custom labels, add a category field to the source data and use that field in the PivotTable.
Does grouping change the original Excel data?
No. Grouping changes the PivotTable’s presentation and summary, not the underlying source records. Editing or cleaning the source data is a separate action.
How do I undo grouping in Excel?
Right-click an item in the group and choose Ungroup, or select it and use PivotTable Analyze > Ungroup. The individual items return to the PivotTable; the source data remains intact.
Related
- Excel Conditional Formatting for Absolute Values
- Excel Conditional Formatting Across Sheets Explained
- Excel Conditional Formatting: Highlight Dates 1 Year Later
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Waterfall Chart: Show a Total at the EndTo show a final total in an Excel waterfall chart, select the last bar, right-click it, and choose…
- What Does a Trendline Tell You in Excel? A Clear GuideA trendline in Excel summarizes the general direction or pattern of data in a chart: it can show…
- Excel Pivot Chart: How to Remove ButtonsTo remove the buttons from an Excel PivotChart in Microsoft 365 for Windows, select the chart, open the…
- Correlation Graph in Excel: Create and Interpret OneTo make a correlation graph in Excel, select two columns of paired numeric data and choose Insert >…
- How to Make a Graph in Excel the Easy WayTo make a graph in Excel, put your labels and numbers in a simple table, select the data,…
- Step Progress Chart in Excel: Build a Clear TrackerA step progress chart is a visual way to show how far a project, process, or checklist has…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.