Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Remove GST from Total Amount in Excel (With Easy Steps)

In this tutorial, I am going to show you step-by-step procedures to remove GST from the total amount in excel. If you own a business, then you might be facing GSTs all the time. So, it is important to know how to work with them inside excel. The following section will walk you through the steps to do calculations with GST.


Download Practice Workbook

You can download the practice workbook from here.


What Is GST?

GST stands for goods and services tax which is actually a value-added tax (VAT). It is imposed on most goods and services that are sold for domestic consumption. This tax is added to the final price of the good and paid by customers at the time of purchase.


Step-by-Step Procedures to Remove GST from Total Amount in Excel

To explain the steps clearly, we have taken a simple and concise dataset. This dataset has 2 columns and 4 rows. We have formatted cells C4, C6, and C7 as Accounting data. Also, we have applied the Percentage format in cell C5.

how to remove gst from total amount in excel


Step 1: Inserting Price Including GST

In this first step, we have to insert a total amount for our dataset. This amount includes the GST. So, it is actually the selling price of a product.

  • First, go to cell C4 and insert the Total Amount in dollars.

Entering the Total Amount Including GST

Read More: Creating GST Purchase Order Format in Excel (Free Template)


Step 2: Entering GST Percentages

Here, we need to insert the GST rate for our specific product. Note that, GST rates vary from one location to the other. So, make sure that you find the appropriate GST Rate.

  • For this step, navigate cell C5 and insert the GST Rate as Percentages.

Entering GST Percentages of Total Amount

Read More: How to Create GST Rental Invoice Format in Excel


Step 3: Calculating GST Amount

For this step, we just need to do some basic mathematical operations. We will use Relative Cell Referencing for this purpose.

  • Now in this step, double-click on cell C6 and enter the below formula:
=C4*C5

Calculating GST Amount to Remove from Total Amount

  • Then, press the Enter key and it will calculate the GST Amount in dollars.

Read More: How to Create GST Bill Format in Excel with Formula


Step 4: Determining Price after Removing GST

Similar to the previous step, we will insert a simple formula in this step. This formula will perform the subtraction operation to remove the GST from the total amount.

  • Finally, double-click on cell C7 and type in the following formula:
=C4-C6

Determining the Price After Removing GST from Total Amount

  • Next, press the Enter key again and as a result, excel will remove the GST from the total amount.


Calculate GST for Multiple Products Using VLOOKUP in Excel

If you have a list of multiple products with different GST rates, then it will take a long time to calculate the GST amount for them one at a time. To do this more quickly, we can use the VLOOKUP function in excel to calculate the GST for one item and then just use that formula for the rest of the items. Let us see how to do that.

Calculate GST for Multiple Products Using VLOOKUP in Excel to Find Total Amount

Steps:

  • To begin with, go to cell G5 and type in the following formula:
=F5+F5*VLOOKUP(E5,$B$4:$C$9,2,FALSE)

Entering the VLOOKUP function Referencing GST to Find Total Amount

  • Then, press the Enter key and this will give you the Total for the product TV.

  • Next, drag the Fill Handle to copy the formula to all the cells below.
  • As a result, you will get the Total price of all the products which includes the GST.

Read More: Create GST Invoice Format in Excel (Step-by-Step Guideline)


Things to Remember

  • Remember to format the cells that have monetary values as Accounting.
  • Also, format the cells that have the GST rates as Percentages.
  • Make sure to add the $ sign in the second parameter inside the VLOOKUP function.

Conclusion

I hope that you were able to apply the steps I have shown in this tutorial to remove GST from the total amount in excel. Although we have used a simple dataset, you can use these steps to work with more complex datasets as well. If you get stuck in any of the steps, I recommend going through the tutorial a few times to understand them properly. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

2 Comments
  1. Hi Nazmul Hossain,

    Unfortunately your deduction is not correct. This can be proven by calculating the 9% tax on $318.50, which is $28.67 rather than the $31.50 you took off.
    The correct formula would be =C5/(C5+1)*C4, which gives a total of $28.90 for tax and $321.10 for the untaxed value. 9% of $321.10 is also $28.90.

    • Reply
      Nazmul Hossain Shovon Nov 14, 2022 at 10:59 AM

      Hi James,
      Thanks for your correction. To add, you can also calculate the GST amount using this formula:
      GST Amount = (C4*C5)/(1+C5)
      Then we can deduct this value of cell C6 from C4 to get the original price in cell C7.

Leave a reply

ExcelDemy
Logo