To group data by a column in Excel Power Query, open the query, select Home > Group By, choose the column to group on, then select an operation such as Sum, Count Rows, or All Rows. Choose the value column when the operation needs one, and select OK; Power Query returns one result row per group.
What Group By does in Power Query
Group By combines records that share the same value in one or more columns and summarizes, or nests, the other records for each group. For example, grouping sales records by Region and summing Sales changes a detail table with many transactions into a shorter table with one total per region. The source data is not erased: Power Query records the transformation as a query step and produces a result that can be loaded to Excel.
This is different from sorting or filtering. Sorting changes row order, and filtering removes rows from the current view. Grouping changes the level of detail by gathering matching records into groups. It also differs from Excel’s worksheet Subtotal command, which inserts summary rows into a range, and from a PivotTable, which provides an interactive report layout. Power Query is especially useful when the same cleanup and aggregation should be repeatable after new source data arrives.
Prepare the source and open the query
For a straightforward workflow in Excel for Microsoft 365 on Windows, turn the source range into an Excel table before importing it. Select a cell in the data and choose Insert > Table (or press Ctrl+T), confirm whether the table has headers, and select OK. A table expands as new rows are added, making it a practical source for a query that will be refreshed later.
- Select a cell in the table.
- Choose Data > From Table/Range to open the Power Query Editor.
- Check that the column headers and data types look right. In particular, confirm that the values to be summed are numeric rather than text.
- Choose Home > Group By.
In Excel for Mac, Power Query is available in Microsoft 365, but connector and editing availability can differ from Windows and between releases. Look under the Data tab for the Get Data or Power Query options available in that installation. Once the query is open in the editor, the grouping idea is the same; menu placement and available sources may vary. Excel for the web also has changing, connector-dependent Power Query capabilities, so use desktop Excel if the editor or a needed source option is unavailable.
Group by one column step by step
In the Group By window, the default mode is usually Basic. Select the column whose values define the groups, give the result column a useful name, and choose an operation. If you choose Sum, Average, Min, or Max, also choose the numeric column to summarize. For Count Rows, there is no value column to select because the operation counts records in each group.
- Sum: adds the selected numeric values within each group.
- Average: returns the arithmetic mean for each group.
- Min and Max: return the smallest or largest value in the selected column.
- Count Rows: counts records in each group, including records whose other fields may be blank.
- All Rows: keeps the matching records as a nested table in each output row instead of reducing them to one aggregate.
After selecting OK, the editor displays the grouped result and adds a Grouped Rows step to the Applied Steps list. You can reopen the step by selecting its gear icon when available, or delete and recreate it if you need different settings. Give output columns names that explain what they contain, such as Total Sales or Order Count, rather than leaving generic names that are hard to interpret later.
Group by multiple columns and choose more than one result
Use Advanced in the Group By window when each group should be defined by a combination of fields. For example, grouping by both Region and Product creates a separate result for every region-product pair. North with Tea and South with Tea are different groups, even though the product is the same. Add grouping columns in the desired sequence; the result includes each key column.
Advanced mode also lets you define multiple aggregations in the same step. You could create Total Sales with Sum of Sales and Order Count with Count Rows, so the output includes both measures for each region. Ensure that each new result column has a distinct, descriptive name. If you instead group only by Region and choose All Rows, the result has one row per region plus a nested table containing that region’s original records; this is useful when you want to perform more transformations within each group later.
A concrete sales example
Suppose a table contains four records: East, 100; East, 150; West, 80; and West, 120, with columns named Region and Sales. Group by Region, select Sum, and choose Sales. The output is East with Total Sales of 250 and West with Total Sales of 200. The arithmetic is 100 + 150 = 250 for East and 80 + 120 = 200 for West. The four transaction rows become two summary rows; the original table remains available as the query source.
If you also add Count Rows in Advanced mode, each region has a count of 2. This count is the number of records, not the number of distinct customers and not a count of nonblank Sales values. If the question is how many different customers purchased in each region, first decide what a distinct customer means in the data, then use a distinct-count approach appropriate to that structure rather than treating Count Rows as interchangeable.
Dates, text, and choosing a grouping key
Grouping operates on the values in the selected key columns. A date field containing individual days therefore creates daily groups, not automatic month or year groups. To summarize by month, create a month-level key first—for example, use the date transformation options in the editor to convert dates to the start of their month—then group by that new column. A month-start date preserves the year, unlike a month name alone: January 2025 and January 2026 should usually remain separate groups.
Text keys deserve a quick cleanup check. Values such as “North”, “North ” with a trailing space, and “NORTH” may not represent the same intended category in a report. Trim unnecessary spaces and standardize spelling or capitalization before grouping if those differences should be combined. Do not normalize automatically when capitalization or spacing carries meaning. Also check for blank or null key values: Power Query can return a group for them, which may be appropriate but should not be mistaken for a named category.
Power Query grouping versus a PivotTable
Use Power Query Group By when you want to transform a source into a prepared summary table, repeat the same steps during refresh, or combine grouping with other query steps such as filtering and cleaning. Use a PivotTable when readers need to rearrange fields, expand or collapse categories, change report measures, or explore the data interactively. These tools can also work together: load a cleaned query result to the worksheet or Data Model, then build a PivotTable from it.
A grouped query does not update continuously as someone edits a source cell. It recalculates when the query is refreshed, such as through Data > Refresh All. For a regularly updated report, confirm that the query points to the intended table or source and make refresh part of the reporting routine. Loading settings can be changed through Home > Close & Load or its menu, depending on the editor version; the usual choice is to load the result as a table in a worksheet.
Common problems and how to fix them
- Sum is unavailable or returns an unexpected result: inspect the data type of the value column. Text that looks like a number may need conversion to a numeric type. Check for symbols, spaces, or locale-specific decimal separators that prevent conversion.
- There are more groups than expected: inspect the key values for spelling differences, extra spaces, blanks, or different date precision. If grouping by a timestamp, two times on the same calendar day are different values; remove the time or create a date-only key if the report should be daily.
- The count seems too large: Count Rows counts every record in the group. It does not automatically count unique IDs. Verify that the source has the intended row grain—for example, one row per order line rather than one row per order.
- New source rows do not appear: refresh the query and check whether the source is an expanding Excel table rather than a fixed range. Confirm that the new rows are inside the table and that the query is connected to it.
- Totals differ from a worksheet calculation: compare the filters and included records, confirm that both calculations use the same column and data types, and check whether blank or error values are being handled differently.
Frequently Asked Questions
Can I group by two columns in Power Query?
Yes. Open Home > Group By, choose Advanced, and add both columns under Group by. Power Query creates groups for each unique combination of their values.
How do I group rows and sum a column?
Choose the grouping key, select Sum as the operation, then choose the numeric column to add. If Sum is not offered or the result is wrong, check that Power Query recognizes the values as numbers.
Does Group By change my original Excel data?
No. It creates a query transformation and a separate result. The original source remains intact unless you separately edit it in Excel. Refreshing reruns the query using the current source data.
How do I count records in each group?
Select Count Rows as the Group By operation. It counts all rows in each group; it does not mean count unique values or count only nonblank cells in a chosen column.
Can Power Query group dates by month?
Yes, but make a month-level date key first. Converting each date to the first day of its month and grouping by that key keeps the year and month together, avoiding a single combined January group across multiple years.
Related
- Philippine Wedding Budget Spreadsheet: Build a Budget
- How to Self-Record a Presentation in PowerPoint
- How to Run a PowerPoint Slideshow on a Loop Automatically
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Box Plot in Excel: Example, Steps, and InterpretationIn Excel for Microsoft 365 on Windows, put each group of values in its own column, select the…
- Outlook Rules on Windows 11: How to Create and Manage ThemTo create an Outlook rule in Windows 11, open Outlook’s rules settings, choose a condition such as a…
- Lock Cell References in Excel With Dollar Signs EasilyTo lock a cell reference in an Excel formula, add a dollar sign before the column letter, the…
- Excel Consolidate Lists With Duplicates: Keep or Remove?To combine lists and keep repeated items in Excel for Microsoft 365 on Windows, enter =VSTACK(A2:A5,B2:B4) in an…
- Export Outlook Contacts: Steps, Formats, and TipsTo export Microsoft Outlook contacts, open the People or Contacts area and use its export command to save…
- Excel Split Cell Shortcut: The Fastest Ways to Separate DataIn Excel for Microsoft 365 on Windows, there is no single dedicated key that splits a cell; the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.