Track Expenses by Category in Excel

An expense tracker in Excel is a simple log, one row per expense, plus a summary that totals the log by category and month. SUMIF does the category totals, SUMIFS adds the month, and a pivot table can do both without formulas. This guide builds a tracker for work expenses that you can also use for a household, a project or a small business.

Set up the expense log#

The log is the heart of the tracker. Each expense gets one row, and each detail gets its own column.

Three rules keep the log useful:

• Real dates, not text like "March 3", so you can total by month.
• Numbers only in Amount, with no currency symbols typed in. Format the column as currency instead with Ctrl+Shift+$ (Mac: Control+Shift+$).
• No blank rows, subtotals or notes inside the log.

Turn the log into a table and add a category list#

Click any cell in the log and press Ctrl+T (Mac: ⌘T), check My table has headers and click OK. An Excel table grows by itself when you type in the row below it, so new expenses are included in every formula and pivot that uses the table.

Categories must be spelled the same way every time, because SUMIF matches the text. "Travel" and "Travel " (with a space) are different to Excel. Add a drop-down:

1. Type your categories on a spare sheet, one per cell.
2. Select the Category column of the log.
3. Choose Data > Data Validation, set Allow to List and select your category cells as the Source.

Our guide to creating a drop-down list in Excel covers the details.

Total expenses by category with SUMIF#

SUMIF adds the numbers in rows that match one condition. List the categories in G2:G5 and, in H2, write:

=SUMIF($C$2:$C$500,G2,$E$2:$E$500)

SUMIF checks the Category column, keeps the rows that match the name in G2, and adds their amounts. The ranges are locked with $ and run past the end of the log, so they keep working as the log grows. Fill down to H5.

The share column divides each total by the grand total in H6 (=SUM(H2:H5)), locked with $: =H2/$H$6. If the log is an Excel table named Expenses, the formula can use the column names instead: =SUMIF(Expenses[Category],G2,Expenses[Amount]). See how to use SUMIF in Excel and the SUMIF function guide for more criteria, such as amounts over 100.

Check the totals. =SUM(H2:H5)=SUM(E2:E500) should show TRUE. If it shows FALSE, an expense has a category that isn't on your summary list.

Total expenses by month with SUMIFS#

To split each category by month, add date conditions. Put the first day of each month across the top of the summary: I1 holds 3/1/2025 and J1 holds 4/1/2025. In I2:

=SUMIFS($E$2:$E$500,$C$2:$C$500,$G2,$A$2:$A$500,">="&I$1,$A$2:$A$500,"<"&EDATE(I$1,1))

That reads: add the amounts where the category is G2, the date is on or after the first of the month, and the date is before the first of the next month. EDATE adds one month to the date in I1. Fill the formula across and down.

The mixed references are what make one formula fill the whole grid: $G2 always reads column G, and I$1 always reads row 1. Payment method works the same way: =SUMIF($D$2:$D$500,"Cash",$E$2:$E$500) gives 100.35 paid in cash.

Summarise expenses with a pivot table#

A pivot table builds the same category-by-month summary in a few clicks.

1. Click any cell in the log and choose Insert > PivotTable, then OK.
2. Drag Category to Rows and Amount to Values.
3. Drag Date to Columns. Recent versions of Excel group dates by month automatically. If yours shows single dates, right-click a date in the pivot, choose Group and select Months (and Years if the log spans more than one year).
4. Sort the biggest category to the top: right-click a total and choose Sort > Sort Largest to Smallest.

Click Refresh (on the PivotTable Analyze tab) after you add expenses. Pivot table vs SUMIFS explains when each approach fits better.

Ways to extend the expense tracker#

• Receipt column: a Yes/No drop-down, plus =COUNTIF(F2:F500,"No") to count missing receipts.
• Monthly limit per category: put a limit next to each category and flag the totals that go over with conditional formatting.
• Chart: select the category summary and choose Insert > Recommended Charts for a bar chart of where the money goes.
• Biggest expense: =MAX(E2:E500) finds it; =XLOOKUP(MAX(E2:E500),E2:E500,B2:B500) returns its description.

Tracking and summarising costs like this is a core task in Excel skills for finance jobs, and the same SUMIF summaries show up in Excel skills for sales and operations roles. If you also pay people for their time, the next projects are a sales commission calculator and an overtime hours sheet.

Practise it#

Do the exercise on this page to total one category with a locked SUMIF. Then practise SUMIF in a live spreadsheet, with text, number and cell criteria, and build the pivot version in the first pivot tables lesson.