How to calculate sales tax in Excel

Put the rate in one input cell

Rates depend on where you sell and what you sell, and they change. Type the rate that applies to you in one cell (G2 here, using 8.25% purely as a sample) and point every formula at it with $G$2. When the rate changes, you change one cell.

Type it as 8.25% or 0.0825. If you type 8.25, Excel treats it as 825%.

Tax on an amount

Multiply the amount by the rate and round to cents. Invoices and receipts show tax to the cent, so round the tax itself rather than only formatting it; formatting just hides the extra decimals and your totals can drift.

The ergonomic chair at $249.00 with an 8.25% rate:

Tax only the taxable lines

Mixed orders often have lines that are not taxed. Mark each line Y or N in a Taxable column (which items qualify is up to the rules where you sell; this sample marks the coffee beans N). SUMIFS adds only the Y lines, and you apply the rate to that subtotal.

Here $361.43 of the $404.93 order is taxable.

Order total including tax

Add the full subtotal and the tax. Keep the tax as its own rounded figure so the invoice adds up exactly when someone checks it by hand.

For a single taxable amount, =ROUND(D4*(1+$G$2),2) gets the total in one step: 1 + rate is the price plus the tax.

Remove tax from a tax-inclusive price

If a price already includes tax, divide by 1 plus the rate to get the pre-tax amount. The tax part is the difference. $108.25 at 8.25% is $100.00 before tax and $8.25 of tax.

Do not multiply by (1 - rate). $108.25 x 91.75% gives $99.32, which is wrong because the tax was calculated on the smaller pre-tax amount, not on $108.25.

Round per line or on the total?

Rounding the tax on each line and adding can differ by a cent from rounding the tax on the order total. Neither is an Excel error. Pick the method your invoicing system or accountant uses and apply it consistently. To round per line, add a Tax column with =ROUND(D2*$G$2,2) for each taxable line and SUM it.