How to Build an Invoice Template in Google Sheets (With Working Formulas)
Looking for a tool?
A useful invoice template does the arithmetic for you. You type the hours and the rate; the sheet works out each line, the subtotal, the tax, what a deposit leaves owing, the due date, and whether the invoice is open, paid or overdue. That is about eight formulas in total, and this page gives you all of them in the order you would build them. If you want the process of invoicing — numbering, terms, chasing late payers — that lives in how to invoice as a freelancer. This page is the file.
The layout: four blocks, not one big table
Almost every messy invoice sheet comes from putting everything in one table. Split it into four blocks and the formulas stay short and unbreakable:
- Settings (top-left, a few rows) — your business details, tax rate, payment terms in days, late-fee rate. Nothing here is typed twice anywhere else.
- Invoice header — invoice number, invoice date, client, due date.
- Line items — the only block you actually type into per job.
- Totals — subtotal, tax, total, paid, balance due.
Put the settings block on its own tab once you have more than one client. Everything below assumes a single sheet for clarity.
The line-item formulas
Say your line items start in row 12, with description in column C, quantity or hours in D, rate in E, and line total in F. The only formula in the block is:
F12 = IF(D12="", "", ROUND(D12*E12, 2))
The IF wrapper matters more than it looks. Without it every unused row shows
$0.00 and the invoice looks padded and amateurish; with it, empty rows stay blank. Fill that
formula down ten or fifteen rows so you never have to add one mid-job. ROUND(..., 2) stops the
classic one-cent mismatch where the displayed total and the real total disagree because the sheet is
carrying fractions of a cent behind the formatting.
Subtotal, tax and total
With the tax rate in B4 of the settings block, and line items in F12:F26:
| Row | Formula | What it does |
|---|---|---|
| Subtotal | =SUM(F12:F26) | Adds only the filled lines |
| Tax | =ROUND(F27*B4, 2) | Tax on the subtotal, rounded to the cent |
| Total | =F27+F28 | What the invoice is for |
| Paid / deposit | typed by hand | Any deposit or part payment received |
| Balance due | =F29-F30 | The number the client actually owes today |
Balance due is the number that belongs in the biggest, boldest cell on the page — not the total. If you took a deposit, the total is a historical fact and the balance is the request. Putting the wrong one in bold is the single most common cause of a client paying the wrong amount.
A worked example
Three lines: 12 hours at $65, 4 hours at $85, and one fixed item at $150.
| Line | Qty | Rate | Line total |
|---|---|---|---|
| Design work | 12 | $65.00 | $780.00 |
| Revisions | 4 | $85.00 | $340.00 |
| Asset licence | 1 | $150.00 | $150.00 |
| Subtotal | $1,270.00 | ||
| Tax at 8.25% | $104.78 | ||
| Total | $1,374.78 | ||
| Deposit already paid | -$400.00 | ||
| Balance due | $974.78 | ||
Note the tax line: 1,270 × 0.0825 is 104.775, and the sheet must round it to $104.78 before it enters the total. If you let the raw figure through and only format it to two decimals, your invoice total and your bookkeeping total will differ by a cent — small, and irritating enough to cost you ten minutes at year end.
A due date that calculates itself
With the invoice date in B8 and your terms in days in B5 of the settings block:
=B8+B5
That is the whole formula — Google Sheets stores dates as numbers, so adding 14 gives you a date 14 days later. Format the cell as a date and write it out in full on the invoice. An invoice dated 4 September 2026 on Net 14 is due 18 September; the same invoice on Net 30 is due 4 October. Write the real date, not the words "Net 30" alone — a specific date is far harder to quietly ignore.
An automatic paid / open / overdue status
This is the formula that turns a document into a tracker. With total in F29, paid in
F30 and the due date in B9:
=IF(F30>=F29, "PAID", IF(TODAY()>B9, "OVERDUE", "OPEN"))
Read it in plain English: if what has been paid covers the total, it is paid; otherwise, if today is past the due date, it is overdue; otherwise it is simply open. Add conditional formatting so PAID is green and OVERDUE is red, and a folder of invoices becomes a chase list you can scan in three seconds.
Two cautions. TODAY() recalculates every time the file opens, which is exactly what you want
in a live tracker and exactly what you do not want in an archived PDF — export to PDF when you send,
so the client receives a fixed document. And use >= rather than = in the paid
test, so an overpayment or a rounding difference still reads as paid.
Late fees, if you charge them
If your terms state a late fee — say 1.5% per month — the calculation on the balance above is
=ROUND(F31*0.015, 2), which on $974.78 is $14.62 for the first month. Keep it in
its own cell rather than folding it into the total, so the client can see plainly what the charge is and what
it is for. A late fee you never mention until it appears on a statement causes an argument; one printed on the
original invoice usually just gets the invoice paid.
Invoice numbers that stay unique
Spreadsheets strip leading zeros from numbers, which is why hand-typed invoice numbers drift into
INV-1, INV-01 and INV-001 across a year. Build the number from a counter instead — with a plain sequence
number in B7:
="INV-" & TEXT(YEAR(B8),"0000") & "-" & TEXT(B7,"000")
That produces INV-2026-001, INV-2026-002 and so on, always three digits, always tied to the invoice year. Increment the counter for each new invoice and never reuse a number, even for a cancelled one — mark it void and move on. Your future self reconciling a bank statement will be grateful.
Where a spreadsheet invoice stops being enough
Honestly: one sheet per invoice works beautifully up to a few invoices a month. Past that, the thing that breaks is not the invoice — it is knowing which invoices are outstanding and how much is owed in total. At that point you want one row per invoice in a log, with the status formula above running down the column and a single sum of everything unpaid. That is the point where an invoice template turns into cash-flow tracking, and where the numbers start feeding your bookkeeping rather than sitting in a folder of separate files.
Two of our free tools do the one-off versions of this if you would rather not build the sheet at all: the invoice calculator for line items, tax and balance, and the late fee calculator for what an overdue invoice has accrued.
Quick FAQ
Does this work in Excel too?
Every formula above is identical in Excel except TODAY(), which also exists there and behaves
the same way. The only real difference is sharing: a Google Sheet is a link, an Excel file is an attachment.
See Google Sheets vs Excel for small business if
you are choosing between them.
Should I send the invoice as a spreadsheet or a PDF?
Always a PDF. A spreadsheet can be edited, recalculates on open, and shows your other tabs if you share the wrong thing. File, Download, PDF — and check it fits on one page before it goes.
How do I handle an invoice with no tax?
Set the tax rate cell to 0 rather than deleting the row. The formula keeps working, the line shows $0.00, and you can hide that row when it is not needed. Deleting cells to represent "not applicable" is how templates break three months later.
What if a client pays part of an invoice?
Type the amount received into the paid cell. The balance-due formula and the status formula both handle partial payment automatically — the invoice stays OPEN or OVERDUE until the paid figure reaches the total.
Do I need a separate invoice number for each client?
No, and you should not. One sequence across all clients keeps every invoice unique and makes reconciliation straightforward. Track the client in its own column instead.
Related: how to invoice as a freelancer · cash-flow tracking · bookkeeping in Google Sheets · invoice calculator