How to Auto-Calculate CGST/SGST/IGST in Excel

Q: What is the formula to calculate GST in Excel?

`=TaxableValue*Rate/100` for the GST amount. Split it as CGST `=Value*(Rate/2)/100` and SGST the same for intra-state, or charge IGST `=Value*Rate/100` for inter-state.

🧮 Prefer a quick answer? Try our free GST calculator — instant, no sign-up.

Last updated: 27 June 2026 · Formulas for the GST 2.0 rates (Nil / 5% / 18% / 40%).

To calculate GST in Excel, multiply the taxable value by the GST rate, then split it by place of supply: for a same-state (intra-state) sale, charge half as CGST and half as SGST; for an inter-state sale, charge the full rate as IGST. With taxable value in B2 and rate (as a number like 18) in C2, CGST = =B2*(C2/2)/100, SGST is the same, and IGST = =B2*C2/100. A single IF formula picks the correct split automatically.

Key takeaways

Intra-state sale (buyer in your state) → CGST + SGST, each half the rate.
Inter-state sale (buyer in another state) → IGST, the full rate.
One IF formula compares buyer state to seller state and applies the right tax — no manual switching.
Total tax is identical either way: 18% is 9% + 9% (CGST+SGST) or 18% IGST. Only the heads differ.
Reverse-calculate GST from a tax-inclusive price with =Price - Price/(1+Rate/100).
Store the rate in a cell (or look it up by HSN) so a rate change updates every formula at once.

Fact box. GST is a destination-based tax split into CGST + SGST for intra-state supplies and IGST for inter-state supplies. The total rate is the same; only how it is divided between Centre and State changes. (Source: CGST/IGST Acts.)

What is the basic GST formula in Excel?

GST amount equals taxable value times rate. If B2 is the taxable value (say ₹10,000) and C2 is the rate as a whole number (18):

Field	Formula	Result on ₹10,000 @ 18%
GST amount	`=B2*C2/100`	₹1,800
Invoice total	`=B2+(B2*C2/100)`	₹11,800

Tip: store the rate as a number (18) and divide by 100 in the formula, or store it as a percentage (18%) and use =B2*C2. Be consistent across the sheet.

How do I split CGST and SGST in Excel?

For an intra-state sale, divide the rate by two and apply each half. With taxable value in B2 and rate in C2:

CGST = =B2*(C2/2)/100
SGST = =B2*(C2/2)/100

On ₹10,000 at 18%, that is ₹900 CGST and ₹900 SGST — ₹1,800 total, the same as IGST would be.

How do I calculate IGST in Excel?

For an inter-state sale, charge the full rate as IGST:

IGST = =B2*C2/100

On ₹10,000 at 18%, IGST is ₹1,800. There is no CGST or SGST on an inter-state invoice.

How do I make Excel choose CGST/SGST vs IGST automatically?

Compare the place of supply (buyer's state) with your own state in one IF. Suppose D2 holds the buyer's state code and your state code is in $F$1:

CGST  =IF($D2=$F$1, B2*(C2/2)/100, 0)
SGST  =IF($D2=$F$1, B2*(C2/2)/100, 0)
IGST  =IF($D2=$F$1, 0, B2*C2/100)

When the states match, CGST and SGST fill and IGST is zero; when they differ, only IGST fills. Use the two-digit state code from the GSTIN (the first two digits) for a reliable match instead of comparing spelled-out state names.

Fact box. The first two digits of any GSTIN are the state code. Comparing the buyer's GSTIN state code with the seller's is the most reliable way to decide CGST+SGST vs IGST in a formula. (Source: GSTN GSTIN structure.)

How do I extract GST from a tax-inclusive price?

When a price already includes GST and you need the tax portion, work backwards. With the inclusive price in B2 and rate in C2:

Base (taxable) value = =B2/(1+C2/100)
GST included = =B2 - B2/(1+C2/100)

On a ₹1,180 inclusive price at 18%, the base is ₹1,000 and the GST is ₹180.

A worked example

Taxable value	Rate	Type	CGST	SGST	IGST	Total
₹50,000	18%	Intra-state	₹4,500	₹4,500	—	₹59,000
₹50,000	18%	Inter-state	—	—	₹9,000	₹59,000
₹20,000	5%	Intra-state	₹500	₹500	—	₹21,000
₹2,00,000	40%	Inter-state	—	—	₹80,000	₹2,80,000

How Ankeshan helps: Ankeshan reads the buyer's GSTIN, picks CGST+SGST or IGST automatically, and applies the current rate from a server-updated table — so a rate change never leaves a stale formula behind. It's launching soon; join the waitlist.

Frequently asked questions

What is the formula to calculate GST in Excel? =TaxableValue*Rate/100 for the GST amount. Split it as CGST =Value*(Rate/2)/100 and SGST the same for intra-state, or charge IGST =Value*Rate/100 for inter-state.

How do I decide between CGST/SGST and IGST? By place of supply. Same-state sale uses CGST + SGST; different-state sale uses IGST. An IF comparing the buyer's and seller's GSTIN state codes automates the choice.

Is the total GST different for IGST vs CGST+SGST? No. The total is the same — 18% is either 9%+9% or a single 18% IGST. Only the split between Centre and State changes.

How do I remove GST from a total in Excel? Divide the inclusive amount by (1 + rate/100) to get the base, then subtract: =Total - Total/(1+Rate/100) gives the tax included.

Which GST rate should I use in 2026? Nil, 5%, 18% or 40% by the item's HSN/SAC. The 12% and 28% slabs were abolished on 22 September 2025.

Sources

CGST Act and IGST Act (place of supply; CGST/SGST/IGST mechanism).
GSTN: GSTIN structure and state codes — gst.gov.in.
CBIC GST rate notifications, effective 22 September 2025.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.