Sage 50 Excel Import Problems — Why They Happen and How to Fix Them

Sage 50 is one of the most widely used accounting packages in the UK, and its ability to import data from spreadsheets is genuinely useful — whether you're bringing in customer and supplier records, posting transactions in bulk, or importing payroll data. But import failures are frustratingly common, and the error messages Sage gives you are rarely helpful enough to tell you what actually went wrong.

This guide covers the two main categories of Sage 50 import problems: the add-in compatibility issue (which stops the software from loading in Excel altogether) and the data file problems (which cause imports to fail or produce incorrect results even when the software loads fine).

Problem 1: The Sage 50 Payroll EIR add-in won't load

The Employee Import Record (EIR) is an Excel add-in that Sage 50 Payroll installs to help you format employee data for import. If you've recently set up a new computer, upgraded from an older version of Office, or switched to Microsoft 365, you may find the EIR doesn't appear in Excel — or that Sage throws an error when it tries to install it.

The reason is the same 32-bit/64-bit issue that affects VT Transaction and other accounting add-ins. The EIR is a 32-bit COM add-in, and it cannot run inside 64-bit Excel. Most new Microsoft 365 installations default to 64-bit, which means the add-in silently fails to load.

The fix is to reinstall Microsoft Office using the 32-bit installer. Your Microsoft 365 subscription covers both versions — you'll find the 32-bit download option under "Other install options" when you go to download Office from your Microsoft account page (account.microsoft.com). Uninstall your current Office installation first, then install the 32-bit version. The EIR should then load correctly.

Problem 2: Data import failures and incorrect data

Even when Sage 50 itself is running fine, getting data in from Excel is prone to failure. Sage's import functions are strict about what they'll accept — understandably so, because importing corrupt or malformed data into an accounting system can cause serious problems that are difficult to undo.

These are the most common reasons a Sage 50 import fails or produces wrong results:

• Macros and VBA code in the workbook A file that has ever had automation built in — even if the macros have been "deleted" from the user interface — can still have VBA modules embedded in it. Sage's file reader can trip over these unexpectedly.
• Dates in the wrong format Sage 50 UK expects dates in DD/MM/YYYY format. If your spreadsheet was created on a US-locale machine, or the dates were pasted from a source that uses MM/DD/YYYY, every single date will import incorrectly — and because the values are still recognisable as dates, Sage won't warn you. You'll discover the problem later, when invoices are dated in the wrong month.
• Formulas instead of values Cells that contain formulas appear to show a number, but the underlying content is a formula string. Some Sage import routes read the raw cell value — which means they import the formula text, not the result. This is especially common with cells that reference totals from other sheets.
• Merged cells and extra header rows Sage expects a clean, flat structure — one header row, then data rows. Merged cells that span columns, decorative header rows, subtotal rows, and totals rows at the bottom all disrupt this structure and cause data to be imported into the wrong fields.
• Negative amounts where positive is expected Some Sage import routes expect all amounts to be entered as positive values, with the transaction type (debit/credit, payment/receipt) determining the sign. If your source data has negative numbers for outgoings, these will import with an incorrect sign.
• Sheet protection A worksheet or workbook with protection flags set may prevent Sage from reading the cell data programmatically, even though you can see and interact with the data perfectly well in Excel.

How to prepare a clean file for Sage 50 import

The manual approach to fixing these issues involves going through each problem one by one in Excel: opening the VBA editor to check for and remove modules, using Paste Special to convert formulas to values, checking date formats cell by cell, removing merged cells, and clearing protection settings. It works, but on a file of any size it's time-consuming and easy to miss something.

SheetPrep automates the whole process. Here's what it does to your file:

1. Strips all macros and VBA code — not just the surface-level macros, but the underlying VBA modules embedded in the workbook file.
2. Removes embedded objects and ActiveX controls — charts, images, OLE links and any other non-data content that can confuse the importer.
3. Converts formulas to static values (optionally) — every formula cell is replaced with its calculated result, so Sage reads actual numbers and text rather than formula strings.
4. Normalises dates to DD/MM/YYYY — date cells are converted to explicit text strings in the format Sage 50 UK expects, so there's no ambiguity about which number is the day and which is the month.
5. Converts negative amounts to positive (optionally) — useful for any import route that expects unsigned amounts.
6. Clears protection flags — sheet and workbook protection is removed before the file is written, so Sage can access the data without restriction.

The result is a clean file with none of the hidden content that causes import failures. You can output as XLS, XLSX, or CSV depending on what Sage's import function expects, and if your source file has multiple sheets, SheetPrep will export each one as a separate CSV in a ZIP archive — useful when you have transactions from different periods or accounts on different tabs.

A note on Sage 300 Financial Reporter

Sage 300's Financial Reporter module uses Excel as its primary interface for building and running financial reports. Report layouts are created in Excel workbooks and processed by Sage 300 to pull in live data. If those workbooks have accumulated VBA modules, broken external references, or embedded objects over years of use, the Financial Reporter can produce incorrect output or fail to run the report entirely.

Cleaning the workbook with SheetPrep before using it as a Financial Reporter template — stripping macros, removing embedded objects, converting formulas to values — often resolves these issues without needing to rebuild the report from scratch.

Still stuck?

If you've cleaned the file and Sage 50 is still rejecting it, the next thing to check is the column structure. Each Sage import type has a required column layout — customer imports need specific fields in a specific order, transaction imports need a particular CSV structure. Sage's own import documentation (available in the Sage 50 help files or on their support site) shows the exact column requirements for each import type. SheetPrep takes care of the file quality; getting the column structure right is a separate step that needs to match your specific import type.