Spreadsheets

Remove GST from a price in Excel: inclusive to exclusive formula

Remove GST from a bill in Excel: divide the total by one plus the rate for the base price, subtract for the tax, and add a check column for every invoice.

Editorial illustration for Remove GST from a price in Excel: inclusive to exclusive formula

To take GST out of a GST-inclusive price, divide the total by one plus the rate: base = total/(1+rate), and tax = total - base. In Excel that is =B2/(1+C2) for the base and =B2-D2 for the tax. For a bill of ₹1,180 at a rate of 18%, the base is ₹1,000 and the tax is ₹180.

Why dividing is the right move

A GST-inclusive price was built as base + base x rate, which is base x (1 + rate). To go backwards you divide by (1 + rate), not by multiplying the total by the rate. That is the whole idea. Keep the rate in its own cell, formatted as a percentage, so that the same formula works for any rate that appears on your invoices. The rates below are only illustrations; use whatever rate your bill shows for that item.

Setting up ten invoices

  1. Row 1 headings: A Invoice, B Total paid, C GST rate, D Base price, E GST amount, F Check.
  2. Type the ten bill totals in B2 to B11 and the rate printed on each in C2 to C11.
  3. D2: =ROUND(B2/(1+C2), 2), copied down to D11.
  4. E2: =B2-D2, copied down. Subtracting, instead of calculating again, ensures the two parts add back to the total.
  5. F2: =D2+E2-B2. Every row of this column should show 0.
  6. Row 12: totals with =SUM(B2:B11) and the same for D and E.

What do the rows look like?

Total paidRateBase priceGST amountCheck (should be 0)
₹1,180.0018%₹1,000.00₹180.00₹0.00
₹2,625.005%₹2,500.00₹125.00₹0.00
₹5,600.0012%₹5,000.00₹600.00₹0.00
₹999.0018%₹846.61₹152.39₹0.00
₹12,800.0028%₹10,000.00₹2,800.00₹0.00

The ₹999 row shows why rounding matters: 999/1.18 is 846.6101..., which becomes ₹846.61, and the tax, taken by subtraction, is ₹152.39. The two parts add to exactly ₹999.00.

Why not multiply the total by the rate?

It is the most common mistake. 18% of ₹1,180 is ₹212.40, but the GST inside the bill is ₹180, because the 18% applies to the base of ₹1,000, not to a total that already contains tax. The error grows with the rate, so a sheet that does this will quietly overstate the tax on every line.

What if one bill has items at different rates?

You cannot split one total into two rates with this formula, because the maths needs to know how much of the total belongs to each rate. Work line by line from the invoice, where each item shows its own amount, and run the formula on each line. If only the grand total is printed, ask the seller for an itemised bill.

Does the formula change in Google Sheets?

No. =ROUND(B2/(1+C2), 2) behaves the same, and so does the check column. If you type the rate as 18 instead of 18%, the sheet divides by 19, and you will notice because the check and the totals stop making sense. Fix it by typing 18% or by dividing the cell by 100.

Before you rely on the numbers

  • Confirm the rate against the invoice or the product's classification, rather than guessing it.
  • Rules and rates change, so confirm the current position with your CA or the official source, especially for anything you file.
  • Keep the original bills; the sheet is a working copy, not a replacement.

On the personal-spending side, SpendixAI lets you set a GST rate on a category you create and shows an estimate of the GST inside your spending. It is an estimate to compare with invoices and your CA, not a filing tool.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports