FORMULAS & FUNCTIONS

Sales Tax Formula in Excel: Add Tax, Remove Tax (Reverse) and VAT

Sales tax in Excel is one multiplication, until you need to work backward from a total, combine state and local rates, skip non-taxable lines or record the tax in the books. This guide covers every sales tax formula a small business uses: add tax to a price, remove tax from a tax-inclusive total (and the common wrong way), combined rates, an invoice with taxable and non-taxable items, the journal entry, and VAT, with a free calculator.

Reverse sales tax formula in Excel: =ROUND(C12/(1+C3),2) splits a 150.00 total at 7.75% into a 139.21 price and 10.79 of tax; the wrong method is off by 0.83.
Reverse sales tax: divide by 1 + rate, never multiply by 1 – rate.

The sales tax formulas at a glance

You wantFormulaExample at 7.75%
Tax on a price=ROUND(price*rate,2)249.00 → 19.30
Price including tax=ROUND(price*(1+rate),2)249.00 → 268.30
Price before tax, from a total=ROUND(total/(1+rate),2)150.00 → 139.21
Tax inside a total=ROUND(total*rate/(1+rate),2)150.00 → 10.79
Free download: Get the sales tax and VAT calculator (.xlsx). It has the Calculator, a Rates table, an Invoice with a journal entry, a VAT sheet and How It Works. Works in Excel 2010 and later, Microsoft 365 and Excel for the web.

Step 1: Add sales tax to a price

Keep the tax rate in its own cell (C3) and enter it as a percentage: 7.75%, not 7.75. The price before tax is in C6. In C7 and C8:

=ROUND(C6*C3,2)
=C6+C7

A 249.00 price at 7.75% has 19.30 of tax, so the customer pays 268.30. In one step, =ROUND(C6*(1+C3),2) gives the same 268.30. Multiplying by 1 + rate is the same as adding the tax: 100% of the price plus 7.75%.

Excel sales tax formula =ROUND(C6*C3,2) adding 7.75% tax of 19.30 to a 249.00 price for a total of 268.30.
Tax on a price, then the total.
Why ROUND? 249.00 x 7.75% is 19.2975. A receipt shows 19.30, so round each tax amount to cents with ROUND(…, 2). Formatting a cell to 2 decimals only hides the extra digits; they still add up in totals and can leave you a cent off.

Step 2: Remove tax from a total (reverse sales tax)

When a price already includes tax, for example a 150.00 receipt, divide by 1 + rate to get the price before tax. In C13 and C14:

=ROUND(C12/(1+C3),2)
=C12-C13

150.00 / 1.0775 = 139.21 before tax, so the tax included is 10.79. To get the tax in one step, use =ROUND(C12*C3/(1+C3),2). The check in C16 proves it: 139.21 plus 7.75% tax on 139.21 is exactly 150.00.

Excel reverse sales tax formula =ROUND(C12/(1+C3),2) returning 139.21 before tax and 10.79 tax from 150.00, with the wrong total x (1 - rate) method giving 138.38.
Divide by 1 + rate; the red cells show the common mistake.
The common mistake: total x (1 – rate). It looks logical, but the tax was calculated on the price before tax, not on the total. 150.00 x (1 – 7.75%) = 138.38, which is 0.83 too low. The error grows with the amount and the rate: a 10,000.00 total at 7.75% comes out 55.74 too low.

The same logic explains why margin and markup differ: a percentage of the selling price is not the same as a percentage of the cost. See gross margin vs markup in Excel.

Step 3: Build the combined rate for each location

In the US, the rate you charge is usually the state rate plus county, city or district rates. Keep the rates in a table, one row per location, and add them:

=SUM(B4:D4)

Main Street: 6.00% + 1.00% + 0.75% = 7.75%. The Calculator’s rate in C3 is =Rates!E4, so when a rate changes you update one cell and every formula follows. The rates in the template are examples: look up the current rates for your location, because they change.

Excel combined sales tax rate table: state, county and city rates add up to 7.75%, 6.50% and 8.25% for three locations.
A combined rate is a sum of the rates, not a product.

Step 4: Tax only the taxable lines on an invoice

On the Invoice sheet, choose the location in B3 (a drop-down list) and pull its rate with VLOOKUP in E3:

=VLOOKUP(B3,Rates!$A$4:$E$6,5,FALSE)

FALSE asks for an exact match, so a misspelled location returns #N/A instead of a wrong rate (or use XLOOKUP for an exact match in Microsoft 365). Then mark each line Yes or No in the Taxable? column and tax only the Yes lines:

=IF(B6="Yes",ROUND(E6*$E$3,2),0)

The $ signs lock the rate cell when you copy the formula down (press F4 to add them). For more conditions, such as tax-exempt customers, see IF with AND and OR.

Excel invoice with a Taxable? column and the formula =IF(B6="Yes",ROUND(E6*$E$3,2),0), taxing only the taxable lines at 7.75%.
Service and delivery lines are marked No in this example.
Taxability varies. Whether services, delivery or shipping charges are taxable depends on the state or country. The template lets you mark each line; it does not decide for you.

Step 5: Total the invoice and record the tax

Split the sales into taxable and non-taxable with SUMIFS, then calculate the tax on the taxable total:

=SUMIFS(E6:E11,B6:B11,"Yes")
=ROUND(E14*E3,2)
LineAmount
Taxable sales656.42
Non-taxable sales155.00
Sales tax (on the taxable total)50.87
Sales tax (sum of line taxes)50.88
Invoice total862.29

Rounding each line and then adding can differ from rounding the total by a cent or two: here 50.88 against 50.87. Neither is a formula error. Many systems calculate tax on the invoice total; whichever method you use, use it consistently and follow your tax authority’s rules.

Sales tax you collect is not revenue. It belongs to the tax authority until you pay it over, so the invoice is recorded as:

AccountDebitCredit
Accounts Receivable862.29
Sales811.42
Sales Tax Payable50.87
Total862.29862.29
Excel invoice totals: 50.87 sales tax on 656.42 of taxable sales, an invoice total of 862.29 and a balanced journal entry crediting Sales Tax Payable.
Sales Tax Payable is a liability until you file and pay.

When you file the return and pay, debit Sales Tax Payable and credit Cash. The balance of Sales Tax Payable on your trial balance should equal the tax collected but not yet paid. To follow up unpaid customer invoices, use an invoice payment tracker.

Step 6: VAT in Excel

VAT uses the same formulas. With the VAT rate in C3 (12% in the template; use your country’s rate):

You wantFormulaExample at 12%
VAT on a net price=ROUND(C6*C3,2)1,000.00 → 120.00
Net price from a VAT-inclusive price=ROUND(C11/(1+C3),2)2,464.00 → 2,200.00
VAT inside a VAT-inclusive price=C11-C122,464.00 → 264.00

The difference from sales tax is the monthly or quarterly return: you pay the VAT you charged on sales (output VAT) minus the VAT you paid on purchases (input VAT). For October, output VAT of 5,820.00 minus input VAT of 2,562.00 leaves 3,258.00 payable.

=C16-C17
Excel VAT calculation at 12%: 2,464.00 including VAT is 2,200.00 net plus 264.00 VAT, and the monthly return shows 3,258.00 VAT payable.
Output VAT minus input VAT is what you owe.
Ready-to-share test: On the Invoice sheet, choose Airport kiosk in B3. The rate changes to 8.25%, the sales tax to 54.15 and the invoice total to 865.57; the journal entry still shows Balanced. Switch back to Main Street store when you are done.

Frequently asked questions

What is the formula for sales tax in Excel?

=ROUND(price*rate,2) for the tax and =ROUND(price*(1+rate),2) for the total, with the rate entered as a percentage, for example 7.75%.

How do I calculate the price before tax from a total?

Divide by 1 + rate: =ROUND(total/(1+rate),2). For a 150.00 total at 7.75% that is 139.21, and the tax is 10.79.

Why is total x (1 – rate) wrong?

The tax is a percentage of the price before tax, not of the total. Subtracting the rate from the total removes too much: 138.38 instead of 139.21 in the example.

How do I calculate tax only on some items?

Add a Taxable? column and use =IF(B6=”Yes”,ROUND(E6*rate,2),0), or total the taxable lines with SUMIFS and apply the rate once.

Is sales tax collected income?

No. It is a liability: credit Sales Tax Payable when you invoice and debit it when you pay the tax authority.

Final checklist

  • The rate is in one cell, entered as a percentage.
  • Tax amounts are rounded to cents with ROUND, not just formatted.
  • Reverse tax divides by 1 + rate.
  • The combined rate adds every state and local rate that applies.
  • Only taxable lines are taxed, and the method (per line or on the total) is consistent.
  • Tax collected is credited to Sales Tax Payable, not to Sales.
Practice file: Download the sales tax and VAT calculator and replace the example rates with the current rates for your location. Not tax advice: check the rules that apply to your business.

Get the tax right on every invoice

Browse the ready-to-use Excel templates for invoices and accounting, visit the OneXcel Tutorial Hub for step-by-step guides, or chart your sales and tax in the OneXcel Dashboard Builder.