Guided project · 5 steps Cellwise Plus
Freelance income tracker in Excel
Bill clients from your hours, see what is unpaid and overdue, and work out what to set aside.
You will use: IF, SUMIF, SUM, ROUND
Starting data
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Client | Hours | Rate | Billed | Invoiced | Paid | Days unpaid | As of | Nov 1, 2026 | |
| 2 | Alder Coffee | 14 | 90 | Sep 15, 2026 | Oct 2, 2026 | Unpaid total | ||||
| 3 | Brightline Dental | 18.5 | 85 | Sep 20, 2026 | Oct 14, 2026 | Overdue (30+ days) | ||||
| 4 | Northpine Outdoor | 31 | 90 | Oct 3, 2026 | Set-aside % | 0.25 | ||||
| 5 | Kestrel Labs | 22 | 80 | Oct 1, 2026 | Oct 9, 2026 | Set aside from paid | ||||
| 6 | Fernway Trust | 40 | 85 | Sep 5, 2026 | ||||||
| 7 | Tallow and Co | 12 | 95 | Oct 20, 2026 |
Build it step by step
Step 1
Billed is hours times rate. Write it in D2 and fill it down to D7.
Step 2
For unpaid invoices, count the days since invoicing as of J1; paid ones show 0. Write it in G2, fill to G7.
Step 3
Total everything still unpaid in J2 (rows where days unpaid is more than 0).
Step 4
Total the invoices unpaid for more than 30 days in J3.
Step 5
Set aside the % in J4 of the money already paid (total billed minus unpaid). Round to cents in J5.