The core of the formula is a comparison: if the seller's state equals the place of supply, fill CGST and SGST; otherwise fill IGST. In a sheet that is =IF($B$1=B5, ROUND(C5*D5/2, 2), 0) for CGST and =IF($B$1<>B5, ROUND(C5*D5, 2), 0) for IGST, with the seller's state in B1.
Set up the cells
- B1: the seller's state, chosen from a dropdown.
- Row 4 headings: A Invoice, B Place of supply, C Taxable value, D Rate, E CGST, F SGST, G IGST, H Invoice total.
- B5 downwards: place of supply for each invoice, chosen from the same dropdown.
- E5: =IF(TRIM($B$1)=TRIM(B5), ROUND(C5*D5/2, 2), 0).
- F5: =IF(TRIM($B$1)=TRIM(B5), ROUND(C5*D5, 2)-E5, 0). Subtracting keeps the two halves equal to the total.
- G5: =IF(TRIM($B$1)<>TRIM(B5), ROUND(C5*D5, 2), 0).
- H5: =C5+E5+F5+G5, and copy E5 to H5 down the rows.
How do you make the state dropdown?
Type the state names once in A2 downwards on a sheet called Lists. Then select B1 and the place-of-supply cells, choose Data, Data Validation, List from a range, and enter =Lists!$A$2:$A$40. The dropdown stops spelling variations, which matter because an extra space or a different spelling makes the IF treat the same state as two. TRIM removes stray spaces, and the equals sign in Excel ignores capital letters.
What do four sample invoices give?
Say the seller is in Karnataka:
| Invoice | Place of supply | Taxable value | Rate | CGST | SGST | IGST | Total |
|---|---|---|---|---|---|---|---|
| INV-101 | Karnataka | ₹10,000.00 | 18% | ₹900.00 | ₹900.00 | ₹0.00 | ₹11,800.00 |
| INV-102 | Tamil Nadu | ₹25,000.00 | 18% | ₹0.00 | ₹0.00 | ₹4,500.00 | ₹29,500.00 |
| INV-103 | Karnataka | ₹4,000.00 | 5% | ₹100.00 | ₹100.00 | ₹0.00 | ₹4,200.00 |
| INV-104 | Maharashtra | ₹7,500.00 | 12% | ₹0.00 | ₹0.00 | ₹900.00 | ₹8,400.00 |
The two Karnataka rows split into two equal halves. The other two rows carry a single IGST amount, and the CGST and SGST cells stay at zero.
What if I do not want zeros showing?
Use a number format such as 0.00;-0.00;- or change the formulas to return an empty text with "" instead of 0. Be careful with the second option: a total formula that adds cells containing empty text can return an error, so use SUM(E5:G5) rather than E5+F5+G5 if you go that way.
How do you stop half-filled rows from showing zeros or errors?
Wrap each formula in a test for a blank place of supply, for example =IF(B5="", "", IF(TRIM($B$1)=TRIM(B5), ROUND(C5*D5/2, 2), 0)). Unused rows then stay clean. At the foot of the sheet, =SUM(E5:E24), =SUM(F5:F24) and =SUM(G5:G24) give the total CGST, SGST and IGST, and SUM ignores the empty text, so the summary still works. A tax summary split by type is what you would hand a CA at the end of the month.
How do I test the formulas before trusting them?
Type four test rows: one in the seller's state, one in another state, one with a trailing space after the state name, and one with the place of supply left blank. The first should give two equal halves, the second and third should give IGST (the third only if the spelling really differs), and the blank row should show zeros or nothing. If a row behaves unexpectedly, the cause is almost always the text in the state cell, not the arithmetic.
Does the same sheet work in Google Sheets?
Yes. IF, ROUND and TRIM behave the same, and the dropdown is under Data, Data validation, Dropdown (from a range). The only difference is the menu names, and the formulas can be pasted as they are.
A last check before using the totals
- Add a check column =E5+F5+G5-ROUND(C5*D5, 2); it must be 0 on every row.
- Sort by place of supply to see which invoices went to which tax type.
- A row where both CGST and IGST are filled means a state was mistyped; look for a stray space.
- Treat the figures as working numbers and confirm the treatment of each invoice with your CA before filing.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

