How to Reverse Tax in Excel Without Getting the Formula Wrong

When a receipt, invoice, or sales report gives you only the total price including tax, you may need to calculate the original price before tax.

Excel can do this automatically, but the formula must reverse the tax calculation correctly. Simply subtracting the tax percentage from the total produces an inaccurate result.

This guide explains how to create a simple reverse-tax spreadsheet that calculates:

• The price before tax

• The included tax amount

• A rebuilt total

• Any difference caused by rounding or incorrect data

What Is the Reverse Tax Formula in Excel?

Suppose:

• Cell A2 contains the total including tax

• Cell B2 contains the tax rate

The formula for calculating the price before tax is:

=A2/(1+B2)

For example, suppose the tax-inclusive total is $108 and the tax rate is 8%.

Enter:

• A2: 108

• B2: 8%

Then enter this formula in C2:

=A2/(1+B2)

Excel returns:

100

Therefore:

• Price before tax: $100

• Tax included: $8

• Total: $108

The calculation works because an 8% tax turns $100 into $108 by multiplying the original amount by 1.08. To reverse that calculation, you divide $108 by 1.08.

For additional examples and spreadsheet setups, see this complete guide to using the reverse tax formula in Excel.

How to Calculate the Included Tax Amount

Once you have calculated the price before tax, subtract it from the tax-inclusive total.

If the before-tax price is in C2, enter this formula in D2:

=A2-C2

Using the previous example:

$108-$100=$8

You can also calculate the tax amount directly with:

=A2-(A2/(1+B2))

However, keeping the before-tax price and tax amount in separate columns usually makes the spreadsheet easier to understand and audit.

Recommended Excel Column Structure

A basic reverse-tax worksheet can use the following columns:

Column	Heading	Purpose
A	Total Including Tax	Original amount from the receipt or invoice
B	Tax Rate	Applicable percentage
C	Before-Tax Amount	Calculated original price
D	Tax Amount	Tax included in the total
E	Rebuilt Total	Before-tax amount plus tax
F	Variance	Difference between the original and rebuilt total

Enter the following formulas:

Before-tax amount in C2

=A2/(1+B2)

Tax amount in D2

=A2-C2

Rebuilt total in E2

=C2+D2

Variance in F2

=A2-E2

The variance should normally be zero or close to zero. A small difference may appear because of rounding.

A larger difference may indicate:

• The wrong tax rate was entered

• The total was not actually tax-inclusive

• The receipt contains multiple tax rates

• Some items are exempt

• Shipping, tips, discounts, or fees were included differently

• A formula was overwritten or copied incorrectly

Why Multiplying by One Minus the Rate Is Wrong

A common reverse-tax mistake is using:

=A2*(1-B2)

This formula treats tax like a discount.

For example:

$108 × (1-8%)=$99.36

The result is $99.36, but the correct price before tax is $100.

The problem is that 8% tax was originally calculated from the $100 base price, not from the final $108 total.

Reverse tax must undo the original multiplier:

$108 ÷ 1.08=$100

Therefore, use division by one plus the rate, not multiplication by one minus the rate.

Make Sure Excel Understands the Tax Rate

Enter the rate as either:

• 8%

• 0.08

Do not enter 8 unless your formula divides the value by 100.

If users will enter whole-number rates, such as 8 instead of 8%, use:

=A2/(1+B2/100)

Alternatively, create a normalized-rate column with:

=B2/100

This makes the spreadsheet less vulnerable to percentage-formatting mistakes.

How to Round the Result

Many invoices and accounting systems display amounts to two decimal places.

When row-level rounding to cents is appropriate, you can use:

=ROUND(A2/(1+B2),2)

The tax amount can then be calculated with:

=A2-C2

However, rounding rules vary between businesses, jurisdictions, and source systems. Some systems round each item, while others calculate tax on the combined subtotal.

When an invoice already displays the tax amount, it may be better to subtract the displayed tax directly:

=Total-ShownTaxAmount

This preserves the source document’s own rounding method.

How to Handle Multiple Tax Rates

Do not apply one tax rate to an entire receipt when the receipt contains different tax categories.

For example, a transaction might include:

• Standard-rate products

• Reduced-rate products

• Tax-exempt products

• Zero-rated items

Place each tax group on a separate row and assign the correct rate to that row.

Do not average multiple rates together when accuracy is required. An average rate may produce an approximate result, but it hides how each item was treated.

Using Excel Table References

When your data is formatted as an Excel table, you can use named column references instead of cell addresses.

For example:

=[@[Tax-Inclusive Total]]/(1+[@[Tax Rate]])

This formula is easier to understand than:

=A2/(1+B2)

It also adjusts automatically when new rows are added to the table.

When the Reverse-Tax Formula Should Not Be Used

The reverse-tax formula should only be used when the starting amount already includes tax.

Do not use it when:

• The amount is a subtotal before tax

• The tax amount is already known and should be subtracted directly

• The total contains several rates that have not been separated

• The amount includes tips, deposits, shipping, or fees with different tax treatment

• The rate is unknown or has not been verified

• The item is exempt or zero-rated

Excel can perform the arithmetic, but it cannot determine which tax rules apply to a transaction.

Final Excel Checklist

Before relying on your spreadsheet, confirm that:

• The original amount includes tax

• Rates are entered as percentages or decimals

• Each row contains the correct rate

• Exempt and mixed-rate items are separated

• Formula columns have not been overwritten

• The rebuilt total matches the original total

• Rounding differences have been reviewed

• A few rows have been checked manually

A reverse-tax spreadsheet does not need to be complicated. The most important parts are using the correct division formula, labeling the input clearly, and adding a verification column that reveals mistakes before the results are used elsewhere.

For a more detailed breakdown of structured references, VAT and GST examples, shown tax amounts, multiple rates, and spreadsheet quality checks, read the full Reverse Tax Formula in Excel guide.