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.

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.

• 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.

• Finally, press the Enter button.

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.

• 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.

• 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.

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.

• Press the Enter button for execution.

• Finally, drag the Fill Handle icon downwards.

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

• How to Calculate Marginal Tax Rate in Excel
• How to Calculate Social Security Tax in Excel
• Formula for Calculating Withholding Tax in Excel
• How to Calculate Federal Tax Rate in Excel
• How to Calculate Income Tax in Excel Using IF Function
• How to Calculate Income Tax on Salary with Example in Excel
• Reverse Tax Calculation Formula in Excel
• How to Calculate Income Tax on Salary with Old Regime in Excel

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