GST Formula in Excel: Add GST & Reverse GST Calculation
Excel formulas to add GST, remove GST from an inclusive price (reverse GST) and split CGST/SGST, with 18%, 12% and 5% examples.
To calculate GST in Excel, put the amount in A2 and the GST rate in B2 (typed as 18%). Then:
- GST amount:
=A2*B2 - Total including GST:
=A2*(1+B2) - Reverse GST (price before tax, from an inclusive amount):
=A2/(1+B2) - GST inside an inclusive amount:
=A2-A2/(1+B2)
For example, ₹10,000 plus 18% GST is ₹11,800, and an inclusive price of ₹11,800 contains ₹1,800 of GST on a base of ₹10,000. For a single quick check, the GST calculator does both directions and shows the CGST/SGST split.
Setting up the sheet
Use one row per item and these columns:
| Column | Heading | What goes in |
|---|---|---|
| A | Amount | The price you know (before or after GST) |
| B | GST rate | Typed as a percentage, e.g. 18% |
| C | Base value | Formula |
| D | GST | Formula |
| E | CGST | Formula |
| F | SGST | Formula |
| G | Total | Formula |
Typing 18% stores the value 0.18, which keeps the formulas short. If you type 18 instead, divide by 100 in every formula, for example =A2*B2/100. Mixing the two styles in one sheet is the most common cause of totals that are a hundred times too big.
Adding GST to a price (exclusive to inclusive)
When A2 is the price before GST:
- C2 (base):
=A2 - D2 (GST):
=ROUND(A2*B2,2) - E2 (CGST):
=ROUND(D2/2,2) - F2 (SGST):
=D2-E2 - G2 (total):
=C2+D2
Writing SGST as GST minus CGST guarantees the two halves always add back to the exact GST, even after rounding.
Examples
| Amount (before GST) | Rate | GST | CGST | SGST | Total |
|---|---|---|---|---|---|
| ₹10,000 | 18% | ₹1,800 | ₹900 | ₹900 | ₹11,800 |
| ₹2,500 | 12% | ₹300 | ₹150 | ₹150 | ₹2,800 |
| ₹2,000 | 5% | ₹100 | ₹50 | ₹50 | ₹2,100 |
Reverse GST calculation (inclusive to exclusive)
When A2 is the final price that already includes GST:
- C2 (base):
=ROUND(A2/(1+B2),2) - D2 (GST):
=A2-C2 - E2 (CGST):
=ROUND(D2/2,2) - F2 (SGST):
=D2-E2 - G2 (total):
=A2
The reason you divide, rather than subtract 18%, is that the 18% was applied to the base price, not to the total. 18% of ₹11,800 is ₹2,124, which is far more than the ₹1,800 of tax actually inside it.
Reverse GST examples
| Inclusive price | Rate | Divide by | Base value | GST |
|---|---|---|---|---|
| ₹11,800 | 18% | 1.18 | ₹10,000 | ₹1,800 |
| ₹5,000 | 18% | 1.18 | ₹4,237.29 | ₹762.71 |
| ₹1,120 | 12% | 1.12 | ₹1,000 | ₹120 |
| ₹2,100 | 5% | 1.05 | ₹2,000 | ₹100 |
| ₹1,400 | 40% | 1.40 | ₹1,000 | ₹400 |
A shortcut for the tax part alone is GST = inclusive price × rate ÷ (1 + rate), which in Excel is =A2*B2/(1+B2).
One formula for both directions
If your sheet mixes inclusive and exclusive amounts, add a column H with "Incl" or "Excl", and compute the base in C2 with:
=IF(H2="Incl",ROUND(A2/(1+B2),2),A2)
The GST, CGST/SGST and total formulas from the exclusive section then work for every row, as long as G2 is =C2+D2 and D2 is =ROUND(C2*B2,2).
CGST + SGST or IGST?
For a sale within one state, GST splits equally into CGST and SGST. For a sale between states, the whole amount is IGST. In Excel, add a column I with the supplier's state and J with the place of supply, then:
- IGST:
=IF(I2<>J2,D2,0) - CGST:
=IF(I2=J2,ROUND(D2/2,2),0) - SGST:
=IF(I2=J2,D2-ROUND(D2/2,2),0)
The total tax is the same either way; only the heads change. For the rules behind this, see our guide to GST calculation, CGST, SGST and IGST.
Which rate to use: 18%, 12% or 5%?
The GST Council rationalised the rates with effect from 22 September 2025. Most goods and services now fall under 5% or 18%, with 0% for exempt items and 40% for certain luxury and sin goods. Most items that were at 12% or 28% moved into these slabs. A few items, such as gold, have special rates.
So 12% still shows up mainly on invoices from before that date and in some specific cases. Always use the rate on the invoice or in the current CBIC rate schedule, not a general list.
Our GST calculator offers 5%, 12%, 18% and 28%, plus 3% and 0.25%. It does not currently list 40%, so for 40% items use the Excel formula above, or divide by 1.40 by hand.
Google Sheets
Every formula here works the same in Google Sheets. If your locale uses commas as decimal separators, Sheets will expect semicolons between arguments, for example =ROUND(A2/(1+B2);2).
Common mistakes
- Subtracting 18% from an inclusive price. Divide by 1.18 instead.
- Rate typed as 18 in some rows and 18% in others. Pick one style and stick to it.
- Rounding CGST and SGST separately so they do not add up. Calculate one half and subtract it from the total GST to get the other.
- Applying GST before a discount that is shown on the invoice. Reduce the price first, then add tax. For discounts, see the percentage calculator or our discount guide.
- Using the old 12% or 28% rate for items that moved slabs in September 2025.
- Charging CGST + SGST on an inter-state sale. That needs IGST.
Build the sheet once with these formulas and you can paste in a month of invoices and get correct base values, tax and splits in seconds.
Questions
What is the formula to calculate GST in Excel?
With the amount in A2 and the rate in B2 typed as 18%, the GST is =A2*B2 and the total including GST is =A2*(1+B2). Format B2 as a percentage.
How do I do reverse GST calculation in Excel?
To get the price before GST from an inclusive amount in A2, use =A2/(1+B2). The GST inside the price is =A2-A2/(1+B2), or equivalently =A2*B2/(1+B2).
How do I remove 18% GST from a total amount?
Divide the total by 1.18. For ₹11,800, the price before GST is ₹10,000 and the GST is ₹1,800. Do not take 18% of ₹11,800 and subtract it; that gives the wrong answer.
Is 12% GST still applicable?
From 22 September 2025, most items in the old 12% and 28% slabs moved to 5%, 18% or 40%. You will still see 12% on older invoices and a few special cases, so always use the rate printed on the invoice or in the current CBIC schedule.