- Introduction
- How to Build a Weekly Income & Expense Tracker in Excel (Step-by-Step)
- Step 1: Create the Weekly Log Sheet
- Step 2: Tag Every Transaction With a Week-Start Date
- Step 3: Build the Type and Category Drop-Downs
- Step 4: Generate the 52-Week Summary Table
- Step 5: Pull Income and Expenses With SUMIFS (Two Criteria)
- Step 6: Calculate Net Profit and the Running Total
- Step 7: Apply Conditional Formatting to Net Profit
- Step 8: Build the Weekly Trend Line Chart
- Template Features
- When to Upgrade From Excel
- Frequently Asked Questions
- Log This Week While You Still Remember It
Introduction
Monthly reporting has one structural flaw: by the time you see the number, the decisions it should have informed are already made. If marketing overspent in week two, a month-end summary tells you in week five — after the budget is gone.
“Weekly” is awkward to build in Excel, which is why most people give up on it. Week numbers reset every January and can’t be sorted or charted cleanly across a year boundary.
The Weekly Income & Expense Tracker sidesteps that by tagging each transaction with a week-start date instead, so SUMIFS handles the grouping. It’s a three-sheet workbook — Instructions, Weekly Log, and Weekly Summary — where the summary holds all 52 weeks, pulls Income and Expenses from the log, calculates Net Profit, and carries a year-to-date running total. Net Profit is conditionally formatted, and a weekly trend line chart plots the year at a glance.
Download it free above, or build it with the tutorial below in 30 minutes.
📥 Download the Weekly Income & Expense Tracker Template Stop building from scratch. Get the pre-formatted workbook with the transaction log, drop-down categories, 52 SUMIFS-driven weekly rows, conditional formatting, and the trend chart built. Save 30+ minutes of setup.
Tested by our data analysts at Healora Studio — we run this workbook on live freelance income and business expenses, and it’s the fastest way to catch a category drifting before it becomes a monthly problem.
How to Build a Weekly Income & Expense Tracker in Excel (Step-by-Step)
Step 1: Create the Weekly Log Sheet
Rename Sheet1 to Weekly Log and add these headers in row 7. Data starts in row 8.
| Col | Header |
|---|---|
| A | Date |
| B | Week Start (Monday of that week) |
| C | Type (Income or Expense) |
| D | Category (drop-down) |
| E | Description |
| F | Amount (always positive) |
Keep Amount positive in both directions — the Type column decides the sign. Mixing positive income with negative expenses makes every SUMIFS formula harder to debug.
Step 2: Tag Every Transaction With a Week-Start Date
In B8, enter:
=A8-WEEKDAY(A8,2)+1
WEEKDAY(A8,2) returns 1 for Monday through 7 for Sunday, so subtracting it from any date and adding 1 lands on that week’s Monday. Fill B8 down, then format B8:B1000 as a date with Ctrl + 1.
[Insert Real Screenshot Here: the Weekly Log sheet with column B filled by the WEEKDAY formula, showing one Monday date per transaction date in column A]
Pro Tip from Healora Studio Analysts: Paste transactions in one block per week, then fill the Week Start column with a single formula instead of typing forty dates. Batch entry is what makes the habit survive.
Step 3: Build the Type and Category Drop-Downs
- Select
C8:C1000, openData > Data Validation, choose List, set Source to:Income,Expense - Select
D8:D1000, repeat, and set Source to:Client Payment,Product Sales,Other Income,Payroll,Rent,Software,Marketing,Travel,Supplies,Taxes,Other Expense - Click OK and test both
Eleven categories is deliberate: granular enough to act on, small enough that you’ll still use them.
Step 4: Generate the 52-Week Summary Table
Add a sheet (Shift + F11) named Weekly Summary. In row 4, add five headers: Week Start, Income, Expenses, Net Profit, Running Total.
Type your first Monday in A5, enter =A5+7 in A6, and fill down to A56 — a full year of weeks with no manual date entry.
Step 5: Pull Income and Expenses With SUMIFS (Two Criteria)
In B5:
=SUMIFS('Weekly Log'!$F$8:$F$1000,'Weekly Log'!$B$8:$B$1000,$A5,'Weekly Log'!$C$8:$C$1000,"Income")
Three instructions in one function: sum the Amount column, but only where Week Start equals A5, and only where Type equals Income. In C5, the same formula with the second criterion swapped to "Expense". Fill both down through row 56.
Step 6: Calculate Net Profit and the Running Total
In D5:
=B5-C5
For the running total, enter =D5 in E5 (first week only), then this cumulative pattern in E6:
=E5+D6
Each row adds this week’s net profit to everything above it. Fill to E56, then add a total row with =SUM(B5:B56) in B57:D57.
Pro Tip from Healora Studio Analysts: Extend ranges to $1000 rather than your last row of data. There’s no performance penalty at this scale, and next month’s transactions are picked up automatically.
Step 7: Apply Conditional Formatting to Net Profit
Select D5:D56, then Home > Conditional Formatting > New Rule > Format only cells that contain:
- Cell value less than 0 → light red fill, dark red text
- Cell value greater than 0 → light green fill
Net Profit becomes a heat map — scrolling it shows exactly which weeks went wrong, with no arithmetic.
[Insert Real Screenshot Here: the Weekly Summary sheet with Net Profit formatted red and green, and the Running Total column filled to row 56]
Step 8: Build the Weekly Trend Line Chart
- Select
A4:A56, holdCtrl, and also selectD4:D56 - Go to
Insert > Charts > Lineand choose Line with Markers - Right-click > Select Data and confirm the axis shows Week Start dates
- Right-click > Select Data > Add and set the second series to
E5:E56so the running total plots too
Then log two real weeks and verify: a Sunday transaction lands in the correct week, and the latest Running Total equals the sum of the Net Profit column.
Pro Tip from Healora Studio Analysts: Set the vertical axis minimum to a negative value rather than letting Excel auto-scale from zero. Losing weeks then dip visibly below the axis line.
Template Features
✅ Three pre-built sheets — Instructions, Weekly Log, and Weekly Summary
✅ Week Start auto-tagging — every transaction maps to its week’s Monday via WEEKDAY
✅ Type drop-down — Income or Expense, no ambiguous entries
✅ 11-category drop-down — Client Payment, Product Sales, Other Income, Payroll, Rent, Software, Marketing, Travel, Supplies, Taxes, Other Expense
✅ 52 pre-built weekly rows — a full year of Week Start dates
✅ SUMIFS-driven Income and Expenses — two-criteria formulas that update as you log
✅ Conditional formatting — profitable weeks green, losing weeks red
✅ Running Total column — year-to-date profit, updated weekly
✅ Weekly trend line chart — income, expenses, and cumulative profit by week
✅ Compatible with: Excel 2016+, Microsoft 365, Google Sheets, Windows and macOS
✅ 100% unlocked — edit categories, add columns, restyle freely
When to Upgrade From Excel
For a solo operator under about 200 transactions a month, this workbook is faster to set up and easier to read than any dashboard. Two things push people off it: volume, and tax-ready reporting.
QuickBooks Online — Best for Tax-Ready Books
When your tracker starts feeding a Schedule C or sales tax return, QuickBooks gives you double-entry books, bank feeds, and accountant-ready reports. – Pricing: Starts around $30/month for Simple Start, with frequent promotional discounts – [Affiliate Link: Get started with QuickBooks Online]
FreshBooks — Best for Freelancers
FreshBooks automates the income side: send an invoice, and the payment appears categorized without you logging a row — most of the weekly entry work for a freelancer. – Pricing: Starts around $17–19/month – [Affiliate Link: Try FreshBooks free for 30 days]
Xero — Best for Bank Reconciliation
Xero’s bank feed matches transactions automatically and reports the same weekly trend without formula maintenance. – Pricing: Starts around $15–20/month for the entry tier – [Affiliate Link: Start a free Xero trial]
Our recommendation: Stay on the spreadsheet while a weekly review takes under ten minutes. Export your Weekly Log as CSV before migrating — all of these platforms accept a clean CSV import.
Frequently Asked Questions
Why tag a week-start date instead of using week numbers?
A week-start date is a real date, so it sorts, groups with SUMIFS, and plots on a chart axis. Week numbers do none of that reliably, and they reset every January.
What if my week starts on Sunday?
Change the log formula to =A8-WEEKDAY(A8,1)+1, then rebuild the 52 dates starting from your first Sunday.
My SUMIFS returns 0 even though I have transactions that week. Why?
Usually a date mismatch — the summary holds a real date while the log’s Week Start column was pasted as text. Check with =ISNUMBER(B8), which should return TRUE. Trailing spaces in the Type or Category column are the next most common cause.
Can I track more than one year in the same file?
Yes, but give each year its own log and summary sheets rather than extending one table. The running total restarts at zero each year by design.
Log This Week While You Still Remember It
Weekly reporting fails for one reason: it gets skipped, and then it becomes monthly reporting with extra steps. Five minutes on a Friday is the entire commitment.
📥 Download the Weekly Income & Expense Tracker Template Pre-formatted workbook with the weekly log, drop-down categories, 52-week SUMIFS summary, and trend chart — ready in seconds.
This tutorial and template are part of SheetMaster Hub by Healora Studio. For more data efficiency tools, visit healorastudio.com.