Many small shops start billing in Excel because it is free and familiar. A good bill format in Excel needs the right columns, a few formulas and a fixed invoice number series. This guide shows the layout, the exact formulas for GST and the limits you will hit as the shop grows.
Columns for a simple bill in Excel
| Column | Example | Formula (row 12) |
|---|---|---|
| A: S.No | 1 | — |
| B: Item | Cement OPC 53 | — |
| C: HSN | 2523 | — |
| D: Qty | 50 | — |
| E: Unit | Bag | — |
| F: Rate (₹) | 320 | — |
| G: Taxable value | 16,000 | =D12*F12 |
| H: GST % | 18% | — |
| I: CGST | 1,440 | =G12*H12/2 |
| J: SGST | 1,440 | =G12*H12/2 |
| K: Line total | 18,880 | =G12+I12+J12 |
For a sale to another state, replace CGST and SGST with one IGST column: =G12*H12.
Header and footer of the bill
- Top (rows 1–10): shop name, address, phone, GSTIN, the words "Tax Invoice", invoice number, date, buyer name, address and GSTIN, place of supply.
- Bottom: totals with
=SUM()for taxable value, CGST, SGST and grand total; round-off with=ROUND(total,0); amount in words; bank or UPI details; signature.
The fields a GST bill must carry are listed in our GST invoice format guide.
Step by step: make the template once
- Create the header, item table and totals on one sheet and set the print area to A4.
- Lock formula cells (Format Cells → Protection) so staff do not overwrite them.
- Add a second sheet "Items" with item name, HSN, unit, rate and GST %. Use
VLOOKUPorXLOOKUPso typing an item fills HSN, rate and GST %. - Keep a third sheet "Register" and copy each bill's number, date, buyer, taxable value and tax there. Your CA needs this for GSTR-1.
- Save each bill as PDF with the invoice number as the file name.
Invoice number rules
Under GST the invoice number must be consecutive, unique for the financial year and up to 16 characters. Start a new series on 1 April, for example MB/26-27/0001. Never reuse a number, even for a cancelled bill.
Where Excel billing breaks down
- No stock tracking: the bill does not reduce stock, so you cannot see low stock or dead stock.
- Manual numbering: two staff can create the same invoice number.
- No udhaar ledger: payments and balances sit in a separate notebook.
- Formula errors: one wrong cell changes tax on every bill after it.
- GST returns: you still have to copy every bill into a register for your CA.
Frequently asked questions
Is a bill made in Excel valid under GST?
Yes, if it has all the fields required by Rule 46 and a proper invoice number series. The software does not matter; the content does.
How do I calculate GST in Excel?
Taxable value × GST rate. For 18%: =G12*18%. Split it in half for CGST and SGST on sales within your state.
Can I add multiple GST rates in one Excel bill?
Yes. Keep a GST % column per line and add a small summary that totals tax for each rate. See multiple GST rates in one invoice.
Is there a free alternative to Excel billing?
A free billing app like MaterialBill does the same work with automatic numbering, stock and udhaar tracking, and shares bills on WhatsApp.
Skip the formulas: bill in 1 minute
MaterialBill fills these fields for you: GSTIN, HSN, CGST/SGST or IGST split, invoice number series and total in words. Bills are shared on WhatsApp or printed on A4 or thermal paper.
Download the MaterialBill app from Google Play (free Android billing app) or start free on the web.