To explain the steps clearly, we’ll use a simple dataset to calculate taxes on an individual item. We have formatted cells C4, C6, and C7 as Accounting data. Also, we have applied the Percentage format in cell C5.
Steps:
- Go to cell C4 and insert the Total Amount in dollars.
- Go to cell C5 and insert the GST rate as a percentage.
- Double-click on cell C6 and enter this formula:
=(C4*C5)/(1+C5)
- Press the Enter key and it will calculate the GST Amount in dollars.
- Double-click on cell C7 and copy in the following formula:
=C4-C6
- Press the Enter key to get the final result.
Calculate GST for Multiple Products Using VLOOKUP in Excel
Let’s expand the dataset to show how to use variable GST rates for a group of products.
Steps:
- Go to cell G5 and insert the following formula:
=F5+F5*VLOOKUP(E5,$B$4:$C$9,2,FALSE)
- Press the Enter key.
- Drag the Fill Handle to copy the formula to all the cells below.
- 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.
- Format the cells that have the GST rates as Percentages.
- Add the $ sign in the second parameter inside the VLOOKUP function to use absolute references.
Download the Practice Workbook
You can download the practice workbook from here.
<< Go Back to GST | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
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.
GST Amount = (C4*C5)/(1+C5)
not work
Formula =C5/(C5+1)*C4 working
pls Any other simplify formula
Hello Kumar,
Both the formulas you mentioned work fine and generate same results. Try again. If it still does not work, please send us your Excel file at [email protected]
And yes. There is a simpler formula to find out the original price directly without calculating the GST amount. Use this formula inside cell C7: Original price = C4/(1+C5)
Thank you.
Regards,
Exceldemy