Quick Answer
To add GST in Excel, multiply by 1 + rate. To remove GST from an inclusive total, divide by 1 + rate. The same formulas work in Google Sheets, Apple Numbers, and LibreOffice Calc.
Add GST (net → inclusive total)
=A2*(1+rate)NZ 15%: =A2*1.15 · AU 10%: =A2*1.10 · India 18%: =A2*1.18 · UK VAT 20%: =A2*1.20
Remove GST (inclusive total → net)
=A2/(1+rate)NZ 15%: =A2/1.15 · AU 10%: =A2/1.10 · India 18%: =A2/1.18 · UK VAT 20%: =A2/1.20
Extract GST already inside a total
=A2-(A2/(1+rate))NZ 15%: =A2-(A2/1.15) · India 18%: =A2-(A2/1.18)
Copy-Paste Excel GST Formula Reference
Use A2 as the amount cell and B2 as the GST rate cell. Enter 18% as 18% or 0.18, then copy the formula down your invoice rows.
Add GST to a net price
Turns an exclusive price into a GST-inclusive total.
=A2*(1+B2)GST amount only
Use this when A2 is the net price before GST.
=A2*B2Remove GST from a total
Finds the base price hidden inside a GST-inclusive total.
=A2/(1+B2)GST included in a total
Extracts the tax component from a receipt or invoice total.
=A2-(A2/(1+B2))CGST or SGST split
For India intrastate GST when the total GST is split equally.
=(A2*B2)/2Common rate examples
10% GST: =A2*1.10
How GST Calculation Works in Excel
The safest Excel GST formula depends on the direction of the calculation. If your amount is before tax, multiply it by `1 + rate` to get the GST-inclusive total. If your amount already includes GST, divide it by `1 + rate` to recover the base price. That division step is the part many spreadsheets get wrong. Use the quick reference above first, then follow the detailed examples below to build a reusable Excel or Google Sheets template for invoices, receipts, and multi-rate GST work.
A Step-by-Step Walkthrough for Beginners
If you are new to spreadsheet automation, learning GST calculation in Excel might seem daunting, but it follows simple mathematical principles. First, organize your data into clear columns: 'Product Name', 'Base Price', 'GST Rate', and 'Total Price'. Once your structure is set, applying the logic becomes a matter of entering the right formula in a single cell and dragging it down. This automated workflow is why thousands of businesses prefer using spreadsheets over manual calculators. By the end of this tutorial, you will have a fully functional template that demonstrates the method for any business scenario.
The GST Calculation Formula Behind Every Excel Cell
Every Excel GST formula above is just a spreadsheet translation of one of two underlying equations. Learn the math once and you can audit any invoice or build the formula from scratch in any tool — including a calculator app, paper, or Google Sheets.
Exclusive GST Formula (Adding Tax to a Net Price)
Use the exclusive GST formula when you have the net price (before tax) and need to add GST on top. This is the standard B2B invoicing direction.
Inclusive GST Formula (Removing Tax from a Total)
Use the inclusive (reverse) GST formula when the price already includes tax — the standard for B2C retail receipts where displayed prices are tax-inclusive.
The Number-One GST Formula Mistake
Multiplying a tax-inclusive total by the rate (e.g., $118 × 0.18 = $21.24) does NOT give you the GST inside that total — it gives you the tax on the larger figure, which is wrong. To correctly extract GST from a total, you must divide by (1 + rate) first. This single rule prevents the majority of GST formula errors in spreadsheets and manual accounting.
GST Rate Divisors — Magic Numbers Reference
Skip the multi-step inclusive formula by dividing any GST-inclusive total directly by these constants. Covers every common GST/VAT rate worldwide — Canada (5%), Australia (10%), New Zealand (15%), India (5/12/18/28%), United Kingdom (20%).
| GST Rate | Magic Divisor | Example |
|---|---|---|
| 5% | 1.05 | $105 ÷ 1.05 = $100 (Canada GST / India 5% slab) |
| 10% | 1.10 | $110 ÷ 1.10 = $100 (Australia GST) |
| 12% | 1.12 | $112 ÷ 1.12 = $100 (India 12% slab) |
| 15% | 1.15 | $115 ÷ 1.15 = $100 (New Zealand GST) |
| 18% | 1.18 | $118 ÷ 1.18 = $100 (India standard slab) |
| 20% | 1.20 | £120 ÷ 1.20 = £100 (United Kingdom VAT) |
| 28% | 1.28 | $128 ÷ 1.28 = $100 (India luxury slab) |
Scenario 1: Adding GST to a Base Price (Exclusive)
This is the most common task for B2B businesses: you have the net cost (exclusive of tax) and need to apply the GST calculation in Excel to find the final billable amount for your customer.
Identify your base price (e.g., cell A2) and your GST rate (e.g., 18% or 0.18).
To find the tax amount only, use the formula: `=A2 * 0.18`.
To find the total inclusive price directly, use `=A2 * 1.18`. This single-step method is the most efficient way to implement it for large datasets.
Scenario 2: Removing GST from a Total Price (Inclusive)
If you are a retailer or B2C service provider, your prices often already include tax. To find the true revenue, you must use the reverse formula in Excel to extract the base price.
Start with the total inclusive price (e.g., cell A2).
To find the original base price, apply the reverse logic: `=A2 / 1.18`.
To find the specific tax component that was included, use `=A2 - (A2 / 1.18)` or simply `=A2 / 1.18 * 0.18`.
Common Pitfalls When Calculating GST in Excel
The most frequent error when learning GST in Excel is the 'Percentage Trap'. Many users mistakenly try to remove 18% GST by multiplying the total by 0.18 and subtracting it. This is mathematically incorrect because the tax was added to a smaller base. Another common mistake is neglecting decimal precision. Tax authorities often require rounding to the nearest cent, and failing to use the ROUND function in your Excel GST formula can lead to small discrepancies that cause major headaches during a tax audit. Understanding these nuances is key to mastering the method.
Expert Tips for Professional Excel GST Automation
1. Use Absolute Cell References ($) for Scalability
When implementing GST in Excel across hundreds of rows, avoid hardcoding tax rates. Instead, place your GST rate in a single dedicated cell (e.g., $B$1). By using `=$B$1` in your formula, you can update the entire spreadsheet instantly if the government changes the tax slabs, keeping your workflow dynamic.
2. Handling Precision with the ROUND Function
To ensure your results match official tax filings, wrap your GST calculation with the ROUND function: `=ROUND(A2 * 0.18, 2)`. This step prevents hidden decimal points from skewing your financial totals.
3. Creating a Dynamic Multi-Rate Template
For businesses with products in different tax categories, the best way to handle multiple rates in Excel is using the IF or VLOOKUP function. This allows your formula to automatically detect the correct rate (5%, 12%, 18%, or 28%) based on a category code, making your template incredibly robust.
Frequently Asked Questions About Excel GST Formulas
Q: Does this logic for how to calculate GST in Excel work in Google Sheets?
A: Absolutely. The mathematical syntax is universal across all major spreadsheet software, including Google Sheets, Apple Numbers, and LibreOffice Calc.
Q: How do I calculate CGST and SGST separately in Excel?
A: For intrastate sales, divide your total GST result by two. If the Excel GST formula calculates 18% total tax with =A2*0.18, split it into two columns of 9% each (=A2*0.09) to represent CGST and SGST accurately on the invoice.
Q: Can I use Excel to calculate GST on discounted prices?
A: Yes. First calculate the discounted net price, then apply the GST formula to that result. Tax is always calculated on the final transaction value after discounts.
Q: What is the fastest way to learn how to calculate GST in Excel?
A: The fastest way is to practice with a real dataset. Follow our step-by-step guide and try to build a template that handles both inclusive and exclusive scenarios using the GST formula for Excel provided above.
Q: What is the Excel formula to remove 15% NZ GST from an inclusive total?
A: For New Zealand GST at 15%, use =A2/1.15 to recover the net price from a GST-inclusive total, or =A2-(A2/1.15) to extract the 15% GST component already included in the receipt amount. The same divide-by-1.15 rule applies whether your numbers are in NZD or any other currency.
Q: What is the Excel GST formula for 10% Australian GST?
A: For Australian GST at 10%, use =A2*1.10 to add GST to a net price, =A2/1.10 to remove GST from an inclusive total, and =A2-(A2/1.10) to extract the GST component. The Australian Taxation Office (ATO) standard for tax invoices accepts either method as long as the rate is shown explicitly.
Conclusion: Mastering Your Business Finances
Using these formulas in Excel helps you save time, reduce errors, and gain better insights into your business's tax position. Keep this guide as a reference, and use our online GST calculator for quick on-the-go verifications.
Need a Quicker Way?
If you don't have Excel open, use our high-precision **online GST calculator** to get instant results.
Use Online Calculator