DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on Originally published at ashishexcelnotes.hashnode.dev

How CGST/SGST vs IGST logic works in an Excel invoice template (with formulas)

#excel #spreadsheet #tutorial #productivity

Many small businesses in India still make invoices in Excel. The one thing most DIY sheets get wrong is the tax split. Within your own state, GST is split into CGST + SGST. Across states, it's a single IGST. Here's a simple way to make Excel decide that automatically, using plain formulas that work in older Excel versions too.

This is about spreadsheet logic, not tax advice. GST rates, place-of-supply rules and what an invoice must show depend on your situation, so check them with your CA.

1. Store your state once

Make a Settings sheet and type your state in one cell, say Settings!B4 = Maharashtra. Every invoice formula points at this cell, so you never type it twice.

2. Give every customer a state

On a Customers sheet, use columns A = name, B = GSTIN, C = state. Use a data-validation dropdown for state, or keep a fixed list. The split works by comparing text, so Maharashtra and Maharastra count as different states.

3. The invoice log

On an Invoices sheet, one row per invoice: B = date, C = customer, E = taxable value, F = GST rate. Put the rates (0%, 5%, 12%, 18%, 28%) in a list you can edit and use it as a dropdown, so you can change them later without touching any formulas.

Look up the customer's state (G2):

=IF($B2="","",IFERROR(INDEX(Customers!$C$2:$C$101,MATCH($C2,Customers!$A$2:$A$101,0)),"?"))
Enter fullscreen mode Exit fullscreen mode

? shows up if the customer isn't in the list, so the mistake is easy to spot.

Decide the supply type (H2):

=IF($B2="","",IF(G2=Settings!$B$4,"Intra-state","Inter-state"))
Enter fullscreen mode Exit fullscreen mode

Split the tax (I2 = CGST, J2 = SGST, K2 = IGST):

I2: =IF($B2="","",IF(H2="Intra-state",ROUND(E2*F2/2,2),0))
J2: =IF($B2="","",I2)
K2: =IF($B2="","",IF(H2="Inter-state",ROUND(E2*F2,2),0))
Enter fullscreen mode Exit fullscreen mode

Total (L2): =IF($B2="","",E2+I2+J2+K2)

Quick check: ₹20,000 at 18% within your state comes to CGST ₹1,800 + SGST ₹1,800, a total of ₹23,600. The same sale to another state gives IGST ₹3,600, the same ₹23,600 total. If those two totals ever differ, a formula is wrong.

Rounding each half separately can, for some amounts, make CGST + SGST differ from IGST by a paisa. Decide with your CA how you want to round.

4. Why every formula starts with IF($B2="","",…)

Fill the formulas down 500 rows ahead of time. If they aren't wrapped, empty rows show 0, ? or errors, and your totals and charts get messy. Using the date cell as the "is this row in use?" check keeps unused rows truly blank.

5. Bonus: the invoice log becomes a receivables tracker

Add columns for due date (date + credit days), amount received and:

Balance:      =IF($B2="","",L2-N(O2))
Days overdue: =IF($B2="","",IF(AND(P2>0,TODAY()>N2),TODAY()-N2,0))
Enter fullscreen mode Exit fullscreen mode

Then a per-customer summary with SUMIFS on balance and days overdue gives you 0–30 / 31–60 / 61–90 / 90+ buckets. That's your follow-up list for the week.

6. A print sheet from the same data

Put an invoice number in one cell. Every field on a printable layout then becomes INDEX(Invoices!col, MATCH(number, Invoices!A:A, 0)). You keep one source of data and print any invoice from it.

Common mistakes

  • Typing the state differently in different places. Use dropdowns.
  • Hard-coding 18% inside formulas. Keep rates in cells.
  • Mixing taxable value and total in the same column.

I made this setup, and drafted this article, with AI tools, then reviewed it before publishing. It's Excel only and isn't tax or legal advice.

Top comments (0)