Why Excel Files Need Reformatting for Xero
Even a clean Excel file with correct bank data requires significant reformatting before Xero will accept it. Here are the specific issues:
| Excel Issue | Xero Impact | Fix Required |
|---|---|---|
| Excel dates formatted as MM/DD/YYYY | Dates silently transposed — silent data corruption | Reformat to DD/MM/YYYY before CSV export |
| Amount cells with currency format ($) | Xero import fails or shows 0 amounts | Clear formatting, use plain decimal values |
| Separate "Debit" / "Credit" columns | Xero expects "Spent" / "Received" column names | Rename columns or merge to signed Amount |
| Summary/total rows in spreadsheet | Xero treats total row as a transaction — corrupts data | Delete all non-transaction rows before export |
| Merged cells in header row | CSV export breaks header detection | Unmerge all cells before saving as CSV |
Fastest approach: Upload the Excel file directly to Zera Books. All reformatting (dates, amounts, column names, encoding) is handled automatically. The output is a Xero Statement Import CSV ready to upload without any manual editing. Full Xero import guide here.
Xero Statement Import Column Requirements
Xero Statement Import requires specific column names. These differ from common bank export column names:
| Xero Expects | Common Bank Export Name | Format |
|---|---|---|
| Date | Transaction Date, Value Date, Post Date | DD/MM/YYYY |
| Description | Narrative, Memo, Payee, Transaction Detail | Plain text |
| Spent | Debit, Withdrawal, Amount Out, Paid | Positive decimal (no sign) |
| Received | Credit, Deposit, Amount In, Payment | Positive decimal (no sign) |
| Reference | Check No., Ref, Transaction ID | Text (optional) |
Rename Before Exporting
In Excel, rename column headers to Xero's exact names (Date, Description, Spent, Received) before saving as CSV. This prevents the column mapping step in Xero.
Excel Date Cell Formatting
Excel stores dates as numbers. When you export to CSV, the date format you see in the cell is what gets exported. Select all date cells and set format to DD/MM/YYYY before exporting.
Spent vs Received Structure
Xero's Statement Import works best with Spent and Received as separate columns. Each row has a value in one column and blank in the other — not a signed Amount column.
Save as CSV UTF-8
Use File > Save As > "CSV UTF-8 (Comma delimited)" in Excel. This prevents encoding issues with non-ASCII characters in payee names.
Step-by-Step Import Guide
Complete workflow from source file to imported, reviewed transactions.
Option A: Upload Excel to Zera Books (recommended)
Go to zerabooks.com and upload your Excel file. Zera Books reads the data, normalizes dates to DD/MM/YYYY, renames columns to Xero's expected names, and exports a clean Xero Statement Import CSV.
Option B: Manually reformat Excel for Xero
In Excel: (1) Reformat date column to DD/MM/YYYY. (2) Rename headers to Date, Description, Spent, Received. (3) Remove currency formatting from amount cells. (4) Delete summary/total rows. (5) Save as CSV UTF-8.
In Xero: Accounting → Bank Accounts → Import a Statement
In Xero, go to Accounting > Bank Accounts. Find the target bank account and click Import a Statement.
Upload CSV and verify preview
Xero shows a preview of imported data. Check the first 3-5 rows: confirm dates are day-first (not month-first), Spent values are positive numbers for debits, Received values for credits.
Complete import and reconcile
Click Import to process all rows. Xero shows the transaction count. Imported transactions appear in the Statement view ready for reconciliation against bills, invoices, and Xero rules.
Convert Excel to Xero Statement Import CSV in minutes
DD/MM/YYYY dates. Spent/Received columns. Clean encoding. AI categorization. $79/month unlimited.
Try for one weekCommon Errors and Fixes
These are the most frequent issues and their specific solutions.
Xero shows 0 imported transactions
Most common cause: date format issue. Xero silently rejects rows with invalid DD/MM/YYYY dates (e.g., 05/13/2025 has no 13th month so it fails). Fix: Verify all dates are DD/MM/YYYY with valid day values. Re-export from Zera Books using the Xero option.
Amounts show as 0 or blank after import
Excel cell formatting (currency symbols, comma thousands separators, parentheses for negatives) was preserved in the CSV export. Fix: In Excel, select amount cells > Format Cells > Number > remove all formatting > export to CSV again. Or use Zera Books.
Xero rejects file with encoding error
Excel's default CSV uses Windows-1252 encoding which Xero rejects. Fix: In Excel, use File > Save As > 'CSV UTF-8 (Comma delimited)' specifically. Not the plain 'CSV' option.
Reconciliation matches aren't found after import
Xero matches transactions against bills/invoices using the Description field. If descriptions are truncated or replaced during conversion, match rates drop. Zera Books preserves original bank descriptions in the output.
Frequently Asked Questions
Can I import Excel files directly into Xero?
No. Xero only accepts CSV for Statement Import. Convert Excel to CSV with DD/MM/YYYY dates and Spent/Received columns, or upload to Zera Books for automatic reformatting.
What date format does Xero require for CSV imports?
DD/MM/YYYY (day first). Xero is an Australian company using day-first dates. Using MM/DD/YYYY silently corrupts dates for days 1-12 of each month — a data quality issue that may not surface until reconciliation.
What is the difference between Spent and Amount columns in Xero?
Xero's Statement Import prefers separate Spent (withdrawals) and Received (deposits) columns, each with positive decimal values. You can also use a single signed Amount column (negative for spent, positive for received).
Can Zera Books batch-convert multiple Excel files for Xero?
Yes. Zera Books processes multiple Excel files at once and exports individual Xero Statement Import CSV files per account. For 50+ client statements, batch processing cuts export time from hours to minutes.