21. Real-World Project Briefs

21.5 GST Invoice Register

By Ravindra Bagale · Last updated: 6 October 2026

The ask. Amir keeps the sales invoice register for a fictional Pune trading firm registered in Maharashtra. For each invoice he must split tax into CGST + SGST (sale inside Maharashtra) or IGST (sale to another state), and give a summary by tax rate. The rates in this file are practice inputs; always confirm current rates and rules with your CA.

Sheet Invoices, A1:D6 (fictional):

What to deliver (one under the other):

1. Make the range a Table named tblInv.
2. Add CGST and SGST: half the tax each when Place of supply is Maharashtra, else 0.
3. Add IGST: the full tax when Place of supply is another state, else 0.
4. Add Invoice total = Taxable value + CGST + SGST + IGST.
5. Build a summary by rate with SUMIFS (5%, 12%, 18%).
6. Check: total tax = CGST + SGST + IGST = Taxable × rate, row by row.

Key formulas (row 2; copy down or let the Table fill them):

E2: =IF([@[Place of supply]]="Maharashtra",[@[Taxable value (₹)]]*[@[GST rate]]/2,0)
F2: =[@CGST]
G2: =IF([@[Place of supply]]<>"Maharashtra",[@[Taxable value (₹)]]*[@[GST rate]],0)
H2: =[@[Taxable value (₹)]]+[@CGST]+[@SGST]+[@IGST]
Summary: =SUMIFS(tblInv[Taxable value (₹)],tblInv[GST rate],0.18)

Expected result (check your numbers):

Summary by rate

Checks before you send it:

1. Every row has either CGST + SGST or IGST, never both.
2. The summary taxable total equals the register total.
3. The rate column holds numbers (0.18), formatted as %, not the text "18%".
4. Place of supply spellings are consistent (use a drop-down).