How to Calculate Sales Tax in Excel (4 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

Tax is the amount of money paid by the customer. It is mandatory to pay tax while purchasing a product except for duty-free products. In this article, we will show how to calculate sales tax in Excel.


How to Calculate Sales Tax in Excel: 4 Suitable Ways

While calculating tax usually two situations seen. One is tax is included with the price, or this may be exclusive. We will discuss both cases here. We can see a sample receipt of the product purchase.


1. Get Sales Tax by Simple Subtraction

In this section, we will determine tax using simple subtraction. When we buy a product price, the tax rate and the total price are mentioned usually on the receipt. From that information, we can get the tax simply through a subtraction process.

We can see in the receipt the price, tax rate, and the total price are given.

📌 Steps:

  • We simply subtract the price value from the total price and get the tax amount. Go to Cell C7 and put the following formula.
=C6-C4

Sales Tax by Simple Subtraction

  • Then, press the Enter button.

We get the amount of tax.


2. Calculate Sales Tax Not Included in Price

In this section, we can see the tax is exclusive of the price. The tax rate is given here. We will calculate the tax based on the product price and tax rate.

📌 Steps:

  • Go to Cell C6. Put the following formula based on the multiplication of the product price and tax rate.
=C4*C5

Calculate Sales Tax When Not Included in Price

  • Finally, press the Enter button.

Calculate Sales Tax When Not Included in Price

This is the standard method to calculate sales tax.


3. Calculate Sales Tax Included in Price

In this section, a different situation appears. Here, the product price is included with tax. We do not know the actual price of the product. We know only the tax rate. In this situation, we have two ways to determine the sales tax. See below for details.

Case 1:

  • Put the following formula on Cell C6.
=C4-C4/(1+C5)

Calculate Sales Tax When Included in Price

  • Hit the Enter button.

In the second part of the formula, we calculate the price without tax and then subtract that to get the tax.

Case 2:

  • Copy and paste the formula below on Cell C6.
=(C4/(1+C5))*C5

Calculate Sales Tax When Included in Price

  • Hit the Enter button again.

In the section, we determine the price without tax first. Then, multiply that with the tax rate to get tax.


4. Calculate Two-Tier Sales Tax

In this section, we discuss how to calculate two-tier sales tax in Excel. In this system, up to a certain limit, the authority fixes a tax rate. Above that value, the tax rate increases. This system is applied to luxury products.

The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

In the data, we can see one box for sale amount and tax. And another box for tax rates. We set that those values are below or equal to $1000, and the tax rate is 5%. And, the above amount is taxable at a rate of 8%. We used the IF function in the calculation.

📌 Steps:

  • Put the formula on Cell C5.
=IF(B5<=$F$7,B5*$F$5,$F$7*$F$5+(B5-$F$7)*$F$6)

Calculate Two-Tier Sales Tax

  • Press the Enter button for execution.

Calculate Two-Tier Sales Tax

In the formula first, we check if our price value is below or above the limit. If within limit then simply calculate tax with Tier 1 rate. Or if the value is above the limit, then follow execute the second part of the formula. Here, we calculate the tax to the Tier 1 rate and extra value after the limit, multiplied by the Tier 2 rate.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we how to calculate sales tax in Excel in different situations. I hope this will satisfy your needs. Please give your suggestions in the comment box.


Related Articles


<< Go Back to Excel Tax Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo