PIVOTBY builds a pivot-style table from one formula: =PIVOTBY(B2:B500,TEXT(A2:A500,"yyyy-mm"),C2:C500,SUM,0) puts categories down the side, months across the top and the total spent in each cell. It exists only in recent Microsoft 365 builds, so check by typing =PIVOTBY( in a cell. If Excel does not offer it, a SUMIFS grid (below) gives the same table.
How is the PIVOTBY formula written?
The arguments are, in order: the field for rows, the field for columns, the values, the function, and then optional settings. In this sheet, A holds dates, B categories and C amounts.
- Rows: B2:B500, so each category gets a row.
- Columns: TEXT(A2:A500,"yyyy-mm"), which turns each date into a label like 2025-04.
- Values: C2:C500, the amounts.
- Function: SUM, written without brackets. You could use COUNT, AVERAGE or MAX.
- Field headers: 0, because the ranges do not include a header row. Use 1 or 3 if you select the header cells too.
The text labels sort in date order because year comes first, so Apr-25 does not jump ahead of Jan-26. PIVOTBY needs no month helper column, though adding one makes the formula shorter.
What does the output look like?
Take three categories and two months of made-up amounts:
| Category | 2025-04 | 2025-05 | Total |
|---|---|---|---|
| Groceries | 9,200 | 10,100 | 19,300 |
| Fuel | 3,400 | 2,900 | 6,300 |
| Eating out | 2,650 | 3,100 | 5,750 |
| Total | 15,250 | 16,100 | 31,350 |
Row and column totals come by default. To remove them, set row_total_depth and col_total_depth to 0, in positions 6 and 8 of the formula.
PIVOTBY or a classic pivot table?
| Point | PIVOTBY | Pivot table |
|---|---|---|
| Refresh | Updates instantly when data changes | Needs Refresh, unless a macro does it |
| Drag-and-drop | No, you edit the formula | Yes, with a field list |
| Date grouping | You build the label with TEXT | Built-in months, quarters and years |
| Where it works | Recent Microsoft 365 only | Almost every Excel version |
| Formatting | Plain cells you style yourself | Own style options |
A pivot table is better when you want to explore by dragging fields around. PIVOTBY is better when the table must update by itself and sit inside a report with other formulas.
What if my Excel does not have PIVOTBY?
Use a SUMIFS grid. Put categories in A12 downward, month-start dates in B11 across (type 01-04-2025 in B11 and use =EDATE(B11,1) in C11, then fill right), and in B12 write:
- =SUMIFS($C$2:$C$500,$B$2:$B$500,$A12,$A$2:$A$500,">="&B$11,$A$2:$A$500,"<"&EDATE(B$11,1))
Copy it across and down. It is more typing, but it works in every desktop Excel and in Google Sheets with the same logic. Format row 11 as mmm-yy.
Worked check: in the sample above, Groceries 9,200 + 10,100 gives 19,300, and the April column 9,200 + 3,400 + 2,650 gives 15,250. Always add up one row and one column of a result by hand like this, because a table that looks tidy can still be built on text amounts that quietly count as zero.
Why do I get #SPILL! or blank categories?
- #SPILL!: something sits in the cells where the table wants to land. Clear them or move the formula.
- A blank row in your range appears as an empty category. Trim the range to your data, or use an Excel Table such as Txn[Category] so it covers only filled rows.
- Months showing as text codes: that is expected, since TEXT creates labels. Dates in the data must be real dates, not text.
- Wrong totals: check that the amounts are numbers; a figure typed with a comma inside text counts as text.
When should I stop building tables by hand?
If you rebuild this table every month after pasting new statements, the formula is not the problem; cleaning the rows is. SpendixAI turns uploaded statements into categorised rows and has ready-made category and month reports, which you can download as CSV for your own sheet.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

