How to Group in Excel Pivot Table

A Pivot Table is one of Excel’s most powerful tools for summarizing large datasets, but the initial output isn’t always report-ready. A table showing sales for every single day of the year or for every individual product isn't a summary - it's just another long list. This is where grouping comes in. By grouping items, you can transform your granular Pivot tables into insightful, high-level summaries perfect for real analysis.

This tutorial will walk you through exactly how to group data in Excel Pivot Tables. We’ll cover how to automatically group by dates (like months or weeks), by numbers (like creating age or price brackets), and how to manually group text fields (like products or cities).

What is Grouping in a Pivot Table?

Grouping in a Pivot Table is the process of combining individual items in your "Rows" or "Columns" labels into categories. Instead of analyzing hundreds of individual data points, you get a clean summary organized by larger buckets that make sense for your analysis.

For example, you can use grouping to:

• Combine daily sales dates into months, quarters, or years.
• Condense a list of customer ages into age brackets (e.g., 20-29, 30-39, 40-49).
• Cluster different products or services into broader product lines or categories.
• Group individual states or cities into sales regions (e.g., "West Coast," "East Coast").

The main benefit is clarity. Grouping helps you and your audience see the big picture, spot high-level trends, and make decisions without getting bogged down by details that don't matter at that moment. It turns your data into a story.

How to Group Data by Date

Grouping transactional data by dates is one of the most common and useful tasks in any kind of business reporting. Excel makes this incredibly simple. Let's imagine you have a sales report with a row for every transaction, including the date of sale.

Step-by-Step: Grouping by Months and Years

Following our example, let's group these daily sales into a much more useful view showing performance by year and month.

1. Step 1: Right-click on any single date cell within the "Row Labels" column of your Pivot Table.
2. Step 2: From the menu that appears, click "Group...".
3. Step 3: The "Grouping" dialog box will appear. Here, you can choose how you want to consolidate your dates. To see a yearly and monthly breakdown, select both "Months" and "Years" from the list. You can select multiple items by clicking on them (they will become highlighted in blue). Click OK.

Excel will instantly restructure your Pivot Table. It automatically adds a new "Years" field and neatly nests the "Months" within each year, complete with subtotals. You now have a clean, readable summary of sales performance over time.

How to Group by Weeks

You might have noticed that "Weeks" isn't an option in the standard grouping box. Don't worry, you can still do it with a couple of clicks. Grouping by weekly intervals is great for short-term campaign analysis or project management dashboards.

1. Step 1: Right-click a date cell in your Pivot Table and choose "Group...".
2. Step 2: In the dialog box, first deselect any other options (like Months or Quarters) by clicking on them. Then, select only "Days".
3. Step 3: Next, change the "Number of days" spinner to 7.

Pro-Tip: Pay attention to the "Starting at" date. Excel defaults to the first date in your dataset. If you want your weeks to start on a Monday, for example, you can manually change this date to the previous Monday.

Click OK, and your report will now show sales totals for each 7-day period.

How to Ungroup Your Data

If you need to revert to the original, ungrouped view, the process couldn't be simpler.

1. Step 1: Right-click on any of the grouped date labels in your Pivot Table (e.g., "Jan", "2023").
2. Step 2: Select "Ungroup" from the menu. Your Pivot Table will immediately return to showing individual dates.

How to Group Data by Numbers

Grouping numeric data is essential for understanding distribution. For example, if you want to analyze customer demographics, looking at every single age is not useful. You'd want to create age brackets. Similarly, for product analysis, you might want to create price ranges.

Step-by-Step: Grouping into Numerical Ranges

1. Step 1: Right-click on any numbered cell in the "Row Labels" (in this case, any age value).
2. Step 2: Click "Group...".
3. Step 3: The Grouping dialog box for numbers appears. It has three main settings:

• Starting at: The first value for your ranges. Excel will automatically detect the minimum value in your data. You can override it if you want your brackets to start at a nice round number like 20.
• Ending at: The last value. Excel detects the maximum value.
• By: This is the interval, or the size of each group. If you want age brackets that span 10 years (20-29, 30-39, etc.), you would enter 10 here.

For our example, we’ll set it to group by 10. After clicking OK, the Pivot Table is immediately transformed into a clear demographic summary.

How to Group by Text Fields (Manual Grouping)

What if your categories don't have a logical numeric or date sequence? For instance, you might want to group a list of specific products into a "Summer Collection" or a list of cities into custom sales territories. This requires manual grouping.

Step-by-Step: Grouping Text Manually

1. Step 1: Select the items you want to put into your first group. Hold down the Ctrl key to select multiple items that are not next to each other. In our example, we'll select "New York," "Toronto," and "Vancouver."
2. Step 2: Right-click on your selection and choose "Group...".
3. Step 3: Excel will instantly create a "Group1" containing your selected cities. Notice two things: a new field called "City2" has also appeared in your Pivot Table Fields list, and "Group1" is now a collapsible item.
4. Step 4: To give the group a meaningful name, simply click on the cell that says "Group1" and type your new name, such as "North America," then press Enter.
5. Step 5: Repeat the process for your other groups. Select "London," "Paris," and "Berlin," right-click, select "Group...," and rename "Group2" to "Europe."
6. Step 6: You now have a clean regional report. To make it even tidier, you can drag the original "City" field out of the "Rows" area in your PivotTable Fields list, leaving you with just the high-level regional totals.

Troubleshooting: The "Cannot Group That Selection" Error

One of the most common hiccups users face is the dreaded "Cannot group that selection" error message. This almost always happens for one very specific reason: inconsistent data in your source column.

If you're trying to group a column of numbers, and even one cell contains text or is blank, the operation will fail. The same applies to dates, if you have a date column that contains a blank cell or a date entered as simple text (e.g., "Jan 15"), Excel won't know how to treat the entire column for grouping.

How to Fix It:

1. Go back to your source data tab (not the Pivot Table).
2. Select the column that's causing the issue.
3. Apply a filter to it (Data tab > Filter).
4. Click the filter dropdown. Uncheck "Select All," and then scroll to the bottom. Look for "(Blanks)" or any text values that don't belong in a date or number column.
5. Once you've found the outlier cells, either delete the rows or correct the data to the proper format (e.g., enter 0 instead of a blank, or re-type a text-date in a proper date format).
6. Go back to your Pivot Table, refresh it (Right-click > Refresh), and try grouping again. It should now work perfectly.

Final Thoughts

Mastering the group function in Pivot Tables elevates your Excel skills from just organizing data to producing powerful, decision-driving reports. Whether you’re consolidating dates into fiscal quarters, categorizing customers into demographic brackets, or creating custom sales territories, grouping brings a professional level of clarity to your analysis that raw data can't match.

While Pivot Tables are fantastic, the process of pulling data from multiple places, formatting it, and building these reports every week or month can turn into a tedious cycle. That’s where automation makes all the difference. With a tool like Graphed (target="_blank" rel="noopener"), we handle this busy work for you. You can connect your marketing and sales platforms (like Google Analytics, Shopify, or Facebook Ads) in one click, and then create entire dashboards just by asking in plain English. For example, you can just ask, “Show me sales revenue from Shopify grouped by month for the last year" and receive an interactive dashboard in seconds, which updates automatically and can be shared with anyone.