VThe Vera Standard

How to track invoices and expenses in a spreadsheet for a one-person business

Short answer: Keep an Income sheet with one row per invoice and its paid date, an Expenses sheet with one row per cost, and a Summary that adds income by the month it was paid and expenses by month with SUMIFS, giving profit per month and for the year so far. An as-of date decides which unpaid invoices are overdue.

Who it is for: freelancers and other one-person businesses that send invoices, whose records sit in email and receipts, and who want invoices and expenses in one place before tax time. It works in Excel or Google Sheets.

It is not the right fit if you need:

  • payroll or pay for employees;
  • sales tax, VAT or GST lines on your invoices;
  • a tax filing, or tax worked out for you;
  • an automatic bank feed.

This is a record-keeping method. It adds up what you already did and lays it out; it is not accounting, tax or legal advice. Which figures your accountant needs, and how your income and costs are treated for tax, your accountant decides.

What you will have at the end

Four sheets and a print page. Income lists every invoice and whether it is paid. Expenses lists every cost by category. Summary shows profit for each month and for the year so far. Unpaid shows who still owes you. A one-line check beside each row tells you when a row needs fixing. The Year pack is the same figures laid out to print.

Step 0: One date that drives "overdue"

Make a sheet called Summary. Type As of in A1 and the as-of date in B1, for example 6/30/2026. Type Year in A2 and the year in B2, 2026; the monthly table in Step 3 reads it. Change B1 whenever you sit down to update the file. An invoice counts as overdue only when the as-of date is after its due date, so this one cell decides what shows as overdue everywhere.

(You can type =TODAY() in B1 instead. Typing a date lets you look at a past date, such as the end of last month.)

Dates in this guide are typed month/day/year, as in the US. If your spreadsheet uses day/month/year, type them that way. Keep one file for each year: copy it at year end and clear the rows.

Step 1: The Income sheet

Make a sheet called Income with these headings in row 1:

ColumnHeadingWhat goes in it
AInvoiceYour invoice number, such as INV-001
BClientThe client's name, spelled the same way every time
CInvoice dateDate you sent it
DDue dateDate payment is due
EAmountThe invoice total, a positive number
FPaid dateDate the money arrived. Leave empty while unpaid
GStatusA formula (below)
HRow checkA formula (below)
IDays overdueA formula (below)

You type in A to F. The other three fill themselves. In G2:

=IF(COUNTA(A2:F2)=0,"",IF(F2<>"","Paid",IF(Summary!$B$1>D2,"Overdue","Unpaid")))

In I2:

=IF(G2="Overdue",Summary!$B$1-D2,"")

In H2, the row check:

=IF(COUNTA(A2:F2)=0,"",IF(OR(A2="",B2="",NOT(ISNUMBER(C2)),NOT(ISNUMBER(D2)),NOT(ISNUMBER(E2))),"Fill the blanks or fix a date",IF(E2<=0,"Amount must be above zero",IF(D2<C2,"Due date is before invoice date",IF(AND(F2<>"",NOT(ISNUMBER(F2))),"Fix the paid date",IF(AND(F2<>"",F2<C2),"Paid before invoiced","OK"))))))

Fill G2:I2 down as far as you expect to need, for example to row 300. A blank row shows nothing in all three; a row with a problem says what to fix. A date typed as text (it sits on the left of the cell) is caught too.

One choice to make now: this method counts income in the month the money arrived (the Paid date), not the month you sent the invoice. Some people count the other way. Which one is used for your tax return, your accountant decides; if you change your mind, point the income formula in Step 3 at column C instead of column F, and make the income cross-check =SUM(Income!$E:$E).

Step 2: The Expenses sheet

Make a sheet called Expenses:

ColumnHeadingExample
ADate1/8/2026
BVendorCloud Tools Ltd
CCategorySoftware
DDescriptionMonthly subscription
EAmount45.00
FRow checkA formula

Rules that save trouble later:

The row check in F2:

=IF(COUNTA(A2:E2)=0,"",IF(OR(NOT(ISNUMBER(A2)),C2="",NOT(ISNUMBER(E2))),"Fill the blanks or fix the date",IF(E2<=0,"Amount must be above zero","OK")))

Step 3: Monthly and year-to-date profit with SUMIFS

On Summary, add headings in row 4: Month, Income, Expenses, Profit, Profit year to date. Put the first day of each month in A5 to A16: in A5 type =DATE($B$2,1,1), in A6 type =EDATE(A5,1) and fill A6 down to A16. Format A5:A16 to show the month (Format, then Number, then Custom date and time in Google Sheets; Format Cells, then Custom, mmm yyyy in Excel).

In B5, income received in that month:

=SUMIFS(Income!$E:$E,Income!$F:$F,">="&A5,Income!$F:$F,"<="&EOMONTH(A5,0))

In C5, expenses in that month:

=SUMIFS(Expenses!$E:$E,Expenses!$A:$A,">="&A5,Expenses!$A:$A,"<="&EOMONTH(A5,0))

In D5, profit (income minus expenses):

=B5-C5

In E5, profit so far this year:

=SUM(D$5:D5)

Fill B5:E5 down to row 16. In row 17 put Total in A17 and =SUM(B5:B16) in B17, then the same pattern for C17 and D17.

Unpaid invoices have no Paid date, so SUMIFS leaves them out of income until the money arrives. That is the point of the Paid date column.

Add two cross-checks under the table, with Cross-check in A19. In B19, all paid invoices:

=SUMIFS(Income!$E:$E,Income!$G:$G,"Paid")

and in C19, all expenses:

=SUM(Expenses!$E:$E)

B19 must equal B17, and C19 must equal C17. If one does not, a date is outside the year in B2, or a row has a typo in its date.

Step 4: The unpaid-invoices list

The quickest way is a filter. On Income, turn on a filter on row 1 (Data, then Create a filter) and set Status to show Overdue and Unpaid. Sort by Due date.

If you use Microsoft 365 or Google Sheets, you can build a separate sheet called Unpaid instead. Type the headings Invoice, Client, Invoice date, Due date, Amount in A1 to E1. In A2:

=IFERROR(SORT(FILTER(Income!A2:E300,(Income!G2:G300="Overdue")+(Income!G2:G300="Unpaid")),4,TRUE),"None")

Format columns C and D as dates. Put Total unpaid in G1 and the total in G2:

=SUMIFS(Income!$E:$E,Income!$G:$G,"Overdue")+SUMIFS(Income!$E:$E,Income!$G:$G,"Unpaid")

The list is sorted by Due date, so the oldest overdue invoice is at the top, which is the one to chase first.

Step 5: A row check you read every week

Once a week, before anything else, glance down the Row check columns on Income and Expenses. Anything that is not OK says what to fix. Then go to Summary and read the cross-checks: B19 against B17, and C19 against C17. Two minutes, and the totals can be trusted.

Step 6: The year pack to print for your accountant

At year end, make one sheet called Year pack that puts the year's figures on one page: income by client, expenses by category, profit by month, and unpaid invoices. Everything on it is a formula pointing at the sheets above, so it never needs retyping.

Income by client, with the client names in A15 down (type each name once), in B15:

=SUMIFS(Income!$E:$E,Income!$B:$B,A15,Income!$G:$G,"Paid")

Expenses by category, with the categories in A22 down, in B22:

=SUMIFS(Expenses!$E:$E,Expenses!$C:$C,A22)

Under them, put the monthly profit table from Summary (link each cell, for example =Summary!D5) and the unpaid total from Step 4.

To print in Google Sheets: File, then Print, then Current sheet, Landscape and Fit to width; add the year and the as-of date under Headers and footers. In Excel: Page Layout, then Orientation, Landscape, and Width, 1 page; add the same in the header. Save a PDF copy as well as the file. What your accountant wants to see, and in what form, your accountant decides, so ask before you assemble it.

A worked example you can check

A designer, six months of 2026, as-of date 6/30/2026.

Income

InvoiceClientInvoice dateDue dateAmountPaid dateStatusDays overdue
INV-001Harbor Cafe1/5/20261/19/20261,200.001/15/2026Paid
INV-002Lin Design1/20/20262/3/2026800.002/2/2026Paid
INV-003Harbor Cafe2/10/20262/24/20261,500.002/20/2026Paid
INV-004Oak Dental3/3/20263/17/2026950.003/30/2026Paid
INV-005Lin Design4/7/20264/21/2026700.004/18/2026Paid
INV-006Oak Dental5/4/20265/18/20261,100.006/10/2026Paid
INV-007Harbor Cafe5/25/20266/8/2026600.00Overdue22
INV-008Lin Design6/15/20266/29/2026450.00Overdue1
INV-009Oak Dental6/24/20267/8/2026900.00Unpaid

Expenses (17 rows: Software 45.00 and Phone 60.00 every month, plus these)

DateCategoryAmount
2/14/2026Office120.00
3/12/2026Travel85.00
4/16/2026Fees30.00
5/9/2026Office200.00
6/18/2026Travel140.00

Summary

MonthIncomeExpensesProfitProfit year to date
Jan1,200.00105.001,095.001,095.00
Feb2,300.00225.002,075.003,170.00
Mar950.00190.00760.003,930.00
Apr700.00135.00565.004,495.00
May0.00305.00-305.004,190.00
Jun1,100.00245.00855.005,045.00
Jul to Dec0.00 each0.00 each0.00 each5,045.00 each
Total6,250.001,205.005,045.00

Cross-checks: paid invoices 6,250.00 = B17; all expenses 1,205.00 = C17.

Year pack figures

Income by client (paid)AmountExpenses by categoryAmount
Harbor Cafe2,700.00Software270.00
Lin Design1,500.00Phone360.00
Oak Dental2,050.00Office320.00
Travel225.00
Fees30.00
Total6,250.00Total1,205.00

Unpaid at 6/30/2026: INV-007 600.00 and INV-008 450.00 overdue (1,050.00), INV-009 900.00 not yet due, total 1,950.00. All invoiced: 6,250.00 paid plus 1,950.00 unpaid is 8,200.00.

May shows a loss of 305.00 although INV-006 was sent in May: it was paid on 6/10, so it counts in June. That is the paid-date choice from Step 1 at work.

Questions people ask

How do I get my invoices and expenses in one place before tax time?

Use one file per year with an Income sheet (every invoice, with the date it was paid) and an Expenses sheet (every cost by category). A year pack sheet then lays out income by client, expenses by category and profit by month for your accountant.

How do I see which invoices are overdue?

A Status column compares the as-of date with each due date and shows Paid, Unpaid or Overdue, with the days overdue. A filtered or sorted list puts the oldest overdue invoice first.

Apps want a monthly fee and my bank login. Do I need one?

No. A spreadsheet with these sheets works in Excel or Google Sheets with no subscription and no bank login; your records stay in your file.

Is income counted when I invoice or when I'm paid?

This method counts income in the month the money arrived. Which way your tax return needs it, your accountant decides; the guide shows the one change to count by invoice date instead.

If you would like it ready-made

If you would rather not build and check these sheets yourself, the Solo Business Records Workbook is the same method already built: invoices with balance, status and days overdue, expenses, a Dashboard for profit and who owes you, and a Year pack that prints income by client and month, expenses by category, profit, miles and unpaid invoices for your accountant. Every row you type checks itself and says OK or what to fix. It comes with 14 sheets, a blank copy and a one-page PDF guide, was run through 45 test scenarios and 34,183 checks, and was tested in Microsoft Excel (Microsoft 365, Windows). It is one payment, the file is yours, and there is no bank login: your records stay in your file. The same not-fit list as above applies, and it also has no double-entry accounts. It is on The Vera Standard's Gumroad page. The method above works without it.