Try the sheet here, on the example
The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.
Example from the sheet
| Invoice no. | Customer | Total | Paid | Balance | Due date | Status |
|---|---|---|---|---|---|---|
| 1001 | Harbor Coffee Roasters | 2,604.00 | 2,604.00 | 0.00 | 46298 | Paid |
| 1002 | Maple Street Dental | 1,464.75 | 1,464.75 | 0.00 | 46290 | Paid |
| 1003 | Oakridge Property Group | 5,359.90 | 2,000.00 | 3,359.90 | 46328 | Overdue |
| 1004 | Redwood Legal LLP | 1,953.00 | 1,953.00 | 46307 | Overdue | |
| 1005 | Brightline Studios | 3,298.40 | 3,298.40 | 0.00 | 46327 | Paid |
| 1006 | Pinecone Outdoor Supply | 2,332.75 | 2,332.75 | 46333 | Overdue | |
| 1007 | Lena Fischer | 651.00 | 651.00 | 0.00 | 46316 | Paid |
| 1008 | Harbor Coffee Roasters | 1,855.35 | 1,855.35 | 46345 | Sent | |
| 1009 | Maple Street Dental | 1,030.75 | 1,030.75 | 46337 | Overdue | |
| 1010 | Brightline Studios | 4,394.25 | 1,500.00 | 2,894.25 | 46359 | Sent |
| 1011 | Redwood Legal LLP | 2,983.75 | 2,983.75 | 46349 | Draft | |
| 1012 | Summit Fitness Club | 1,469.63 | 1,469.63 | 46368 | Sent |
Example from the “Register” tab, with the numbers and formulas that are in the file.
What is inside
- Settings: Your company details, tax name and rate, currency symbol, payment instructions and the as-of date used for the overdue check.
- Customers: One row per customer with address, email and payment terms in days. The right-hand columns show how many invoices each customer has, what you invoiced, what they paid, what they still owe and what is overdue.
- Items: Your price list: a code, a name and a unit price. Pick the code on the invoice and the description and price fill in.
- Invoice: The printable A4/Letter invoice: company header, bill-to block with address from the customer list, up to 15 lines, a custom price per line, a discount percentage, tax, total due, due date and payment terms.
- Register: The invoice log. Choose Draft or Sent, type what was paid, and it works out tax, total, balance, due date, days overdue and status. Overdue rows turn red.
- Summary: Invoices by status, total invoiced, collected and outstanding, outstanding by age, invoiced per month, and three charts.
The file opens on a start tab (“Start here”) that explains how to use it.
What makes it different
The invoice and the log live in one file, so the due date comes from the customer's payment terms and the overdue flag from the as-of date you type, with no formulas of your own to maintain. Tax and discount are settings, so the same file works in any currency and under any local tax name.
How do I track invoices in Excel?
Keep one row per invoice in a register: number, date, customer, amount, amount paid and a due date. Status should come from a formula, not from you remembering, so the Register sheet here marks an invoice Draft if you chose Draft, Paid once the amount paid covers the total, Overdue if it is unpaid and the due date is before the as-of date, and Sent otherwise.
On the sample data, 11 invoices have been sent for a total of 26,413.78. 11,518.15 has been collected, 14,895.63 is outstanding and 8,676.40 of that is overdue across four invoices. The Summary sheet splits the overdue part into 1-30 and 31-60 days so you can see which ones to chase first.
Drafts are left out of every total, so an invoice you have not sent yet does not inflate what you are owed.
How do I add tax and a discount to an invoice in Excel?
The Invoice sheet works down the page in the order most invoices do: subtotal, discount, amount after discount, tax, total due. The discount is a percentage you type; the tax rate comes from the Settings sheet, and tax is calculated on the amount after discount and rounded to two decimals.
Rules differ by country and by what you sell, including whether prices include tax and whether tax applies after a discount. Check your local rules and change the tax name and rate on the Settings sheet to match. On the sample invoice, a subtotal of 1,505.00 less a 10% discount gives 1,354.50, tax of 115.13 and a total due of 1,469.63.
How do I set payment terms and due dates?
Type the payment terms in days against each customer (30 for Net 30, 0 for due on receipt). The invoice then shows "Net 30 days" and the due date, and the Register uses the same number to set each invoice's due date.
Because the overdue check compares the due date with the as-of date on the Settings sheet, change that one cell to today's date when you open the file, or to a month-end date to see what was overdue then.
How to use it
- On the Settings sheet type your company details, tax rate, currency symbol, payment instructions and the as-of date (today, or the date you want the overdue check to use).
- On the Customers sheet list each customer with their payment terms in days. On the Items sheet list what you sell with a code and a price.
- On the Invoice sheet type the invoice number and date, pick the customer, then pick an item code and type a quantity on each line. Type a discount percentage if you give one.
- Print the invoice or save it as a PDF. Then copy the invoice number, date, customer, subtotal and discount amount into the next free row of the Register sheet (paste as values).
- In the Register choose Draft or Sent for each invoice and type what the customer has paid. Status, due date and balance fill in; an unpaid invoice past its due date turns Overdue.
- Open the Summary sheet for what is outstanding, how old it is, who owes it and what you invoiced each month.
Before you start:
- Tax is a rate you type on the Settings sheet (for example 8.5% for sales tax, VAT or GST). Check the rate and invoice rules that apply where you trade; this is a simple invoice, not a filed e-invoice.
- Status rules: Draft if you chose Draft; Paid when the amount paid covers the total; Overdue when it is not fully paid and its due date is before the as-of date; otherwise Sent. Drafts are left out of every total on the Summary.
- Due date = invoice date + the customer's payment terms in days (0 means due on receipt).
- Capacity: 300 customers, 300 items, 15 lines per invoice, 300 invoices in the Register.
- Type percentages with the percent sign or as decimals: 10% or 0.1.
Still running this on Excel?
This sheet needs someone to copy every invoice and payment into it. In Accountwiro an invoice and a payment are recorded the moment they happen, and the customer's balance shows in a single statement of account.
| In this sheet | In Accountwiro |
|---|---|
| You copy every invoice and payment into the sheet by hand. | It shows the customer's invoices, payments and outstanding balance in a single statement of account. |
| Invoices are typed or copied by hand. | It creates an invoice by scanning a barcode or searching by product name, calculates discounts and taxes, and sends it to the customer over WhatsApp. |
| Stock shortages are discovered late. | It updates quantities after every sale and alerts you when a product drops below the required level. |
| Anyone who opens the sheet sees every number. | Permissions per user: the sales employee issues invoices, and the warehouse employee manages stock without access to financial data. |
Frequently asked questions
Is this invoice template really free?
Yes. Download it, change it and use it for your business without paying or signing up.
Does it work in Google Sheets and LibreOffice?
Yes. It only uses ordinary formulas (SUMIFS, COUNTIFS, INDEX/MATCH, IFERROR), so it opens in Excel 2016 or later, LibreOffice and Google Sheets. Charts and drop-downs carry over.
Why do I copy invoice figures into the Register instead of it filling in automatically?
An Excel invoice sheet shows one invoice at a time, so it cannot keep the old ones. Pasting the number, date, customer, subtotal and discount as values into the next Register row stores each invoice, and the Register does the rest.
How does it decide an invoice is overdue?
It is not fully paid, you marked it Sent, and its due date is earlier than the as-of date on the Settings sheet. The due date is the invoice date plus the customer's payment terms in days.
Can I use my own currency and tax name?
Yes. Type your currency symbol, tax name (sales tax, VAT, GST...) and tax rate on the Settings sheet. Amounts use plain number formats, so nothing is tied to a currency.
Is the template free?
Yes, with no sign-up and no email address. Use it, edit it and share it as you like.
Does it work in Google Sheets and on mobile?
The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.
What is the difference between the white and the gray cells?
You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.
Can I use the sheet from an AI assistant?
Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html
More free templates for: Shops and stores
More free templates
- Cash Book Template for Excel
- Clothing shop inventory sheet by colour and size
- Customer accounts, credit sales and customer statement sheet
- Cash book and customer accounts sheet
- Installments and collection tracker
- General shop inventory tracker (stock in/out, reorder, slow movers)
- Supplier purchase orders and payables tracker
- Multi-warehouse and branch inventory sheet with transfers
- Periodic stock count and count-difference reconciliation sheet
- Barcode, batch and expiry inventory sheet
- Grocery store management sheet
- Stationery shop and photocopy management sheet
- Electronics shop sheet: serials, warranty and repairs
- Wholesale clothing trade sheet: traders, stock and collection
- Food wholesale and distribution sheet in Excel: routes, expiry and collection
- Garment style costing sheet
- Production tracking sheet by stage
- Piece-rate payroll and worker efficiency sheet
- Production tracking sheet for home textiles factories
- Fabric requirements and marker sheet
- Delivery company sheet: shipments and cash collection
- Student attendance sheet
- Study planner and lesson tracker