Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate VAT in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=A2*B2✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Net Price | VAT Rate | VAT Amount |
| 100 | 0.2 | |
| 500 | 0.15 | |
| 75 | 0.1 | |
| 1000 | 0.2 |
=A2*B2→20
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Create columns: Net Price (A) and VAT Rate as a decimal (B, e.g., 0.20 for 20% VAT).
- 2In the VAT Amount column, enter =A2*B2 to compute the VAT amount.
- 3In the Gross Price column, enter =A2*(1+B2) to get the total price including VAT.
Tips
- To extract the VAT from a gross (VAT-inclusive) price, use =A2*B2/(1+B2) where A2 is the gross price.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I reverse-calculate the net price from a VAT-inclusive amount?
Divide the gross price by (1 + VAT rate): =A2/(1+B2). This strips the VAT out of the total.
Can I handle multiple VAT rates in the same spreadsheet?
Yes. Put the applicable rate in the B column for each row, and the formula =A2*B2 automatically uses each row's own rate.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.