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.
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.
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.
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:
- Then, press the Enter key and it will calculate the GST Amount in dollars.
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:
- 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.
- To begin with, go to cell G5 and type in the following formula:
- 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.
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.
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.