How to Calculate Sales Tax in Excel - 4 Methods

The tax may be included with 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

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

Press Enter.

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)

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

Press Enter.

Method 4 – Calculate the Two-Tier Sales Tax

Up to a certain limit, the authority fixes a tax rate. Above that value, the tax rate increases.

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

Use the IF function.

Steps:

Enter the formula in C5.

=IF(B5<=$F$7,B5*$F$5,$F$7*$F$5+(B5-$F$7)*$F$6)

Press Enter.

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

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 .

Calculate Sales Tax.xlsx