Excel Formula for Sales Tax Calculation

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for sales tax is =A2*B2 where A2 holds the pre-tax price and B2 holds the tax rate as a decimal (e.g., 0.08 for 8%). To get the total including tax, use =A2*(1+B2). This article explains the variations, how to handle rates stored as percentages, and common pitfalls.

How Sales Tax Formulas Work in Excel

Sales tax math is straightforward: tax amount = price × rate. The rate is typically expressed as a decimal in Excel, even if you display it as a percentage. For example, a 7.25% rate is stored as 0.0725. If you type 7.25% into a cell, Excel automatically converts it to 0.0725 for calculations, but the cell may show 7.25% if formatted as a percentage.

The two core formulas are:

  • Tax only: =A2*B2 (returns the tax amount)
  • Total with tax: =A2*(1+B2) (returns price + tax)

Assume A2 contains 100 and B2 contains 0.08. The tax formula returns 8, and the total formula returns 108. If you need to compute the tax amount separately and then sum, you could also use =A2*B2 in C2 and =A2+C2 in D2, but the single formula is more efficient.

Handling Rates Stored as Percentages vs Decimals

Many users type 8% instead of 0.08. Excel treats 8% as 0.08 internally, so =A2*B2 still works. However, if you type 8 (without the percent sign), Excel treats it as 800%, which would give a tax of 800 on a 100 price. To avoid this, always enter the rate as a decimal (0.08) or use the percent format (8%). If you have a column with rates as whole numbers (e.g., 8 for 8%), you can divide by 100 in the formula: =A2*(B2/100).

For a quick conversion when you have a list of rates in column B as whole numbers, use =A2*(B2/100) to get the total. Alternatively, you can change the cell format to Percentage, but that only affects display, not the underlying value.

Using Absolute References for a Single Tax Rate

If you have a fixed tax rate in one cell, say C1, and a list of prices in A2:A100, you want to lock that reference so it doesn’t change when you copy the formula down. Use an absolute reference: =A2*$C$1 for tax, or =A2*(1+$C$1) for total. The dollar signs keep the row and column fixed. When you drag the fill handle, A2 changes to A3, A4, etc., but $C$1 stays constant.

For example, with prices in A2:A5 and the rate 0.08 in C1, enter =A2*$C$1 in B2 and copy down to get tax for each price. This is more efficient than typing the rate in each row.

Common Mistakes and How to Avoid Them

One frequent error is using a comma as the decimal separator in locales where that is the norm, but Excel expects a period. Another is forgetting to multiply by 1 in the total formula. Also, be careful with rounding. Sales tax often needs to be rounded to the nearest cent (two decimal places). Use the ROUND function: =ROUND(A2*B2,2) for the tax amount, and =ROUND(A2*(1+B2),2) for the total. Without rounding, you might get 8.000000001, which displays as $8.00 but can cause issues in further calculations.

Another pitfall is applying a tax rate to a price that already includes tax. If you have a tax-inclusive price and want to extract the pre-tax amount, use =A2/(1+B2). For example, with A2=108 and B2=0.08, the formula returns 100. This is common in countries where prices are shown inclusive of VAT.

Comparing Tax-Inclusive and Tax-Exclusive Calculations

Tax-exclusive (common in the US) means the price is before tax, and you add tax on top. Tax-inclusive (common in the EU and Australia) means the displayed price includes tax. Excel handles both easily:

  • Tax-exclusive total: =A2*(1+B2)
  • Tax-inclusive base price: =A2/(1+B2)

For a tax-inclusive price of 119.90 with a 19% VAT, the pre-tax amount is =119.90/1.19, which equals 100.75 (rounded). This is useful for accounting or when you need to report net vs gross amounts.

Using SUMPRODUCT for Multiple Items with Different Rates

If you have a list of items with different tax rates, you can calculate the total tax for all items in one formula. Suppose prices are in A2:A10 and rates (as decimals) are in B2:B10. Use =SUMPRODUCT(A2:A10, B2:B10) to get the total tax. For the total including tax, use =SUMPRODUCT(A2:A10, 1+B2:B10). This avoids helper columns and is efficient for large lists.

For example, with prices 100, 200, 300 and rates 0.08, 0.05, 0.10, the total tax is 100*0.08 + 200*0.05 + 300*0.10 = 8+10+30 = 48. The total including tax is 100*1.08 + 200*1.05 + 300*1.10 = 108+210+330 = 648.

Frequently Asked Questions

How do I add sales tax to a price in Excel? Use =A2*(1+B2) where A2 is the price and B2 is the tax rate as a decimal. For example, 100 in A2 and 0.08 in B2 gives 108.

How do I calculate sales tax from a total that includes tax? Use =A2/(1+B2) to get the pre-tax price, then subtract that from the total to get the tax: =A2 - A2/(1+B2). For a total of 108 and 8% tax, the pre-tax price is 100 and the tax is 8.

Why is my sales tax formula giving a wrong number? Likely because the rate is entered as a whole number (8) instead of a decimal (0.08). Check the cell format and the value. Also ensure you are using a period as the decimal separator.

Can I use a percentage format in the formula? Yes, if you type 8% in a cell, Excel stores 0.08, so the formula works. But if you type 8 and format as percentage, it becomes 800%, which is wrong. Always type the percent sign or use decimal.

How do I round sales tax to the nearest cent? Use =ROUND(A2*B2,2) for the tax amount and =ROUND(A2*(1+B2),2) for the total. This ensures two decimal places.

Is there a built-in sales tax function in Excel? No, Excel does not have a dedicated sales tax function. The standard arithmetic formulas above are all you need.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.