Introduction

An expense report is only worth keeping if it answers two questions at tax time: how much did I spend, and how much is deductible? Most people who track expenses in a spreadsheet discover in April that their log has no vendor column, no payment method, and no deductible flag — so reconstructing three months of receipts turns into a lost weekend.

The problem is structural, not analytical. You need one transaction log with consistent columns, drop-down lists so “Office Depot” doesn’t appear five different ways, and a summary layer that totals each category automatically. Three decisions cover almost all of it: which categories you use, how you record payment method, and whether each transaction is deductible.

Our free Monthly Expense Report does exactly that: a log sheet for individual transactions, a summary tallying 12 categories with percentages and a tax-deductible total, and a monthly totals sheet with a line chart.

Download it and start logging transactions today:

📥 Download the Monthly Expense Report Template Stop building from scratch. This workbook comes with the expense log, category drop-downs, SUMIFS summary formulas, and both charts already built.

Download Free Template (.xlsx)

Tested by our data analysts at Healora Studio — this template has reconciled a full year of deductible business expenses for freelance tax filings.

How to Build a Monthly Expense Report in Excel (Step-by-Step)

Here’s the manual build, in the order we’d do it. Roughly 40 minutes of work.

Step 1: Plan the Four Sheets Before You Type Anything

Create four sheets and rename them Instructions, Expense Log, Summary, and Monthly Totals (right-click a tab > Rename). Decide this first — the Summary sheet is built from the columns you define on Expense Log.

Pro Tip from Healora Studio Analysts: Turn your log into an Excel Table with Ctrl + T immediately after entering headers. Tables expand automatically when you type in the row below, which means your drop-downs, formats, and formatting carry forward and your formula ranges never need rewriting as the year fills up.

Step 2: Build the `Expense Log` Headers

In row 1, enter these headers starting at A1:

Cell	Header
A1	Date
B1	Category
C1	Vendor
D1	Description
E1	Payment Method
F1	Amount
G1	Tax Deductible

Format column A as a date (Ctrl + 1 > Date) and column F as currency, then freeze the header row with View > Freeze Panes > Freeze Top Row.

[Insert Real Screenshot Here: Expense Log sheet with the seven headers in row 1, date and currency formats applied, and the header row frozen]

Step 3: Create Drop-Down Lists with Data Validation

Drop-downs are what keep a log analyzable — free text produces near-duplicates that SUMIFS silently splits apart. For each column, select the range (start with B2:B1000 so future rows inherit the rule) and go to Data > Data Validation:

Category — under Allow, choose List, then in Source enter: Housing/Rent,Groceries,Utilities,Transportation,Insurance,Dining Out,Entertainment,Subscriptions,Health & Fitness,Personal Care,Travel,Taxes
Payment Method — Cash,Credit Card,Debit Card,Bank Transfer,PayPal,Check
Tax Deductible — Yes,No

On the Error Alert tab keep “Stop” selected so out-of-list entries are rejected. Repeat for E2:E1000 and G2:G1000.

Step 4: Build the `Summary` Sheet with SUMIFS

On Summary, list the same 12 category names in A2:A13. In B2, enter:

=SUMIFS('Expense Log'!$F:$F,'Expense Log'!$B:$B,$A2)

The syntax order matters: SUMIFS takes the sum range first, then criteria range / criteria pairs. Here you sum column F (Amount) where column B (Category) matches the label in A2. The $ signs lock the ranges so the formula drags down cleanly through B13 — one formula replacing twelve manual SUM calls.

Step 5: Add Percentages with an Absolute Reference

In B14, add the grand total:

=SUM(B2:B13)

In C2, calculate each category’s share of total spending:

=B2/$B$14

Format C2:C13 as a percentage. The $B$14 absolute reference is the key detail — it pins the denominator so dragging down doesn’t shift the total cell.

Step 6: Add the Tax-Deductible Total Line

Below the category block, add a labeled line for deductible spending:

=SUMIFS('Expense Log'!$F:$F,'Expense Log'!$G:$G,"Yes")

This gives you the number you actually hand to your accountant, and it’s why the Tax Deductible column is worth the extra two seconds per transaction.

Step 7: Build the Pie Chart

Select A2:B13 — the category labels and their totals — then insert the chart: Insert > Pie Chart > Pie. Add data labels with Chart Design > Add Chart Element > Data Labels > Outside End, then Chart Design > Format > Data Labels and tick the Percentage checkbox.

A pie chart is right for composition at a point in time and wrong for trends — it can’t show change across months, which is what the next sheet handles.

Step 8: Build `Monthly Totals` with a Line Chart

On Monthly Totals, set headers in row 1: Month, Total Expenses, Transactions in A1:C1, then list Jan through Dec in A2:A13.

For each month, sum the matching date range with SUMIFS:

=SUMIFS('Expense Log'!$F:$F,'Expense Log'!$A:$A,">="&DATE(2026,1,1),'Expense Log'!$A:$A,"<="&DATE(2026,1,31))

Change the month and last-day arguments for each row. Then count transactions on the same range with COUNTIFS:

=COUNTIFS('Expense Log'!$A:$A,">="&DATE(2026,1,1),'Expense Log'!$A:$A,"<="&DATE(2026,1,31))

Select A1:B13 and insert the trend chart: Insert > Line Chart > Line with Markers. Now you see both how much you spend each month and how often — a month with double the transactions at the same total is worth investigating.

[Insert Real Screenshot Here: Monthly Totals sheet with the 12-month expense totals, transaction counts, and a line chart with markers]

What’s Included in the Downloadable Template

Everything above, already assembled:

✅ Four pre-built sheets — Instructions, Expense Log, Summary, Monthly Totals ✅ Seven-column expense log — Date, Category, Vendor, Description, Payment Method, Amount, Tax Deductible ✅ Drop-down lists for Category, Payment Method, and Tax Deductible — no free-text errors ✅ SUMIFS summary totaling all 12 categories automatically ✅ % of total for every category, with a grand total row ✅ Tax-deductible total line — ready for your accountant or your tax software ✅ Category pie chart on the Summary sheet ✅ 12-month totals with transaction counts and a line chart on Monthly Totals

When to Upgrade From Excel

Spreadsheets excel at structure and struggle with automation. Past 30–40 transactions a month, manual entry becomes the bottleneck.

FreshBooks

FreshBooks captures expenses, snapshots receipts, and links spending directly to client invoices. It’s the natural first step up from a manual log because it keeps the category structure you already built. – Pricing: Starts at $19/month – Best for: freelancers and service businesses who bill clients for reimbursable costs – [Affiliate Link: Try FreshBooks free for 30 days]

QuickBooks Online

QuickBooks handles the full accounting layer — bank feeds, reconciliation, sales tax, and a chart of accounts that maps into your Schedule C at tax time. – Pricing: Starts at $35/month – Best for: small businesses that need real books, not just an expense log – [Affiliate Link: Get 50% off QuickBooks for 3 months]

Expensify

Expensify is built for receipt capture: photograph a receipt and it extracts the merchant, date, and amount, then codes it to a category. – Pricing: Free for individuals; paid plans from $5/user/month – Best for: anyone drowning in paper receipts – [Affiliate Link: Start free with Expensify]

Our recommendation: keep the Excel template until your log passes about 500 rows a year. Below that, a spreadsheet beats setting up an accounting platform.

Frequently Asked Questions

Why does my SUMIFS return 0 even though the log has matching transactions?

Almost always a text mismatch, not a formula error. Check for trailing spaces or a category typed manually instead of picked from the drop-down. SUMIFS compares text exactly, so “Utilities” and “Utilities ” are different categories.

Can I use this as a business expense tracker instead of a personal one?

Yes — it’s the more common use case. Adjust the 12 categories to match your chart of accounts and the Tax Deductible column replaces a separate year-end deduction list.

How do I add a 13th category later?

Add the category to the end of the Data Validation source list, add a row to the Summary sheet, and drag the SUMIFS formula down into it — the new row picks up automatically.

Does it work in Google Sheets?

Yes. Import via File > Import > Upload. Data validation converts to Sheets’ native drop-downs and SUMIFS behaves identically; both charts are preserved.

Conclusion

Expense tracking fails when the log is inconsistent and the summary is manual. Fix those two things and a monthly expense report becomes a two-minute job instead of a two-hour one.

📥 Download the Monthly Expense Report Template Load it up, log your first transaction, and let the formulas do the totaling.

Download Free Template (.xlsx)

Related free templates from SheetMaster Hub: – [Personal Budget Tracker Template — for planning against actual spending] – [Annual Budget Planner Template — for 12-month planning across categories]

This tutorial and template are part of SheetMaster Hub by Healora Studio. For more data efficiency tools, visit healorastudio.com.

Monthly Expense Report | Free Excel & Google Sheets Template [2026]