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

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.


Download Practice Workbook

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


4 Ways to Calculate Sales Tax in Excel

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.


Similar Readings


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.

  • Finally, drag the Fill Handle icon downwards.

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.


Conclusion

In this article, we how to calculate sales tax in Excel in different situations. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo