# Summarize a spreadsheet by category and month, with totals

> Markdown version of https://stella.sh/how/pdf-excel/summarize-spreadsheet-by-category for agents and LLMs.
> Total spending per category per month with a PivotTable, after fixing the categories and text amounts that make it wrong, or let Stella (stella.sh) build the summary.

A PivotTable does it: Insert, PivotTable, Category in Rows, Date in Columns grouped by month, Amount in Values as Sum. It goes wrong on messy data: “groceries” and “Groceries ” become separate rows, and amounts typed as “£12.50” are silently left out. Clean those first. Stella (stella.sh) merges the category spellings, counts text amounts, and saves a summary with totals per category, per month and for the year.

- Checked: 13 November 2026
- Stella does it on: Windows, Mac, Linux

## Stella doing it

Recorded 13 November 2026 on Linux (Omarchy) with Claude Code with Opus, on a Claude plan.
Result: A summary in about 3 minutes. Checked afterwards against an answer key written before the run: all 80 category-by-month totals correct to the penny, exactly the 8 real categories, the year's total (£21,695.61) and a total per category shown, and the original data still there. The household and its spending are fictitious, made for this run. The work is sped up 11×.

Video (47 s): https://stella.sh/media/proofs/pdf-excel/summarize-spreadsheet-by-category/proof.mp4

Transcript:

- 0:00 Stella on a Linux computer. A year of household spending is in a spreadsheet in Documents.
- 0:01 The prompt: how much I spent in each category each month, with totals.
- 0:14 Stella replies that it will make the summary and leave the original alone.
- 0:20 Cleaning categories and adding up, sped up 11×.
- 0:32 Done: £21,695.61 so far this year, 22 category spellings merged into 8, text amounts counted, and two bills to check.

What Stella does:

1. Finds the spending spreadsheet in Documents: 314 rows from January to 28 October
2. Merges 22 spellings of categories (capitals, stray spaces) into the 8 real ones
3. Counts 8 amounts typed as text with a £ sign, which a PivotTable would skip, and takes refunds off the month and category they're in
4. Saves “Spending summary 2026.xlsx” next to the original with a category-by-month grid, category totals, month totals and the year total
5. Sums it up in plain words (biggest categories, dearest month) and flags a possible double broadband charge and some very low bills

## The prompt to paste

```text
My spending spreadsheet for this year is in Documents. Make me a summary of how much I spent in each category in each month, with totals.
```

## Build it yourself

1. **Clean the categories.** Add a column with `=PROPER(TRIM(C2))` and fill down: it removes stray spaces and fixes capitals, so “groceries” and “Groceries ” become one. Use this column instead of the original in the PivotTable.
2. **Make every amount a number.** Amounts typed with a £ sign or as text are left-aligned. Add `=VALUE(SUBSTITUTE(D2,"£",""))` and fill down, or select the column and use Data, Text to Columns, Finish. Check the column's SUM matches what you expect.
3. **Insert the PivotTable.** Select the data, Insert, PivotTable, New Worksheet. Drag the clean category to Rows, Date to Columns, the clean amount to Values (it should say Sum of). Excel groups dates by month; if not, right-click a date, Group, Months.
4. **Add totals.** PivotTables show row and column grand totals by default (Design, Grand Totals). Format the values as currency with Ctrl+1.
5. **Without a PivotTable.** Type the categories down the side and months across the top, then use `=SUMIFS(Amount, Category, $A2, Date, ">="&B$1, Date, "<"&EDATE(B$1,1))` with the first of each month in row 1.

## Where Stella stops

- Stella summarises the categories in your sheet. It can suggest new ones but won't invent them.
- Refunds reduce the category in the month they happened, so a month can show a negative total (Fun was −£32 in October here).
- The spending data goes to the AI model you use in Stella. The summary is built on your computer.
- Recorded on Linux with a free Python library; the summary is an ordinary .xlsx for Excel, Numbers or Google Sheets.

## Questions

**Why does my PivotTable show the same category twice?**
The two aren't identical: a trailing space or different capitals. Clean the column with TRIM and PROPER first.

**Why is my PivotTable total too low?**
Some amounts are text, which Sum skips. Convert them to numbers. Stella counted 8 such amounts here.

**Can I get a chart too?**
Yes: in Excel, select the PivotTable and Insert, PivotChart. Or ask Stella for a chart sheet in the same file.

**My data is still in bank PDFs. Where do I start?**
Get it into a sheet first: [bank statement PDFs to Excel](https://stella.sh/how/pdf-excel/bank-statement-pdf-to-excel) or [receipts to a spreadsheet](https://stella.sh/how/pdf-excel/receipts-to-spreadsheet). Then [find your subscriptions](https://stella.sh/how/pdf-excel/find-subscriptions-in-bank-statement).

**Is Stella free for this?**
Yes. Free with the ChatGPT or Claude plan you already have. Sign in with your ChatGPT plan, or use your Claude plan through Claude Code, and this task costs nothing extra. New accounts also get a small free trial of Stella’s own models.

**Can I control what Stella changes on my computer?**
Yes. Stella works on Windows, Mac and Linux with your own files and shows each step in the chat, so you can watch it and stop it at any point. Tell it what it may not do, as the prompts on these pages do (“don't delete anything without asking”). In the Downloads recording it deleted nothing until it was told to go ahead. It also refuses commands that would wipe a whole disk, your home folder or system folders.

Sources: [Microsoft: Create a PivotTable to analyze worksheet data](https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576), [Microsoft: SUMIFS function](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b).

## Related problems

- [Find every subscription and regular payment in your bank statements](https://stella.sh/how/pdf-excel/find-subscriptions-in-bank-statement.md)
- [Convert bank statement PDFs to Excel](https://stella.sh/how/pdf-excel/bank-statement-pdf-to-excel.md)
- [Split bills with roommates and settle up in the fewest payments](https://stella.sh/how/pdf-excel/split-bills-with-roommates.md)
- [Reduce PDF file size when it's too large to email](https://stella.sh/how/pdf-excel/compress-pdf-too-large-to-email.md)
- [Combine multiple Excel files into one sheet](https://stella.sh/how/pdf-excel/combine-excel-files-into-one-sheet.md)
- [Turn receipts into a spreadsheet for your expense report](https://stella.sh/how/pdf-excel/receipts-to-spreadsheet.md)

Get Stella: https://stella.sh/download. Free with the ChatGPT or Claude plan you already have.
