How to Prepare Your Expense Spreadsheet for MTD Import

Why Your Spreadsheet Probably Won't Import First Time

If you've been keeping your income and expenses in a spreadsheet, you're already ahead of many people starting MTD. The problem is that spreadsheets built for your own reference rarely match the format that bridging software needs to read. Column names are inconsistent, dates are formatted differently across rows, and categories that make sense to you don't map to the HMRC headings used in a quarterly update. The result: an import that fails, or worse, one that appears to succeed but puts numbers in the wrong places.

This guide walks you through exactly how to structure your spreadsheet before you try to import it into Affordable MTD. It covers column naming, date formats, how to handle mixed income (if you have both self-employment and rental income), and the most common formatting mistakes that cause failures. If you follow the steps here, your first import should work cleanly.

Start With a Clean, Flat Sheet

Bridging software reads data row by row. Each row should represent a single transaction - one income receipt or one expense. This is called a "flat" format, and it's different from the summary tables or pivot layouts many people naturally build in spreadsheets.

Before you do anything else, check your spreadsheet for these structural problems:

• Merged cells - these break imports entirely. Unmerge everything.
• Summary rows or totals in the middle of your data - delete them or move them to a separate sheet.
• Multiple header rows - you need exactly one row of column headers at the top.
• Blank rows between transactions - remove them. The importer stops reading when it hits a blank row.
• Data split across multiple tabs - consolidate everything into one sheet before importing.

If your current spreadsheet uses multiple tabs (one per month, for example), copy all the rows into a single sheet. Keep the column headers only on the first row.

The Columns Affordable MTD Expects

Your spreadsheet needs specific column headers for the import to work. The names need to match what Affordable MTD is looking for. Here are the required and optional columns:

Required columns

• Date - the date of the transaction (see formatting guidance below)
• Amount - the value of the transaction in pounds, without a £ sign
• Description - a brief note about what the transaction was
• Type - whether this is income or expense

Recommended additional columns

• Category - the MTD expense or income category (more on this below)
• Income Source - used when you have both self-employment and rental income
• Reference - an optional invoice or receipt number for your own records

Date Formatting: The Most Common Cause of Import Failures

Date formatting causes more failed imports than anything else. Spreadsheet applications like Excel and Google Sheets store dates internally as numbers and display them in whatever format your regional settings prefer. When you export to CSV, that display format is what gets saved - and if it's not what the importer expects, rows get rejected or dates get misread.

Use DD/MM/YYYY

Affordable MTD expects dates in DD/MM/YYYY format. For example: 14/03/2025. Do not use:

• 14-03-2025 (hyphens instead of slashes)
• 03/14/2025 (US-style month first)
• 14 March 2025 (written out)
• 14/3/25 (two-digit year or single-digit month)

How to fix date formatting in Excel

1. Select your date column.
2. Right-click and choose "Format Cells".
3. Under "Number", select "Custom".
4. Type DD/MM/YYYY in the format box and click OK.
5. When you save as CSV, Excel will write the dates in that format.

How to fix date formatting in Google Sheets

1. Select your date column.
2. Go to Format > Number > Custom date and time.
3. Set the format to DD/MM/YYYY.
4. When you download as CSV, it will use that format.

Amount Formatting: Keep It Simple

The Amount column should contain plain numbers only. No currency symbols, no comma-thousand separators, no text. These all cause import failures:

• £150.00 - remove the £ sign
• 1,250.00 - remove the comma
• "150.00" - remove any quotation marks
• 150.00 expenses - no text in the amount column

Use the number 150.00 or 150. Both work. Pence should be entered as decimals: 149.99.

Do not use negative numbers to represent expenses. Use the Type column (set to "expense") to tell the importer that this is money going out, not coming in.

The Category Column: Matching HMRC's Headings

HMRC's quarterly update uses specific income and expense categories - not the free-text descriptions you might use in your own records. When you import, Affordable MTD needs to map each row to one of those categories.

If you're filling in the Category column yourself, use the category names exactly as they appear in Affordable MTD. The main ones for sole traders are:

Self-employment expense categories

• Cost of goods sold
• Construction industry costs
• Staff costs
• Travel and subsistence
• Premises and maintenance
• Advertising and marketing
• Office and admin
• Financial charges
• Professional fees
• Other expenses

Property (landlord) expense categories

• Rent rates and insurance
• Property repairs and maintenance
• Loan interest and finance costs
• Legal management and professional fees
• Cost of services provided
• Other allowable property expenses

Not sure which category a particular expense belongs to? See our guides on self-employed business expenses in MTD and landlord expenses in MTD for help with tricky cases.

If you leave the Category column blank or use category names that don't match, Affordable MTD's AI categorisation will attempt to fill them in from the Description column. This works well for clear descriptions like "printer ink" or "office rent", but less well for vague entries like "BACS payment" or "transfer".

Handling Mixed Income: Self-Employment and Rental on the Same Sheet

If you have both self-employment income and rental income, HMRC treats these as separate income sources for MTD purposes. They need to be reported separately in your quarterly update - not lumped together.

When importing into Affordable MTD, you need an Income Source column that tells the software which source each row belongs to. Use:

• Self-employment - for sole trader income and expenses
• Property - for rental income and landlord expenses

Every row in your spreadsheet should have one of these values in the Income Source column. Do not leave it blank for any row, even if you think it's obvious from context.

If you have multiple rental properties, you may also need to identify which property each income or expense row relates to. Affordable MTD handles multiple properties as separate property accounts within the same annual period. Check our guide on which expenses count for self-employment vs property if you're unsure how to split mixed costs.

What a Correctly Formatted Row Looks Like

Here is an example of a correctly formatted row for a sole trader expense:

• Date: 03/04/2025
• Description: Monthly broadband - home office
• Amount: 39.99
• Type: expense
• Category: Office and admin
• Income Source: Self-employment
• Reference: INV-2025-0041

And here is an example of a correctly formatted row for rental income:

• Date: 01/04/2025
• Description: April rent - Flat 3B
• Amount: 950.00
• Type: income
• Category: Rental income
• Income Source: Property
• Reference: TEN-APR-01

Saving in the Right File Format

Affordable MTD accepts both CSV (.csv) and Excel (.xlsx) files. Both work, but there are a few things to bear in mind:

CSV files

CSV stands for "comma-separated values". It's a plain text format that any spreadsheet application can export. It's usually the most reliable format for imports because there's no risk of Excel-specific formatting causing problems. To save as CSV in Excel: File > Save As > choose "CSV (Comma delimited)".

One thing to watch: if any of your description fields contain commas (for example, "Rent, council tax, water"), a basic CSV export may misread the columns. Wrap text fields in quotation marks if your export tool doesn't do this automatically, or avoid commas in description text.

XLSX files

If you prefer to import directly from an Excel file, make sure your data is on the first sheet (tab) of the workbook. Affordable MTD reads the first sheet only. Remove any charts, pivot tables, or formula-driven summary sections from that sheet before importing.

Checking Your File Before You Import

Before you upload anything, spend five minutes doing these checks:

1. Open the file in a plain text editor (Notepad on Windows, TextEdit on Mac set to plain text mode) and confirm dates look like DD/MM/YYYY.
2. Count the columns in your header row and confirm every data row has the same number of columns.
3. Search for any £ signs, commas in numbers, or percentage symbols in the Amount column.
4. Check that the Type column contains only "income" or "expense" - no variations like "Income" (capital I) or "exp".
5. If you have mixed income, confirm every row has an Income Source value.
6. Remove any rows that represent transfers between your own accounts - these are not income or expenses.

If you're importing historical records covering multiple quarters, check our guide on which quarter a late receipt or transaction belongs to, as the date you enter determines which quarterly update the item appears in.

Common Import Errors and What They Mean

If your import fails, Affordable MTD will usually show an error message. Here are the most common ones and what to do:

• "Unrecognised date format" - your dates are not in DD/MM/YYYY format. Go back and reformat the date column.
• "Invalid amount value" - your Amount column contains a £ sign, comma, or text. Strip these out.
• "Missing required column" - one of the required column headers is absent or misspelled. Check your header row against the list above.
• "Unrecognised income source" - the Income Source column contains a value other than "Self-employment" or "Property". Correct the spelling.
• "Row count mismatch" - the number of columns in a data row doesn't match the header row. You probably have a rogue comma or merged cell somewhere.

If you get an error that isn't listed here, download the error log from the import screen. It will show you the row number where the problem occurred, which makes it much faster to find and fix.

Ongoing Records vs Historical Upload

If you're using Affordable MTD from the start of a quarter and keeping records as you go, you can import monthly or even weekly rather than waiting until just before the deadline. Smaller, more frequent imports are easier to check and correct than one large file covering an entire quarter.

If you're importing historical records - for example, you kept records in a spreadsheet before signing up and now want to bring them into Affordable MTD - apply the same formatting rules. The only additional step is confirming which quarter each transaction falls into. Affordable MTD assigns each import to the correct quarter automatically based on the transaction date, so as long as your dates are accurate, this should work without any extra steps.

For a broader look at how spreadsheets fit into your overall MTD record-keeping, see our post on whether HMRC accepts spreadsheets for MTD.

Summary

Getting your spreadsheet into the right format before importing saves a lot of frustration. The key points: use a flat, single-sheet layout with one transaction per row; format dates as DD/MM/YYYY; keep amounts as plain numbers without symbols; use the correct column names; and if you have both self-employment and rental income, include an Income Source column on every row. Check the file in a text editor before you upload, and if you get an error, use the row number in the error log to find the problem quickly. A few minutes of preparation at this stage will save you considerably more time later.