How to create a simple invoice in Excel and send it as a PDF

If you work for yourself or run a small business, you probably don’t need a full invoicing program. A well-organized Excel sheet lets you issue clear invoices with no calculation errors and a professional look. The trouble is usually in the details: totals that don’t add up, dates that change on their own, or a PDF that gets split across two pages.

Here you’ll build an invoice from scratch: the layout, the formulas that calculate each line, the tax and the total, how to turn it into a reusable template, and how to send it as a PDF so your client can’t edit it.

What an invoice should include

The required details depend on the tax rules of each country, so always check what your tax authority asks for. Even so, almost every invoice shares these elements:

• Invoice number, sequential and with no gaps.
• Issue date and, if applicable, due date.
• Your details: name or company name, tax ID and address.
• Client details: the same fields as yours.
• Line items: description, quantity, unit price and amount.
• Subtotal, tax applied and total due.
• Payment method: bank transfer, account number or payment terms.

Set up the sheet layout

A good layout saves you from moving cells every time you add a line. This is the structure we’ll use in the example:

1. In rows 1 to 8, place the header: your name at the top left, the invoice number in B3, the date in B5 and the due date in B6.
2. In rows 8 and 9, on the right, enter the client’s details.
3. In row 11, add the line headers: Code, Description, Quantity, Price and Amount (columns A to E).
4. Keep rows 12 to 21 for the invoice lines. Ten rows are enough in most cases.
5. Leave rows 23 to 25 for the subtotal, the tax and the total.

Here’s what the line area looks like with sample data:

The formulas that do the math

Amount for each line

In E12, enter this formula and copy it down to E21. If the line is empty, the cell stays blank instead of showing a zero:

=IF(A12="","",C12*D12)

Subtotal, tax and total

The subtotal is the sum of all line amounts. SUM ignores cells containing empty text, so blank lines cause no problems:

=SUM(E12:E21)

Enter the tax rate in D24 (for example, 21%) and calculate the tax amount in E24, rounded to two decimals to avoid differences of a cent:

=ROUND(E23*D24,2)

Finally, the total in E25:

=E23+E24

With the sample data, the subtotal is 561.00, the tax at 21% is 117.81 and the total is 678.81.

Fill in description and price from a catalog

If you always sell the same products or services, create a sheet named Products with the code in column A, the description in column B and the price in column C. Then, on the invoice, you only need to type the code. In Excel 365 and 2021, use XLOOKUP:

=IFERROR(XLOOKUP(A12,Products!A:A,Products!C:C),"")

In older versions, the equivalent alternative is VLOOKUP:

=IFERROR(VLOOKUP(A12,Products!$A$2:$C$100,3,FALSE),"")

For the description, return column B instead (or change the index 3 to 2 in VLOOKUP).

Invoice number and dates

If you prefer a format like INV-0027, type only the number in B3 and show the full code in another cell:

="INV-"&TEXT(B3,"0000")

For a due date 30 days later, enter this in B6:

=B5+30

Be careful with the issue date. It’s tempting to put =TODAY() in B5, but that function recalculates every time you open the file: if you reopen the invoice a month later, the date will have changed. Type the date manually or press Ctrl + ; to insert today’s date as a fixed value.

Format it and get it ready to print

A readable invoice builds trust. A few tweaks make all the difference:

• Apply currency format, or a number format with two decimals, to the price and amount columns.
• Make the total row bold and add a top border to set it apart.
• Turn off gridlines from the View tab so the sheet looks more like a document.
• Select the invoice range (for example, A1:E30) and go to Page Layout › Print Area › Set Print Area.
• In File › Print, choose the option to fit the sheet on one page so no column gets cut off.

Turn it into a reusable template

Once the invoice is ready, delete the variable data (client, lines, number and date) but keep the formulas. Then:

1. Go to File › Save As.
2. For the file type, choose Excel Template (*.xltx).
3. Give it a clear name, such as “Invoice template”.

Each time you open the template, Excel creates a new workbook without touching the original. Save each invoice with a name that includes the number and the client, for example INV-0027 Client A.xlsx, so you can find it quickly.

One extra tip: lock the cells that contain formulas. Select the cells you do need to fill in, clear the Locked box under Format Cells › Protection, and then turn on Review › Protect Sheet. That way you won’t delete a formula by accident.

Export the invoice to PDF and send it

Never send an invoice as an Excel file: your client could change it by mistake, and the formulas or data from other sheets would be visible. PDF is the standard format. From Excel:

1. Go to File › Export › Create PDF/XPS Document.
2. Under Options, make sure only the active sheet is published, not the entire workbook (otherwise the product catalog would end up in the PDF too).
3. Save the file and check it before sending.

If you’re on a computer without Excel, or the PDF doesn’t come out as expected, you can use our free tool to convert Excel to PDF: upload the file and download the PDF without installing anything. And if you need to send the original workbook to your accountant, you can protect it with a password first.

Common mistakes when invoicing in Excel

• Using TODAY() for the issue date: the date changes every day. Enter it as a fixed value.
• Totals that are off by a cent: this happens when the format shows two decimals but the real value has more. Use ROUND on the tax.
• Numbers stored as text: if you import prices from another system, they may arrive as text and the multiplication returns #VALUE!. Convert them to numbers before using them.
• Duplicate numbering: keep a log of issued invoices on another sheet so you never repeat a number.
• Exporting the whole workbook: check the export options so only the invoice sheet is published.

Conclusion

An Excel invoice only needs a tidy layout and four well-placed formulas: line amount, subtotal, tax and total. If you save it as a template, protect the formulas and always send it as a PDF, you’ll have a simple, reliable invoicing system at no cost. For large volumes or complex tax requirements, consider dedicated software, but to get started Excel is more than enough.

Frequently asked questions