How to Use PIVOTBY in Excel to Summarize Data by Rows and Columns

Excel’s PIVOTBY function builds a PivotTable-style summary with a single formula. It groups your data by one field down the rows and another across the columns, then calculates each intersection. For example, =PIVOTBY(A1:A9,B1:B9,C1:C9,SUM,3) turns eight sales rows into a region-by-quarter table with totals — and it updates the moment the source numbers change.

Applies to: Excel for Microsoft 365. The formulas follow Microsoft’s PIVOTBY reference, and every total below was checked by hand. If Excel doesn’t recognize PIVOTBY, see the error fixes at the end. If you only need to group in one direction, GROUPBY is simpler.

Build your first PIVOTBY summary

1. Enter the sample below in cells A1:C9, with the headers in row 1 and the sales as numbers.

Region	Quarter	Sales
East	Q1	120
West	Q1	90
East	Q2	150
West	Q2	110
North	Q1	70
North	Q2	80
East	Q1	30
West	Q2	40

2. Select cell E1. Leave the cells below and to the right empty so the result has room.
3. Type =PIVOTBY(A1:A9,B1:B9,C1:C9,SUM,3) and press Enter.
4. Check the result against the numbers below.

Region	Q1	Q2	Total
East	150	150	300
North	70	80	150
West	90	150	240
Total	310	380	690

To confirm the summary is live, change C2 from 120 to 130. East’s Q1 value becomes 160, East’s total becomes 310, and the grand total becomes 700. Change it back when you’re done.

Understand the arguments

The full syntax is:

=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])

Argument	In the example	What it does
row_fields	A1:A9	Labels to group down the rows (Region)
col_fields	B1:B9	Labels to group across the columns (Quarter)
values	C1:C9	Numbers to calculate (Sales)
function	SUM	How to combine them — type the name without parentheses
field_headers	3	0 = no headers, 1 = headers present but hidden, 2 = no headers but generate them, 3 = headers present and shown

Keep all three ranges the same height. Set field_headers yourself while you learn; it stops Excel from treating a header row as data.

Control totals and sort order

The total arguments accept 0 (no totals), 1 (grand totals), 2 (grand totals and subtotals), and −1 or −2 to put them first. A negative sort number reverses the order.

What you want	Formula
Default layout with totals	=PIVOTBY(A1:A9,B1:B9,C1:C9,SUM,3)
No row or column totals	=PIVOTBY(A1:A9,B1:B9,C1:C9,SUM,3,0,1,0)
Regions in reverse order (West, North, East)	=PIVOTBY(A1:A9,B1:B9,C1:C9,SUM,3,1,-1)
Quarters in reverse order (Q2 before Q1)	=PIVOTBY(A1:A9,B1:B9,C1:C9,SUM,3,1,1,1,-1)

Subtotals only change the result when you group by more than one row or column field.

Average, count, or filter the sales

Swap SUM for another function to answer a different question:

• =PIVOTBY(A1:A9,B1:B9,C1:C9,AVERAGE,3) shows the average sale. East in Q1 becomes 75, because its two sales are 120 and 30.
• =PIVOTBY(A1:A9,B1:B9,C1:C9,COUNT,3) shows how many sales each cell contains. East in Q1 shows 2.

To leave rows out, use filter_array — a TRUE/FALSE test that is the same height as your data. This version skips North. Because the ranges start at row 2, the header argument is 0:

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM,0,1,1,1,1,A2:A9<>"North")

The result shows only East and West, and the grand total drops to 540.

Show percentages with PERCENTOF

Use the PERCENTOF function to show each value as a share of a total. The last argument, relative_to, chooses which total: 0 = column totals (the default), 1 = row totals, 2 = the grand total.

=PIVOTBY(A2:A9,B2:B9,C2:C9,PERCENTOF,0,,,,,,2)

This shows each cell as a share of all sales. East in Q1 is 150 of 690, so it shows about 0.217. Select the result and choose Home > Percent Style to display 21.7%. With relative_to left at 0, East in Q1 shows its share of the Q1 column instead: 150 of 310, or 48.4%.

Let the summary grow with a table

Ranges like A1:A9 don’t expand when you add rows. Convert the data to an Excel table so the summary picks up new sales automatically.

1. Click any cell in the data and press Ctrl + T, then select OK.
2. On the Table Design tab, change Table Name to Sales.
3. In an empty cell outside the table, enter =PIVOTBY(Sales[Region],Sales[Quarter],Sales[Sales],SUM,0).

Table column references don’t include the header row, so the header argument is 0. Keep the formula outside the table — dynamic array formulas can’t spill inside one.

Show two calculations at once

The function argument can hold more than one function. Wrap them in HSTACK, and PIVOTBY shows each one side by side under every column label:

=PIVOTBY(A1:A9,B1:B9,C1:C9,HSTACK(SUM,COUNT),3)

Each quarter now has a SUM column and a COUNT column. East shows 150 and 2 for Q1 (two sales added together), then 150 and 1 for Q2. This is handy when a big total might come from one large sale or from many small ones.

Use your own calculation with LAMBDA

If none of the built-in functions fits, write your own with LAMBDA. The LAMBDA receives the values for each cell of the summary. This formula shows the gap between the largest and smallest sale:

=PIVOTBY(A1:A9,B1:B9,C1:C9,LAMBDA(x,MAX(x)-MIN(x)),3)

East in Q1 shows 90, because its sales are 120 and 30. Cells with only one sale show 0.

Group by more than one field, or by month

row_fields and col_fields can cover more than one column. If your data had Region in column A and Product in column B, using A1:B9 as row_fields would group by region and then by product inside each region. Set row_total_depth to 2 to add a subtotal for each region. For columns that are not next to each other, combine them with HSTACK, such as HSTACK(A1:A9,D1:D9).

You can also group by a calculated value instead of a column. If column A held dates, TEXT(A2:A100,"yyyy-mm") as row_fields would group the sales by month, and YEAR(A2:A100) would group them by year. Use 0 for field_headers when you do this, because the calculated labels have no header row.

Fix an error or choose a PivotTable instead

• #NAME? — Check the spelling. If it’s correct, your version of Excel doesn’t include PIVOTBY yet. Check your Excel version and update Microsoft 365.
• #SPILL! — Something is in the way of the result, or the formula is inside a table. Clear the cells below and to the right, or move the formula. See how to fix a spill error.
• #VALUE! — The ranges are different heights, or the filter test doesn’t line up with the data. Make every range cover the same rows.

Choose PIVOTBY when you want a summary that recalculates with your data and lives in normal cells you can reference. Choose a PivotTable when you want drag-and-drop layout, slicers, or to explore a large data set — just remember a PivotTable needs a refresh after the data changes. Microsoft’s PIVOTBY reference lists every option.

Frequently asked questions

Which versions of Excel have PIVOTBY?

PIVOTBY is in Excel for Microsoft 365 on Windows and Mac, and in Excel for the web. It is not in the one-time purchase versions such as Excel 2021 or Excel 2024, where the formula returns #NAME?.

Can the data be on another sheet?

Yes. Point the ranges at the other sheet, such as =PIVOTBY(Data!A1:A9,Data!B1:B9,Data!C1:C9,SUM,3). The summary still updates when the data changes. A table name such as Sales[Region] works from any sheet too.

Can I format the PIVOTBY result?

Yes. Select the spilled cells and apply number formats, borders, or conditional formatting as usual. The formatting stays on those cells, so if the summary grows, format a few extra rows and columns ahead of time.

How do I turn the result into plain numbers?

Select the spilled cells, press Ctrl + C, then right-click and choose Paste Special > Values. The pasted copy no longer updates, which is useful for a snapshot you want to email or archive.

Related: how to use TRIMRANGE in Excel.

Related: how to do a forecast in Excel.