Quick Answer
In an Excel worksheet, multiply a net amount by 1 + rate to add GST. Divide an inclusive total by 1 + rate to recover the net amount. These cell formulas also 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
Remove GST (inclusive total → net)
=A2/(1+rate)NZ 15%: =A2/1.15 · AU 10%: =A2/1.10 · India 18%: =A2/1.18
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
Choose the Excel formula by checking what the amount in your worksheet represents. If the cell contains a price before GST, multiply it by `1 + rate`. If the cell contains a GST-inclusive total, divide it by `1 + rate` to recover the base price. Use the examples below to build a reusable Excel or Google Sheets worksheet for invoices, receipts and multi-rate GST work.
Build an Excel GST Worksheet Step by Step
Create columns for Product Name, Base Price, GST Rate and Total Price. Enter the rate as a percentage in one cell, reference that cell in your formula, then fill the formula down the remaining rows. This keeps every result tied to visible worksheet cells and lets you update the rate without rewriting each row.
Check the Math Used by Your Excel Cells
Each Excel GST formula above uses one of two equations. Use the equations to check a worksheet result or rebuild a formula with different cell references.
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
In a spreadsheet, divide a GST-inclusive total by the matching constant to return the base amount. The table provides cell-ready examples for common GST rates.
| GST Rate | Magic Divisor | Spreadsheet 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 |
| 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.
Tips for Reliable Excel GST Worksheets
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: Do these Excel GST formulas work in Google Sheets?
A: Yes. The same cell formulas work in 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: How do I copy an Excel GST formula down a column?
A: Enter the formula in the first result cell, use an absolute reference such as $B$1 for the GST rate, then drag the fill handle down the column. Excel updates the amount row while keeping the rate cell fixed.
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.
Keep These Excel GST Formulas Handy
Save the formulas with clear amount and rate columns, use absolute references for shared rate cells, and apply ROUND when your reporting rules require fixed decimal places. For a quick check outside your spreadsheet, use the GST calculator.
Need a Quick Result Without Excel?
Use the online GST calculator when you need a one-off result instead of a worksheet formula.
Use GST Calculator