Function Problems

How to Use the PIVOTBY Function in Excel for Data Summaries

Ayan Masood Oct 7, 2026 869 views

Question details

The user wants to understand and use the new PIVOTBY function in Excel to create pivot-table-like summaries using formulas.

How to Use the PIVOTBY Function in Excel for Pivot-Style Summaries

Product: Microsoft Excel
Device & OS: not provided
Scenario: Organizing and summarizing large datasets into dynamic pivot-style tables without manually creating a traditional PivotTable.
Observed behavior: The user seeks to dynamically summarize data arrays by row and column groups using a single, efficient formula directly in the spreadsheet grid.

Before you start

Ensure you are using a compatible version of Microsoft 365, as the PIVOTBY function is a newer addition and may not be available in standalone versions like Excel 2019 or 2021.

Solution 1Recommended

Use the PIVOTBY Function to Summarize Data

Apply the basic syntax of the PIVOTBY function to aggregate your data array by specifying row, column, and value fields.

The PIVOTBY function allows you to group, aggregate, and sort data in a single formula. The basic syntax is =PIVOTBY(row_fields, col_fields, values, function).

Select the output cell

Click on the cell where you want the top-left corner of your dynamic pivot-style summary to begin. Make sure there is enough empty space below and to the right to allow the data array to spill.

Enter the PIVOTBY formula

Type =PIVOTBY( to begin the formula. Select the range of cells you want to group by rows (for example, A2:A100 for 'Region').

Define columns and values

Add a comma, then select the range for your column groups (for example, B2:B100 for 'Product'). Add another comma and select the data you want to aggregate (for example, C2:C100 for 'Sales').

Choose the aggregation function

Type , SUM) to finish the basic formula and press Enter. The function will dynamically generate a pivot table-like summary directly in the grid.

Use the PIVOTBY Function to Summarize Data

Dynamic Array Spilling: Because PIVOTBY returns an array, do not manually enter data in the cells where the results need to appear, or you will trigger a #SPILL! error.

Solution 2

Customize PIVOTBY with Advanced Arguments

Utilize optional arguments in PIVOTBY to control headers, subtotals, grand totals, and sorting preferences.

Easily Summarize Data in WPS Office

Create Pivot-Style Summaries in WPS Spreadsheet

While advanced dynamic formulas are useful, creating a traditional PivotTable in WPS Spreadsheet is an intuitive, drag-and-drop way to summarize massive datasets without writing complex functions. WPS Office provides an incredibly user-friendly interface that is fully compatible with Excel formats.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your dataset. Ensure all your data columns have clear, descriptive headers.
2. Insert a PivotTable: Navigate to the 'Insert' tab on the top ribbon and click the 'PivotTable' button.
3. Select your data range: Confirm the range of your dataset in the dialog box and choose whether to place the PivotTable in a new worksheet or the existing one, then click 'OK'.
4. Build your summary: In the PivotTable Fields pane on the right side of the screen, drag and drop your fields into the Rows, Columns, and Values areas to instantly summarize your data.

Create dynamic pivot tables with an intuitive drag-and-drop interface.Fully compatible with Microsoft Excel (.xlsx) formats and standard spreadsheet formulas.Lightweight, fast, and completely free to use for everyday data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the PIVOTBY function returning a #NAME? error?

The #NAME? error typically occurs if your version of Excel does not support the PIVOTBY function. Ensure you have an active Microsoft 365 subscription and have updated to the latest channel that includes this newer feature.

What is the difference between PIVOTBY and a traditional PivotTable?

PIVOTBY is a dynamic formula that automatically updates when the source data changes without needing a manual refresh. Traditional PivotTables offer a visual drag-and-drop interface that is often easier for complex, multi-layered data analysis without requiring you to write code.

Can I use multiple functions like SUM and AVERAGE at the same time in PIVOTBY?

Yes, you can use the HSTACK or VSTACK functions within the 'function' argument of PIVOTBY to apply multiple aggregations simultaneously. For example: =PIVOTBY(A2:A10, B2:B10, C2:C10, HSTACK(SUM, AVERAGE)).

How do I fix a #SPILL! error when using PIVOTBY?

A #SPILL! error means there is existing data blocking the formula's output range. Locate the cells below and to the right of your PIVOTBY formula, and delete or move any overlapping data to allow the array to expand properly.

Excel & Data Problems