How to Make a Pro Forma Invoice in Excel: Template With Formulas

shape
shape
shape
shape
shape
shape
shape
shape
How to Make a Pro Forma Invoice in Excel: Template With Formulas - featured image

A pro forma invoice is a preliminary invoice that a seller sends before goods or services are delivered. It describes what will be sold, at what price and on what terms, so the buyer can approve the order, arrange payment, apply for an import license or clear customs. It is not a request for payment in the same way as a final invoice, and it is not normally recorded as a sale in the accounts. Excel is an easy way to create one, because formulas calculate the line totals, the discount, the tax and the grand total for you. This guide shows the layout, the formulas, the extra details that international buyers need and the difference from other documents. A small example with numbers you can check by hand is used throughout.

Updated November 2026

In this guide

  1. What a pro forma invoice is for
  2. Pro forma, quote and commercial invoice
  3. What to include
  4. Example data
  5. Step 1: set up the header
  6. Step 2: create the items table
  7. Step 3: calculate the subtotal
  8. Step 4: add a discount
  9. Step 5: add shipping and tax
  10. Step 6: calculate the grand total
  11. Step 7: add payment terms and notes
  12. Step 8: prepare for printing and sending
  13. Automatic numbering and lookups
  14. Common mistakes
  15. Practice problems
  16. Quick recap

What a pro forma invoice is for

A pro forma invoice is used in four common situations. A buyer may need it to approve a purchase inside the company. A customer may need it to pay in advance by bank transfer. An importer may need it to apply for a license or a letter of credit. A customs office may use it to estimate duty on a shipment before the final invoice is ready. Because it is an estimate, it often has a validity date, after which the prices may change. It should clearly say Pro Forma Invoice at the top, so no one mistakes it for a final tax invoice.

Pro forma, quote and commercial invoice

A quote or quotation offers a price and is usually sent before the buyer has decided. A pro forma invoice looks like an invoice and is sent when the buyer is ready to buy, but before delivery. A commercial invoice is the final document issued when the goods are shipped or the work is done, and it is the one that records the sale and requests payment. The three documents often share the same layout, so you can build a single Excel file and change the title. Always check the rules of your country, because tax and customs requirements differ.

What to include

Include your business name, address, contact details and tax number at the top, and the buyer details below. Add a document title, a pro forma number, the issue date and a validity date. Then list the items with a description, quantity, unit price and line total. Below the items, show the subtotal, any discount, shipping, tax and the grand total. For export orders, add the currency, the incoterm such as FOB or CIF, the country of origin, the weight and the harmonized code of the goods. Finish with payment terms, bank details and a short note.

Example data

A small business sells three items. Item one is 20 units of cotton fabric at 12 each. Item two is 10 units of thread boxes at 8 each. Item three is 5 units of scissors at 15 each. The line totals are 240, 80 and 75, so the subtotal is 395. The seller gives a 10 percent discount, which is 39.50, charges 25 for shipping and adds 5 percent tax on the discounted amount plus shipping. The expected grand total is calculated step by step below, so you can check each formula.

Step 1: set up the header

Type the business name in A1 with a large bold font and put the address and contact details below it. In the right block, type PRO FORMA INVOICE, the number in a cell such as F2, the date in F3 and the validity date in F4. Use the formula =F3+30 for the validity date if offers last 30 days. If the date is 1 March 2027, the formula gives 31 March 2027. Below, type Bill To and the buyer name and address. Merge and center the title cells for a clean look, but keep the data cells unmerged.

Step 2: create the items table

In row 12 type the headers No, Description, Quantity, Unit price and Amount. Enter the three items below. In the Amount column of the first item, type =C13*D13, which gives 20 times 12, or 240. Fill it down, and the other two lines give 80 and 75. If the quantity or price is empty, the amount shows 0, so you can leave spare rows for extra items. Format the price and amount columns with two decimals and a thousand separator, and add borders to the table.

Step 3: calculate the subtotal

Under the table, type Subtotal and use =SUM(E13:E20), where E13:E20 covers the item rows including the spare ones. The result is 240 plus 80 plus 75, which is 395. Using a range that includes empty rows means that you do not have to change the formula when you add an item. If you prefer a table, press Ctrl+T and use a total row, as explained in our table guide. Keep the subtotal in a cell that other formulas can point to.

Step 4: add a discount

Type the discount rate, 10 percent, in a cell and label it Discount rate. The discount amount is =E21*B22, assuming E21 holds the subtotal and B22 the rate, which gives 395 times 10 percent, or 39.50. The amount after discount is the subtotal minus the discount, which is 355.50. Show the discount as a negative number if you want it to read as a deduction. A separate rate cell lets you change the discount for each customer without editing a formula.

Step 5: add shipping and tax

Type the shipping cost, 25, in its own line. The taxable amount in this example is the discounted amount plus shipping, which is 355.50 plus 25, so 380.50. With a tax rate of 5 percent, the tax is =380.5*0.05, which is 19.025, and it rounds to 19.03. Use ROUND to avoid fractions of cents: =ROUND(F24*B25,2). Tax rules differ by country, and some do not tax shipping or exports, so apply the rule that fits your business. Our rounding guide explains the ROUND function.

Step 6: calculate the grand total

The grand total is the amount after discount, plus shipping, plus tax. In our example that is 355.50 plus 25 plus 19.03, which is 399.53. In Excel, use =E23+E24+E25 with each cell holding one of the parts. Format it in bold with a top border, and show the currency, such as USD, so there is no confusion. Check the total by adding the parts on a calculator once, and compare it with the sheet before you send the document to a customer.

Step 7: add payment terms and notes

Below the totals, add the payment terms, for example 50 percent in advance and the rest before shipping, and the bank details of the account that should receive payment. Add a line that says this document is a pro forma invoice and not a demand for payment, and that prices are valid until the date shown. For export orders, add the incoterm, the country of origin and the estimated shipping date. A note on the lead time helps the buyer plan. Keep the notes short, in a small font, at the bottom of the page.

Step 8: prepare for printing and sending

Set the print area to the invoice, choose Fit to 1 page wide in Page Layout and preview it. Save the file as a PDF, so the buyer cannot change the numbers by accident. Name the file with the pro forma number and the customer name, such as PF-0001 Acme. Keep an Excel copy for your records and for creating the final invoice later. When the order is confirmed, copy the sheet, change the title to Invoice and the number to the new invoice number, and keep the same figures if nothing has changed.

Automatic numbering and lookups

For many customers, keep a Customers sheet and a Products sheet. Use a drop-down to choose the customer, and fill the address with XLOOKUP. Use the same method for the description and the unit price, so you only type the product code and the quantity. Our XLOOKUP guide explains the formula. For numbering, keep the last number in a cell and use a formula that adds 1 for each new document, then copy the value so it does not change. This saves time and keeps the document numbers in order.

Common mistakes

The most common mistake is to call the document an invoice without saying Pro Forma, which can cause problems with tax records. Another is to forget the validity date. A third is to leave the tax number or the currency off an export document. A fourth is to hard-code totals instead of using formulas. A fifth is to round each line and the total in different ways, so the numbers do not add up. A sixth is to treat the pro forma as a sale in the accounts. Record the sale only when the final invoice is issued.

Practice problems

Try these. Problem 1: 20 units at 12 each is 240. Problem 2: a subtotal of 395 with a 10 percent discount leaves 355.50. Problem 3: add shipping of 25 to get 380.50, then 5 percent tax of 19.03, so the total is 399.53. Problem 4: if the date is 1 March 2027 and the validity is 30 days, the offer ends on 31 March. Problem 5: change the discount to 5 percent and check that the discount is 19.75, the amount after discount is 375.25, the tax is 20.01 and the new total is 420.26.

Quick recap

Create a header with the title Pro Forma Invoice, a number, the issue date and a validity date. Build an items table with quantity, unit price and a line amount formula. Add the subtotal, the discount, the shipping, the tax and the grand total with formulas, and check the final figure by hand. Add payment terms and notes, save as a PDF and keep the Excel file for the final invoice. Use lookups for products and customers if you issue many documents.

Frequently asked questions

What is a pro forma invoice?

It is a preliminary invoice sent before delivery to confirm the price and terms of an order.

Is a pro forma invoice a legal demand for payment?

Not usually. It is an estimate, and the final invoice is the one that records the sale.

How do I make a pro forma invoice in Excel?

Create a header, an items table with line total formulas, and totals for subtotal, discount, tax and shipping.

What is the difference between a quote and a pro forma invoice?

A quote offers a price, and a pro forma invoice is sent when the buyer is ready to buy.

Do I need a validity date?

It is good practice, because prices can change.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *