Invoice format in Excel: a simple layout with an optional tax % column

You don't need billing software to send a clean invoice. A well laid out Excel workbook can keep your details in one place, pull customer and item details from lists, add tax if you charge it, and print or save a PDF. This guide builds a simple, worldwide invoice format in Excel step by step.

Note: whether you charge tax, which rate applies and what an invoice must show depend on where you operate. This article covers spreadsheet layout and formulas only. It is not tax, legal or accounting advice.

Step 1: A Settings sheet

Make a sheet called Settings with the details that appear on every invoice:

Cell	Value
B2	Business name
B3	Address
B4	Email
B5	Currency label, e.g. USD
B6	Tax name (for example VAT or Sales tax)
B7	Default tax % (type 0% if you don't charge tax)
B8	Invoice prefix, e.g. INV-
B9	Payment details

Every invoice formula points at these cells, so if your address changes you edit one cell.

Step 2: Customers and Items lists

On a Customers sheet: A = customer name, B = email, C = billing address, D = payment terms in days.

On an Items sheet: A = item or service, B = unit price, C = tax % (leave blank to use the default from Settings).

Turn both into Excel Tables (Ctrl+T) so new rows appear in your dropdowns automatically.

Step 3: The invoice header

On an Invoice sheet:

• F2: a running number you type, e.g. 41
• F3: =Settings!B8&TEXT(F2,"0000") gives INV-0041
• F4: the invoice date
• B6: the customer, as a dropdown (Data > Data Validation > List, pointing at the customer names)
• F5, due date: =F4+IFERROR(INDEX(Customers!$D:$D,MATCH($B6,Customers!$A:$A,0)),30)

If the customer isn't in the list, the due date falls back to 30 days.

Pull the customer's address the same way:

B7: =IFERROR(INDEX(Customers!$C:$C,MATCH($B$6,Customers!$A:$A,0)),"")

Step 4: The line items

Start the item rows at row 10 with these columns:

Col	Field
A	Item (dropdown from the Items list)
B	Qty (you type)
C	Rate
D	Amount
E	Tax %
F	Tax
G	Line total

C10: =IF($A10="","",IFERROR(INDEX(Items!$B:$B,MATCH($A10,Items!$A:$A,0)),0))
D10: =IF($A10="","",ROUND(B10*C10,2))
E10: =IF($A10="","",IFERROR(IF(INDEX(Items!$C:$C,MATCH($A10,Items!$A:$A,0))="",Settings!$B$7,INDEX(Items!$C:$C,MATCH($A10,Items!$A:$A,0))),Settings!$B$7))
F10: =IF($A10="","",ROUND(D10*E10,2))
G10: =IF($A10="","",D10+F10)

The IF($A10="","",…) wrapper keeps unused rows blank. Copy row 10 down for as many lines as you need. These are plain IF, INDEX and MATCH formulas, so they work in older versions of Excel as well as Excel 365.

The tax % column is optional. An item with a blank tax % uses the default; an item with 0% stays at zero, which is handy for items you don't charge tax on.

Step 5: Totals

Below the items (say rows 25 to 27), with the tax label pulled from Settings:

Subtotal:        =SUM(D10:D24)
=Settings!B6:    =SUM(F10:F24)
Total due:       =SUM(G10:G24)

Add the currency label next to the total with ="Total due ("&Settings!B5&")".

Step 6: Keep a register of every invoice

Before you clear the invoice for the next customer, copy its number, date, customer, subtotal, tax and total into an Invoices register sheet, one row per invoice. Add columns for Amount received and Balance (=F2-N(G2)). That register is what you use for monthly totals and for chasing payments. A pivot table on it (Rows: month; Values: sum of total) gives a quick monthly view.

Step 7: Print or save as PDF

Select the invoice area, Page Layout > Print Area > Set Print Area, choose Fit to 1 page wide, then File > Export > Create PDF. Name the file with the invoice number so it's easy to find later.

Common mistakes

1. Hard-coding a tax rate in formulas. Keep rates in Settings and Items.
2. Typing customer details by hand each time. Look them up from the list instead.
3. Overwriting the last invoice without logging it. Add it to the register first.
4. Rounding only the grand total. Round each line's tax so the lines add up to the total.
5. Reusing invoice numbers. A check such as =COUNTIF(A:A,A2)>1 on the register's number column flags duplicates.

Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.

Practical spreadsheet templates for small businesses, made with AI assistance.