Free MTD Spreadsheet Templates (Excel and Google Sheets)

If you are committed to using spreadsheets for Making Tax Digital, you need a properly structured template that meets HMRC’s requirements. A blank Excel file with some numbers typed into it is not MTD compliant. Your spreadsheet needs specific columns, correct VAT formulas, proper categorisation, and a connection to bridging software for submission. This guide provides the exact template structures you need, with column layouts, formula examples, and step-by-step bridging connection instructions.

Before using any spreadsheet template, understand the critical compliance point: HMRC requires digital records with digital links. Data must enter your spreadsheet via digital means (bank statement CSV import, EPOS export), not by re-typing from paper receipts. Breaking this rule makes your spreadsheet non-compliant regardless of how well-structured it is.

Important disclaimer: We provide these template structures to help businesses understand what a compliant MTD spreadsheet looks like. For most businesses, proper accounting software (especially FreeAgent free via NatWest) is a better, faster, and more reliable solution. See our free MTD software guide for genuinely zero-cost alternatives that eliminate the manual work spreadsheets require.

What Your MTD Spreadsheet Must Include

HMRC requires specific data fields in your MTD records. Your spreadsheet template must capture all of these:

Required Fields for MTD VAT

• Date of transaction (tax point date)
• Description of goods or services
• Net value excluding VAT (in GBP)
• VAT rate applied (20%, 5%, 0%, or Exempt)
• VAT amount
• Gross value including VAT
• Whether it is a sale (output) or purchase (input)
• Category (for your own management, not HMRC required but strongly recommended)

Required Fields for MTD ITSA (From April 2026)

• Date of income received or expense paid
• Description or source
• Amount (income or expense)
• Category (trading income, property income, expense type)
• Quarter allocation (Q1, Q2, Q3, Q4)

Template 1: MTD VAT Sales Record

Use this structure for recording all sales (output VAT). One row per invoice or transaction.

Column Layout

Key Formulas

VAT Amount (Cell G2): =E2*F2

Gross Amount (Cell H2): =E2+G2

Quarter allocation (Cell I2): =IF(AND(MONTH(A2)>=4,MONTH(A2)<=6),”Q1″,IF(AND(MONTH(A2)>=7,MONTH(A2)<=9),”Q2″,IF(AND(MONTH(A2)>=10,MONTH(A2)<=12),”Q3″,”Q4″)))

Cash basis note: If you use cash accounting for VAT, column K (Payment Date) determines which quarter the transaction falls in, not column A (Invoice Date). Your quarter allocation formula should reference column K instead of column A for cash basis businesses.

Template 2: MTD VAT Purchase Record

Use this structure for recording all purchases (input VAT). One row per bill, receipt, or expense.

Column Layout

Recommended Expense Categories

Template 3: MTD VAT Return Summary (The 9 Boxes)

This sheet pulls data from your Sales and Purchases sheets to calculate the 9 boxes of your VAT return. These are the cells your bridging software reads when submitting to HMRC.

VAT Return Summary Layout

How bridging software reads this: Your bridging software (VitalTax, 123Sheets, ANNA, etc.) reads specific cells from this summary sheet. During setup, you tell the bridging software which cells contain Box 1 through Box 9. It reads those cells and transmits the values to HMRC. The formulas calculate everything automatically from your Sales and Purchases sheets.

Template 4: MTD ITSA Quarterly Income and Expense Tracker

For sole traders and landlords preparing for MTD ITSA from April 2026, this template tracks quarterly income and expenses for submission.

Income Sheet Layout

Expense Sheet Layout

Quarterly Summary Sheet

Note: MTD ITSA uses cash basis as the default from April 2024. Under cash basis, use ‘Date Received’ for income and ‘Date Paid’ for expenses. Under accruals, use invoice dates instead.

Template 5: Landlord Rental Income Tracker

Column Layout (One Row Per Month Per Property)

Column G (Mortgage Interest) is tracked separately because Section 24 restrictions mean residential BTL mortgage interest is NOT deducted as an expense for Income Tax. Instead, it generates a 20% tax reducer applied at year-end. Your quarterly MTD ITSA submission reports rental income minus allowable expenses (columns H through K), with mortgage interest reported separately.

How to Connect Your Spreadsheet to Bridging Software

Step 1: Choose Your Bridging Software

Select an HMRC-approved bridging tool. See our free MTD software guide for options. VitalTax (from £15/year) and 123Sheets (£40/year) are the most popular. ANNA offers free bridging with their business account.

Step 2: Create Your VAT Return Summary Sheet

Your bridging software needs a summary sheet with the 9 VAT boxes in specific cells. Use Template 3 above. Most bridging tools let you choose which cells contain Box 1 through Box 9 during setup.

Step 3: Map Cells in Bridging Software

1. Open your bridging software
2. Select ‘Connect Spreadsheet’ or similar
3. Point it to your Excel or Google Sheets file
4. Map each VAT box to the correct cell (e.g., Box 1 = cell B3 on Summary sheet)
5. Save the mapping (one-time setup)

Step 4: Authenticate with HMRC

Your bridging software connects to HMRC via Government Gateway. Follow the MTD registration walkthrough for the authentication steps. This is a one-time setup.

Step 5: Submit Each Quarter

6. Complete your Sales and Purchases sheets for the quarter
7. Check the VAT Return Summary sheet formulas have calculated correctly
8. Open bridging software
9. Click ‘Submit to HMRC’
10. Bridging software reads the cells, transmits to HMRC, returns confirmation
11. Save the confirmation number

Maintaining Digital Links in Spreadsheets

This is where most spreadsheet users fall foul of HMRC’s MTD record keeping rules. Digital links must be maintained throughout your record-keeping process. Here is what counts and what breaks compliance:

Compliant Data Entry Methods

• Importing bank statement CSV file into your spreadsheet (digital link maintained)
• Copying and pasting data from online banking portal into cells (acceptable)
• Linking cells between separate spreadsheets using formulas (digital link)
• Importing EPOS or till data via CSV export (digital link)
• Using Google Sheets IMPORTDATA or IMPORTRANGE functions (digital link)

NON-Compliant Data Entry Methods

• Reading a paper receipt and typing the figures into cells (broken digital link)
• Reading a printed bank statement and typing totals (broken)
• Using paper as an intermediate step between two digital systems (broken)
• Typing figures from memory or estimation (broken and potentially fraudulent)

The practical reality: Maintaining digital links in spreadsheets is genuinely difficult for businesses that deal with paper receipts (retail, trades, hospitality). Every paper receipt needs scanning or photographing before the data enters your spreadsheet. This is why proper accounting software with mobile receipt capture (built into FreeAgent, Xero, QuickBooks) is dramatically easier than spreadsheet-based MTD compliance.

7 Common Spreadsheet Mistakes That Break MTD Compliance

1. No Quarter Allocation

Transactions must be allocated to the correct VAT quarter. Without a quarter column and formula, you cannot accurately calculate the 9 boxes for each period.

2. Mixing Personal and Business Transactions

If your bank statement includes personal purchases alongside business ones, you must exclude personal items from your MTD records. A separate column flagging business vs personal keeps your spreadsheet clean.

3. Wrong VAT Rate Application

Applying 20% to zero-rated items (food, books, children’s clothing) or missing reduced-rate items (5% on energy) creates systematic errors. Build a VAT rate dropdown (20%, 5%, 0%, Exempt, Outside Scope) to prevent manual errors.

4. No Receipt Evidence Trail

Each purchase row should reference a digital receipt (scanned image, email attachment, bank statement line). Column L in Template 2 (‘Digital Receipt?’) exists for this reason. HMRC can request evidence for any transaction.

5. Overwriting Formulas

Accidentally typing a number into a formula cell breaks the calculation chain permanently. Protect formula cells in your spreadsheet to prevent overwriting. In Excel, use Sheet Protection on formula cells while leaving data entry cells unlocked.

6. Not Backing Up

Local Excel files can be lost through hardware failure, accidental deletion, or file corruption. Use OneDrive, Google Drive, or Dropbox automatic sync to maintain backups. HMRC requires 6 years of record retention. Losing your spreadsheet means losing compliance evidence.

7. Manual Re-Typing Between Sheets

Copying figures manually from your Sales sheet into the VAT Return Summary (rather than using cell references or formulas) breaks the digital link chain. Always use formulas that reference the source cells. Manual re-typing is the single most common spreadsheet compliance failure.

When You Should Stop Using Spreadsheets for MTD

Spreadsheets work for very simple businesses. They stop working when:

• You process more than 50 transactions per month (manual entry becomes unsustainable)
• You need multi-user access (spreadsheets do not handle concurrent editing well)
• You make errors that your spreadsheet does not catch (no validation rules)
• Your accountant asks you to switch (they cannot work efficiently with your spreadsheet)
• You spend more than 3 hours per quarter on MTD VAT (software would cut this to 30 minutes)
• You are preparing for MTD ITSA (quarterly ITSA submissions on top of quarterly VAT makes spreadsheet workload unsustainable for most businesses)
• You have been penalised for a record-keeping failure (HMRC found broken digital links)

If any of these apply, see our free MTD software guide for zero-cost alternatives, or our full best apps comparison for the complete landscape.

Case Study: From Broken Spreadsheet to Compliant Setup in One Week

A mobile hairdresser based in the Cardiff area contacted us in early 2026 after receiving a letter from HMRC requesting evidence of her MTD VAT digital records. She had been using an Excel spreadsheet for 2 years, submitted via VitalTax bridging software. The submission itself was technically compliant, but her underlying records were not.

The problems:

• All transaction data typed manually from paper receipts (broken digital links)
• No quarter allocation column (she calculated quarterly totals manually on a separate sheet)
• Manual totals on the VAT summary sheet (not formula-linked to the transaction data)
• No backup of spreadsheet files (stored only on one laptop)
• Three quarters of purchase receipts had no digital scan or photograph

What we did:

12. Restructured her spreadsheet using Template 1 and Template 2 above
13. Added formula-driven VAT summary sheet (Template 3) linked to transaction data
14. Set up bank statement CSV import to replace manual data entry (digital link restored)
15. Photographed and stored all remaining paper receipts digitally using Dext
16. Moved spreadsheet to Google Drive for automatic backup and HMRC-accessible retention
17. Reconfigured VitalTax to read from the new formula-driven summary cells
18. Prepared documentation explaining the newly compliant system for HMRC’s review

Outcome: HMRC accepted the newly compliant records. No penalties issued because we had proactively fixed the compliance failures before HMRC’s follow-up. Ongoing quarterly workflow now takes approximately 90 minutes (down from 4 hours with the old broken system). Our one-off remediation fee: £280. We also recommended she consider switching to FreeAgent free via Mettle to eliminate the spreadsheet complexity entirely, which she plans to do before MTD ITSA starts in April 2027.

MTD Spreadsheet Templates: FAQs

Can I use Excel for MTD?

Yes, with bridging software connecting to HMRC. Your spreadsheet must maintain digital links (data entering via CSV import, not manual re-typing from paper). Bridging software transmits the 9 VAT return boxes from specified cells. Legal but more work than proper accounting software.

What columns do I need in my MTD spreadsheet?

Minimum: date, description, net amount, VAT rate, VAT amount, gross amount, quarter allocation, and sale/purchase indicator. Purchase sheets should also include expense category and digital receipt confirmation.

Which bridging software works with Excel?

VitalTax (from £15/year), 123Sheets (£40/year), ANNA (free with business account), Absolute VAT Filer (from £20/year), Tax Optimiser (from £25/year). All read specified cells from your spreadsheet and submit to HMRC.

Do I need separate sheets for sales and purchases?

Strongly recommended. Separating sales (output VAT) from purchases (input VAT) makes the VAT return summary formulas cleaner and reduces errors. A third ‘Summary’ sheet pulls from both to calculate the 9 boxes.

How do I maintain digital links in a spreadsheet?

Import data digitally: bank statement CSV, EPOS CSV export, or copy-paste from online banking. Never re-type from paper. Use formulas between sheets (not manual re-entry). The chain from source data to submitted return must be unbroken digitally.

Can I use Google Sheets instead of Excel?

Yes. Google Sheets works with bridging software that supports it (123Sheets, some others). Google Sheets has the advantage of automatic cloud backup and version history. IMPORTDATA and IMPORTRANGE functions maintain digital links between sheets.

What happens if HMRC checks my spreadsheet records?

HMRC can request your underlying records at any time. They check: digital links are maintained, transaction data supports the submitted VAT figures, receipts exist digitally, and records have been retained for 6 years. Non-compliance triggers penalties of £400 per failure.

Is a spreadsheet good enough for MTD ITSA?

Technically possible but increasingly impractical. MTD ITSA requires quarterly submissions on top of any MTD VAT submissions. The combined workload of maintaining spreadsheets for both is substantial. Most accountants recommend switching to proper software before MTD ITSA starts.

How do I protect my spreadsheet from accidental changes?

Use Excel’s Sheet Protection to lock formula cells while leaving data entry cells unlocked. Password-protect the summary sheet specifically. In Google Sheets, use Protected Sheets and Ranges for the same purpose.

Can my accountant work with my spreadsheet?

Yes, but most chartered accountants prefer working with proper accounting software. If you insist on spreadsheets, share via Google Sheets (easy collaboration) or send Excel files. Be aware your accountant may charge more for spreadsheet-based work due to the extra manual effort.

How long does quarterly MTD VAT take with spreadsheets?

Typically 2 to 4 hours per quarter for a simple business (50 to 150 transactions). With proper accounting software and bank feeds, the same work takes 15 to 30 minutes. The time difference is the main reason accountants recommend software over spreadsheets.

Do I need a separate spreadsheet for each tax year?

Not required but recommended. Starting a fresh workbook each tax year keeps file sizes manageable and makes year-end easier. Archive previous years in cloud storage for the required 6-year retention period.

What if I make an error in my submitted spreadsheet figures?

Small errors (under £10,000 net) can be corrected on the next VAT return. Large errors require a VAT652 form to HMRC. Keeping formula-linked spreadsheets reduces errors because the summary auto-calculates from your transaction data.

Are there downloadable MTD templates available?

HMRC does not provide official templates. Some bridging software providers include basic templates. The template structures in this guide can be built in 30 to 60 minutes in Excel or Google Sheets. Alternatively, contact us for a pre-built template matched to your business type.

Should I switch from spreadsheets to accounting software?

If you process more than 50 transactions per month, make regular errors, spend more than 3 hours per quarter on VAT, or are preparing for MTD ITSA on top of VAT, yes. FreeAgent free via NatWest eliminates the spreadsheet complexity at zero cost.

Need Help Setting Up Your MTD Spreadsheet? Free Consultation

Whether you need a properly structured spreadsheet, help connecting to bridging software, or advice on whether to switch to proper accounting software, we can help. Most businesses find that a 30-minute consultation saves hours of trial and error with spreadsheet setup.

At MTD – Making Tax Digital (part of B1 Accountants), we can:

• Build a custom MTD spreadsheet template matched to your business
• Set up bridging software connection to HMRC
• Audit your existing spreadsheet for digital links compliance
• Migrate you from spreadsheets to FreeAgent (free) or Xero/QuickBooks
• Handle ongoing MTD submissions at fixed monthly fees from £40