How do I check my VAT return against my sales?

Sum net sales and VAT from the sales ledger for the period, split by VAT rate, and compare with the return's sales boxes (Box 6 and Box 1 in the UK).

Why doesn't my VAT return match my sales ledger?

Common causes are credit notes, invoices entered after the return was prepared, cash vs invoice accounting, and wrong VAT codes.

What's the difference between Box 1 and Box 6?

On a UK return, Box 1 is VAT due on sales; Box 6 is total sales excluding VAT.

How to reconcile a VAT return with your sales ledger

By the Analistable team · Updated 2026-10-07 · 5 min read

Rebuild the sales boxes of the VAT return from your invoice list. For a UK return, Box 6 is total sales excluding VAT and Box 1 is the VAT due on sales. Sum the ledger's net and VAT columns for the return period, split by VAT rate, and compare with the return. Differences usually come from credit notes, invoices dated in a different period, or the VAT scheme (cash vs invoice accounting).

Part of our guide: How to reconcile data in Excel

Rebuild the boxes

Box 6 (net sales)  =SUMIFS(Sales[Net], Sales[Date], ">=" & PeriodStart, Sales[Date], "<=" & PeriodEnd)
Box 1 (VAT)        =SUMIFS(Sales[VAT], Sales[Date], ">=" & PeriodStart, Sales[Date], "<=" & PeriodEnd)
Check by rate      =SUMIFS(Sales[Net], Sales[Rate], 0.2, …) * 0.2

Include credit notes as negative lines. If you use cash accounting, filter on the payment date instead of the invoice date.

Worked example

Quarter to 30 September
	Ledger	VAT return	Difference
Net sales at 20%	48500
Net sales at 0%	6200
Box 6: total net sales	54700	55900	-1200
Box 1: VAT on sales	9700	9940	-240

The return is £1,200 net and £240 VAT higher than the ledger. A £1,200 credit note issued in September was entered in the ledger but left out of the return — an over-declaration to correct.

Checks by VAT rate

VAT at 20% should be exactly 20% of the 20%-rated net sales (allowing for rounding per invoice). If Box 1 ÷ standard-rated net isn't close to 0.20, an invoice probably has the wrong rate code. A quick per-invoice check: =ROUND([@Net] * [@Rate], 2) - [@VAT] should be 0 or a penny.

Typical causes of differences

Credit notes dated in the period but missing from one side.
Invoices dated on the last day of the quarter entered after the return was prepared.
Cash accounting: payments received, not invoices raised, drive the return.
Zero-rated or exempt sales coded with the wrong rate.
Sales from other systems (marketplace, POS) not posted to the ledger yet.

This guide covers the sales side; purchases (input VAT) reconcile the same way against the purchase ledger. Always confirm treatment with your accountant or HMRC guidance.

Keep the reconciliation with the return

Save the working (ledger totals, return figures and explanations) alongside each submitted return. It answers most questions from your accountant or the tax authority without redoing the work.

Step by step

Export the sales ledger (or day book) for the VAT period at line level: invoice or credit note number, date, customer, net, VAT, rate code.
If sales come from more than one system — shop, marketplace, till — export each and stack them with a Source column.
Filter to the period using the date that drives your scheme: invoice date for standard accounting, payment date for cash accounting.
Rebuild Box 6 and Box 1 with the formulas above, and pivot net and VAT by rate code.
Compare each figure with the return as submitted (or the draft from your software) and list every difference with its cause.

In Google Sheets, SUMIFS with date criteria works the same way; make sure the date column holds real dates, not text, or the criteria will silently return zero.

Second example: a wrong rate code

The rate check shows VAT on standard-rated sales below 20% of their net value. Pivoting by rate code and customer finds the cause:

Rate check for the quarter
Rate code	Net	VAT recorded	Expected VAT	Difference
20%	46500	9300	9300	0
0%	8200	0	0	0
of which INV-1188 (should be 20%)	2000	0	400	-400

Invoice INV-1188 for £2,000 was coded zero-rated, but the goods were standard-rated. VAT of £400 (20% of £2,000) is under-declared. Total net sales stay at £54,700, so Box 6 is unaffected; only Box 1 is wrong. How to correct an error depends on its size and timing, so check the current HMRC guidance or ask your accountant.

Rounding differences

If VAT is calculated per line on invoices but per invoice in the ledger, or the other way round, small differences of a few pence per invoice add up. A difference that is small, roughly proportional to the number of invoices and has no single invoice behind it is usually rounding. Test it by recalculating VAT per invoice with ROUND and comparing with the recorded VAT: if the totals then agree, document the rounding rather than hunting for a missing entry.

Pivot by rate in SQL

SELECT rate,
       ROUND(SUM(net), 2)              AS net,
       ROUND(SUM(vat), 2)              AS vat_recorded,
       ROUND(SUM(net) * rate, 2)       AS vat_expected
FROM sales
WHERE invoice_date BETWEEN DATE '2026-07-01' AND DATE '2026-09-30'
GROUP BY rate
ORDER BY rate DESC;

Run it on the stacked ledger from every sales system, then compare the totals with the return. For stacking exports from several systems first, see combining data from multiple sources.

Troubleshooting

Symptom, cause, fix
Symptom	Likely cause	Fix
Ledger higher than the return by one invoice	Invoice posted after the return was prepared	Check posting dates against the submission date
Return higher than the ledger	Credit note left out of the return	Filter the ledger for negative lines in the period
VAT ÷ standard-rated net is not about 20%	Wrong rate code on some lines	Pivot by rate and customer, then check invoices
Totals match but individual months don't	Period boundaries different from the quarter	Filter on the exact VAT period dates
Marketplace sales missing	Marketplace summary posted later	Post the summary before preparing the return

Checking the result

The reconciliation is complete when every difference between ledger and return has a named cause and an amount, and the causes add up to the total difference. In the first example, a single £1,200 credit note explains both the Box 6 difference and the £240 Box 1 difference (20% of £1,200), so nothing is left unexplained. Keep a separate line for anything you decide not to correct, with the reason. To compare two versions of the ledger export — before and after late postings — the compare spreadsheets tool shows which invoices were added or changed.

Common mistakes

Reconciling to a ledger report run after late postings, rather than to the figures that existed when the return was prepared.
Leaving out sales from a till, marketplace or second invoicing system that are posted to the ledger only as monthly summaries.
Filtering on invoice date when the business uses cash accounting, or the other way round.
Ignoring small differences that turn out to be a systematic rate-code problem.

Doing it on a Mac

Excel for Mac supports SUMIFS, PivotTables and the formulas above in the same way as Windows. If your ledger export opens as a single column, use Data → From Text (or Text to Columns) and set the date column's format explicitly, because a CSV with day-first dates can be read as month-first and move invoices into the wrong quarter.

Frequently asked questions

How do I check my VAT return against my sales?: Sum net sales and VAT from the sales ledger for the period, split by VAT rate, and compare with the return's sales boxes (Box 6 and Box 1 in the UK).
Why doesn't my VAT return match my sales ledger?: Common causes are credit notes, invoices entered after the return was prepared, cash vs invoice accounting, and wrong VAT codes.
What's the difference between Box 1 and Box 6?: On a UK return, Box 1 is VAT due on sales; Box 6 is total sales excluding VAT.