How to Calculate the Sales Tax in Excel – 4 Methods

The tax may be included in the price or added to it.


Method 1 – Getting the Sales Tax using a Subtraction

The receipt shows price, tax rate and total price.

Steps:

  • Subtract the price value from the total price to get the tax amount: Go to C7 and enter the following formula.
=C6-C4

Sales Tax by Simple Subtraction

  • Press Enter.

This is the output.


Method 2 – Calculate the Sales Tax Not Included in the Price

Calculate the tax based on the product price and tax rate.

Steps:

  • Go to C6. Enter the following formula.
=C4*C5

Calculate Sales Tax When Not Included in Price

  • Press Enter.

Calculate Sales Tax When Not Included in Price

This is the output.


Method 3 – Calculate the Sales Tax Included in the Price

The price includes the tax. You know the tax rate.

Case 1:

  • Use the following formula in C6.
=C4-C4/(1+C5)

Calculate Sales Tax When Included in Price

  • Press Enter.

Calculate the price without tax and then subtract it to get the tax:

Case 2:

  • Enter the formula below in C6.
=(C4/(1+C5))*C5

Calculate Sales Tax When Included in Price

  • Press Enter.

 


Method 4 – Calculate the Two-Tier Sales Tax

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

The dataset below shows the sale amount and tax columns. There is another dataset for tax rates.

For values below or equal to $1000, the tax rate is 5%. The above amount is taxable at 8%.

 

Steps:

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

Calculate Two-Tier Sales Tax

  • Press Enter.

  • Drag down the Fill Handle to see the result in the rest of the cells.

Calculate Two-Tier Sales Tax

The formula checks if the price value is below or above the limit. If within limit, it calculates tax with Tier 1 rate. If the value is above the limit, it calculates the tax to the Tier 1 rate and the extra value after the limit, multiplied by the Tier 2 rate.


Download Practice Workbook

Download the practice workbook .


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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