Table of Contents
Calculating sales tax in Excel is a simple and efficient way to accurately determine the total cost of a product or service. To do so, you can use the “=SUM” formula to add the cost of the item and the corresponding tax rate, which can be found by multiplying the sales tax percentage by the cost. For example, if an item costs $50 and the sales tax rate is 8%, the formula would be “=SUM(50, 50*0.08)”, resulting in a total cost of $54. Additionally, you can use the “=ROUND” formula to round the final amount to two decimal places for a more precise calculation. By utilizing these formulas, you can easily and accurately calculate sales tax in Excel for any given cost and tax rate.
Calculate Sales Tax in Excel (With Examples)
As the name implies, sales tax is a tax added to the sale of goods.
For example, suppose the price of a given product is $10.
If the sales tax rate is 20%, then the final price of the product after sales tax is added is:
Price with sales tax = $10 * (1 + 0.20) = $10 * 1.2 = $12
You can use the following formulas to add or remove sales tax from prices in Excel:
Formula 1: Add Sales Tax to Price
=B2*(1+$F$1)
Formula 2: Remove Sales Tax from Price
=B2/(1+$F$1)
Both formulas assume the price of a good is located in cell B2 and the sales tax rate is located in cell F1.
The following examples show how to use each formula in practice.
Example 1: Add Sales Tax to Prices in Excel
Suppose we have the following list of products with prices in Excel:
We can type the following formula into cell C2 to add a sales tax rate of 20% to the price in cell B2:
=B2*(1+$F$1)
We can then click and drag this formula down to each remaining cell in column C:
Column C now shows each price in column B with the sales tax added to it.
For example:
- A $10 item with a 20% sales tax rate becomes $12.
- A $15 item with a 20% sales tax rate becomes $18.
- A $20 item with a 20% sales tax rate becomes $24.
And so on.
Example 2: Remove Sales Tax from Prices in Excel
Suppose we have the following list of products with prices that already have sales tax added to them in Excel:
We can type the following formula into cell C2 to remove a sales tax rate of 20% from the price in cell B2 to find the price before the sales tax was added:
=B2/(1+$F$1)
We can then click and drag this formula down to each remaining cell in column C:
Column C now shows each price in column B with the sales tax removed from it.
For example:
- A $12 item with a 20% sales tax rate removed becomes $10.
- A $18 item with a 20% sales tax rate removed becomes $15.
- A $24 item with a 20% sales tax rate removed becomes $20.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: