Excel Invoice & Quotation Guide — Auto-Calculate Totals, Tax & Create Professional Documents

A quotation is a formal document that presents pricing terms to a potential client before a transaction is confirmed. A well-structured quotation builds trust, prevents disputes, and sets clear expectations. This guide walks you through creating professional quotations and invoices in Excel from scratch.

What Is a Quotation and Why Does It Matter?

A quotation (also called a quote or estimate) is a pre-sale document that outlines the products or services you offer, along with their prices and terms. Unlike a contract, it is not legally binding in most jurisdictions, but it serves as the foundation for the entire transaction lifecycle — from initial agreement to final invoice.

Key purposes of a quotation:

• Formally present pricing and scope to a client
• Serve as an internal approval document for purchasing departments
• Act as the basis for purchase orders, invoices, and receipts

Required Fields in a Professional Quotation

Every quotation should include the following elements:

Field	Description	Example
Quote Number	Unique document identifier	QT-2026-001
Date	Date of issue	2026-03-31
Validity Period	How long the quote is valid	15 days from issue
Supplier Info	Company name, address, tax ID	—
Client Info	Client name, contact person	—
Line Items	Product/service description	Website Development
Quantity	Number of units	1
Unit Price	Price per unit (excl. tax)	$5,000.00
Subtotal	Quantity × Unit Price	$5,000.00
Tax	VAT/GST/Sales Tax	$500.00
Total	Subtotal + Tax	$5,500.00

Building a Quotation in Excel Step-by-Step

Step 1: Page Setup for A4 Printing

Go to Page Layout and set the paper size to A4 (or Letter if you are in the US). Set margins to 2 cm on all sides. Define column widths — a practical layout is: Column A (Item No.) at 4 cm, Column B (Description) at 16 cm, Column C (Qty) at 4 cm, Column D (Unit Price) at 6 cm, and Column E (Amount) at 6 cm. Setting the print area early prevents formatting issues when exporting to PDF.

Step 2: Auto-Calculation Formulas for Line Items

For each row in your item list, use a simple multiplication formula:

Amount (Column E) = Quantity (Column C) × Unit Price (Column D)
=C5*D5

Copy this formula down for all item rows.

Step 3: Tax Calculation

Below the subtotal row, add tax calculation. The tax rate varies by country — 10% VAT in Korea and Japan, 20% in the UK, or varying state sales tax in the US:

Subtotal: =SUM(E5:E20)
Tax (10%): =E21*0.1
Grand Total: =E21+E22

Adjust the tax rate to match your jurisdiction. If you deal with multiple tax rates, add separate rows for each.

Step 4: Amount in Words (Optional)

Some business cultures require the total amount spelled out in words (e.g., “Five Thousand Five Hundred Dollars Only”). You can use Excel’s SPELLNUMBER function (available in some versions) or a custom VBA macro for this purpose.

Quote Number Management

Systematic numbering makes document tracking easy:

• QT-2026-001: Prefix (QT for Quote) + Year + Sequential Number
• INV-20260331-01: Prefix + Date + Daily Sequence

Maintain a master log in a separate sheet or spreadsheet. Each time you create a new document, increment the last number by one. For high-volume businesses, consider using separate numbering sequences per year.

The Document Flow: Quotation → Invoice → Receipt

Understanding how business documents connect helps you maintain consistency:

1. Quotation — Present pricing to the client
2. Purchase Order — Client confirms the order
3. Delivery / Service — You fulfill the order
4. Invoice — You request payment
5. Receipt / Tax Invoice — Payment confirmation and tax documentation

Since quotations and invoices share nearly identical structures, a well-designed quotation template can be converted to an invoice by simply changing the title and a few fields.

Setting the Validity Period

Choose a validity period based on your industry and risk:

• 7 days: Manufacturing with volatile raw material prices, imported goods subject to exchange rate fluctuations
• 15 days: General services, IT projects, consulting
• 30 days: Large-scale projects, government tenders, long-term contracts

Always include a validity date. Without one, a client could accept a months-old quote when your costs have changed significantly.

PDF Conversion and Email Tips

Never send editable Excel files to clients — always convert to PDF first.

• How to convert: File → Save As → select “PDF” as the format
• File naming: “Quotation_QT-2026-001_ClientName.pdf”
• Email subject line: “[Quotation] Project Name — Valid until April 15, 2026”

Free Templates

Skip the manual setup and use our ready-made templates:

• Quotation Template — Fill in your company details and start quoting immediately
• Invoice Template — Matching invoice format for seamless document flow
• Transaction Statement Generator — Auto-calculate totals and tax with just line item inputs

Frequently Asked Questions

Do I need a signature or stamp on my quotation?

In most countries, quotations do not legally require a signature. However, adding a digital signature or company stamp increases credibility, especially in East Asian business cultures where stamps (chops) carry significant weight.

Can I revise a quotation after sending it?

Yes. Issue a revised quotation with a revision suffix — for example, QT-2026-001-R1. Always clearly state that the new version supersedes the previous one, and reference the original quote number.

How do I handle multi-currency quotations?

Specify the currency code (USD, EUR, GBP, etc.) clearly on the document. State the exchange rate reference date and whether the rate is fixed or will be adjusted at the time of payment. For international transactions, also clarify which party bears the exchange rate risk.

What is the difference between a quotation and a proforma invoice?

A quotation is a price proposal sent before any agreement. A proforma invoice looks like a real invoice but is issued before delivery — it is commonly used for customs, import/export, or advance payment requests. Both are non-binding, but a proforma invoice implies a stronger commitment to the stated terms.