Confident Cells

VAT in Excel: adding it, taking it off and rounding

20 November 2026 · 2 min read

To add VAT at 20%, multiply the price before VAT (the net) by 1.2: £50 becomes £60. To take VAT off a price that includes it (the gross), divide by 1.2: £60 becomes £50. Don't take 20% off the gross: that gives £48, which is wrong.

Working out the VAT

An invoice is £120 before VAT. VAT is 20% of that: 120 × 0.2 = £24.

Put the rate in its own cell, such as H1, rather than typing 20% into every formula. If the rate changes, or an item is at the reduced rate of 5%, you change one cell.

=B2*$H$1

The $ signs keep the formula pointing at H1 when you copy it down (see what the $ does).

Rounding VAT to the penny

£89.99 × 20% is £17.998. Excel shows £18.00 with the £ format, but it keeps the 0.8 of a penny underneath, and those fractions add up across a long list. On an invoice, VAT should be a whole number of pence, so round it:

=ROUND(B2*$H$1,2)

The 2 means two decimal places.

ABCDEFGH
1InvoiceNetVATGrossVAT rate20%
2INV-01£120.00£24.00£144.00
3INV-02£89.99£18.00£107.99
4INV-03£12.49£2.50£14.99
C2 holds =ROUND(B2*$H$1,2) and D2 holds =B2+C2, both copied down.

Adding VAT: net to gross

Gross is the net plus the VAT: £120 + £24 = £144. In one step, multiply by 1.2, which is the 1 (the price itself) plus 0.2 (the VAT):

=B2*(1+$H$1)

If you've already worked out the VAT in its own column, =B2+C2 is clearer and matches the invoice line by line.

Taking VAT off: gross to net

A receipt says £144 including VAT. What was the price before VAT?

  1. £144 is 120% of the net price: the net (100%) plus the VAT (20%).
  2. So divide by 1.2: 144 ÷ 1.2 = £120.
  3. The VAT is the difference: £144 − £120 = £24.
=ROUND(B2/(1+$H$1),2)

A handy check at 20%: the VAT inside a gross price is always one sixth of it. £144 ÷ 6 = £24.

ABCDEFGH
1ReceiptGrossNetVATVAT rate20%
2R-101£144.00£120.00£24.00
3R-102£60.00£50.00£10.00
4R-103£25.99£21.66£4.33
C2 holds =ROUND(B2/(1+$H$1),2) and D2 holds =B2-C2, copied down.

Common mistakes

Taking 20% off the gross

£144 less 20% is £115.20, not £120. The 20% was worked out on the net, so you have to divide by 1.2 to get back to it.

Typing 20 instead of 20%

If H1 holds 20 rather than 20% (or 0.2), every VAT figure is a hundred times too big. Type the % sign, or format the cell as a percentage.

The rate cell moves when you copy down

Without the $ signs, the formula in row 3 looks at H2, which is empty, so the VAT comes out as £0.00. Use $H$1, or press F4.

Totals a penny out

The total VAT on the invoice doesn't match the sum of the lines because the lines weren't rounded. Round each line with ROUND, then add up the rounded figures.

Try it now

Your turn

In C2, work out the VAT on each invoice to the penny, using the rate in H1. Your formula will be copied down to C7.

ABCDEFGH
1InvoiceNetVATVAT rate20%
2INV-11£120.00
3INV-12£89.99
4INV-13£45.55
5INV-14£12.49
6INV-15£260.33
7INV-16£7.45