Introducing Excel’s New GROUPBY and PIVOTBY Functions

Overview

• Excel is rolling out two powerful new functions: GROUPBY and PIVOTBY.
• These functions change the way formulas are written, making complex data analysis easier and more dynamic.
• Suitable for all Excel users, regardless of experience level.

---

Objective

• Simplify the process of aggregating and summarizing data.
• Provide a dynamic, formula-based alternative to traditional PivotTables.

---

GROUPBY Function

Basic Usage

• Syntax:

  =GROUPBY(RowFields, Values, Function, [FieldHeaders], [TotalDepth], [SortOrder], [FilterArray])
  

• Mandatory Arguments:
  1. RowFields: Categories to group by (e.g., Division, Sales Manager)
  2. Values: Data to aggregate (e.g., Sales)
  3. Function: Aggregate calculation (e.g., SUM, AVERAGE, MEDIAN, COUNT, MAX, MIN, or custom Lambda)

Optional Arguments

• FieldHeaders: Show column headers in output.
• TotalDepth: Show/hide subtotals or grand totals, and control their position.
• SortOrder: Sort by a specific column in ascending (default) or descending order (-column number).
• FilterArray: Filter data before aggregation; can exclude blanks or specific patterns.

Key Advantages

1. Dynamic Updates: Any source data changes will instantly update results.
2. Text Values: Can aggregate text fields (e.g., concatenate or list unique names).
3. Data Cleansing: Can exclude unwanted rows (e.g., calculation subtotals, specific text patterns) using FilterArray.
4. Custom Aggregation: Write Lambda functions for advanced aggregation not natively supported.

Example Scenarios

• Total sales for each Sales Manager by Division:
  Use SUM as the aggregate function.
• Text aggregation:
  List names per Division (use ARRAYTOTEXT(UNIQUE(...)) inside GROUPBY).
• Data cleaning:
  Exclude rows with blanks or those starting with "Total".
• Percentage of total:
  Use PERCENTOF as the function to view contribution per category.
• Date grouping:
  Summarize by year or year-month using YEAR() or TEXT(Date,"yyyy-mm").
• Multiple grouping columns:
  Use HSTACK() to combine multiple fields (e.g., period and manager).

---

PIVOTBY Function

Key Difference from GROUPBY

• Includes an extra argument to define which field becomes columns (similar to columns in a PivotTable).
• Use when you want to pivot data—present one category along rows and another along columns.

Basic Usage

• Syntax:

  =PIVOTBY(RowFields, ColumnFields, Values, Function, [OtherOptionalArguments...])
  

• RowFields: What you want as rows (e.g., Sales Manager)
• ColumnFields: What you want as columns (e.g., Year)
• Values: Data to aggregate (e.g., Sales)
• Function: Aggregate calculation (e.g., SUM)

Optional Arguments

• Column Total Depth: Show/hide column totals.
• Column Sort Order: Sort columns as needed.
• FilterArray: Filter data before pivoting.

Example Scenario

• Yearly sales per Sales Manager as columns:
  Use Sales Manager as rows, Year as columns, Sales as values.

---

Advantages Over Pivot Table

• Everything is dynamic: Changes in the data update outputs automatically.
• Direct formula access: No need for manual refresh or drag-and-drop.
• Text aggregation and custom calculations: More flexibility than classic PivotTables.
• Inline data cleaning: Filter out unwanted rows directly in the formula.

---

Summary

• GROUPBY and PIVOTBY empower you to summarize and reshape data dynamically within formulas.
• They offer more control, flexibility, and automation than traditional PivotTables.
• Mastering these functions will streamline your data analysis and reporting in Excel.

---

Sample Formula Templates

Basic GROUPBY

=GROUPBY(A2:B100, C2:C100, "SUM")

GROUPBY with Custom Field Headers

=GROUPBY(A1:B100, C1:C100, "SUM", 3)

GROUPBY with Filtering

=GROUPBY(A2:A100, C2:C100, "SUM", , , , B2:B100 <> "")

GROUPBY with Date Formatting

=GROUPBY(TEXT(A2:A100, "yyyy-mm"), C2:C100, "SUM")

PIVOTBY for Yearly Summary

=PIVOTBY(B2:B100, YEAR(A2:A100), C2:C100, "SUM")

---

Conclusion

• Embrace GROUPBY and PIVOTBY to supercharge your Excel analytics.
• Experiment with their arguments for maximum flexibility.
• Share feedback and experiences to help the Excel community grow.

---

Thank you for exploring Excel’s newest game-changing functions!