AIgo Notes

››Note details

Excel GROUPBY Hacks to Instantly Improve Your Reports!

BY p7pdb

July 14, 2025•

Public

Private

4069 views

Excel GROUPBY Function: 7 Advanced Tricks

Objective

This is a tutorial/article summary showing advanced techniques and tricks with Excel's GROUPBY function, aiming to help users go beyond simple grouping and leverage powerful dynamic aggregation capabilities.

Table Structure Example

Sample table: TSales
Columns: Date, Division, Product, Sales Manager, Sales
Size: 320 rows

GROUPBY Overview

GROUPBY is used for grouping and aggregating data dynamically.
Offers more function options than a Pivot Table (e.g., median, mode, custom LAMBDA functions).
Can toggle headers, totals, sorting, filtering, etc.

7 GROUPBY Tips and Tricks

1. GROUPBY with Multiple Non-Adjacent Columns

Problem: Need to group by columns that aren’t side by side.
Solution: Use HSTACK to horizontally stack columns before passing to GROUPBY, e.g.:
```
GROUPBY(HSTACK([Division], [Sales Manager]), ...)
```

2. Multiple Aggregations for the Same Column

Requirement: Aggregating the same column with different metrics (e.g., sum, average, median, percent of total).

Solution: Use HSTACK for function argument:

GROUPBY(..., ..., HSTACK(SUM, AVERAGE, MEDIAN, PERCENTOF))

Note: Adjust formatting as needed since grouped output may not be auto-formatted.

3. Distinct Count (Unique Items) per Group

Goal: Count unique products by division.
Challenge: No built-in DISTINCTCOUNT in GROUPBY.

Solution: Use a LAMBDA with COUNTA(UNIQUE(a)):

GROUPBY([Division], [Product], LAMBDA(a, COUNTA(UNIQUE(a))))

Can also show headers dynamically.

4. Dynamic Formatting of Total Row

Move Totals to Top: Use the total row position option in GROUPBY.
Dynamic Total Row Formatting: Use conditional formatting rules based on the cell value ("Total") for bold/border styling.
Formatting auto-adjusts as the data changes.

5. Sorting GROUPBY Results by Month Name

Issue: Months sorted alphabetically, not chronologically.
Solution: Include the month number as a hidden sorting column:
1. Use YEAR, MONTH, and TEXT for columns.
2. Stack with HSTACK(YEAR(Date), MONTH(Date), TEXT(Date, "MMMM")).
3. Hide month number after sorting with CHOOSECOLS.
```
CHOOSECOLS(HSTACK(YEAR([Date]), MONTH([Date]), TEXT([Date], "MMMM"), ...), 1, 3, 4)
```

6. Grouping Multiple Value Columns with Different Aggregations

Task: Show for each division:
- Average sales
- Distinct product count

Solution:

GROUPBY([Division], HSTACK([Sales], [Product]), HSTACK(AVERAGE, LAMBDA(a, COUNTA(UNIQUE(a)))))

Optional: Use DROP to remove unnecessary header rows.

7. Return List of Items (Not Just Count)

Requirement: Instead of getting the count, return the list of product names per group.

Solution: Replace COUNTA with ARRAYTOTEXT in the LAMBDA function:

GROUPBY([Division], [Product], LAMBDA(a, ARRAYTOTEXT(UNIQUE(a))))

Tip: Hide the total row when it is not meaningful in this report (e.g., with total depth argument).

Additional Resources

Excel Black Belt Package: Comprehensive Excel learning resource (link provided in the original video/article).
Download Workbook: Download materials used in the tutorial.
Related Videos: Watch the in-depth GROUPBY & PIVOTBY overview.