ExcelInsider Team
When you buy a product, you pay the price that includes GST. You might be interested to know what price you would have to pay if you removed the GST from the price you paid. There are two ways to remove GST from the Total Amount in Excel. To use these methods, you don’t need to know complex Excel functions.
In this article, you will learn them using examples so that you can grasp the methods clearly. We will use simple mathematical expressions to do the calculations.
➤ Use the following formula and change the cell references accordingly:
=(D2/(100+10))*100
➤ Replace D2 with the total amount that contains the GST, and 10 with the GST percentage.
➤ Autofill other rows in the column if you have multiple products to remove GST from.
That was a quick method to remove GST when the rate is the same for all products. But that might not be the case for your usage. In this article, you will be able to learn how to remove GST from the total amount in Excel in detail. Therefore, read the full article and download the workbook we used in the tutorial to follow along.
What is GST?
GST or Goods and Services Tax is a type of value-added tax that the buyer has to pay on any service or product they purchase. The amount of GST varies from country to country, and sometimes from state to state.
While calculating the GST from the source price is relatively easy, removing GST is not. You can just multiply the GST by the source price to calculate the price with GST. But to remove the GST from the new price, the formula becomes a bit more complicated. Worry not, because you will learn how to do that in this article with some easy steps.
Removing GST with Constant Rate from the Total Amount
In some countries, like Australia, they use a flat GST rate for all products. In this method, we will use a flat 10% rate to remove the GST from the total amount. We have a dataset of retail transactions to demonstrate the method. There are transaction IDs, customer names, items they bought, and the total prices with GST. Follow the steps below to remove the GST from those prices:
➤ First, add another column for the prices without GST. We are choosing column E with the heading “Price without GST”.
➤ Put this formula in the E2 cell and press Enter :
=(D2/(100+10))*100
➤ Click the small plus (+) sign on the bottom-right of the E2 cell and drag it to the E13 cell to fill the whole column.
Extracting Variable GST and Removing It from Total Amounts
In some countries and states, the GST rates vary from product to product. If we use the same formula and autofill, it won’t work for that. What we can do is take the GST rates from another column and use them in our formula. We can also try to extract the GST values and subtract them from the total price. Let’s do that:
➤ First, create a column for extracting the GST. In the F2 cell, write any of the following formulas:
=D2-(D2/(1+E2))
=(D2/(1+E2))*E2
➤ Press Enter and autofill the column.
➤ In another column, write any of the following formulas:
=D2-F2
=(D2/(1+E2))
➤ Press Enter, then use your mouse to autofill the rest of the rows in the column.
Frequently Asked Questions
How to calculate GST?
You have to multiply the product price by the GST rate to calculate the GST. Here is the formula to do that in Excel:
=A1*B1
Put the GST rate in the B1 cell and the product price in the A1 cell. Remember to put the GST rate in decimal format or in percentage, not in whole integers.
What is the formula for the total in Excel?
You can use the following formula to calculate the total in Excel:
=SUM(A1:A10)
This formula totals the data from the A1 cell to the A10 cell.
How do I remove a percentage in Excel?
From the Home tab, go to the Number group. From the Number Format dropdown menu, select Number instead of Percentage. The number in that cell will no longer have a percentage in it.
How to calculate the amount in Excel?
If you are trying to calculate the amount from a column, you can use AutoSum. Just go to the cell under the values, and press Alt+=. Excel will automatically insert the SUM formula, and you can press Enter to complete the calculation.
How do I remove GST from an item?
If you know the GST amount, you can subtract the amount using Excel. Use the following formula in Excel:
=A1-B1
Replace A1 with the item total price, and B1 with the GST amount.
Wrapping Up
In this article, we have learned how to remove GST from the total amount in Excel. We have discussed two essential methods and multiple formulas to apply those methods. If you have questions, put them below, and we will take care of them.
