How to Reverse-Calculate VAT in Excel

To reverse-calculate VAT in Excel, divide the VAT-inclusive cell by 1 plus the VAT rate as a decimal: =A2/1.15 for a 15% rate. Subtract that result from the original cell in a separate column to get the VAT amount on its own. Put the formula in the first row and drag it down the column to apply it to an entire list of gross prices at once, rather than calculating each one by hand.

The Basic Formula

Put your VAT-inclusive prices in column A. In column B, the net price formula is the gross amount divided by 1 plus the VAT rate. In column C, the VAT amount is the gross figure minus that net figure. At a 15% rate, that looks like this in a spreadsheet with a gross price of 115.00 in cell A2:

Referencing the VAT Rate From Its Own Cell

Typing 1.15 directly into every formula works until the rate changes, at which point you're editing every row by hand. Put the rate in its own cell instead, say B1, entered as 15%, and reference it in the formula: =A2/(1+$B$1). The dollar signs lock the reference to B1 so it doesn't shift when you drag the formula down to other rows.

Applying It to a Whole Column

Once the formula in row 2 gives the right answer, click the cell, grab the small square at its bottom-right corner (the fill handle), and drag it down as far as your list of gross prices goes. Excel copies the formula to every row, adjusting the gross-price reference automatically while keeping the locked rate cell fixed.

Common Mistakes

• Subtracting the percentage directly: a formula like =A2-(A2*15%) overstates the VAT amount, the same error as doing it by hand. The rate has to divide into 1 plus itself, not multiply against the gross figure.
• Forgetting the absolute reference: dragging a formula that references a plain B1 instead of $B$1 breaks every row after the first.
• Rounding too early: format cells to two decimal places for display, but let the underlying formula keep its full precision, so totals at the bottom of the column stay accurate.

When a Formula Isn't Worth Setting Up

Building out a spreadsheet makes sense when you're reconciling a batch of figures at once, like checking a stack of supplier invoices in one sitting. For a single price you need to check right now, opening our free VAT calculator and typing in one number is faster than opening Excel, setting up columns, and getting the reference right.