Quotation format in Excel: the fields, the formulas, and a GST sample that adds up
Build a GST quotation format in Excel: the fields to include, a worked sample, and formulas for discount, CGST/SGST or IGST, totals and validity.
Leads — Done with junk leads? Capture OTP-verified buyers from your own site.
A quotation format in Excel is one sheet with four blocks. The header carries your name, address and GSTIN, with the quote number, date and validity. The buyer block carries their name, GSTIN and the place of supply. The item table lists description, HSN/SAC, quantity, unit, rate, discount, taxable value and GST. The footer holds the taxable total, CGST + SGST or IGST, the grand total, the amount in words, the terms and the sign-off. Build the sheet once with formulas, and every quote after that takes a few minutes to fill in and always adds up.
This guide is about building that sheet. If you want the basics first (what a quotation is, how it differs from a proforma invoice, and which fields make a buyer say yes), start with our quotation format guide. This page goes cell by cell: the layout, a worked GST sample, the exact formulas, the three Excel traps that quietly break quotes, and the point where a spreadsheet stops being the right tool.
The layout: every field, mapped to a cell
Excel doesn’t care where things go, but your formulas do, so fix the layout once and keep it. This is the layout behind the sample and the formulas below. The hero figure above shows the same sheet.
| Block | Cells | What goes there |
|---|---|---|
| Your header | A1–A2, B3 | Business name, address and phone; your GSTIN in B3 |
| Quote details | G1–I4 | ”QUOTATION”, the quote number (I2), the date (I3), valid till (I4) |
| Buyer | B5–B8 | Name and address (B5), buyer GSTIN (B6), subject (B7), place-of-supply state code (B8) |
| Item table | A9–J24 | Column headings in row 9, then 15 item rows |
| Totals | J26–J31 | Taxable value, CGST, SGST, IGST, round off, grand total |
| Words and terms | A26, A28–A31 | Amount in words; payment, delivery and warranty terms |
| Sign-off | Below the terms | ”For [your business]: Authorised Signatory” |
Four of these fields need more care in Excel than they would on paper:
- The two GSTINs. A GSTIN is 15 characters. The first two digits are the state code and the next ten are the business’s PAN. That matters in Excel:
LEFT(B3,2)reads your state, so the sheet can work out which tax applies by itself. - HSN/SAC codes. On GST tax invoices, businesses with aggregate turnover up to ₹5 crore in the previous financial year must show 4-digit HSN codes, and those above ₹5 crore must show 6 digits. Businesses up to ₹5 crore may leave the code off invoices to unregistered buyers. A quote isn’t a tax invoice, but if you use the same codes now, the invoice later is a copy, not a rewrite.
- The quote number. The invoice rules require a consecutive serial number of up to 16 characters, unique for the financial year, made of letters, numbers, hyphens or slashes. No such rule covers quotes, but a scheme that also fits the invoice rule keeps your paperwork consistent.
KCS/Q/26-27/041is 15 characters. - The place of supply. This decides the tax type, not the buyer’s address. For goods it is broadly where delivery ends, and for goods installed on site it is where they’re installed. For most services sold to a GST-registered business it is the buyer’s registered location, though services tied to a building follow the building’s location. B8 starts as the state code in the buyer’s GSTIN, and you type over it when the goods go somewhere else. If the buyer has no GSTIN, type in the state code of their address yourself, because a blank B8 would push the whole tax into IGST.
A worked sample: an inter-state GST quote
Here is a complete quote built on that layout: a Coimbatore AC dealer quoting a Bengaluru office for supply, installation and a year of maintenance. Every line is qty × rate less the discount, and the tax and totals tie out. The businesses, GSTINs and prices are made up for illustration.
Kaveri Cooling Solutions · AC sales & service · Coimbatore, Tamil Nadu GSTIN 33ABCDE1234F1Z5
QUOTATION: No. KCS/Q/26-27/041 · Date: 21 Oct 2026 · Valid till: 05 Nov 2026 To: Anand Office Spaces LLP, Whitefield, Bengaluru · GSTIN 29ABCDE5678F1Z2 For: Supply and installation of 5 split ACs with a 1-year maintenance contract · Place of supply: 29 (Karnataka)
Item HSN/SAC Qty Rate (₹) Disc Taxable (₹) IGST 18% (₹) Amount (₹) 1.5 T inverter split AC 8415 4 Nos 38,500 5% 1,46,300 26,334 1,72,634 2 T inverter split AC 8415 1 Nos 46,000 5% 43,700 7,866 51,566 Installation (per unit) 9987 5 Nos 1,500 – 7,500 1,350 8,850 AMC, 1 year (5 units) 9987 1 Job 6,000 – 6,000 1,080 7,080 Total 2,03,500 36,630 2,40,130 CGST: nil · SGST: nil · IGST @ 18%: ₹36,630 · Round off: 0.00 · Grand total: ₹2,40,130 Amount in words: Rupees Two Lakh Forty Thousand One Hundred Thirty Only Terms: 50% advance with PO, balance on installation · Supply and installation within 10 working days of PO · AC warranty as per manufacturer · E.&O.E. For Kaveri Cooling Solutions: Authorised Signatory
Three things in that sample are worth copying:
- IGST, not CGST + SGST. The seller is in Tamil Nadu (state code 33). The ACs are installed in Karnataka (29) and the buyer is registered there, so the goods and the services all have their place of supply in Karnataka. A supply where the supplier and the place of supply are in different states is inter-state, and inter-state supplies carry IGST at the combined rate. Within your own state you would charge CGST and SGST at half the rate each, here 9% + 9%. The formulas below make that switch for you.
- The discount comes off before tax. The invoice rules ask for the taxable value after any discount, so GST is calculated on ₹1,46,300, not on the ₹1,54,000 list price of the four 1.5 T units.
- Each line carries its own rate. Every line here happens to be 18%. Air-conditioning machines (HSN 8415) moved from 28% to 18% on 22 September 2025, and maintenance, repair and installation services other than construction (SAC 9987) are at 18%. Your items may not all share a rate, so the sheet keeps GST % per line instead of applying one rate to the total. Codes and rates are the two things a template can’t check for you. Installation done as part of construction work, for instance, is classed under construction services (heading 9954) rather than 9987. So confirm each item’s code and rate before you add it to your price list.
The formulas, cell by cell
Keep a second sheet called Items with four columns: item name (A), HSN/SAC (B), rate (C) and GST % (D). Turn A10:A24 on the quote into a drop-down list using Data > Data Validation > Allow: List, with the item names as the source. If your item list is an Excel table, the drop-down picks up new items automatically. Then enter these formulas:
| Cell | Formula | What it does |
|---|---|---|
| I4 | =I3+15 | Valid till: the quote date plus 15 days |
| B8 | =LEFT(B6,2) | Place of supply, defaulting to the buyer’s state code |
| B10 | =IF(A10="","",XLOOKUP(A10,Items!A:A,Items!B:B,"")) | Fetches the HSN/SAC code for the item picked in A10 |
| E10 | =IF(A10="","",XLOOKUP(A10,Items!A:A,Items!C:C,"")) | Fetches the rate from your price list (type over it to negotiate) |
| H10 | =IF(A10="","",XLOOKUP(A10,Items!A:A,Items!D:D,"")) | Fetches the item’s GST % |
| G10 | =IF(C10="","",ROUND(C10*E10*(1-F10),2)) | Taxable value: qty × rate, less the discount % in F10 |
| I10 | =IF(G10="","",ROUND(G10*H10,2)) | GST on that line at its own rate |
| J10 | =IF(G10="","",G10+I10) | Line amount including GST |
| J26 | =SUM(G10:G24) | Total taxable value |
| J27 | =IF(LEFT($B$3,2)=LEFT($B$8,2),ROUND(SUM(I10:I24)/2,2),0) | CGST: half the GST if seller and place of supply share a state |
| J28 | =J27 | SGST: always equal to CGST |
| J29 | =IF(LEFT($B$3,2)=LEFT($B$8,2),0,SUM(I10:I24)) | IGST: all the GST if the states differ |
| J30 | =ROUND(SUM(J26:J29),0)-SUM(J26:J29) | Round off to the nearest rupee |
| J31 | =SUM(J26:J30) | Grand total |
Copy the row 10 formulas down to row 24. The IF(…="","",…) wrapper keeps unused rows blank instead of filling them with zeros, and SUM ignores the blanks. ROUND(…,2) keeps every line to the paisa, so your totals match the buyer’s calculator.
A few version and formatting notes:
- XLOOKUP works in Microsoft 365, Excel 2021 and Excel 2024, but not in Excel 2016 or 2019. On those versions use
=IF(A10="","",VLOOKUP(A10,Items!$A:$D,2,FALSE)), changing the 2 to 3 for the rate and 4 for GST %. - Lakh-style commas. To show 240130 as 2,40,130.00 instead of 240,130.00, give the amount cells this custom number format:
[>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00. The square brackets set the conditions and the backslashes print literal commas. It’s built for positive amounts, so leave the round-off cell on the standard format. - State codes below 10. Delhi’s GSTINs start with 07. If you ever type a place-of-supply code by hand, format B8 as Text first so Excel keeps the leading zero.
Three Excel traps that quietly break quotes
- TODAY() in the date cell. It looks clever and it’s wrong for quotes.
TODAY()updates whenever the sheet recalculates, so a quote you reopen next month shows next month’s date, and the validity moves with it. Press Ctrl+; in I3 instead. That types today’s date as a fixed value that never changes. - The amount in words. Excel has no built-in function to spell out a number. You can type the words, which is fine until someone edits a line and forgets to retype them. Or you can add a VBA macro, which means saving the workbook as a macro-enabled .xlsm file, because a normal .xlsx can’t store macro code. The sample macro Microsoft’s support staff share counts in thousands and millions, not lakhs and crores, so test any rupee version against a few totals before you trust it.
- A typed-over formula. Someone types 2,40,000 over the grand total to “round it off”, the formula is gone, and the next quote from that file is wrong without anyone noticing. Unlock only the input cells (Format Cells > Protection, clear Locked), then use Review > Protect Sheet. Without a password anyone can unprotect it again, which is fine. The aim is to stop accidents, not people.
Two smaller ones: don’t quote a single “GST 18%” line on the total when your items carry different rates, and never apply the discount after tax.
Save it as a template and send a PDF
Once the sheet works, stop editing it. In desktop Excel, go to File > Export > Change File Type > Template, and start every quote from that template instead of opening last week’s quote and saving over it.
For each quote, set the print area to just the quote (Page Layout > Print Area > Set Print Area) so only the quote prints, not any notes or working cells beside it. Fit it to one page: in the Page Setup dialog, under Fit to, choose 1 page wide by 1 tall. Then File > Save As and choose PDF. Send the buyer the PDF, not the .xlsx. A PDF keeps your layout exactly as you built it, and the buyer can’t change a rate by accident and send it back to you.
A small extra: add a QR code to the footer that opens a WhatsApp chat with you, so the buyer can reply to the quote in one tap. MoonQR makes one free with no signup and exports a PNG you can paste in with Insert > Pictures.
Excel or a lead inbox: where the spreadsheet stops
A well-built Excel quote is accurate, consistent and quick to fill in. What it can’t do is anything that happens before or after the arithmetic:
- It doesn’t know who asked. The enquiry came in on a call, a WhatsApp message or your website form. The quote file has no link to it, so neither does your follow-up.
- It doesn’t know which quotes are open. Each quote is a separate file, so the only pipeline you have is the one you keep by hand.
- It doesn’t remind you. The validity date in I4 is your best excuse to call back (“your rate holds till Wednesday, shall I lock it?”), and nothing in Excel tells you the date is coming.
If you stay on Excel, add one more sheet, a quote register with one row per quote: quote number, date, buyer, phone, amount, valid till and status. Sort it by valid till every morning and call whoever expires this week. It takes two minutes a day, and it’s the follow-up most quotes never get.
When enquiries arrive faster than you can log them, the quote should start where the enquiry lands, not in a blank sheet. That’s where Leads fits for enquiries from your own website. On its Premium plan, every submission is mobile and email verified, the alert reaches you on WhatsApp, SMS, email and the app within seconds, and the same plan includes instant quotation PDF generation. You quote a real, reachable buyer while they’re still comparing suppliers. Even the free plan exports your leads to Excel, so the spreadsheet can stay as your record. For more on getting enquiry alerts onto your phone, see WhatsApp notifications for website contact forms. If you quote interior or fit-out work from a BOQ, the interior-work quotation format covers the extra fields that trade needs.
The short version
Lay the quote out once: header and GSTIN, quote number, date and validity, buyer GSTIN and place of supply, an item table with HSN/SAC, qty, rate, discount and per-line GST, then the totals, the amount in words and the terms. Put the arithmetic in formulas. Take the discount before tax, keep GST per line, and use an IF on the two state codes to choose CGST + SGST or IGST. Enter the date with Ctrl+;, protect the formula cells, save the file as a template, and send PDFs. Then keep a quote register, because the follow-up before the validity date is the part Excel will never do for you.