How to Calculate Tax and Prices With Tax in Excel

To calculate tax in Excel, multiply the price by the tax rate, and to get the price with tax, multiply by 1 plus the rate. Going the other way, from a total that already includes tax back to the price before tax, means dividing, not subtracting a percentage. This guide covers all three on one small price list, plus rounding to the cent and different rates per item. It works the same for sales tax, VAT or GST; just use the rate that applies to you.

The tax formulas you need#

Every tax calculation is one of four formulas. With the price in B2 and the rate in F1:

The results have more than two decimal places, so you'll round them; that comes in a moment. The 1+ part works because a price with tax is 100% of the price plus the tax percentage. With 8.25%, that is 108.25%, or 1.0825.

Set up the tax rate in one cell#

Put the rate in its own cell, so you can change it once and every formula updates. Type it with the % sign, like 8.25%. Excel stores it as 0.0825 and shows it as a percentage.

Here is the price list the examples use, with the rate in F1:

When you fill a formula down, plain F1 would shift to F2, F3 and so on, which are empty. Lock it with dollar signs, $F$1, so every row uses the rate. Press F4 after clicking the reference to add them (Mac: ⌘T). The lesson on percentages and percent change covers this kind of percentage maths.

How to add tax to a price#

To show the tax and the total separately, use two columns. In C2, the tax amount:

=ROUND(B2*$F$1,2)

And in D2, the price including tax:

=B2+C2

Fill both down (Windows: Ctrl+D; Mac: ⌘D). The results:

If you only need the final price, one formula does it: =ROUND(B2*(1+$F$1),2) gives the same 49.25 for the Backpack. Adding a percentage works the same way for price rises and markups; see how to increase a number by a percentage.

How to round tax to the cent#

Wrap the formula in ROUND so the stored value is whole cents. Formatting a cell with two decimals only changes what you see. The Backpack's unrounded tax is 3.75375, and it still adds up as 3.75375 behind the scenes, so a column of totals can end up a cent away from what the cells show.

=ROUND(B2*$F$1,2)

The 2 means two decimal places. ROUND follows the usual rule: 5 and above rounds up, so 6.599175 becomes 6.60. Use ROUNDUP or ROUNDDOWN only if your tax rules say so. The ROUND function guide covers all three.

Rounding each line and rounding the total can give different results by a cent or two. Here they agree: the line taxes add up to 14.18, and =ROUND(SUM(B2:B6)*$F$1,2) is also 14.18. On longer lists they can differ, so follow whichever rule your invoices or accounting system use.

How to remove tax from a price that includes it#

To find the price before tax, divide the total by 1 plus the rate. Say the Headphones cost 86.59 including 8.25% tax, in B2:

=ROUND(B2/(1+$F$1),2)

The result is 79.99. The tax inside the total is the difference: =B2-ROUND(B2/(1+$F$1),2) gives 6.60.

A common mistake is to take the rate off the total instead. With a 20% rate and a total of 120:

The 20% was added to the price before tax, 100, not to 120. So taking 20% of 120 removes too much. The same logic applies to discounts in reverse; the guide to calculating discounts shows how to find an original price.

How to use different tax rates for different items#

When items have different rates, store the rates in a small table and look them up. Say a price list has the price in B2, each item's category in C2, and a rate table in H2:I4:

The tax for each row is then:

=ROUND(B2*VLOOKUP(C2,$H$2:$I$4,2,FALSE),2)

VLOOKUP finds the category and returns its rate, and the rest is the same tax formula. In Excel 2021 or Microsoft 365, XLOOKUP(C2,$H$2:$H$4,$I$2:$I$4) does the same lookup.

Common tax formula mistakes#

Check these when a tax total looks wrong:

1. The rate typed as a whole number. 8.25 instead of 8.25% multiplies by 8.25, so a 45.50 price gets 375.38 in tax. Retype it with the % sign.
2. An unlocked rate cell. Without the $ signs in $F$1, the reference slides down as you fill and the tax becomes 0 below the first row.
3. Removing tax by subtracting a percentage. Divide by (1 + rate) instead.
4. Rounding only with formatting. Use ROUND, or totals can be a cent off.
5. Tax on the wrong amount. If a discount applies, it usually comes off before tax. Check your local rules.

Once you're comfortable with tax, the same percentage skills power a profit margin formula and comparisons with ratios in Excel.

Practise it#

Tax is percentage maths plus rounding, and both are quick to master with practice. Practise percentages and percent change in a live spreadsheet, then take the rounding lesson to use ROUND, ROUNDUP and ROUNDDOWN on prices. Every answer is checked instantly. Start with the exercise on this page: add tax to the Backpack and round it to the cent.