Create an Invoice in Excel With Formulas
By ExcelReady TeamUpdated 6 min read
The short answer
To create an invoice in Excel with formulas, keep a price list on its own sheet and use VLOOKUP to bring each item's description and price into the invoice from its code. Multiply quantity by price for each line, add the lines with SUM, work out tax with ROUND so it shows exact cents, and add a due date by adding the payment terms to the invoice date.
Key takeaways
- A price list plus VLOOKUP means you type only a code and a quantity on each line.
- Wrap line totals and tax in ROUND(…,2) so the stored amounts match the cents the customer sees.
- Use IF(A10="","",…) so empty invoice lines stay blank instead of showing #N/A.
- Due date = invoice date + payment days, because Excel dates are numbers.
- Keep the rates (tax, payment days) in their own labelled cells, not typed inside formulas.
On this page+
An invoice in Excel can do all of its own math: you type a product code and a quantity, and formulas fill in the description, price, line total, tax, grand total and due date. This guide builds one with VLOOKUP, ROUND and simple arithmetic. It is for freelancers and small businesses, and for anyone who wants a realistic project that uses the formulas finance roles expect.
What the invoice will calculate#
The invoice has a header, line items and a totals block. You type only a few cells; everything else is a formula.
| Part | You type | Excel works out |
|---|---|---|
| Header | Invoice number, date, customer | Due date |
| Line items | Code and quantity | Description, unit price, line total |
| Totals | Tax rate | Subtotal, tax, total due |
The prices live on a separate sheet, so changing a price in one place updates every new invoice.
Step 1: Make a price list on its own sheet#
Add a sheet called Prices with one row per product or service. The code in column A must be unique, because the invoice looks items up by it.
| A | B | C | |
|---|---|---|---|
| 1 | Code | Description | Unit price |
| 2 | SV-01 | Website audit | 450.00 |
| 3 | SV-02 | Logo design | 320.00 |
| 4 | SV-03 | Copywriting (per page) | 85.00 |
| 5 | SV-04 | Hosting (per month) | 12.99 |
| 6 | SV-05 | Training session (per hour) | 65.00 |
Short codes are faster to type than full names and they don't break when you reword a description.
Step 2: Set up the invoice header and due date#
Put the invoice details at the top of the Invoice sheet:
| A | B | |
|---|---|---|
| 3 | Invoice number | INV-1042 |
| 4 | Invoice date | 3/14/2025 |
| 5 | Payment terms (days) | 30 |
| 6 | Due date | =B4+B5 |
Excel stores dates as numbers of days, so adding 30 to a date gives the date 30 days later: 4/13/2025. Format B6 as a date with Ctrl+Shift+# (Mac: Control+Shift+#) if it shows a number. If the due date must fall on a working day, use =WORKDAY(B4,B5) instead, which counts working days rather than calendar days.
Keeping the 30 in its own cell matters. When a customer gets 14-day terms, you change one cell instead of editing a formula.
Step 3: Pull descriptions and prices with VLOOKUP#
The line items start in row 10, with headers in row 9: Code, Description, Qty, Unit price, Line total. In B10, bring in the description:
=VLOOKUP(A10,Prices!$A$2:$C$50,2,FALSE)VLOOKUP finds the code from A10 in the first column of the price list and returns column 2, the description. FALSE asks for an exact match, which is what you want for codes. In D10, use the same formula with column 3 for the unit price.
The table is locked with $ signs so it stays in place when you fill the formulas down to row 14, and it runs to row 50 so new products are included. The VLOOKUP function guide explains each argument.
Keep empty lines blank#
An invoice has spare lines. With no code in A12, VLOOKUP shows #N/A, which looks bad on a printed invoice. Wrap the lookup in IF:
=IF(A10="","",VLOOKUP(A10,Prices!$A$2:$C$50,2,FALSE))If A10 is empty, the cell shows nothing; otherwise it does the lookup. A mistyped code still shows #N/A, which is useful: it tells you the code isn't on the price list.
Step 4: Calculate line totals with ROUND#
A line total is quantity times unit price. In E10:
=IF(A10="","",ROUND(C10*D10,2))ROUND with 2 keeps the stored value to whole cents. Fill B10:E10 down to row 14. With four lines filled in, the invoice reads:
| Code | Description | Qty | Unit price | Line total |
|---|---|---|---|---|
| SV-01 | Website audit | 1 | 450.00 | 450.00 |
| SV-03 | Copywriting (per page) | 4 | 85.00 | 340.00 |
| SV-04 | Hosting (per month) | 12 | 12.99 | 155.88 |
| SV-05 | Training session (per hour) | 2.5 | 65.00 | 162.50 |
The exercise on this page combines the lookup and the rounding in one formula: =ROUND(B2*VLOOKUP(A2,$F$2:$H$6,3,FALSE),2).
Step 5: Add subtotal, tax and total#
The totals block sits under the line items:
| Cell | Label | Formula | Result |
|---|---|---|---|
| E16 | Subtotal | =SUM(E10:E14) | 1,108.38 |
| E17 | Tax rate | 8.25% (typed) | 8.25% |
| E18 | Tax | =ROUND(E16*E17,2) | 91.44 |
| E19 | Total due | =E16+E18 | 1,199.82 |
SUM skips the blank "" cells in empty lines, so spare rows don't cause errors.
Why round the tax? 1,108.38 × 8.25% is 91.44135. Formatting would show 91.44, but Excel would keep the full number and add it to the total. Across many lines and invoices, those hidden fractions make the shown amounts disagree with the shown total by a cent. ROUND(…,2) stores exactly what the customer sees. The ROUND function guide also covers ROUNDUP and ROUNDDOWN. Check your local tax rules for whether tax is rounded per line or on the subtotal.
Step 6: Make it ready to send#
- Lock the formulas. Unlock only the typed cells (Ctrl+1 / ⌘1 > Protection > untick Locked), then use Review > Protect Sheet. Our guide to protecting a sheet and locking cells shows each step.
- Set the print area. Select the invoice and choose Page Layout > Print Area > Set Print Area, then fit it to one page. See printing an Excel sheet neatly.
- Save each invoice as a PDF with File > Save As and the PDF file type, and keep the workbook as your template.
- Format money with Ctrl+Shift+$ (Mac: Control+Shift+$) so every amount shows two decimals.
An invoice is a compact test of real skills: lookups, rounding, dates and totals. These are the same skills in Excel skills for finance and accounting jobs, and a similar order-and-price-list task often appears in an Excel test for a job interview. If you are wondering how long it takes to learn Excel for a job, a project like this one is a good checkpoint. Next, try a gradebook or a project timeline.
Practise it#
Do the exercise on this page to combine VLOOKUP and ROUND in one line total. Then write your first formulas if multiplying cells is still new, practise VLOOKUP exact match on a price list, and practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The price list is in F1:H6. In C2, work out the line total for the code in A2: the quantity in B2 times the unit price from VLOOKUP (column 3 of the locked table $F$2:$H$6, exact match). Round the result to 2 decimals with ROUND.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Line total | Unit price | |||||||
| 2 | |||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 |
Tip: Start inside out: VLOOKUP(A2,$F$2:$H$6,3,FALSE) is the price. Multiply it by B2, then wrap everything in ROUND(…,2).
Practise this in a lesson
Frequently asked questions
Does Excel have invoice templates?
Yes. Choose File > New and search for "invoice" to see Microsoft's templates. Building your own, as in this guide, means you understand and control every formula.
Why does my invoice total look one cent off?
Formatting hides decimals but Excel still adds the full values. If tax or line totals have more than two decimals, the shown amounts may not add up to the shown total. Rounding each amount with ROUND(…,2) fixes it.
How do I number invoices automatically?
Excel has no built-in invoice counter. The simplest method is to keep a list of issued invoices on another sheet and take the next number from it, for example =MAX(Register!A:A)+1, then save each invoice as a separate file or PDF.
How do I save an Excel invoice as a PDF?
Choose File > Save As (or File > Export on Windows) and pick PDF as the file type. Set the print area first so only the invoice is included.