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:
How to Find the Top 10% of Values in an Excel Column
How to Calculate Cumulative Percentage in Excel
How to Multiply Column by a Percentage in Excel
