Guide
Disclosure: I sell the Freelance Client & Invoice Tracker ($19) linked on this page. The steps below work just as well if you build your own.
Most freelance trackers break the same way. Clients are in one place, invoices in another, and the "who still owes me" answer is in your head. The fix isn't a fancier file. It's three tabs that share one column: the client name.
Here's how to set that up in Google Sheets or Excel. It takes about 30 minutes.
The three tabs
1. Clients: one row per client.
| Client | Contact email | Payment terms (days) | Rate | Notes |
|---|---|---|---|---|
| Harbor Bakery | ops@harborbakery.example | 14 | $60/hr | Pays by bank transfer |
| Pine Studio | anna@pinestudio.example | 30 | $1,200/project | Needs a PO number |
2. Projects: one row per job, with the client picked from a dropdown.
| Project | Client | Start | Status |
|---|---|---|---|
| Menu redesign | Harbor Bakery | 2026-09-02 | Done |
| Brand refresh | Pine Studio | 2026-09-20 | Active |
3. Invoices: one row per invoice.
| Invoice # | Client | Project | Issue date | Due date | Amount | Paid date | Status |
|---|---|---|---|---|---|---|---|
| 1041 | Harbor Bakery | Menu redesign | 2026-09-15 | 2026-09-29 | $720 | 2026-09-27 | Paid |
| 1042 | Pine Studio | Brand refresh | 2026-10-01 | 2026-10-31 | $1,200 | Unpaid |
Step 1: Make the client name a dropdown everywhere
Typing "Pine Studio" one day and "Pine Studios" the next quietly breaks every total. In the Projects and Invoices tabs, set the Client column to a dropdown that reads from the Clients tab:
- Google Sheets: Data → Data validation → Dropdown (from a range) → Clients!A2:A200
- Excel: Data → Data Validation → List → =Clients!$A$2:$A$200
Step 2: Let the sheet work out the due date
Don't type due dates. Pull the client's terms and add them to the issue date. In the Invoices tab (issue date in D, client in B):
=IF(D2="","",D2+XLOOKUP(B2,Clients!A:A,Clients!C:C,30))
(Older Excel without XLOOKUP: =D2+VLOOKUP(B2,Clients!A:C,3,FALSE).)
Step 3: Let it work out the status
With the due date in E and the paid date in G:
=IF(G2<>"","Paid",IF(E2="","",IF(TODAY()>E2,"Overdue","Unpaid")))
Add conditional formatting: red fill when the status is "Overdue". Now the only thing you type when money arrives is the paid date.
Step 4: Add per-client totals to the Clients tab
Owed: =SUMIFS(Invoices!F:F,Invoices!B:B,A2,Invoices!H:H,"<>Paid")
Paid YTD: =SUMIFS(Invoices!F:F,Invoices!B:B,A2,Invoices!G:G,">="&DATE(YEAR(TODAY()),1,1))
This answers the question people actually ask: "how much does this client owe me right now?"
Step 5: The 10-minute weekly review
Pick a fixed time (Monday morning works). Then: 1. Sort Invoices by Status, so Overdue is at the top. 2. Send any follow-ups (a short, specific email that asks for a payment date). 3. Enter paid dates for anything that arrived. 4. Check Projects for finished work that hasn't been invoiced yet. This is the leak most people miss.
When a free sheet is enough
- You have fewer than about 10 active clients and send a handful of invoices a month.
- You're fine emailing invoices yourself as PDFs.
- You don't need clients to pay by card through the invoice.
If you need automatic reminders, recurring invoices or card payments, a free invoicing app (Wave, Zoho Invoice and similar) does that part better. You can still keep the sheet as your overview.