Guides › Freelancer Bookkeeping Spreadsheet: A Simple Setup That Works · Updated 26-10-2026

How to set up a freelancer bookkeeping spreadsheet (that you'll actually keep up)

Most freelancers don't need accounting software in their first years. They need one place where income, expenses and tax set-aside are visible at a glance, and a habit of updating it. A well-built spreadsheet does exactly that, costs nothing per month, and stays yours. This guide shows the minimum structure that works, the formulas that remove manual work, and a short routine to keep it current.

The four tabs you actually need

Skip the twenty-tab monster. Four tabs cover almost every solo business:

1. Settings – your currency, tax rate(s), payment terms and a list of expense categories. Everything else reads from here, so changing a rate is a one-cell job.
2. Income – one row per invoice: date, client, invoice number, amount, tax, payment terms, date paid.
3. Expenses – one row per receipt: date, supplier, category, amount, business-use percentage.
4. Dashboard – totals per month and quarter, profit, unpaid invoices and how much to put aside for tax.

Keep raw data (Income, Expenses) strictly separate from calculations (Dashboard). You type in the first two; you never type in the last one. That single rule prevents most spreadsheet errors.

Turn your data into a proper table

Select your Income range and press Ctrl+T (Excel) or use Format → Convert to table (Google Sheets). Tables grow automatically when you add a row, so your totals never miss the latest invoice. Add a data-validation dropdown to the Category column that points at the category list on your Settings tab, so “Software”, “software” and “Softwear” don't end up as three different categories.

The formulas that do the heavy lifting

Three formulas cover 90% of a freelancer dashboard. Assume your income table has dates in column A and net amounts in column D.

Income for one month (month start date in cell B1 of the dashboard):

=SUMIFS(Income!D:D, Income!A:A, ">="&B1, Income!A:A, "<"&EDATE(B1,1))

Expenses per category (category name in A5):

=SUMIFS(Expenses!F:F, Expenses!C:C, A5)

Tax set-aside (profit in B10, your estimated rate in Settings!B3):

=MAX(0, B10) * Settings!B3

Copy the first formula across twelve months and you have a monthly income chart with zero manual adding-up. The same pattern with quarter start dates gives you quarterly totals for VAT/GST or estimated tax payments.

Handle part-business expenses honestly

A phone used 60% for work, or a home internet connection shared with the family, shouldn't be booked at 100%. Add a Business use % column and calculate the deductible part next to it:

=D2 * E2

where D2 is the amount and E2 is the percentage. Your dashboard then totals the deductible column, not the raw amount. If your accountant ever asks how you got to a number, the answer is right there in the row.

A 15-minute monthly routine

• Log every invoice the day you send it. Leave “date paid” empty until the money arrives.
• Once a month, open your bank app, go through business transactions and add any expense you haven't logged. Save the receipt (a photo is fine) in a folder named after the month.
• Check the unpaid list and send polite reminders for anything overdue.
• Move the tax set-aside amount to a separate savings account. This one step prevents the most painful freelancer surprise of the year.

When a spreadsheet is no longer enough

Be honest with yourself: if you have employees, stock, many transactions per day, or your tax authority requires digital submissions from specific software, move to accounting software or an accountant's system. In the UK, for example, Making Tax Digital for Income Tax applies to sole traders above an income threshold, and they need compatible (or bridging) software to send quarterly updates. A spreadsheet is a great record-keeping tool, not tax advice.

Want it ready-made?

Building this yourself takes an afternoon of formula testing. If you'd rather start today, our Freelancer Bookkeeping Tracker has exactly this structure: an income log whose status flips to Paid, Open or Overdue automatically, expenses with business-use %, a quarterly tax summary and a live dashboard with charts. It works in Excel and Google Sheets, in euro, dollar and pound editions, for a one-time €14.