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.

The sales tax formulas at a glance
| You want | Formula | Example 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 |
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%.

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.

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.

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.

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)
| Line | Amount |
|---|---|
| Taxable sales | 656.42 |
| Non-taxable sales | 155.00 |
| Sales tax (on the taxable total) | 50.87 |
| Sales tax (sum of line taxes) | 50.88 |
| Invoice total | 862.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:
| Account | Debit | Credit |
|---|---|---|
| Accounts Receivable | 862.29 | |
| Sales | 811.42 | |
| Sales Tax Payable | 50.87 | |
| Total | 862.29 | 862.29 |

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 want | Formula | Example 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-C12 | 2,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

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