Quote Template in Excel: Line Items, Discounts, Tax, Deposit, Validity Date and a Quote Log That Tracks Wins
How to build a quote template in Excel: what a professional quote must include, line-item formulas, discounts, optional sales tax, deposit and payment terms, automatic quote numbers and expiry dates, a quote log with win rate, and how to turn an accepted quote into an invoice, with a worked example.
Quick answer: a quote (or estimate) template in Excel has four parts: a header (your business details, quote number, date, valid-until date, client details), a line-item table (description, quantity, unit, rate, line total), a totals block (subtotal, discount, tax if applicable, total, deposit) and terms (what’s included and excluded, payment schedule, timeline, how to accept). The line total is simply:
=Quantity*Rate, and the total is =Subtotal-Discount+Tax.
Add a quote log on a second sheet and you can also see which quotes are open, which expired and how many you win. The example below is for a freelance web project; all prices are illustrative.
What a professional quote includes
| Section | Contents | Why it matters |
|---|---|---|
| Header | Business name, contact, quote number, issue date, valid until | Easy to reference; prices don’t stay open forever |
| Client | Name, company, project name | Avoids confusion between versions |
| Line items | Clear descriptions, quantity, unit, rate | The client sees what they’re paying for |
| Totals | Subtotal, discount, tax, total | No surprises on the invoice |
| Payment terms | Deposit, milestones, due dates | Protects your cash flow |
| Scope notes | Included, excluded, assumptions, revisions | Prevents scope creep |
| Acceptance | Signature line or “reply to accept” | Turns the quote into an agreement |
Step 1: The header and quote number
- Quote number: pull the next number from your quote log:
="Q-"&TEXT(MAX(QuoteLog[No])+1,"0000"), or include the year:="Q-"&YEAR(TODAY())&"-"&TEXT(COUNTIF(QuoteLog[Year],YEAR(TODAY()))+1,"000"). - Issue date: type it in (not
TODAY(), which would change every time the file opens). - Valid until:
=IssueDate+30, or whatever validity you set. Materials-heavy work often uses shorter validity.
Step 2: Line items and formulas
| Column | Content | Formula |
|---|---|---|
| A | Item / description | Pick from a Services list with data validation |
| B | Quantity | |
| C | Unit (hours, pages, each, project) | |
| D | Rate | =XLOOKUP(A14,Services[Item],Services[Rate],0) |
| E | Line total | =IF(A14="","",B14*D14) |
Keep your standard rates on a Services sheet. Rates then stay consistent across quotes, and a rate change applies to every new quote at once.
Worked example: website project for a small business
| Item | Qty | Unit | Rate | Total |
|---|---|---|---|---|
| Discovery workshop and sitemap | 1 | project | $600 | $600 |
| Page design templates | 5 | templates | $350 | $1,750 |
| Development and setup | 40 | hours | $85 | $3,400 |
| Copy editing | 6 | hours | $60 | $360 |
| Hosting and domain setup | 1 | project | $150 | $150 |
| Subtotal | $6,260.00 | |||
| Discount 5% | −$313.00 | |||
| Sales tax | $0.00 (see note) | |||
| Total | $5,947.00 | |||
| Deposit due on acceptance (40%) | $2,378.80 | |||
| Balance due at launch | $3,568.20 | |||
Totals formulas: subtotal =SUM(E14:E30); discount =ROUND(Subtotal*DiscountPct,2); tax =ROUND((Subtotal-Discount)*TaxRate,2); deposit =ROUND(Total*DepositPct,2). Whether a service or product is subject to sales tax depends on your state and what you sell, so set the tax rate per line or per quote according to the rules that apply to you.
Before you send the quote, check that the price leaves the margin you need; the profit margin calculator shows the math, and for physical products the handmade pricing guide covers materials and labor.
Step 3: Terms that prevent disputes
- Scope: “Includes up to two rounds of revisions per page template. Additional changes billed at $85/hour.”
- Client responsibilities: “Client provides final copy and images by the agreed date.”
- Payment: “40% deposit to schedule the work; balance due on launch; invoices due within 14 days.”
- Validity: “Prices valid until [date].”
- Acceptance: “To accept, sign below or reply in writing.”
Keep standard terms in a cell range you can reuse, and edit only what changes. For larger or riskier projects, a written contract reviewed by a professional is worth it.
Step 4: The quote log
On a second sheet, keep one row per quote: No, Year, Date, Client, Project, Total, Valid until, Status (Sent, Accepted, Declined, Expired), Decision date, Reason lost.
- Expiry flag:
=IF(AND([@Status]="Sent",TODAY()>[@[Valid until]]),"EXPIRED","") - Follow-up list: quotes sent more than 5 days ago without a decision.
- Win rate on decided quotes:
=COUNTIF(QuoteLog[Status],"Accepted")/(COUNTIF(QuoteLog[Status],"Accepted")+COUNTIF(QuoteLog[Status],"Declined")) - Value won:
=SUMIFS(QuoteLog[Total],QuoteLog[Status],"Accepted")
For example, if you sent 12 quotes this quarter, 5 were accepted, 4 declined and 3 are still open, your win rate on decided quotes is 5 ÷ 9 ≈ 56%. Tracking the “reason lost” column (price, timing, chose someone else) shows whether to adjust prices or how you present them.
Step 5: From accepted quote to invoice
When a client accepts, copy the quote’s line items into your invoice template rather than retyping them. A simple approach: keep each quote as its own sheet named after the quote number, and in the invoice sheet use =INDIRECT("'"&QuoteNo&"'!A14")-style references, or simply copy and paste values. Invoice the deposit first, then the balance, referencing the quote number on both invoices.
Common mistakes
- Vague line items (“Website – $6,000”) that invite scope arguments.
- No expiry date, so old prices get accepted months later.
- Forgetting tax or fees and absorbing them yourself.
- No deposit on large or custom work.
- Not following up: many quotes are won on the second contact.
Frequently asked questions
What’s the difference between a quote and an estimate?
People use the words loosely. Generally, a quote is a firm price for a defined scope, while an estimate is an approximate figure that may change. Say clearly on the document which one you’re giving.
Should I send the quote as an Excel file?
Send a PDF (File → Export → PDF) so formatting and formulas can’t be changed, and keep the Excel file as your master copy.
Does this template work in Google Sheets?
Yes. XLOOKUP, SUM, ROUND, COUNTIF and SUMIFS work the same, and you can download the quote as PDF from the File menu.
More templates are in Small business finance.