Build a simple invoice in Excel with formulas
An invoice where line totals, tax and the grand total work themselves out. Each formula in order, with rounding, a due date and checks that catch mistakes.
An invoice in Excel needs 4 formulas. They are a line total, a SUM for the subtotal, the tax and the total. Type them once. Then the invoice adds itself up every time you change a quantity or a price.
This one is from a small print shop to a café. The shop printed menus and business cards, and billed some design work by the hour. Type the text and numbers below. The empty cells in column D and B3 are where the formulas go.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Invoice date | 1 Feb 2027 | ||
| 2 | Days to pay | 30 | ||
| 3 | Due date | |||
| 4 | ||||
| 5 | Item | Qty | Price | Line |
| 6 | Menus, laminated | 40 | 2.35 | |
| 7 | Business cards (box) | 3 | 18.50 | |
| 8 | Design work (hours) | 2.5 | 34.25 | |
| 9 | Proofreading (hours) | 1.5 | 22.75 | |
| 10 | ||||
| 11 | ||||
| 12 | Subtotal | |||
| 13 | Tax rate | 15% | ||
| 14 | Tax | |||
| 15 | Total |
Step 1: the dates
Type the invoice date in B1. Do not use =TODAY() here. TODAY
changes every day. Open the file next month and the invoice date has moved, and so has the due
date.
To put today's date in as a fixed value, press Ctrl+; in the cell. It types the date and it will not change.
In B3, type =B1+B2. It gives Wednesday 3 March 2027.
A date is a number of days, so adding 30 gives the date 30 days later. Keep the 30 in B2, not inside the formula. A customer with 14 days to pay then needs one change.
Step 2: the line totals
In D6, type =B6*C6 and fill it down to D9. The menus give 94 and
the business cards 55.5.
Now look at the design work. 2.5 hours at 34.25 is 85.625. Formatted with 2 decimal places, it shows 85.63. The proofreading is 34.125, and it shows 34.13.
Here is the problem. The 4 lines on the page add up to 269.26. But
=SUM(D6:D10) gives 269.25, because it adds the real values. The
customer checks with a calculator and finds a penny that is not there.
In D6, type =ROUND(B6*C6,2) and fill it down.
ROUND changes the value, not just what you see. D8 now holds 85.63 and D9 holds 34.13. What the page shows is what SUM adds.
| Menus, laminated | 94.00 |
| Business cards (box) | 55.50 |
| Design work (hours) | 85.63 |
| Proofreading (hours) | 34.13 |
Row 10 is a spare line for the next job. Filled down to D10, the formula shows 0 there. Hide it with an IF:
In D6, type =IF(A6="","",ROUND(B6*C6,2)) and fill it down to
D10.
If there is no item name, the cell shows nothing. SUM ignores the empty text, so the subtotal does not change.
Step 3: subtotal, tax and total
-
In D12, type
=SUM(D6:D10). It gives 269.26. The range includes the spare row, so a fifth item is added without a change to the formula. -
In D14, type
=ROUND(D12*B13,2). It gives 40.39. Without ROUND it is 40.389. -
In D15, type
=D12+D14. It gives 309.65.
The tax rate lives in B13, not in the formula. When the rate changes, you change one cell. The rounding article explains why ROUND beats a number format.
Step 4: checks that catch mistakes
Some mistakes give an error, and you see them. Type "3 boxes" in B7 and D7 shows
#VALUE!, and so does the total. That is annoying but safe.
The mistakes that hurt give no error. Say you add a delivery line in row 10 with a quantity of 1 and forget the price. D10 shows 0. The total stays at 309.65, and the invoice looks fine.
In F6, type
=IF(COUNT(B6:B10)=COUNT(C6:C10),"OK","Check a qty or price").
COUNT counts the cells that hold a number. Every line needs both a quantity and a price, so the two counts must match. With the delivery line and no price, it shows "Check a qty or price". It catches "3 boxes" too, because text is not a number.
Put check cells outside the part you print. Page Layout, then Print Area, sets which cells go on the page.
Is it overdue?
This is where TODAY belongs: in your own list of invoices, not on the invoice. With the due date
in B3, type =IF(TODAY()>B3,"Overdue","").
On Wednesday 10 March 2027, it shows "Overdue".
=TODAY()-B3 gives 7, the days it is late. Both change by
themselves each day. The
dates article explains why a date minus
a date is a number of days.
The next invoice
Right-click the sheet tab, then Move or Copy, and tick Create a copy. Change the date, the items and the quantities. Every formula stays in place.
To send it, save a PDF. Click File, then Save As, and choose PDF as the type. The customer gets the numbers, not the formulas.
The invoice project in the lessons below builds a similar sheet with a discount. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.