DEV Community

Cover image for How to Track Freelance Income in a Spreadsheet (Free Google Sheets & Excel Template)
Ponte Sheets
Ponte Sheets

Posted on

How to Track Freelance Income in a Spreadsheet (Free Google Sheets & Excel Template)

#freelance #googlesheets #excel #productivity

Accounting apps are great, but many of them cost a monthly fee and do far more than a solo freelancer needs. If you're invoicing a handful of clients a month, a well-structured spreadsheet can answer the questions that matter:

  • How much did I actually earn this month?
  • Who still owes me money?
  • Which clients are worth the most?
  • How much of this is mine to spend after tax?

Here's how to build one in Google Sheets or Excel, step by step. Every formula below works in both.

Not tax or accounting advice. This is a practical tracking system. Your local tax rules decide what you have to record and for how long, so check with an accountant.

Step 1: Decide what counts as "income"

Before touching a cell, decide on one rule and stick to it: you log income when the money arrives (cash basis). Most freelancers and sole proprietors use this method because it's simpler: an invoice isn't income until it's paid.

You'll still track invoices you've sent, just in a separate status, so you never confuse "billed" with "banked".

Step 2: Set up your income log

Create a tab called Income. One row per payment (or per invoice). Recommended columns:

Col Header Example Why
A Invoice date 2026-10-02 When you billed
B Paid date 2026-10-16 When cash arrived (blank = unpaid)
C Client Acme Co For per-client totals
D Project / description Landing page Context for later
E Invoice # 2026-014 Matches your invoice PDFs
F Amount 1,500 Gross amount
G Fees 45 Stripe/PayPal/Upwork/platform fees
H Net received =F2-G2 What actually hit your account
I Currency USD / EUR Important for EU/cross-border clients
J Status =IF(B2="","Pending","Paid") Automatic
K Month =IF(B2="","",TEXT(B2,"yyyy-mm")) For monthly totals

Tips:

  • Freeze row 1 and turn the range into a table (Excel: Ctrl+T; Sheets: Format → Convert to table) so formulas fill down automatically.
  • Use data validation on the Client column (a dropdown from a Clients tab) so "Acme", "ACME" and "Acme Co." don't become three different clients.
  • Record platform fees separately instead of logging only the net. Fees are usually a deductible business expense, and you'll want the gross figure when you reconcile against 1099-K/1099-NEC forms (US) or platform statements.

Step 3: Track unpaid invoices

Because Status is calculated from Paid date, your pending invoices are just a filter away. Add a quick "money owed to me" cell:

Outstanding: =SUMIF(J:J,"Pending",F:F)
Enter fullscreen mode Exit fullscreen mode

And an "overdue" flag (assuming 30-day terms) in column L:

=IF(AND(J2="Pending",TODAY()-A2>30),"OVERDUE","")
Enter fullscreen mode Exit fullscreen mode

Add conditional formatting to turn overdue rows red. This one column has probably recovered more money for freelancers than any app.

Step 4: Build monthly totals

Create a Summary tab. In column A list months (2026-01 … 2026-12) as text. In column B:

=SUMIF(Income!K:K,A2,Income!H:H)
Enter fullscreen mode Exit fullscreen mode

Now you have net income per month. Add a column C for running year-to-date:

=SUM($B$2:B2)
Enter fullscreen mode Exit fullscreen mode

Insert a column chart over A:B and you have a visual income trend. Seasonality jumps out fast. Many freelancers discover a slow month they can plan around.

Step 5: Per-client breakdown

Two options:

Option A: formula. List clients in column G of the summary and use:

=SUMIF(Income!C:C,G2,Income!H:H)
Enter fullscreen mode Exit fullscreen mode

Option B: pivot table. Select your income table → Insert → Pivot table. Rows: Client. Values: SUM of Net received. Sort descending.

This answers an uncomfortable but useful question: if your top client left tomorrow, what share of income would disappear? If one client is most of your revenue, that's a risk worth knowing about.

Step 6: Add expenses (so you see profit, not just revenue)

Income alone is a vanity number. Add an Expenses tab with: Date, Vendor, Category, Amount, Receipt link. Then in the summary:

Column D, expenses per month: =SUMIF(Expenses!F:F,A2,Expenses!D:D)
Column E, profit per month: =B2-D2
Enter fullscreen mode Exit fullscreen mode

(Use the same TEXT(date,"yyyy-mm") helper column in Expenses.)

Keep categories short and consistent, e.g. Software, Equipment, Contractors, Travel, Office, Fees, Education. If you're in the US, aligning them loosely with Schedule C lines makes tax time easier. In the EU, check what your local return asks for.

Step 7: Add a tax set-aside column

Put your set-aside rate in a single cell on the summary tab (name it TaxRate) and add:

Set aside per month: =E2*TaxRate
Enter fullscreen mode Exit fullscreen mode

where column E is the profit from Step 6. Picking the rate is a topic on its own. If you're in the US, I wrote a full breakdown here: How Much Should a US Freelancer Set Aside for Taxes? https://dev.to/pontesheets/how-much-should-a-us-freelancer-set-aside-for-taxes-self-employment-tax-quarterly-payments-1mfe. In the EU, VAT and income-tax rules vary widely by country, so ask a local accountant.

Step 8: Handle multiple currencies

If you bill in USD and EUR, add a column Rate to home currency and a Net (home) column:

=H2*M2
Enter fullscreen mode Exit fullscreen mode

In Google Sheets you can pull an approximate rate with =GOOGLEFINANCE("CURRENCY:EURUSD"), but for records, use the rate your bank actually applied (or whatever your tax authority accepts). Then base all summaries on the home-currency column.

Step 9: A 10-minute weekly routine

A tracker only works if it's current. Once a week:

  1. Open your bank / Stripe / PayPal, log any new payments (fill Paid date).
  2. Log new invoices you sent (leave Paid date blank).
  3. Check the overdue column and send polite reminders.
  4. Log expenses and drop receipts into a dated folder.
  5. Move the set-aside amount to your tax savings account.

Monthly: glance at the chart and per-client pivot. That's it.

Common mistakes

  • Mixing personal and business money. Use a separate account if you can; it makes reconciling the sheet trivial.
  • Logging net only. You lose track of fees and won't match platform tax forms.
  • Free-typing client names. Use a dropdown.
  • One giant tab per year with no summary. Raw rows don't tell you anything until you summarize them.
  • No backup. Sheets has version history; for Excel, keep the file in a synced folder.

Skip the build: a free template

If you'd rather start from a finished version, I built Ponte Sheets for Google Sheets and Excel. The free Lite version has 12 monthly tabs, dropdowns and an auto-updating income/expense/net-profit dashboard. Pro ($19 one-time) adds pending invoices, tax set-aside, safe-to-spend and a steady monthly salary. Grab it on Gumroad. (Disclosure: I'm the maker.)

TL;DR

  • Log income when it's paid; track invoices separately with an automatic status.
  • Columns: invoice date, paid date, client, amount, fees, net, currency, status, month.
  • SUMIF by month and by client, or use a pivot table.
  • Add expenses to see profit, and a tax set-aside cell.
  • Spend 10 minutes a week keeping it current.

Top comments (0)