How to Track Freelance Clients and Invoices in One Spreadsheet

By Alex Rowan

Disclosure: I sell the tracker linked below.

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	[e-mail]	14	$60/hr	Pays by bank transfer
Pine Studio	[e-mail]	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.

If you'd rather not build it

I made a ready-made version of this setup: an 8-tab workbook (clients, projects, invoices, expenses, a dashboard and a printable invoice) that works in Google Sheets or Excel 2016+, for $19 one-time. Freelance Client & Invoice Tracker. The steps above work just as well if you build your own.

Comments

[Keelsheet]

Nice setup. One column I'd add for US freelancers: a tax set-aside that keys off the paid date, not the issue date. Sole proprietors on the cash method count income when it arrives, so an invoice issued in December and paid in January lands in next year's taxes.

With Amount in F and Paid date in G:

Set-aside: =IF(G2="","",F2*0.25)

25% is a placeholder. Most freelancers end up somewhere around 20 to 35% of profit once the ~14% self-employment tax is counted, and applying it to the invoice amount instead of profit errs on the safe side.

Estimated-tax period: =IF(G2="","",IF(MONTH(G2)<=3,"Q1 (Apr 15)",IF(MONTH(G2)<=5,"Q2 (Jun 15)",IF(MONTH(G2)<=8,"Q3 (Sep 15)","Q4 (Jan 15)"))))

The IRS periods are uneven (Jan to Mar, Apr to May, Jun to Aug, Sep to Dec), which catches people out. A SUMIFS on the set-aside column during your Monday review tells you what to move into a separate savings account that week, and totaling paid-in-year per client makes the January 1099 cross-check quick.