The easiest way is a PivotTable. It takes about a minute and updates with a click when you add more expenses.
Step 1: Check your data
Make sure your list looks like this:
- One header row at the top (e.g.,
Date, Amount, Category)
- No blank rows or columns in the middle
- Dates are real dates, not text. A quick test: real dates are right-aligned by default, while text is left-aligned.
- Amounts are numbers, not text.
Step 2: Turn your list into a Table (recommended)
- Click any cell in your data.
- Press Ctrl + T (Mac: Cmd + T).
- Make sure "My table has headers" is checked, then click OK.
Why: When you add new expenses later, the PivotTable will include them automatically.
Step 3: Insert the PivotTable
- Click any cell in your table.
- Go to Insert → PivotTable.
- Choose New Worksheet and click OK.
Step 4: Build the summary
In the PivotTable Fields pane on the right, drag:
- Category → Rows
- Date → Columns
- Amount → Values
Step 5: Group the dates by month
Newer versions of Excel often group dates automatically into Years/Quarters/Months. If yours doesn't, or you want to adjust it:
- Right-click any date in the PivotTable's column headers.
- Choose Group…
- Select Months and Years. Include Years so that January 2024 and January 2025 aren't lumped together.
- Click OK.
Step 6: Make sure it's summing
The Values field should say "Sum of Amount." If it says "Count of Amount":
- Click it in the Values area and choose Value Field Settings.
- Select Sum and click OK.
(Seeing "Count" usually means some amounts are stored as text, so you may want to fix those.)
Step 7: Tidy up (optional)
- Format as currency: Right-click a number → Number Format → Currency.
- Sort categories by spending: Right-click a value → Sort → Largest to Smallest.
- Add a chart: PivotTable Analyze → PivotChart, then choose a stacked column chart.
When you add new expenses
Add them to the bottom of your table, then right-click the PivotTable and choose Refresh. You can also use Data → Refresh All.
Alternative: formula approach
If you'd rather have a fixed grid with categories down the side and months across the top, use SUMIFS. With categories in column A, the first day of each month in row 1 (e.g., 1/1/2025), and your data in a table called Table1, put this in B2 and fill it across and down:
=SUMIFS(Table1[Amount], Table1[Category], $A2, Table1[Date], ">="&B$1, Table1[Date], "<="&EOMONTH(B$1,0))
For most people, though, the PivotTable is quicker and more flexible.