Excel Invoice Template Generator

About this template

An invoice is a demand for payment, so the workbook is built around three things a customer needs to find in five seconds: who is billing them, what for, and how much. Everything else on the sheet is arithmetic that keeps those figures honest while you edit the lines.

It is aimed at people who bill occasionally and do not want an accounting subscription for it - freelancers, contractors, small studios, a side business sending a handful of invoices a month. One workbook holds one invoice; you copy the sheet for the next one rather than logging into anything.

The generated sheet lays out like this. Row 1 is the INVOICE banner. Your details go in column A, rows 3 to 7. The customer block sits in column C over the same rows. The reference block - invoice number, invoice date, due date - is in columns E and F, to the right of the table, with the two date cells already formatted as yyyy-mm-dd. Row 9 is the table header: Description, Qty, Rate, Amount. Twelve blank item rows follow by default, and the Subtotal, Tax rate, Tax and Total stack sits directly under the Amount column in rows 22 to 25. A wrapped Notes block for payment terms closes the sheet.

The Amount column is a formula on every row, and the Tax figure reads a rate cell rather than a number typed into a formula, so nothing on the sheet has to be recalculated by hand when a line changes.

How to use it

1. Fill in the two address blocks - A4:A7 for your business, C4:C7 for the customer. They are ordinary text cells - overwrite the placeholder lines, and delete any you do not need rather than leaving them reading Address line 2.
2. Set the reference block - The invoice number goes in F3. F4 and F5 are the invoice date and the due date and are formatted as dates, so type a real date and Excel will store it as one; a due date typed as text will not sort or subtract later.
3. Enter one line per thing you are billing for - Description in column A, quantity in B, rate in C. The Amount in column D fills itself in as soon as B and C both hold numbers, and stays empty until then so an unused row does not print a zero.
4. Set the tax rate once, in the sheet - D23 is percent-formatted: type 20 for twenty percent and the Tax cell below multiplies the subtotal by it. Generating with the tax line switched off removes both rows and makes the Total equal the subtotal.
5. Add item rows without breaking the subtotal - Right-click a row inside the item block - anything between the first and last item row - and choose Insert. SUM(D10:D21) stretches to cover the new row automatically. The new row is genuinely blank, so copy the Amount cell down from the row above to give it the formula.
6. Send it, then keep it - File > Export or Save As with PDF selected produces the copy you email; keeping the .xlsx means the next invoice is a copied sheet with a new number rather than a rebuild.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =IF(COUNT(B10,C10)=2,B10*C10,"") - The Amount cell on every item row. COUNT returns how many of the two cells hold numbers, so the multiplication only runs when both quantity and rate are filled in; otherwise the cell shows an empty string rather than a zero.
• =SUM(D10:D21) - The Subtotal, covering exactly the item rows you asked for - twelve by default. The empty strings the unfilled rows return are ignored by SUM, which is why a half-used invoice still totals correctly.
• =D22*D23 - The Tax line: subtotal multiplied by the rate cell above it. Because the rate lives in a cell rather than inside the formula, changing it is a single edit and the audit trail stays visible on the invoice.
• =D22+D24 - The Total: subtotal plus tax. Generate without the tax line and this becomes =D22, so the Total always sits in the same place on the sheet whichever way you build it.

Customising it

• Add a discount line by inserting a row just above the Total, labelling it in column C, and putting something like =-D22*0.1 in column D. Excel repoints the Total formula as the rows shift; extend it to add the new cell.
• Drop a logo in with Insert > Pictures and position it over the banner row, which is 44 pixels tall and merged across the table width.
• Change the currency by selecting the money column and using Format Cells > Currency. The stored values are plain numbers, so nothing recalculates.
• Set a print area of A1:F29 under Page Layout so the reference block in columns E and F prints with the table rather than spilling onto a second page.
• Turn payment terms into a calculation: with the invoice date in F4, put =F4+30 in F5 and the due date follows the invoice date automatically.