Help Centre · VAT for Accountants · 3 min read

Map the client's VAT spreadsheet

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

Tax Optimiser Updated 17 Oct 2026

Each client’s VAT figures stay in their own spreadsheet, and you upload it each quarter so Tax Optimiser can read the nine HMRC boxes straight out of it. VAT Settings is where you tell it which sheet and cells to read. VAT settings are stored per organisation, so you set this up once for each client; after that the quarterly routine is just upload, check, send.

Open VAT Settings

Inside the client, open HMRC VAT → VAT Settings from the sidebar. You can also open the same settings without leaving the client’s returns: choose Actions → VAT Settings on the VAT Returns list or on a return. 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 the client exports their figures from a known system, pick it from Software Template — Sage 50, Sage 200, Access Dimensions, QuickBooks desktop, 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. Press Save.

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

…let AI find the cells…

If the client has their own layout, Tax Optimiser can work out the mapping for you. Click Find the cells with AI, choose the client’s spreadsheet (Excel .xlsx, .xls, .xlsm or .csv), tick the box to confirm you understand, then click Find cells with AI.

Before you use it, note that the spreadsheet is sent to a third-party AI provider so it can read the layout. Don’t use this option if that isn’t acceptable for the client’s data.

AI fills in the sheet name and the nine cell references, and shows the figure it found under each cell reference. Each figure is read from the file the same way a normal upload reads it. If a cell holds text instead of a number, it is shown in red. If Box 3 doesn’t equal Box 1 + Box 2, or Box 5 doesn’t equal Box 3 − Box 4, you will see a warning.

Check every figure against the client’s spreadsheet and correct any cell before you press Save. Nothing is saved until you do. AI can make mistakes, and you remain responsible for the return you send to HMRC.

…or map the cells yourself

For a client’s 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 the client would rather adopt a ready-made layout, the upload screen 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.
  • 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 for the client.

The short version

Map the client's VAT spreadsheet — in brief

VAT Settings are stored per client and set up once. Pick a software template (Sage, Access Dimensions, QuickBooks, MYOB and others), let AI find the cells in the client's spreadsheet, or map the sheet name and nine cell references yourself.

The AI option sends the spreadsheet to a third-party AI provider. It shows the figure it found in each cell, and nothing is saved until you check the figures and press Save.

The sheet name is the worksheet tab along the bottom of Excel, not the filename, and must match exactly. VAT Settings can also be opened from Actions on the VAT Returns screens.