How To Calculate GST Using Excel Formulas

One of the most frequent requests I receive is to explain how to calculate GST (Goods and Services Tax) using Excel formulas.

If you want to calculate GST quickly and accurately, you’re in the right place! In this tutorial, I’ll show you how to use Excel formulas to add and remove GST, calculate the GST-inclusive total, and remove the GST amount from a total.

Skill Level: Beginner

👉 Bonus: Download my ready-to-use GST Calculator workbook below to follow along and try the formulas! Download the GST Calculator Workbook

Calculate GST for NZ, Australia and Other Countries

I’m based in New Zealand, where the GST rate is 15%, represented as 0.15 in decimal form. If you’re in a country with a different tax rate, adjust the formulas by substituting 15% (0.15) with your local rate. For example:

• In Australia, where GST is 10%, substitute 0.15 with 0.1 or 15% with 10%.
• In India, where GST can be 18%, substitute 0.15 with 0.18 or 15% with 18%.
• In Canada, where some regions have a GST rate of 5%, substitute 0.15 with 0.05 or 15% with 5%.
• In the United Kingdom, where VAT is 20%, substitute 0.15 with 0.2 or 15% with 20%.

This way, the formulas will work correctly with your country’s GST or VAT rate.

Note: You can find in-depth information on calculating GST in New Zealand on the NZ Inland Revenue website. For GST and VAT tax rates in other countries, visit the PWC World Tax Summaries page.

1. Excel Formula to Add GST

Let’s start by calculating the GST component of a GST-exclusive amount.  To do this, multiply the value that excludes GST by 15% or 0.15. To find the total, including GST, add the two values together.

In the example below, cell B6 is multiplied by 0.15, the decimal form of 15%. You can enter 0.15 or 15% into the formula, and Excel will calculate the correct answer.

In cell B8, the GST-exclusive amount held in B6 and the GST held in cell B7 have been added to give the GST-inclusive amount.

2. Excel Formula to Calculate the Total, including GST

Sometimes, you only want to calculate the Total figure, including the GST. So, let’s now look at calculating the total, including GST.

This time, we multiply the total by 1.15 or 115%.

In this example, the value in B14 (exclusive of GST) is multiplied by 1.15 to give the total, including GST.
The same result would have been found if you had used 115% instead of the 1.15.

3. Excel Formula to Remove the GST amount from a Total

If you have a GST-inclusive total and need to calculate the GST-exclusive amount (i.e., remove GST), divide the Total figure by 1.15.  

4. Formula to Calculate the GST amount from a Total

Now, let’s write a formula to find the GST content of a GST-inclusive total, i.e., the amount of GST included in the total.

This is extremely handy when you have a figure and need to find how much GST is included in the Total figure. I use this often when I’ve purchased something for work, and the receipt shows the total figure without the GST figure.

This formula contains two parts. First, multiply the GST-inclusive amount by 3 and divide this figure by 23.

To find the GST portion of a total where the GST or VAT rate differs from 15%, use the following formula: =[GST-inclusive figure]-[GST-inclusive figure]/1+[Tax rate]

Although the formula shown in cell B30 above is the most commonly used formula to find the GST content of a GST-inclusive figure where 15% GST has been charged, the formula =B28-B28/1.15 would also correctly calculate the GST content.

Using these formulas, you should now be able to find and calculate GST quickly.

Common GST Calculation Mistakes and How to Avoid Them

1. Mixing Up GST-Inclusive and GST-Exclusive Amounts

It’s easy to confuse GST-inclusive amounts with GST-exclusive amounts, especially if you’re working with multiple numbers. Using the wrong figure can either overestimate or underestimate the GST content.

Tip: Clearly label your columns and cells as “GST-Exclusive” or “GST-Inclusive” to avoid confusion. Additionally, create separate sections for inclusive and exclusive calculations when setting up your Excel sheet. This small organisational step can help you avoid costly mistakes.

2. Incorrectly Using Decimal vs. Percentage Formats

Excel formulas for GST often use decimal equivalents of percentages (e.g., 15% as 0.15, 10% as 0.1). It’s easy to enter “15” instead of “0.15” mistakenly or to forget that Excel interprets “15%” differently from “15” in a formula. Using an incorrect decimal format will lead to inaccurate calculations.

Tip: Double-check your entries to ensure you’re using the correct format. When using a percentage rate in a formula, consider formatting the cell as a percentage in Excel. This way, you can enter “15%” directly, and Excel will handle it as 0.15 behind the scenes, reducing the chances of error.

3. Forgetting to Lock Cells with the GST Rate

If you’re using the same GST rate across multiple cells in Excel, it’s common to reference a single cell with the rate (e.g., cell B1 containing 0.15 for 15%). However, if you don’t lock this cell reference by adding $ symbols (like B$1), the reference can shift when you drag or copy formulas across cells, causing incorrect calculations.

Tip: Lock the reference to your GST rate cell to prevent errors when copying the formula. The formula in C3 is copied into cells C4 to C7 in the example below, so a $ symbol is added before the row number only (e.g., B$1). This way, the reference will stay fixed to row 1 as you copy the formula downwards.

If you need to copy the formula across and down (to other columns and rows), use $ symbols before the column letter and row number (e.g., $B$1). This will keep the reference locked to cell B1 no matter where you paste the formula.

FAQ: Common GST Calculation Questions in Excel

Q: How do I add GST to a price in Excel?

A: To add GST, multiply the price by 1.15 (for GST at 15%):

= [Price]1.15

Q: How do I subtract GST from a price in Excel?

A: If you have a GST-inclusive price and need to find the GST-exclusive price, divide the total by 1.15: = [Total]/1.15

Q: How do I remove GST from a total in Excel?

A: To remove GST and find the GST-exclusive figure, multiply the total by 3, and then divide this figure by 23:

= [Total]*3/23

Note: The steps in the tutorial will work in Excel versions 2013 and 2016, 2019 and Excel for Microsoft 365.
For more assistance understanding GST (Goods and Services Tax) in New Zealand, please visit the NZ Inland Revenue page.

To Sum Up

Calculating GST in Excel is simple once you know the basics. The formulas above will help you manage GST calculations accurately and efficiently in Excel.

Was this blog helpful? Let me know in the comments below.