Expense tracker in Excel: record income and expenses month by month

In short: one row per transaction with date, type (Income or Expense), category and amount. A summary sheet adds up each month with SUMIFS, subtracts expenses from income and keeps a running surplus for the year. The free template below does all of that, with net and tax columns for anyone who charges sales tax or VAT.

Before you start: SUMIFS, drop-down lists and charts. EOMONTH is explained in the exercise.

Try it now

The first thing most people want from an expenses spreadsheet is a picture of where the money goes. Here are a year's expenses by category.

Task Show in a chart what share of the year's spending each category takes.

	A	B	C
1	Category	Spending for the year	Number of receipts
2	Rent	5400	12
3	Travel	1850	9
4	Office and software	960	24
5	Phone and internet	720	12
6	Training	430	3
7	Other expenses	610	15

Expense tracker or budget template?

The two look alike but do different jobs. A budget template starts with a plan: how much you want to spend on rent, food or travel each month, and how far off you are. An expense tracker starts with facts: every receipt, invoice and bank payment that really happened, in the order it happened. Many people use both, the tracker for the record and the budget for the plan.

This tracker fits freelancers, side businesses, small clubs and rental income, anywhere you want to know the surplus each month. It also works for a household that only needs a clean list of spending, without a budget per category.

Download the expense tracker template

The file has room for 300 entries, drop-downs for type, category and tax rate, net and tax by formula, a monthly summary with surplus year to date, totals per category, ten sample rows and an instructions sheet.

How the income and expense sheet is laid out

The Entries sheet is an Excel table with filter buttons. Headers sit in row 4, yellow cells are inputs and the last two columns calculate themselves.

Column	Contents	Input or formula in row 5
A	Date	Shown as d mmm yyyy, so 5 Jan 2027 reads the same in the US and the UK
B	Ref no.	Your own number, such as R-001
C	Type	Drop-down: Income or Expense
D	Category	Drop-down: Sales and fees, Rent, Travel, Office and software and more
E	Description	Short text, such as Invoice 2027-001
F	Gross	The amount you paid or received
G	Tax rate	Drop-down, 0% if you do not deal with sales tax or VAT
H	Net	=IF(F5="","",ROUND(F5/(1+G5),2))
I	Tax	=IF(F5="","",F5-H5)

Type gets its own column instead of two amount columns. A filter on Expense then shows every cost at once, and every summary formula needs just one more condition. The drop-downs read their entries from the Lists sheet, so a category is always spelled the same way. How that works is shown in the lesson on drop-down lists.

Monthly expenses spreadsheet with SUMIFS

The key number is the surplus per month: all income in the month minus all expenses in the month. SUMIFS handles it with three conditions: the type, a date from the first of the month and a date up to the last day. EOMONTH(G1,0) returns that last day, and a date condition is text joined to a cell: ">="&G1.

Task In G2, calculate the surplus for the month in G1: all income in that month minus all expenses in that month.

	A	B	C	D	E	F	G
1	Date	Type	Amount			Month	1/1/2027
2	12/28/2026	Income	800			Surplus
3	1/5/2027	Income	1190
4	1/8/2027	Expense	11.9
5	1/12/2027	Expense	89.9
6	1/15/2027	Expense	450
7	1/20/2027	Income	2380
8	1/26/2027	Expense	29.75
9	2/9/2027	Income	952
10	2/15/2027	Expense	450

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

In the template the Months sheet splits the work into two columns, which is easier to read and to check. Column B collects the income, column C the expenses, both with the table's column names:

=SUMIFS(Entries[Gross],Entries[Type],"Income",Entries[Date],">="&$A5,Entries[Date],"<="&EOMONTH($A5,0))

Column D is then simply =B5-C5, and column E keeps a running total with =SUM(D$5:D5). The months in column A come from the year in B2: =DATE($B$2,1,1) and below it =EDATE(A5,1). Change the year and the whole summary recalculates. Below the months, a second block adds up each category for the year, which is the data for a chart like the one in the first exercise. More chart types are in the lesson on Excel charts.

Sales tax and VAT: gross, net and tax

Receipts show the gross amount. If you are registered for VAT in the UK, you also need the net amount and the VAT in it. The net amount is the gross divided by (1 plus the rate), and ROUND to two places keeps net and tax adding up to the gross exactly.

Task In D2, calculate the net amount from the gross amount and the tax rate, rounded to 2 decimal places. Each receipt has its own rate in column C, here the UK VAT rates. The formula is filled down to D7.

	A	B	C	D
1	Receipt	Gross	Tax rate	Net
2	Invoice 2027-001	1200	0.2
3	Office subscription	12	0.2
4	Train ticket	89.9	0
5	Office rent	450	0
6	Reference book	39.9	0
7	Online ad	120	0.2

The rates on the Lists sheet (20%, 5% and 0%) are examples taken from UK VAT; change them to what applies to you. The US has no VAT, and sales tax is part of what you pay at the till, so most US users simply pick 0% and track the gross amounts. With 0%, net equals gross and the tax column stays at zero.

Income spreadsheet example: what goes where

The ten sample rows show a typical first two months of a freelancer: three invoices as income, then rent for a shared office, a software subscription, a train ticket to a client, a phone bill, a reference book and an online ad. Use them as a pattern, then select A5:G14 and press Delete; the formulas in H and I stay.

• Regular costs such as rent or subscriptions get one row per payment, dated on the day they leave your account.
• Car trips for work belong in a mileage log; enter the total claim here as one expense.
• Refunds go in as income in the same category, so the category total shrinks.

Which records you need for your taxes, and how long to keep them, depends on where you live and what you do. The template gives you an overview; check with an accountant if in doubt. It is not tax advice.

Common mistakes

Type spelled differently

"Expenses" with an s does not match the criteria "Expense" in SUMIFS, and the row drops out of the summary. The drop-down prevents it.

Tax rate typed as 20 instead of 20%

The formula would divide by 21. Pick the rate from the list or type it with the percent sign.

Entries counted twice

If you work through bank statements and receipts separately, some payments end up in the list twice. The reference number helps: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values shows repeats at once.

Expense tracker formulas in German Excel

English	German
SUMIFS	SUMMEWENNS
ROUND	RUNDEN
EOMONTH, EDATE, DATE	MONATSENDE, EDATUM, DATUM

Which Excel versions support this?

Every function on this page, drop-down lists, Excel tables and pie charts work in all current versions of Excel, including Excel for Mac and Excel for the web.

FAQ

For a ledger by account with a control total, the next step is the page on Excel for accounting.