How to Use GroupBy in Excel: Step-by-Step

Excel doesn’t have a button labeled “GroupBy,” but you can achieve the same result—grouping rows by a category and summarizing them—using the Subtotal feature, PivotTables, or the newer Group By tool in Power Query. This guide walks you through the most practical method (Subtotal) for a quick in-sheet summary, then shows the PivotTable approach for more flexible reporting. By the end, you’ll be able to turn a flat list of transactions into a tidy summary of totals, counts, or averages per group.

What You’ll Need

• Excel 2010 or later (steps apply to Excel for Microsoft 365, Excel 2021, 2019, and 2016).
• A worksheet with data in a contiguous range (no blank rows or columns within the data).
• One column that contains the category/group values (e.g., Region, Product, Date).
• At least one numeric column you want to summarize (e.g., Sales, Quantity).
• Column headers in the first row (Excel uses them as field names).

Step-by-Step

Method 1: Using Subtotal (Quick and Simple)

1. Sort your data by the grouping column. Click anywhere in your data range, go to the Data tab, and click Sort. In the dialog, choose the column that contains your groups (e.g., “Region”) as the Sort by field. Sorting ensures all rows of the same group are adjacent—Subtotal requires this.
2. Open the Subtotal dialog. With your data range selected (or any cell inside it), go to the Data tab and click Subtotal (in the Outline group).
3. Set the grouping field. In the At each change in drop-down, select the column that contains your groups (e.g., “Region”).
4. Choose the summary function. In the Use function drop-down, pick Sum (or Count, Average, Min, Max, etc.). For most cases, Sum is what you want.
5. Select the columns to summarize. In the Add subtotal to list, check the numeric columns you want to summarize (e.g., “Sales”). Uncheck any that are not numeric or not needed.
6. Set options. Leave Replace current subtotals checked (if this is your first time). Check Page break between groups only if you plan to print each group on a separate page. Ensure Summary below data is checked—this places the subtotal rows below each group.
7. Click OK. Excel inserts subtotal rows like “Region Total” and a grand total at the bottom. You’ll also see outline numbers (1, 2, 3) on the left—click 2 to see only the subtotal rows, or 3 to see all rows.
8. To remove subtotals: Go back to Data > Subtotal and click Remove All. This also removes the outline structure.

Method 2: Using a PivotTable (More Flexible)

1. Select your data range (including headers).
2. Go to Insert > PivotTable. In the dialog, choose New Worksheet or Existing Worksheet, then click OK.
3. In the PivotTable Field List (on the right), drag the grouping column (e.g., “Region”) to the Rows area.
4. Drag the numeric column (e.g., “Sales”) to the Values area. By default, Excel sums it. To change the summary (e.g., to Average), click the drop-down arrow next to “Sum of Sales” in the Values area, choose Value Field Settings, and select a different function.
5. Optionally, drag another field to the Columns area to create a cross-tab (e.g., add “Year” to columns to see sales by region and year).
6. Refresh the PivotTable when your source data changes: right-click the PivotTable and choose Refresh.

Method 3: Using Power Query (For Large or Repeated Data)

1. Select your data range and go to Data > From Table/Range (this opens Power Query). If prompted, check “My table has headers.”
2. In Power Query, go to the Home tab and click Group By.
3. In the dialog, choose Advanced (if you want multiple aggregations).
4. Select the grouping column (e.g., “Region”) in the first drop-down.
5. Add rows for each summary: pick a column, choose an operation (Sum, Average, Count Rows, etc.), and give a new column name (e.g., “Total Sales”).
6. Click OK. The preview shows the grouped table. Then click Close & Load to put the result back into a new worksheet.

Common Mistakes

• Not sorting before using Subtotal. If rows of the same group are scattered, Subtotal will create multiple subtotal rows for the same group (one per block). Always sort first.
• Including blank rows or columns in the data range. This confuses Excel and can cause subtotals to appear in wrong places or the range to be split.
• Selecting non-numeric columns for Sum. Subtotal will treat text as 0, giving misleading results. Only check numeric columns.
• Forgetting to refresh a PivotTable. If you update the source data, the PivotTable doesn’t automatically update; you must right-click and choose Refresh.
• Using Subtotal on a Table (Excel Table). The Subtotal command is disabled for Excel Tables. Convert the table to a range (right-click > Table > Convert to Range) or use a PivotTable instead.

Tips

• Use the outline buttons (1, 2, 3) after applying subtotals to quickly collapse/expand the view. This is handy for printing only the summary.
• If you need to change the grouping field later, just re-run the Subtotal dialog and pick a different column—Excel will replace the old subtotals (if the option is checked).
• For a PivotTable, use the Design tab to apply a “Report Layout” like Tabular or Outline to make the output look like a traditional grouped table.
• To copy only the subtotal rows (without the detail), click the outline level 2, then press Ctrl+G (Go To) > Special > Visible cells only, then copy and paste. This avoids copying hidden rows.
• Power Query’s Group By is ideal when you need to repeat the grouping on updated data—just refresh the query.

Troubleshooting

• Subtotal button is grayed out. This happens if your data is an Excel Table (created via Ctrl+T). Convert the table to a range: right-click any cell in the table, choose Table > Convert to Range, then try again.
• Subtotals appear for every row, not per group. You likely didn’t sort the data first. Undo (Ctrl+Z), sort by the grouping column, and reapply Subtotal.
• Subtotal shows “Count” instead of “Sum”. This occurs when the column you selected contains text or blank cells. Ensure the column is truly numeric (no stray text like “N/A”) and re-run the Subtotal with Sum selected.
• PivotTable shows “(blank)” as a group. This means some cells in your grouping column are empty. Fill them with a value (e.g., “Unknown”) or filter them out before creating the PivotTable.
• Power Query Group By returns an error. Check that the column names in the dialog exactly match your headers (case-insensitive). If you renamed a column earlier, the old name might still be referenced.

Frequently Asked Questions

Q: Can I group by multiple columns at once?
A: Yes. In Subtotal, you can add multiple levels by running Subtotal again with a different “At each change in” and unchecking “Replace current subtotals.” In a PivotTable, just drag multiple fields to the Rows area. In Power Query, use the Advanced Group By dialog to add multiple grouping columns.

Q: Does GroupBy change my original data?
A: Subtotal inserts rows into your data, modifying it. PivotTable and Power Query create a separate summary without altering the source data. If you want to keep the original intact, use a PivotTable or Power Query.

Q: How do I get the count of items per group?
A: In Subtotal, choose Count as the function and select any column (even a text column). In a PivotTable, drag any field to Values and change the summary to Count. In Power Query, use the Count Rows operation.

Q: Can I group by date (e.g., by month or year)?
A: Yes. In a PivotTable, right-click a date field in the Rows area, choose Group, and select Months, Years, etc. In Power Query, you can extract the year/month using the Date functions, then group by that new column. Subtotal requires you to create a helper column with the month/year first.

Q: Why does my PivotTable not show the latest data?
A: PivotTables are static snapshots. After changing the source data, right-click the PivotTable and select Refresh. If you added new rows beyond the original range, update the source range in PivotTable Analyze > Change Data Source.

Q: Is there a GROUPBY function in Excel?
A: Yes, a new GROUPBY function is rolling out to Microsoft 365 subscribers (as of 2024). It allows dynamic array formulas like =GROUPBY(region, sales, SUM). If you don’t see it, your Excel version doesn’t have it yet; use the methods above.

Related

• Fix Windows MBR Without Installation Disk
• Configure Windows Memory Cleaner Safely: Full Guide
• MacBook Pro Touch Bar Flickering? Fix It