GST invoice format in Excel: a simple layout with automatic CGST/SGST or IGST

Plenty of small businesses in India make GST invoices in Excel instead of paying for billing software. That works fine if the sheet is laid out sensibly: one place for your details, one list of customers and items, and an invoice sheet that pulls from both. This guide walks through a practical GST invoice format in Excel, step by step.

Note: what a GST invoice must contain, which rate applies and how place of supply works depend on your business and current rules. This article covers spreadsheet layout and formulas only. It is not tax or legal advice; check the requirements with your CA or the official GST sources.

Step 1: A Settings sheet

Make a sheet called Settings and fill in the details that appear on every invoice:

Every invoice formula points at these cells, so if your address changes you edit one cell.

Step 2: Customers and Items lists

On a Customers sheet: A = customer name, B = GSTIN (blank if unregistered), C = state, D = billing address.

On an Items sheet: A = item or service, B = HSN/SAC code, C = unit price, D = GST rate (for example 18%).

Turn both into Excel Tables (Ctrl+T). Tables grow automatically, so new customers and items show up in your dropdowns without editing ranges.

Keep state names consistent. The tax split compares text, so "Karnataka" and "Karnatka" count as different states. A dropdown from a fixed state list avoids this.

Step 3: The invoice header

On the Invoice sheet, lay out the top block:

• Seller block: =Settings!B2, =Settings!B3, ="GSTIN: "&Settings!B5
• Invoice number (F3): type a running number in F2 (e.g. 41) and use =Settings!B6&TEXT(F2,"0000") to get INV-0041
• Invoice date (F4): type the date; format as dd-mm-yyyy
• Customer (B8): a dropdown via Data > Data Validation > List pointing at the customer name column

Pull the customer's details from the list:

B9:  =IFERROR(INDEX(Customers!$D:$D,MATCH($B$8,Customers!$A:$A,0)),"")
B10: =IFERROR(INDEX(Customers!$B:$B,MATCH($B$8,Customers!$A:$A,0)),"")
B11: =IFERROR(INDEX(Customers!$C:$C,MATCH($B$8,Customers!$A:$A,0)),"")

B9 is the address, B10 the customer GSTIN and B11 the place of supply (state).

Step 4: The line-item table

Start the item rows at row 15 with these columns:

Lookups for code, rate and GST rate:

C15: =IF($B15="","",IFERROR(INDEX(Items!$B:$B,MATCH($B15,Items!$A:$A,0)),""))
E15: =IF($B15="","",IFERROR(INDEX(Items!$C:$C,MATCH($B15,Items!$A:$A,0)),""))
G15: =IF($B15="","",IFERROR(INDEX(Items!$D:$D,MATCH($B15,Items!$A:$A,0)),""))
F15: =IF($B15="","",ROUND(D15*E15,2))

Step 5: Let Excel choose CGST/SGST or IGST

Compare your state with the customer's state. Same state: split the tax in half. Different state: all of it is IGST.

H15: =IF($B15="","",IF($B$11=Settings!$B$4,ROUND(F15*G15/2,2),0))
I15: =IF($B15="","",IF($B$11=Settings!$B$4,ROUND(F15*G15/2,2),0))
J15: =IF($B15="","",IF($B$11<>Settings!$B$4,ROUND(F15*G15,2),0))
K15: =IF($B15="","",F15+H15+I15+J15)

Copy row 15 down for as many lines as you need (say 15 rows). These are plain IF and INDEX/MATCH formulas, so they work in older Excel versions as well as Excel 365.

Step 6: Totals and amount in words

Below the items:

Taxable total: =SUM(F15:F29)
CGST total:    =SUM(H15:H29)
SGST total:    =SUM(I15:I29)
IGST total:    =SUM(J15:J29)
Grand total:   =ROUND(SUM(K15:K29),0)

Excel has no built-in function for amounts in Indian words. Either type the words, or show the figure with lakh-style grouping:

="Rupees "&TEXT(K31,"#,##,##0")&" only"

Add a short tax summary by rate if you use more than one rate, with SUMIF:

=SUMIF($G$15:$G$29,18%,$F$15:$F$29)

Step 7: Keep a register of every invoice

Before you clear the invoice for the next one, copy its key values (number, date, customer, taxable value, CGST, SGST, IGST, total) into an Invoices register sheet, one row per invoice. That register is what you'll use for monthly totals and for chasing payments. A pivot table on it (Rows: month; Values: sums of each tax column) gives a quick monthly summary.

Step 8: Print or save as PDF

Select the invoice area, Page Layout > Print Area > Set Print Area, set Fit to 1 page wide, and use File > Export > Create PDF. Name the file with the invoice number so it's easy to find later.

Common mistakes

1. Hard-coding 18% in formulas. Keep rates in the Items list.
2. Typing customer details by hand each time. That's where GSTIN typos creep in.
3. Overwriting the last invoice without logging it. Always add it to the register first.
4. Rounding only the grand total. Round each tax line too, so totals match line by line.
5. Reusing invoice numbers. A COUNTIF on the register's number column (=COUNTIF(A:A,A2)>1) flags duplicates. This article was drafted with AI tools. It's general spreadsheet guidance for Excel, not tax or legal advice.