Excel for Freelancers: Invoices, Time Tracking, and Taxes in One Workbook
Most freelancers run their business across four disconnected places: a notes app for hours, a Word template for invoices, a bank statement for income, and a panic attack every quarter for taxes. The fix isn’t accounting software — it’s one Excel workbook with five sheets that feed each other. This guide builds that system from scratch: a transaction log everything else reads from, a time tracker that does the math for you, an invoice sheet that pulls its own line items, and a tax estimate that updates itself every time you log a payment.
The Five-Sheet Architecture
The whole system rests on one rule: data lives in exactly one place, and every other sheet calculates from it. You never retype a number you’ve already entered.
| Sheet | Purpose | Feeds into |
|---|---|---|
Log | Every dollar in or out, one row per transaction | Taxes, Dashboard |
Time | Every work session, one row per session | Invoice |
Invoice | Printable invoice that pulls from Time | Log (when paid) |
Taxes | Quarterly estimate calculated from Log | — |
Dashboard | Monthly summary of everything | — |
Create the five sheet tabs first, then build them in the order below — the Log is the foundation.
Sheet 1: The Income and Expense Log
Set up six columns: Date, Type, Client, Category, Description, Amount. Then convert the range to a proper Excel Table (Ctrl+T) — this matters more than it looks, because Tables auto-expand formulas and give you structured references like Log[Amount] that never break when you add rows. If Tables are new to you, create a table covers the two-minute setup.
Name the table Log (Table Design tab, far left). Then enforce consistency in the Type column with a drop-down list containing exactly two values: Income and Expense. Do the same for Category — something like Client Work, Software, Hardware, Travel, Fees. The dropdowns aren’t cosmetic: every downstream formula filters on these exact strings, and one stray “income ” with a trailing space silently breaks your tax estimate.
Enter expenses as positive numbers and let the Type column carry the sign logic. It keeps data entry mindless, and the formulas handle the rest:
=SUMIFS(Log[Amount], Log[Type], "Income")
=SUMIFS(Log[Amount], Log[Type], "Expense")That multi-criteria pattern is the workhorse of this entire workbook — if it’s unfamiliar, SUMIFS is worth ten minutes before you continue. Finally, freeze the top row (View > Freeze Panes > Freeze Top Row) so headers stay visible once the log grows past one screen.
Sheet 2: Time Tracking with Date Math
Columns: Date, Client, Project, Start, End, Hours, Rate, Amount, Invoiced. Convert to a Table named Time.
Enter Start and End as times (9:15 AM, 1:40 PM — or use Ctrl+Shift+; to stamp the current time). Excel stores times as fractions of a day, so subtracting them gives you a fraction, not hours. The Hours formula multiplies by 24 to fix that:
=([@End]-[@Start])*24If the result shows as a time like 4:25 instead of 4.42, the cell inherited a time format — converting time to a decimal explains the format fix. For sessions that cross midnight, wrap the subtraction in MOD so the negative result rolls over correctly:
=MOD([@End]-[@Start],1)*24Amount is simply =[@Hours]*[@Rate], and the Rate column means you can bill different clients (or projects) at different rates without any extra machinery. The Invoiced column starts blank and gets an invoice number when you bill the row — that’s the flag the invoice sheet reads. For deeper variations like lunch breaks or weekly totals, calculating hours worked covers the patterns.
Sheet 3: The Invoice Sheet
This is the one sheet designed for printing, so layout matters: your name and details at the top left, three input cells you change per invoice — Invoice # (B2), Client (B3, reuse the same dropdown as the Time sheet), and Date (B4).
The line items pull themselves. In Excel 365/2021, one FILTER formula grabs every unbilled session for the selected client:
=FILTER(Time[[Date]:[Amount]], (Time[Client]=B3)*(Time[Invoiced]=""), "Nothing to bill")The (condition1)*(condition2) multiplication is how FILTER does AND logic. Below the spill range, total it with =SUM(...) over a generous fixed range, add a payment-terms line (=B4+30 gives a Net-30 due date — date math this simple needs no special function), and you’re done. For the formatting side — logo, borders, a layout that survives the printer — the dedicated create an invoice in Excel walkthrough goes deep, so steal its design and keep this sheet’s formulas.
The workflow per invoice: set the three input cells, type the invoice number into the Invoiced column of each Time row you just billed (which removes them from next month’s FILTER), then export the sheet to PDF and send it. When the client pays, add one Income row to the Log. That single row is what updates your taxes and dashboard — the loop closes.
Sheet 4: The Quarterly Tax Estimate
Placeholder rates only — your actual rate depends on where you live and how you file, so confirm the numbers with a tax professional. What Excel contributes is the structure: profit per quarter, times a rate you can change in one cell.
Put your estimated combined rate in B1 (say 30% as a starting placeholder) and name the cell TaxRate. Then build a small grid:
| Quarter | Start | End | Income | Expenses | Profit | Est. Tax |
|---|---|---|---|---|---|---|
| Q1 | 1/1/2026 | 3/31/2026 | … | … | … | … |
| Q2 | 4/1/2026 | 6/30/2026 | … | … | … | … |
With start dates in column B, the End column writes itself: =EOMONTH(B2,2) jumps to the last day of the month two months later — the EOMONTH function is the cleanest way to get quarter boundaries without hardcoding the 28/30/31 mess. Income for the quarter:
=SUMIFS(Log[Amount], Log[Type], "Income", Log[Date], ">="&B2, Log[Date], "<="&C2)Duplicate it with "Expense" for column E, then Profit is =D2-E2 and Est. Tax is =F2*TaxRate. Four rows, and your quarterly set-aside number is always current. The practical habit that makes this work: move that estimated amount to a separate savings account every time you log a payment, not once a quarter.
Sheet 5: The Monthly Dashboard
List the twelve month-start dates down column A (1/1/2026, 2/1/2026, …), then three formula columns — Income, Expenses, Profit:
=SUMIFS(Log[Amount], Log[Type], "Income", Log[Date], ">="&$A2, Log[Date], "<="&EOMONTH($A2,0))Same EOMONTH trick, zero hardcoded dates. Add a Billable Hours column from the Time table with =SUMIFS(Time[Hours], Time[Date], ">="&$A2, Time[Date], "<="&EOMONTH($A2,0)), and an effective hourly rate (=Income/Hours) — for most freelancers that last number is the most honest metric in the workbook, because it exposes the underquoted projects.
Two finishing touches: conditional formatting on the Profit column (red below zero, or data bars for an instant sparkline effect), and a line chart of monthly profit. If you later want to slice by client or category instead of just by month, that’s the point to reach for a pivot table on the Log — it answers “which client actually makes me money?” in three drags. For chart-heavy layouts with slicers, building dashboards covers the design side.
Keeping the System Honest
A formulas-and-data workbook degrades one accidental keystroke at a time, so finish with guardrails. Protect the sheets that are pure formulas (Review > Protect Sheet) — Taxes and Dashboard — after unlocking only the input cells (like TaxRate) you still need to touch. Keep the workbook in a synced folder so a dead laptop doesn’t take your invoice history with it. And in January, copy the file, rename it for the new year, and clear the Log and Time tables — twelve months of rows is fast; sixty months of rows in one file is when things get sluggish and tax-time archaeology gets painful.
The whole build is maybe ninety minutes. The payoff is that “doing your books” stops being a task: logging a payment takes ten seconds, and the invoice, the tax estimate, and the dashboard all update themselves from that one row.
FAQ
I don’t have Excel 365, so FILTER doesn’t work. What’s the alternative?
Skip the auto-pulled line items and type them on the invoice manually, copying from the Time sheet filtered by client (the Table’s built-in column filters handle that). Everything else in this workbook — SUMIFS, EOMONTH, MOD, the Tables — works in Excel 2010 and later.
Should I track time in decimal hours or start/end times?
Start and end times, always. Decimal entries like “3.5” invite estimating after the fact; timestamps are entered in the moment (Ctrl+Shift+;) and the Hours formula does the conversion. You also keep a defensible record if a client ever questions an invoice.
How do I handle a deposit or partial payment?
Log each payment as its own Income row when it lands, with the invoice number in the Description. The Log records cash actually received, which is what your quarterly estimate should be based on — not what’s been billed.
Can multiple currencies live in one workbook?
Yes, but convert at entry: add an “Original Amount” and “FX Rate” column to the Log and make Amount the home-currency product of the two. Every downstream SUMIFS then works unchanged. Mixing currencies in a single Amount column breaks every total in the workbook.
Why not just use accounting software?
If you have inventory, employees, or dozens of clients, you should. For a solo freelancer with a handful of clients, this workbook does the same job with zero subscription cost — and because you built every formula, you can change any rule in seconds instead of fighting someone else’s feature set.
