The PIVOTBY

The PIVOTBY function in Office 365 Excel is a powerful tool that allows you to create summary reports similar to PivotTables but using formulas instead. Here’s a brief overview of how it works and its syntax:

PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])

Arguments:

• row_fields: A range of values to group in rows.
• col_fields: A range of values to group in columns.
• values: The values to aggregate.
• function: The function to summarize the grouped data (e.g., SUM, AVERAGE, COUNT).
• field_headers (optional): Specifies whether to include headers in the results.
• row_total_depth (optional): Determines whether to show total rows.
• row_sort_order (optional): Indicates how to sort rows.
• col_total_depth (optional): Determines whether to show total columns.
• col_sort_order (optional): Indicates how to sort columns.
• filter_array (optional): Filters out specific rows.
• relative_to (optional): Specifies the reference point for relative references

Example:

Let’s say you have a dataset with Category, Year, and Sales columns, and you want to summarize sales by category and year:

1. Row Fields: Category
2. Column Fields: Year
3. Values: Sales
4. Function: SUM

The formula would look like this:

=PIVOTBY(A2:A10, B2:B10, C2:C10, LAMBDA(x, SUM(x)))

This formula groups the data by Category and Year, and sums the Sales for each group.

Benefits of PIVOTBY:

• Dynamic: Updates automatically as your data changes.
• Flexible: Can perform multiple aggregations and sorting.
• Simplifies Analysis: Allows you to create summary reports without the need for separate PivotTables.