Last Updated: 11/08/2026
If you regularly invoice clients, calculating VAT (Value Added Tax) by hand can be slow and error-prone. The good news? LibreOffice Calc – a free, open-source spreadsheet tool – makes it easy to automate VAT calculations with just a few simple formulas. In this tutorial, we’ll walk through exactly how to set up a spreadsheet that calculates VAT amounts, prices including VAT, and even reverses the calculation when you only know the VAT-inclusive price.
Setting Up Your Spreadsheet
Start by opening a blank LibreOffice Calc spreadsheet. In the first row, create three column headers:
- Ex-VAT – the price excluding VAT
- VAT – the VAT amount
- Inc-VAT – the price including VAT
To make these columns easier to read, highlight all three by clicking and dragging across the column letters, then right-click and choose Format Cells → Currency, and click OK. You can also bold the header row for clarity.
Step 1: Calculate the VAT Amount
Let’s say you know the price excluding VAT (this is what you’d charge a client before tax) and need to work out how much VAT to add.
- Click into the VAT column cell (e.g., cell B2).
- Type =A2*0.2 and press Enter.
Here, A2 references the excluding-VAT price, the * symbol (Shift + 8) means multiplication, and 0.2 represents a 20% VAT rate. If A2 is £99, this formula returns £19.80 in VAT.
Once you have the formula in one cell, hover over the small black box in the bottom-right corner of the cell (the fill handle), click and drag down to apply the formula automatically to the rest of your rows.
Step 2: Calculate the Price Including VAT
Now that you have the excluding-VAT price and the VAT amount, calculating the total is simple addition:
- Click into the Inc-VAT column cell (e.g., cell C2).
- Type =A2+B2 and press Enter.
For example, if A2 = £100 and B2 = £20, this formula returns £120 — confirming your calculations are correct. Drag the fill handle down to replicate this across all rows.
Step 3: Reverse the Calculation (Working Backwards from VAT-Inclusive Price)
Sometimes you already know the VAT-inclusive price and need to work out the excluding-VAT amount and the VAT itself. This requires a different set of formulas.
Set up three new columns: Inc-VAT, Ex-VAT, and VAT. Format them as currency the same way as before.
- Find the excluding-VAT price: In the Ex-VAT cell, type =E2/1.2 (where E2 is the inclusive price). Dividing by 1.2 removes the 20% VAT.
- Find the VAT amount: In the VAT cell, type =E2-F2 (the inclusive price minus the excluding price), which isolates the VAT portion.
Drag both formulas down to apply them to every row. You’ll notice the results match your first calculation set exactly – just approached from the opposite direction.
Adjusting for Different VAT Rates
Not every country uses a 20% VAT rate, and rates can change over time. Fortunately, updating your spreadsheet is quick:
- To switch from 20% to 15%, change 0.2 to 0.15 in your VAT formula, and update 1.2 to 1.15 in your reverse-calculation formula.
- For 10%, use 0.1 and 1.1.
- For 22%, use 0.22 and 1.22.
Just remember: you must update both formulas (the forward calculation and the reverse calculation) to keep your two calculation methods consistent. Drag the fill handle down again after each change to refresh all your rows.
Why This Method Works
By using cell references instead of hardcoded numbers, your spreadsheet becomes dynamic – change one input value, and every dependent formula updates automatically across the whole sheet. This is especially useful for freelancers, small business owners, and bookkeepers who need to quickly quote prices, generate invoices, or check that their VAT figures add up correctly.
Final Thoughts
Calculating VAT doesn’t need to be complicated. With just two or three simple formulas in LibreOffice Calc, you can build a reusable spreadsheet template that handles VAT calculations in both directions – and adapts instantly to any VAT rate, anywhere in the world. Give it a try with your own numbers, and you’ll have a professional VAT calculator ready in minutes.
