GROUPBY lets you summarise expenses by category in a single formula that recalculates by itself. For a log with categories in column B and amounts in column C, =GROUPBY($B$1:$B$300,$C$1:$C$300,SUM,3,1,-2) gives each category with its total, biggest first, and a grand total at the bottom. It is available in current Microsoft 365 builds of Excel; older versions, including Excel 2021, show #NAME? instead.
What does the GROUPBY formula look like?
The full syntax is =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship]). Only the first three arguments are required. Here is what the ones you will use do.
- row_fields: the column you want to group by, such as the Category column.
- values: the numbers to add up, such as the Amount column.
- function: SUM, AVERAGE, COUNT, MAX and so on, written without brackets. SUM is what you want for spending.
- field_headers: 3 means your ranges include a header row and you want it shown. 0 means no headers.
- total_depth: 1 adds a grand total at the bottom, 0 adds none, -1 puts the grand total at the top.
- sort_order: a column number from the result, negative for descending. -2 sorts by the second column, the totals, largest first.
How do I build a category summary step by step?
- Keep your expense log in columns A to D: Date, Category, Amount and Payment mode, with headers in row 1. Here the category sits in B and the amount in C.
- In an empty cell on another sheet, type =GROUPBY(Log!$B$1:$B$300,Log!$C$1:$C$300,SUM,3,1,-2) and press Enter.
- The result spills down: a Category and Sum header row, one row per category, and a Total row.
- Format the totals column with the comma style; if your Windows region is set to India, large figures then show lakh grouping such as 1,24,000.
- Add a few new expenses inside the range and watch the summary change without refreshing anything.
Say your log has Rent 18,000, Groceries 6,500, Groceries 5,200 and Travel 3,300. The formula returns Rent 18,000, Groceries 11,700, Travel 3,300 and a Total of 33,000. Sorting with -2 puts Rent first because it is the largest.
How is it different from a pivot table?
| Point | GROUPBY | Pivot table |
|---|---|---|
| Refresh | Updates as the data changes | Needs Refresh or refresh on open |
| Setting it up | Typed as a formula | Drag fields in a pane |
| Date grouping and slicers | Not built in | Both available |
| Layout control | Fixed by arguments | Many formatting options |
| Where it works | Current Microsoft 365 | Every modern Excel version |
The honest trade is control against convenience. A pivot table is better for exploring and for slicers. GROUPBY is better when you want a summary that always matches the data and sits quietly on a dashboard sheet. Excel also has a sibling called PIVOTBY, which adds a column field so you can put months across the top.
Why does my GROUPBY not work or look wrong?
- A #NAME? error means your Excel does not have the function yet. Use a pivot table or SUMIFS instead.
- A #SPILL! error means cells below or beside the formula are not empty. Clear them or move the formula.
- Totals look too small if some amounts are stored as text. Select the column and check that numbers right-align.
- A category appears twice if one has a trailing space or different spelling, such as Grocery and Groceries. The function treats them as separate groups.
- Fixed ranges like row 300 miss new rows. If your log is an Excel Table, use the table column names so the range grows.
What should I do next?
Start with a category total only, then add months later with PIVOTBY or a pivot table. If your log comes from many card and bank statements, the cleaning takes longer than the summary does, and SpendixAI can take over that step by importing statements and categorising rows for you to review. Before relying on any summary, compare its total with the sum of the raw Amount column; they must match to the rupee.
- Confirm GROUPBY exists in your Excel by typing =GROUP and checking the suggestions.
- Use consistent category names, ideally from a drop-down list.
- Check that the Total row equals =SUM of the Amount column.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


