How to track freelance income by client in a spreadsheet
Your total income tells you how you're doing. Income by client tells you how fragile that is. Two formulas show both.
What you need first
A transaction list with one row per payment, where Client / Vendor is a consistent name. If it's typed freehand ("Northwind", "Northwind Cafe", "northwind cafe"), the formulas below will treat them as three clients. Use a dropdown or copy the exact name each time. The layout from the income and expense tracker works as is: Type in column B, Client in E, Amount in F.
Step 1: income per client
On a summary tab, list each client name in column A (rows 2 and down). Then in B2:
=SUMIFS(Transactions!$F:$F,
Transactions!$B:$B, "Income",
Transactions!$E:$E, $A2)Copy it down. To limit it to one year, add two more conditions on the date column, exactly as in the P&L layout.
Step 2: share of total income
Share of income =IF(SUM($B$2:$B$30)=0, 0, B2/SUM($B$2:$B$30))Format the column as a percentage. The IF guard avoids a divide-by-zero error before you have any income logged. Sort the table by income, largest first, and you can see at a glance where the money comes from.
Step 3: read it
- One client at the top with a big share means one late payment or one lost contract hurts a lot. There is no universal "safe" percentage; the point is that you can now see your own number and decide whether you're comfortable with it.
- Many small clients usually means more admin per dollar. Compare income per client with how many invoices each one takes.
- A client who is shrinking shows up if you run the same formula for each quarter side by side.
A tip for new clients
Add the client to the list on the same day you log the first payment. A client missing from the summary tab is the usual reason "the clients don't add up to my total": the total comes from every transaction, the list only from the names you've typed. Add a check row: =SUMIFS(Transactions!$F:$F,Transactions!$B:$B,"Income") - SUM(B2:B30) should read zero.
More freelancer finance guides
- Excel or Google Sheets for freelancer finances? What actually differs
- Freelance expense categories for a spreadsheet: a starter list that stays short
- How to build a freelance income and expense tracker in a spreadsheet
- A simple 12-month cash flow forecast for freelancers
- Freelancer profit and loss (P&L) in a spreadsheet: a one-page layout
- Freelancer runway: three spreadsheet formulas for how long your cash lasts
- Freelancer tax set-aside: what goes into the number, and how to track it
- Invoice tracker spreadsheet: formulas for paid, due and overdue
- Budgeting on irregular freelance income: the baseline-pay method
Skip the setup
The Freelancer Finance Spreadsheet Kit includes the transaction tracker, invoice log with per-invoice status, monthly P&L, cash-flow forecast and tax set-aside calculator, all in one Excel file. $29, no subscription.
Free: the Tax Set-Aside tab as a standalone Excel file
One working tab: type your quarterly income, expenses and the rate you choose, and it shows what is still to put aside. Confirm your email and the file arrives right away, plus an occasional note (at most one a week) on tracking freelance money. Unsubscribe in one click. Privacy.
General information about using spreadsheets, not financial, tax or accounting advice.