How to Create a Purchase Order in Excel
Excel remains the default purchase-order tool for thousands of Indian businesses because it is there, it is free, and the arithmetic is simple enough to automate with four formulas. The challenge is not building the sheet — it is keeping the rate card, the GST computation and the numbering disciplined across months.
This guide gives you the workbook layout, the exact formulas for taxable value and GST, the numbering trick that prevents duplicates, and the checks that keep an Excel PO honest.
If the spreadsheet starts costing more than it saves — or a formula drifts and you only notice at audit — the free Crafex PO generator prints the same document without the maintenance.
The workbook layout that works
Build three sheets: a vendor rate card with one row per item and its agreed rate, a line-item sheet where quantities are entered, and the printable PO sheet with header, items and terms. Three sheets means a rate change happens in one place and every PO picks it up.
- Sheet 1 — Vendors: name, GSTIN, contact, default terms.
- Sheet 2 — Rate card: item, HSN, unit rate, supplier.
- Sheet 3 — PO: header, line items referencing the rate card, GST, totals.
- Print area — set to A4 so the PO never spills onto two pages.
The four formulas a PO needs
Taxable value is quantity times the unit rate. GST is the taxable value times the rate, split into CGST/SGST for intrastate or IGST for interstate. The total is taxable value plus tax. With HSN codes in a lookup, the formula can pull the correct rate automatically.
- Taxable value = quantity × unit rate
- CGST = taxable value × CGST rate
- SGST = taxable value × SGST rate
- Total = sum of taxable values + sum of tax
Automating PO numbering
Give Excel a hand with numbering: store the last PO number in a hidden cell and add one for each new PO, formatted as PO-FY-seq — for example PO-24-0007. Read the number from a fixed reference cell so two people can never type the same number by hand.
Designing the printable PO tab
Set the PO tab to A4 portrait, add the company header and GSTIN, and border the line-item table so the print reads clearly. Put the vendor block and the terms block on the same page — a PO whose signature block sits on page two is a PO that gets signed on page one only.
A worked example in the sheet
Enter 50 units of printed cartons at ₹120 each, HSN 4819, with 18% GST for an intrastate buyer. Taxable value is ₹6,000, CGST ₹540, SGST ₹540, and the PO total ₹7,080. The formulas should land on those numbers exactly — a one-paisa drift means a rate or a formula changed mid-month.
Keeping the PO register alongside Excel
Maintain a fourth tab as the PO register: number, date, vendor, amount, status and the linked invoice number. Monthly, reconcile the open-PO subtotal against committed spend — this one column is the early-warning light for cash flow.
Converting Excel POs to PDF
Print each PO to PDF with the PO number as the filename and email it to the vendor. Store the PDFs in a PO archive folder with the register; the pair is your GST-audit answer sheet.
When to move beyond Excel
Excel scales until the team grows or files multiply. When approvals start happening by WhatsApp screenshots, or the formulas inherit a stale GST rate, switch to a generator that prints a standardised PO and keeps the format locked.
Protecting the workbook from formula drift
Formulas drift in three quiet ways: someone overwrites a formula cell with a typed number, a rate lookup returns the wrong HSN because the code column was edited, or a copied row drags its references past the range and returns zero. The fix is discipline: keep editable cells unfilled and marked, lock the formula and lookup sheets with a worksheet password, and run a sanity check monthly that a known quantity produces the known total.
A simple test keeps Excel honest — enter 10 × ₹100 and confirm ₹1,180 at 18% (or 1,120 at 12%). If the sheet returns anything else, stop and fix before printing the next PO, because a wrong rate on one PO cascades into every monthly reconciliation after it.
- Overwritten formula cells silently freeze a rate.
- Lookup references drift when HSN columns are edited.
- Dragged rows can extend past the table range.
- Monthly known-answer test catches drift early.
Multi-branch POs from one workbook
When a business runs two branches, Excel can still hold one workbook if the PO number encodes the branch — PO-A-0007 and PO-B-0001 — and each branch keeps its own register sheet. The risk is two users editing the shared file simultaneously, so store it on a single shared drive and open it read-only for printing.
The GST angle matters here: an intrastate PO in one state may be interstate from the other branch, so the workbook must recompute CGST/SGST versus IGST from each branch's place of supply rather than assuming one tax route.
A rate-card sheet that stays accurate
The rate card is the sheet that ages fastest: vendors renegotiate, sizes change, GST rates move. Keep one row per item-vendor pair with effective date columns, and have the PO sheet raise an alert when a quantity is keyed without a matching rate-row, so a silent BOM can never pass a stale price.
Review the rate sheet quarterly against the last three invoices from each vendor. When the market price has drifted, update the card before the next PO — the sheet then becomes your negotiating record, not just a lookup.
- One row per item-vendor pair, with effective dates.
- Warn when a line has no matching rate.
- Refresh the card quarterly from recent invoices.
- Freeze edited cells to prevent retyping over formulas.
Printing a PO with a GST-compliant look
The printed PO carries no GST liability, but it should still carry your GSTIN and the vendor's, the HSN against each line, and the tax-inclusive total — the same visual language your invoices use. That consistency makes the PO-invoice three-way match readable to anyone in accounts.
Set the tax block to print CGST and SGST as two lines for intrastate vendors and IGST as one line for interstate vendors, sourced from the vendor sheet's state against the buy-from state in the workbook header.
Grouping lines by HSN on the sheet
Group the line items by HSN code on the PO sheet — all 4819 lines together, all 3926 lines together — and total the taxable value per group. The grouping mirrors exactly how GSTR-2B is reconciled, so the monthly matching task reads directly off the printed PO without a second layout.
When a PO spans multiple HSNs, add a per-group subtotal line before the grand total. The vendor's invoice will typically follow the same grouping; when it does not, the difference is visible at a glance instead of requiring a recalculation.
How to do it, step by step
- 1
Build the three sheets
Create vendor, rate-card and PO sheets; link the PO items to the rate card by VLOOKUP.
- 2
Write the tax formulas
Add taxable value, CGST/SGST or IGST, and the total per line with rounding.
- 3
Set the numbering cell
Store the last PO number in a fixed cell and increment it for each new order.
- 4
Format the print area
Set A4 portrait, border the table, and fit the PO to one page.
- 5
Export and archive
Print to PDF with the PO number as the filename and log it in the PO register.
Frequently asked questions
What is the GST formula for a purchase order in Excel?+
Taxable value is quantity × unit rate. For intrastate, GST splits into CGST and SGST at half the combined rate; for interstate it is IGST at the full rate. Compute tax on the taxable value, never on tax.
How do I auto-number POs in Excel?+
Store the last PO number in a fixed reference cell and use a formula that increments it per new PO, formatted like PO-FY-seq. This prevents duplicate hand-typed numbers.
Can Excel compute GST from HSN codes?+
Yes. Put the HSN-to-rate table in a lookup sheet and use a lookup to pull the rate into the GST formula per line. Keep the table updated when rates change.
Is an Excel purchase order legally valid?+
Yes. There is no layout requirement for a PO in India. As long as it identifies buyer, vendor, items, price and terms, a printed or PDF Excel PO forms a valid commitment when accepted.
How do I print an Excel PO without cutting off columns?+
Set the print area to the PO range, choose A4 portrait and 'Fit to 1 page' before printing. Test once on a real sheet — column-boundary errors show up there, not on screen.
Is there a faster alternative to an Excel PO template?+
Yes. The free Crafex purchase order generator takes line items and HSN codes, computes GST and prints a clean PDF with a terms block — no formulas, no maintenance, no drift.
Start creating with the Purchase Order tool
Create a professional purchase order with GST calculation in minutes.
Free Purchase Order GeneratorKeep reading
Related search terms
Why you can trust this guide
Written by Crafex Procurement Desk (Procurement & Billing Documentation Experts), last reviewed 2026-07-25. We update these guides when statutory rules and formats change. Where Indian regulations apply, we link the official sources below. Verify critical calculations against the current government notifications before relying on them.
A Crafex editorial guide for Indian professionals and businesses.