Invoice Template in Excel: Layout, Formulas and a Simple Payment Tracker
Build an invoice template in Excel: what to include, line-item and sales tax formulas, automatic invoice numbers and due dates, a PDF workflow and an accounts receivable tracker with aging buckets.
Short answer: a professional invoice template in Excel needs your business details, the client’s details, a unique invoice number, the invoice date and due date, a table of line items (description, quantity, rate, amount), a subtotal, any discount and sales tax, the total due and clear payment instructions. Formulas calculate every amount and the due date, and a separate tracker sheet shows which invoices are unpaid and how overdue they are.
This guide builds the template step by step, explains the formulas, shows a worked example and adds a simple accounts receivable tracker so you know who owes you what. It is written for U.S. small businesses and freelancers; sales tax rules vary by state and product, so confirm what applies to you.
What to put on an invoice
| Section | Contents |
|---|---|
| Header | Business name, logo, address, phone, email, website; the word “Invoice” |
| Invoice details | Invoice number, invoice date, due date, payment terms (for example Net 30), purchase order number if the client uses one |
| Bill to | Client name, company, billing address, contact person |
| Line items | Description, quantity or hours, rate, amount, and whether the line is taxable |
| Totals | Subtotal, discount, sales tax, shipping, total due, amount paid, balance due |
| Payment | Accepted methods (ACH, check, card), bank or payment link details, late fee policy if agreed in your contract |
| Notes | Thank-you line, project reference, warranty or return terms |
Use the client’s legal business name and the exact address their accounts payable team expects; invoices with mismatched details are a common reason for late payment.
Step 1: Set up the reference sheets
Create a Settings sheet with your business details, default payment terms (in days), sales tax rate and the next invoice number. Create a Clients sheet with one row per client: name, billing address, email, payment terms. In the invoice, choose the client from a drop-down (Data → Data Validation → List) and pull the address with =XLOOKUP(B8,Clients!A:A,Clients!B:B). You type each client’s details once and never mistype them again.
Step 2: Invoice number, date and due date
- Invoice number: a simple sequence with a year prefix keeps numbers unique and sortable, for example
="INV-"&YEAR(F4)&"-"&TEXT(F3,"000"), where F3 is the sequence number and F4 the invoice date. Never reuse a number. - Due date:
=F4+XLOOKUP(B8,Clients!A:A,Clients!D:D)adds the client’s payment terms in days. For terms that should land on a business day, use=WORKDAY(F4+terms-1,1).
Step 3: Line items
Build the line table from row 15 with columns A Description, B Quantity, C Rate, D Amount, E Taxable (Y/N):
- Amount (D15):
=IF(B15="","",B15*C15) - Taxable amount helper (F15, can be hidden):
=IF(E15="Y",D15,0)
Write descriptions the client’s approver will recognize: “Website copywriting — 5 pages (PO 4471)” gets paid faster than “Services”.
Step 4: Totals and sales tax
- Subtotal:
=SUM(D15:D30) - Discount:
=Subtotal*DiscountRate(or a fixed amount) - Sales tax:
=ROUND(SUM(F15:F30)*(1-DiscountRate)*TaxRate,2)— applied only to taxable lines - Total:
=Subtotal-Discount+SalesTax+Shipping - Balance due:
=Total-AmountPaid
Whether a product or service is subject to sales tax depends on your state and sometimes your city or county, and on whether you have a sales tax obligation in the client’s state. Many services are not taxable in many states, but that is not universal. Keep the Taxable column so each line can follow the right rule.
Worked example
| Description | Qty | Rate | Taxable | Amount |
|---|---|---|---|---|
| Installation labor (hours) | 12 | $100.00 | N | $1,200.00 |
| Shelving materials | 1 | $350.00 | Y | $350.00 |
| Subtotal | $1,550.00 | |||
| Sales tax 8% on taxable lines ($350.00) | $28.00 | |||
| Total due | $1,578.00 | |||
The rate of 8% and the taxability of each line are illustrative; use the rules for your location.
Step 5: Send it as a PDF
Set a print area around the invoice, choose a page size of Letter, and save each finished invoice with File → Save As → PDF using a consistent file name such as INV-2027-014 Client Name.pdf. Send the PDF, not the spreadsheet: it cannot be edited, it looks the same on every device and it does not expose formulas or other sheets. Keep the spreadsheet as your master template and protect its formula cells — our guide on how to protect formulas in Excel shows how.
Step 6: Track what you are owed
An invoice template tells clients what they owe; a tracker tells you. Add an Invoices sheet with one row per invoice: A number, B client, C invoice date, D due date, E total, F amount paid, G date paid, then three formula columns.
- Balance (H):
=E2-F2 - Days overdue (I):
=IF(H2=0,"",MAX(0,TODAY()-D2))using the balance in H - Aging bucket (J):
=IF(H2=0,"Paid",IF(TODAY()<=D2,"Current",IF(TODAY()-D2<=30,"1–30",IF(TODAY()-D2<=60,"31–60",IF(TODAY()-D2<=90,"61–90","90+"))))) - Total outstanding by bucket:
=SUMIFS(H:H,J:J,"31–60")
Review the tracker weekly. A friendly reminder a few days before the due date and a firm follow-up a week after it are often enough. The total of unpaid invoices also feeds your cash planning, and paid invoices flow into your bookkeeping and your profit and loss statement.
Getting paid faster
- Invoice promptly — the day work is delivered, not at the end of the month.
- Make paying easy: include a payment link or bank details for ACH, and state accepted methods.
- State terms clearly on the contract and the invoice. Late fees should be agreed in advance and comply with your state’s rules.
- Ask for deposits on large projects and invoice milestones rather than one sum at the end.
- Collect a Form W-9 from contractors you pay, and give yours to clients who ask, so payments are not held up at year-end reporting.
Common mistakes
- Duplicate or skipped invoice numbers, often from copying an old invoice.
- Vague descriptions that the client’s approver cannot match to a purchase order.
- Taxing everything — or nothing — without checking which lines are taxable.
- Editing a sent invoice. Issue a credit note or a corrected invoice with a new number and reference the original.
- No tracker, so overdue invoices are discovered months later.
Frequently asked questions
Is an Excel invoice legally valid in the U.S.?
There is no federal format requirement for invoices between businesses; what matters is that the invoice is accurate and supported by your records. Some states, industries and government clients have specific requirements, so check if they apply to you.
Can I use this template in Google Sheets?
Yes. XLOOKUP, WORKDAY, SUMIFS and the aging formula work in Google Sheets, and you can download each invoice as a PDF from the File menu.
How should I number invoices?
Any system works as long as numbers are unique and sequential. A year prefix plus a sequence (INV-2027-014) is easy to sort and to reference in emails.
When is it time to switch to invoicing software?
When you send many invoices a month, need automatic reminders and online payments, or want invoices to post directly to your accounting. Until then, a well-built spreadsheet and tracker work well.
More guides for running your small business finances are collected in Small Business Finance.