Calculating VAT in Spreadsheets

South Africa’s Value Added Tax (VAT) rate is 15%. Small business owners and freelancers often need to calculate VAT on all prices, both for outputting VAT-inclusive quotes and for removing VAT to submit VAT returns. Calculating VAT in Excel is trickier if the VAT rate is referenced directly in each formula. Better to enter the VAT rate in only one cell, and make that cell an absolute reference for the add-VAT, remove-VAT, and VAT-extraction formulas. That’s easier said than done; even small spreadsheet habits may differ between Apple’s Numbers, Excel for Windows and Mac, and Excel-alike LibreOffice Calc.

VAT Rate and the One Cell to Hold It

South Africa’s VAT rate is 15%, according to the South African Revenue Service (SARS). Only registered vendors are allowed by law to collect VAT on taxable goods and services. Enter the VAT rate in only one spreadsheet cell: click cell A1 and type 15%. Every formula then points to A1 with an absolute reference, so a rate change needs only one edit.

Absolute Cell in Excel or Calc

In Excel: while typing or editing a formula, select the reference (for example A1) in the formula bar and press F4. Each press cycles A1 → $A$1 → A$1 → $A1 → A1.

In LibreOffice Calc: while editing the formula, put the cursor on the reference and press F4 to cycle through A1, $A$1, A$1 and $A1.

In Apple’s Numbers: in the formula editor, click the triangle on the cell reference and choose Preserve Row and Preserve Column.

Four Formulas for Adding, Removing, and Extracting VAT

Say the rate is in A1, and from row 2 down column A holds item names and column B the amounts:

=B2*(1+$A$1) — price including VAT, from a price excluding VAT

=B2/(1+$A$1) — price excluding VAT, from a price including VAT

=B2*$A$1 — VAT on a price excluding VAT

=B2-B2/(1+$A$1) — VAT contained in a price including VAT

Check: R100 excluding VAT becomes R115, and R115 including VAT contains R15 VAT.

To round a result to cents, wrap it in ROUND, for example =ROUND(B2*(1+$A$1);2) in Calc. Excel normally uses a comma between arguments instead: =ROUND(B2*(1+$A$1),2).

A Simple Invoice Layout in Excel

On a separate invoice sheet, keep the 15% rate in cell F1. In rows 2 to 10, put the description in column A, quantity in B, unit price excluding VAT in C, and the line total in D with =B2*C2.

Below the lines, add the totals: subtotal in D11 =SUM(D2:D10), VAT in D12 =D11*$F$1 and total in D13 =D11+D12.

What to Check Before Issuing the Invoice

Keep zero-rated items, such as brown bread and maize meal, out of the 15% calculation.

Confirm you’re using the correct VAT rate of 15%. Government guidance states that only registered vendors can include VAT in prices.

A full tax invoice must show the words Tax Invoice, your name, address and VAT registration number, a serial number and date of issue, a description and quantity of the goods or services, and the value and VAT charged.

How to Avoid Mistakes in Your Excel VAT Calculation

A common mistake is entering 15.5% or 16%. Those increases were announced in the March 2025 Budget but withdrawn in April 2025, so the rate stayed at 15%.

Another frequent error when extracting VAT is to use the wrong cell, relative not absolute, so that when the formula is copied, it no longer references the 15% VAT rate set up in an earlier cell, and points at an empty cell instead.

When VAT Registration Is Required

A business must register as a VAT vendor once its taxable supplies exceed R2.3 million in 12 months; this threshold rose from R1 million on 1 April 2026.