Can PIVOTBY handle date columns in row_fields or col_fields?

Yes. Dates group by exact value, so each unique date becomes a separate row or column. To group dates by month, quarter, or year, create a helper column using MONTH, QUARTER, or YEAR functions, then pass that helper column to PIVOTBY instead of the raw date.

How do I add grand totals to a PIVOTBY output?

PIVOTBY does not generate a grand-total row or column directly. Add totals manually with SUM or AVERAGE formulas referencing the PIVOTBY output below or to the right, or use GROUPBY if you need more structural control over subtotals and grand totals.

What is the difference between PIVOTBY and GROUPBY?

PIVOTBY creates a two-dimensional cross-tabulation (rows × columns) with one function. GROUPBY creates a hierarchical, one-dimensional report grouped by one or more fields. Use PIVOTBY for traditional pivot tables; use GROUPBY for nested, drill-down data views.

Can PIVOTBY reference data from another worksheet or workbook?

Yes, use fully qualified ranges like SheetName!A2:A11 or [Workbook.xlsx]Sheet!A2:A11. If linking to an external file, ensure it remains open. For large cross-workbook operations, consider copying the source data to the same workbook first to avoid performance issues.

PIVOTBY function

PIVOTBY creates a dynamic pivot table by grouping source data along rows and columns, then applying an aggregation function.

=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order])

Generate a PIVOTBY formula

Describe what you need. The generator will reach for PIVOTBY where PIVOTBY is the right tool, and tell you when it is not.

Arguments

How PIVOTBY reads its arguments

Argument	Required	Description
row_fields	Required	The range whose unique values become the pivot table rows; rows are sorted in order of first appearance unless row_sort_order is specified.
col_fields	Required	The range whose unique values become the pivot table columns; column order follows first appearance by default.
values	Required	The range of data to aggregate; must have the same row count as row_fields and col_fields, and contain values compatible with the chosen function.
function	Required	The aggregation function name as text: SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, or PRODUCT; case-insensitive and required.
field_headers	Optional	Optional; TRUE to use auto-labeled headers, FALSE or omitted for no headers, or an array of text labels for custom header names.
row_total_depth	Optional	Optional integer (0–3) specifying how many row-group hierarchy levels display subtotal rows; 0 or omitted disables row subtotals.
row_sort_order	Optional	Optional sort direction for row groups: omit for insertion order, 1 for ascending, −1 for descending; applies only to the innermost group level.

Returns

A dynamic array containing a multi-dimensional summary table with pivot row and column headers, automatically aligned by group.

Availability

Excel: 365 (2024+) · Google Sheets: Not available

Worked examples

1. Sum total hours logged by task and team member

Task	Alice	Bob	Charlie
Dashboard	20	8	0
API Endpoint	16	12	10
Database	0	6	14
Testing	5	9	0

=PIVOTBY(A2:A11,B2:B11,E2:E11,"SUM")

Result: A cross-tabulation showing total hours per task per owner, with Dashboard totaling 28 hours, API Endpoint 38 hours, Database 20 hours, and Testing 14 hours.

PIVOTBY groups rows by task name (A2:A11) and columns by owner (B2:B11), then sums the hours from E2:E11. Alice logged two Dashboard entries (12+8=20 hours), while Bob logged 8 hours on Dashboard. Empty cells (0) represent task-owner pairs with no logged time.

2. Count how many times each owner appears on each task

Owner	Dashboard	API Endpoint	Database	Testing
Alice	2	1	0	1
Bob	1	1	1	1
Charlie	0	1	1	0

=PIVOTBY(B2:B11,A2:A11,A2:A11,"COUNTA")

Result: A table showing task assignment frequency per owner, with Alice assigned to 4 total tasks (2 instances on Dashboard, 1 on API, 0 on Database, 1 on Testing).

By reversing row and column fields, PIVOTBY now groups by owner (rows) and task (columns). The COUNTA function tallies the number of log entries for each owner-task combination. Alice appears twice for Dashboard, indicating multiple time entries; Bob appears once for each task, indicating one entry per task.

3. Calculate average hours per task and owner

Task	Alice	Bob	Charlie
Dashboard	10	8
API Endpoint	16	12	10
Database		6	14
Testing	5	9

=PIVOTBY(A2:A11,B2:B11,E2:E11,"AVERAGE",TRUE)

Result: A table with average hours per task per owner with labeled headers; Dashboard-Alice averages 10 hours (the mean of 12 and 8), while API Endpoint-Bob averages 12 hours.

The AVERAGE function calculates the mean hours for each task-owner group. Alice's two Dashboard entries average to 10 hours. Blank cells represent combinations with no time logged; AVERAGE returns empty (not 0) when no values exist for a group. Setting field_headers to TRUE includes descriptive column labels.

Common errors

Which PIVOTBY error are you seeing?

Error	Why it happens	How to fix it
#SPILL!	The output range is blocked by existing data, another formula, or a named range that occupies cells immediately below or to the right of where PIVOTBY would expand.	Clear or delete the blocking content, move the PIVOTBY formula to an empty region with enough free space, or delete the interfering named range.
#VALUE!	The function argument is misspelled (e.g., "SUMM" or "AVARAGE"), the row_fields/col_fields/values ranges are malformed, or a range contains incompatible data types that cannot be aggregated by the specified function.	Verify the function name exactly matches SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, or PRODUCT. Ensure all ranges are contiguous, reference correct sheets, and contain appropriate data types.
#REF!	A range reference (row_fields, col_fields, or values) points to deleted rows, moved cells, a sheet that no longer exists, or an external workbook that is closed.	Restore the deleted range, update the reference to the new location, reopen the external workbook, or recreate the formula with valid cell references.

Tips and when to use something else

Use PIVOTBY instead of manually building pivot tables with nested formulas; it automatically handles alignment, deduplication, and aggregation.
Reverse row_fields and col_fields to instantly swap the pivot orientation without recalculating, perfect for exploring data from different angles.
For group-by operations without a specific aggregate function, use GROUPBY instead—it creates a nested hierarchy that is better suited to drill-down reports than PIVOTBY's flat cross-tabulation.
PIVOTBY requires Excel 365 (2024+) and is not available in Google Sheets; older Excel versions must use the Insert menu's Pivot Table feature or GETPIVOTDATA on an existing pivot.

Related functions

GROUPBYGROUPBY groups data by one or more fields and applies an aggregation function, returning a dynamic array table with subtotals and a grand total row.FILTERReturns filtered rows from an array based on a boolean criteria array, retaining the original order and all columns.UNIQUEReturns all distinct values from an array, removing duplicates by row or column; use it to extract clean, unique item lists from repetitive data.SORTSORT arranges array data in ascending or descending order by a specified column, returning a sorted copy as a dynamic array.GETPIVOTDATAGETPIVOTDATA extracts a single value from a PivotTable by specifying the data field and optional field/item filters, returning the exact cell content.

Frequently asked questions

Can PIVOTBY handle date columns in row_fields or col_fields?: Yes. Dates group by exact value, so each unique date becomes a separate row or column. To group dates by month, quarter, or year, create a helper column using MONTH, QUARTER, or YEAR functions, then pass that helper column to PIVOTBY instead of the raw date.
How do I add grand totals to a PIVOTBY output?: PIVOTBY does not generate a grand-total row or column directly. Add totals manually with SUM or AVERAGE formulas referencing the PIVOTBY output below or to the right, or use GROUPBY if you need more structural control over subtotals and grand totals.
What is the difference between PIVOTBY and GROUPBY?: PIVOTBY creates a two-dimensional cross-tabulation (rows × columns) with one function. GROUPBY creates a hierarchical, one-dimensional report grouped by one or more fields. Use PIVOTBY for traditional pivot tables; use GROUPBY for nested, drill-down data views.
Can PIVOTBY reference data from another worksheet or workbook?: Yes, use fully qualified ranges like SheetName!A2:A11 or [Workbook.xlsx]Sheet!A2:A11. If linking to an external file, ensure it remains open. For large cross-workbook operations, consider copying the source data to the same workbook first to avoid performance issues.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17