Help Centre · VAT · 2 min read

Map your spreadsheet in VAT Settings

Tell Tax Optimiser which sheet and cells of your spreadsheet hold the nine VAT-return boxes.

Tax Optimiser Updated 11 Jun 2026

Your VAT figures stay in your own spreadsheet — each quarter you'll upload it and Tax Optimiser reads the nine HMRC boxes straight out of it. VAT Settings is where you tell it which sheet and cells to read. You set this up once; after that the quarterly routine is just upload, check, send.

The VAT Settings screen

Open VAT → VAT Settings from the sidebar. The VAT Submission Software name at the top is what appears on HMRC submissions as the originating software — leave it as Tax Optimiser unless told otherwise.

The VAT Return Settings screen with the software template dropdown and cell mapping fields

Start from a software template…

If your spreadsheet is an export from a known system, pick it from Software Template — Sage, Access Dimensions, Dynamics NAV, MYOB, Manager.io and others are listed. Choosing one pre-fills the sheet name and all nine cell references; you can still adjust any of them afterwards.

The Software Template dropdown listing Access Dimensions, Account Master, Dynamics NAV, Manager.io, MYOB and more

…or map the cells yourself

For your own spreadsheet, fill the mapping in directly:

  1. Excel Sheet Name — the name of the worksheet tab along the bottom of Excel (for example VAT). It must match exactly, including spaces — it is not the filename.
  2. The nine cell references — one per HMRC box, such as B4. Each must point at the cell holding that box's final figure.

Press Save. If you'd rather adopt a ready-made layout, the upload screen (next article) offers a Download Example Front Sheet Excel File that matches the default mapping out of the box.

The VAT Return cell mapping with sheet name VAT and cells B4 to B12

The nine boxes at a glance

  • Box 1 — VAT due on sales and other outputs.
  • Box 2 — VAT due on acquisitions of goods from EU member states (Northern Ireland traders only; 0 for most businesses).
  • Box 3 — total VAT due: Box 1 + Box 2.
  • Box 4 — VAT reclaimed on purchases and other inputs.
  • Box 5 — net VAT due to (or reclaimable from) HMRC: Box 3 − Box 4.
  • Box 6 — total value of sales excluding VAT, in whole pounds.
  • Box 7 — total value of purchases excluding VAT, in whole pounds — including purchases that carried no VAT, so don't expect it to be exactly five times Box 4.
  • Box 8 — total value of goods supplied to EU member states (NI traders only).
  • Box 9 — total value of goods acquired from EU member states (NI traders only).

Next: Connect to HMRC and find your VAT periods.

The short version

Map your spreadsheet in VAT Settings — in brief

VAT Settings is a one-off job: pick a software template (Sage, Dynamics NAV, MYOB and others) or map the sheet name and nine cell references to your own spreadsheet.

The sheet name is the worksheet tab along the bottom of Excel, not the filename, and must match exactly.

If you would rather not map anything, download the example front sheet from the upload screen - it matches the default mapping.