Excel is the most popular format for proforma invoices when you have multiple line items, variable tax rates, or complex discount structures — because totals, tax, and grand totals can auto-calculate from formulas instead of manual arithmetic. A single formula mistake in a manual calculation can cause a customs valuation dispute or an LC discrepancy.
This guide walks you through building a proforma invoice in Excel from scratch, explains the formulas to use, and shows where to download a free ready-made Excel proforma invoice template.
When to use Excel for a proforma invoice
Excel works best when:
- You have 5+ line items that need auto-calculated totals
- You need variable tax rates per line (e.g. GST at 5%, 12%, 18% for different goods)
- You frequently revise quantities or prices before sending
- You want to keep a spreadsheet database of past proforma invoices
For a quick one-off proforma with a few line items, the free online generator is faster — no software needed, and you get a PDF in under two minutes.
How to set up a proforma invoice in Excel
Step 1 — Page layout
Set the sheet to print as A4 or Letter. Go to Page Layout → Page Setup and set margins to 1.5 cm on all sides. Set orientation to Portrait.
Step 2 — Header block (rows 1–8)
Merge cells A1:F1 and type "PROFORMA INVOICE" in 18pt bold. In column G, right-aligned, enter:
- Proforma No.: PI-2026-0001
- Date: (use =TEXT(TODAY(),"DD MMM YYYY"))
- Valid Until: (add 30 days: =TEXT(TODAY()+30,"DD MMM YYYY"))
Step 3 — Seller and buyer blocks (rows 9–16)
Columns A–C: your company name, address, phone, email, tax ID.
Columns E–G: buyer company name, billing address, contact, their PO reference.
Step 4 — Line items table (rows 18 onwards)
Set up these columns:
| Col | Header | Formula or content |
|---|---|---|
| A | No. | 1, 2, 3… (manual) |
| B | Description | Product/service name (text) |
| C | HS Code | HS/HTS code (text, keep leading zeros with ' prefix) |
| D | Qty | Number |
| E | UOM | PCS, KG, SET, etc. |
| F | Unit Price | Number (format as currency) |
| G | Line Total | =D18*F18 (auto-calculates) |
Step 5 — Totals block
Below the line items table, add these rows:
| Label | Formula |
|---|---|
| Subtotal | =SUM(G18:G27) (adjust range to your line count) |
| Freight | Manual entry (or 0 if included in unit price) |
| Insurance | Manual entry |
| Tax / VAT | =G30*0.05 (replace 0.05 with your tax rate) |
| Discount | =-G30*0.10 (10% discount — make negative) |
| Grand Total | =SUM(G30:G34) (sum all rows above) |
Step 6 — Terms and notes block
Below the totals, add a merged row block with:
- Currency: USD (or your quoted currency)
- Payment terms: 30% T/T advance, 70% before shipment
- Incoterms: FOB Singapore
- Validity: 30 days from date
- Bank details: SWIFT, account number
- Note: "This is a proforma invoice — not a tax or final invoice."
Step 7 — Protect and export to PDF
Lock formula cells to prevent accidental edits: select all formula cells → Format Cells → Protection → Locked. Then Review → Protect Sheet. Export: File → Save As → PDF (or File → Export → Create PDF/XPS).
Never send the editable .xlsx file as the final proforma. Always export to PDF before emailing the buyer.
Common Excel proforma invoice mistakes
- Formatting the HS code as a number — HS codes starting with 0 (e.g. 0901.21) lose the leading zero if formatted as a number. Format the column as Text or prefix with an apostrophe.
- Using = in the Grand Total formula instead of referencing subtotals — always build totals by referencing labeled rows so you can audit the calculation.
- Sending the .xlsx file instead of PDF — the buyer or their bank may edit values. Lock your Excel file and export to PDF every time.
- Leaving currency ambiguous — type "USD 12,500.00" not "$12,500.00" since "$" could mean AUD, CAD, or SGD to an international buyer.
Download a free proforma invoice Excel template
Rather than building from scratch, download our free proforma invoice Excel template — pre-formatted with all formulas, HS code column, Incoterms field, and currency formatting. Also available on our proforma invoice template page in multiple formats.
For an even faster workflow, use the free online generator — no Excel needed. Enter your details, add line items, and download a calculated PDF in under two minutes. The generator also lets you share a short link with the buyer or email the PDF directly.
Frequently asked questions
Can I use Google Sheets instead of Excel?
Yes. The same formulas work in Google Sheets. Use File → Download → PDF Document to export. Google Sheets also lets you share a link to the proforma instead of an attachment — useful for collaborative review before finalizing.
How do I add a drop-down for currency in Excel?
Select the currency cell. Data → Data Validation → List. Enter: USD,EUR,GBP,INR,AED,SGD separated by commas. This prevents typos in the currency field.
What is the correct HS code format in Excel?
Format the column as Text (not Number). Type the HS code as a string: 0901.21 or 7326.90. If you enter it as a number, Excel strips leading zeros and may round the last digits.
Comments (0)
No comments yet. Be the first to share your thoughts!
Leave a comment
Your comment will appear after it's reviewed. Your email is never shown publicly.