GST Calculation in Excel: Add GST, Remove GST and Split CGST/SGST
Raja sells training kits from his Kolhapur shop. Some customers ask for the price before GST, others for the price with GST, and his accountant wants CGST and SGST shown separately for local bills and IGST for a buyer in another state. Four small formulas cover all of it.
Add GST: price × (1 + rate). Remove GST from an inclusive price: price ÷ (1 + rate). For a sale inside the same state the GST is split equally into CGST and SGST; for a sale to another state the whole amount is IGST. The rates in this guide are examples only: check the current rate for your item with your accountant.
Download the practice file (gst-calculation-formulas.xlsx)
Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.
Quick answer
- Price before GST in B2, rate in C2 (for example 18%).
- GST amount:
=ROUND(B2*C2,2). - Price with GST:
=B2+ROUND(B2*C2,2). - Price before GST from an inclusive price in D2:
=ROUND(D2/(1+C2),2). - CGST and SGST: half of the GST each,
=ROUND(B2*C2/2,2); IGST: the full GST.
=ROUND(B2*C2,2)
=ROUND(D2/(1+C2),2)
=ROUND(B2*C2/2,2)
Add and remove GST: one item
| Question | Formula | Result (₹1,000 at 18%) |
|---|---|---|
| GST amount | =ROUND(1000*18%,2) | 180.00 |
| Price with GST | =1000+ROUND(1000*18%,2) | 1,180.00 |
| Base price from ₹1,180 inclusive | =ROUND(1180/(1+18%),2) | 1,000.00 |
| GST inside ₹1,180 | =1180-ROUND(1180/(1+18%),2) | 180.00 |
| CGST / SGST (same state) | =ROUND(1000*18%/2,2) each | 90.00 + 90.00 |
| IGST (other state) | =ROUND(1000*18%,2) | 180.00 |
Common mistake
To remove 18% GST, do not take 18% off the inclusive price. ₹1,180 − 18% = ₹967.60, which is wrong. Divide by 1.18 to get ₹1,000.
An invoice with mixed rates
| Item | Qty | Rate | GST % | Taxable value | GST | Line total |
|---|---|---|---|---|---|---|
| Excel course kit | 2 | ₹1,500 | 18% | ₹3,000.00 | ₹540.00 | ₹3,540.00 |
| Printed workbook | 5 | ₹240 | 12% | ₹1,200.00 | ₹144.00 | ₹1,344.00 |
| Notebook | 10 | ₹45 | 5% | ₹450.00 | ₹22.50 | ₹472.50 |
| Pen drive 32 GB | 3 | ₹425 | 18% | ₹1,275.00 | ₹229.50 | ₹1,504.50 |
- Taxable value E2:
=B2*C2. - GST F2:
=ROUND(E2*D2,2). - Line total G2:
=E2+F2. - GST at 18% only:
=SUMIFS(F2:F5,D2:D5,18%). - Totals: taxable ₹5,925.00, GST ₹936.00, invoice ₹6,861.00.
For a local bill, show CGST = SGST = half of each line's GST. For an inter-state bill, show the whole GST as IGST. A drop-down cell with Same state / Other state and an IF formula can switch the columns: =IF($I$1="Same state",ROUND(F2/2,2),0).
Good habits for GST sheets
- Keep the rate in its own column. Never type 1.18 inside formulas; rates change and items differ.
- Round each line's tax to 2 decimals before adding, so the invoice total matches the register.
- Keep HSN/SAC codes as text so leading zeros stay.
- This guide shows arithmetic only. For which rate applies, place of supply and invoice rules, follow your accountant or the official GST portal.
Ravindra Bagale's Tip
Add a check row at the bottom: taxable value + GST must equal the invoice total, and CGST + SGST must equal GST. One small formula like =E7+F7-G7 showing 0 saves a long search for a one-rupee difference.
Ravindra Bagale's Tip – मराठी
खाली एक check row टाका: taxable value + GST = invoice total आणि CGST + SGST = GST. =E7+F7-G7 सारखा छोटा formula 0 दाखवत असेल तर एका रुपयाच्या फरकाचा लांब शोध वाचतो.
Ravindra Bagale's Tip – हिंदी
नीचे एक check row जोड़िए: taxable value + GST = invoice total और CGST + SGST = GST। =E7+F7-G7 जैसा छोटा formula 0 दिखाए तो एक रुपये के फ़र्क की लंबी खोज बच जाती है।
Practice task
- Open the Invoice sheet. Check the GST column uses ROUND.
- Check the totals: taxable ₹5,925.00, GST ₹936.00.
- Change I1 to Other state and see CGST/SGST become 0 and IGST fill in.
- On the Single sheet, change the inclusive price and check the base price.
- Add a line with a 28% example rate and check the 18% total does not change.
Learn it properly
Course lessons:
Related guides: Percentage Formulas in Excel: Growth, Share, Discount, Margin and GST · ROUND, ROUNDUP, ROUNDDOWN, MROUND and CEILING: Which Rounding to Use · Retail Quick Commerce Excel Template · SUMIF and SUMIFS
Frequently asked questions
How do I remove 18% GST from a price?
Divide by 1.18: =price/(1+18%). Do not subtract 18%.
What is the difference between CGST/SGST and IGST?
For supply within one state, the GST is split equally into central (CGST) and state (SGST) tax. For supply to another state, it is charged as integrated tax (IGST).
Should I round GST per line or on the total?
Round each line and add the rounded lines, so every invoice line and the register agree. Follow your accountant's practice if it differs.
Are the rates in this guide official?
No. 5%, 12% and 18% are used as examples. Check the current rate for your item.