If you run a VAT-registered business, the quarterly VAT return is one of those jobs that feels harder than it is. The truth is that a tidy Excel workbook does almost all the heavy lifting for you. In this guide I will walk you through building a simple VAT return in Excel that produces the nine boxes HMRC asks for, using the 20% standard rate that applies to most UK sales. By the end you will have a workbook that takes a quarter’s worth of invoices and turns them into a return in a few minutes, with the arithmetic done for you.
One important note before we start: since Making Tax Digital (MTD) for VAT, you cannot type your figures straight into the HMRC portal by hand. You need to submit through bridging or accounting software. Excel is still perfectly fine for working out and storing your numbers, as long as the final filing goes through MTD-compatible software. Think of the spreadsheet as your working paper. Most bridging tools simply read the nine boxes from a named cell or tab in your workbook, so the cleaner your layout, the smoother the submission. I have seen plenty of businesses keep their VAT in Excel for years this way without a hitch.
Before we build anything, a quick word on registration. You must register for VAT once your taxable turnover passes the threshold, and many businesses register voluntarily before that to reclaim VAT on purchases. Whichever applies to you, the return mechanics below are identical. The aim is to make the quarter end a calm, repeatable routine rather than a scramble.
Step 1: Set up your sales and purchases sheets
Create two tabs: one for sales (money in) and one for purchases (money out). On each, use columns for the date, description, net amount, VAT rate and VAT amount. Keeping net and VAT in separate columns is the single most useful habit you can build, because every box on the return is just a sum of one of those columns.
| Date | Customer | Net (£) | VAT rate | VAT (£) |
|---|---|---|---|---|
| 12/04/2026 | Acme Joinery | 1,200.00 | 20% | 240.00 |
| 18/04/2026 | Bright Cafe | 650.00 | 20% | 130.00 |
| 03/05/2026 | Zero-rated export | 900.00 | 0% | 0.00 |
The VAT column is a formula, not a typed figure. In the row above, with the net in column C and the rate in column D, use =C2*D2. Let Excel do the arithmetic so you never end up with a £239.99 rounding gremlin. I would also round each line to two decimal places with =ROUND(C2*D2,2) so your column adds up exactly to the penny rather than carrying long trailing decimals that only show up when totalled.
Why two tabs rather than one? Because the VAT you charge on sales (output VAT) and the VAT you pay on purchases (input VAT) are treated completely separately on the return. Keeping them on different sheets means you never accidentally mix a sale into a purchase total, and it makes the box formulas in the next step almost trivially short. If your business has more than a handful of transactions a month, consider turning each sheet into a proper Excel Table with Ctrl+T, which lets your totals expand automatically as you add rows.
It helps to add a rate column even for zero-rated and exempt items, entering 0% rather than leaving it blank. A blank cell can quietly drop a sale out of your net totals, which throws Box 6 off. Being explicit about the rate on every single line is one of those small disciplines that saves a surprising amount of detective work later.
Step 2: Work out the nine VAT return boxes
HMRC wants nine boxes. Here is what each one means and the formula you would use, assuming your VAT amounts sit in column E of the relevant sheet.
| Box | What it is | Excel formula |
|---|---|---|
| 1 | VAT due on sales | =SUM(Sales!E:E) |
| 2 | VAT on EC acquisitions (usually 0) | =0 |
| 3 | Total VAT due (Box 1 + Box 2) | =B1+B2 |
| 4 | VAT reclaimed on purchases | =SUM(Purchases!E:E) |
| 5 | Net VAT to pay or reclaim | =ABS(B3-B4) |
| 6 | Total net sales | =SUM(Sales!C:C) |
| 7 | Total net purchases | =SUM(Purchases!C:C) |
Box 8 and Box 9 only apply to goods moved to or from the EU, so for most domestic UK businesses they are zero. Box 5 is the figure that matters: if Box 3 is larger than Box 4, you pay HMRC the difference; if Box 4 is larger, you are due a refund. Using =ABS(B3-B4) means Box 5 is always shown as a positive number, which is how HMRC wants it; you simply note separately whether it is payable or reclaimable.
Notice how short these formulas are. That is the payoff for the tidy two-tab layout: every box is just a SUM of a single column, or a quick addition of two boxes. There is nothing to get wrong because there is barely anything to type. If you ever need to prove a figure to HMRC, you can click on any box and trace it straight back to the underlying transactions, which is exactly the audit trail a good set of records should give you.
It is worth labelling each box clearly in a cell next to the formula, because “Box 1” means nothing to most people six months later. A short note such as “VAT charged on sales” alongside the figure turns your workbook into something a colleague or accountant can pick up and understand without a handover.
Step 3: Add a sense-check
Before you trust any total, add a single cell that re-derives Box 1 from the net sales: =ROUND(B6*0.2,2). If your zero-rated and standard-rated sales are mixed this will not match exactly, which is fine, but for a business that only makes standard-rated sales it should land bang on Box 1. A mismatch is a quick flag that a VAT rate was typed wrong somewhere.
It also pays to wrap any division in =IFERROR(...) so a blank quarter never throws a #DIV/0! error across your summary.
Step 4: Lock the period and file through MTD
Once the quarter is closed, copy the nine boxes into a dated summary tab so the figures cannot drift if you keep editing. Then push those numbers into your MTD bridging software. If you would rather start from a ready-made layout, our small business bookkeeping template pairs neatly with a VAT working tab.
Common mistakes
- Typing the VAT figure by hand. Always calculate it from the net with a formula, or rounding differences will creep into Box 1.
- Mixing gross and net. If a figure already includes VAT, the net is
=A2/1.2, not=A2*0.8. Those give different answers. - Forgetting that Excel alone cannot file. Under MTD you must submit through compatible software, not by retyping figures.
- Ignoring zero-rated and exempt items. They still belong in Box 6, even though they add nothing to Box 1.
Frequently asked questions
Can I still use Excel for VAT under Making Tax Digital?
Yes. You can keep your records and do your workings in Excel; you simply have to file the final figures through MTD-compatible bridging or accounting software rather than typing them into the HMRC website.
What VAT rate should I use in the UK?
The standard rate is 20% and covers most goods and services. Some items are reduced-rated at 5% or zero-rated at 0%. Use a rate column so each line can carry its own rate.
How do I work out the net from a VAT-inclusive total?
Divide the gross by 1.2 for standard-rated items: =Gross/1.2. The VAT element is then =Gross-Gross/1.2.
How often do I submit a VAT return?
Most UK businesses file quarterly. Your VAT online account shows your exact periods and deadlines, which are usually one month and seven days after the quarter ends.
