1. Why Convert Excel to NACHA?
Many businesses still track vendor payments, payroll runs, and customer refunds in Excel spreadsheets. It is a familiar tool, easy to share, and flexible enough for most accounting workflows. The problem arises when those payments need to be submitted electronically as ACH transactions.
Banks and payment processors accept ACH files in the NACHA format — a strict, fixed-width text specification maintained by Nacha (formerly the National Automated Clearing House Association). There is no “Save As NACHA” button in Excel. Converting a spreadsheet into a valid NACHA file requires understanding the format, applying precise field-level rules, and performing several calculations that Excel does not handle natively.
Getting this wrong means rejected files, delayed payments, and potential fees from your bank. This guide walks through the full process — from the data you need in your spreadsheet to the steps for manual conversion — and explains why most teams eventually move to an automated solution.
2. NACHA File Requirements
Before you start converting, you need to understand what a valid NACHA file looks like. Every ACH file follows a rigid structure.
Fixed-Width Records
Each line in a NACHA file is exactly 94 characters long. No more, no less. There are no delimiters — field positions are defined by character offsets within each line.
Record Types
A NACHA file contains five types of records, identified by the first character of each line:
| Record Type | Code | Description |
|---|---|---|
| File Header | 1 | One per file. Identifies the originator and destination bank. |
| Batch Header | 5 | One per batch. Groups entries by SEC code and originator. |
| Entry Detail | 6 | One per payment. Contains routing, account, amount, and payee info. |
| Batch Control | 8 | One per batch. Contains entry count, totals, and hash. |
| File Control | 9 | One per file. Summarizes all batches. File is padded to a multiple of 10 lines. |
Blocking
The total number of lines in the file must be a multiple of 10. If your file has 14 lines of real data, you must append 6 lines of “9” records (94 nines) to reach 20 lines. Banks will reject files that are not properly blocked.
3. Excel Data You'll Need
To produce a NACHA file, your spreadsheet must include at least the following columns for each payment entry:
| Column | Example | NACHA Field | Notes |
|---|---|---|---|
| Payee Name | Acme Supplies LLC | Individual Name | 22 characters, left-justified, space-padded |
| Routing Number | 021000021 | RDFI Routing | 9 digits including check digit |
| Account Number | 123456789 | DFI Account Number | Up to 17 characters, left-justified |
| Amount | 1500.00 | Amount | In cents (1500.00 = 150000), 10 digits, zero-padded |
| Transaction Type | Credit | Transaction Code | Credit (22/32) or Debit (27/37) |
| Account Type | Checking | Transaction Code | Checking (22/27) or Savings (32/37) |
You will also need metadata that is not per-row but per-file: your company's name, company ID (usually your EIN with a leading “1”), ODFI routing number, and the effective entry date.
4. Manual Conversion Steps
If you need to convert an Excel file to NACHA format by hand, here is the general process. Be warned: this is tedious and error-prone, which is why most teams automate it.
Step 1: Format Your Columns
Ensure routing numbers are stored as text (not numbers — Excel may strip leading zeros). Convert dollar amounts to cents by multiplying by 100 and removing decimals. Pad the payee name to exactly 22 characters with trailing spaces.
Step 2: Map Transaction Codes
Translate your “Credit/Debit” and “Checking/Savings” columns into the two-digit NACHA transaction codes:
Checking + Credit = 22 Checking + Debit = 27 Savings + Credit = 32 Savings + Debit = 37
Step 3: Build Entry Detail Records
For each row in your spreadsheet, construct a 94-character line beginning with “6”. Each field must be placed at the exact character position defined by the NACHA spec. Use left-justification and space-padding for alphanumeric fields, and right-justification with zero-padding for numeric fields.
Step 4: Calculate the Entry Hash
Sum the first 8 digits of every routing number in the batch. This value goes into both the Batch Control and File Control records. If the sum exceeds 10 digits, use only the rightmost 10 digits.
Step 5: Build Header and Control Records
Create the File Header (type 1), Batch Header (type 5), Batch Control (type 8), and File Control (type 9) records. Each has its own field layout and must reference the correct totals — entry count, total debits, total credits, and the entry hash.
Step 6: Export as Fixed-Width Text
Save the output as a plain text file (typically with an .ach extension). Make sure every line is exactly 94 characters, with no extra whitespace or line breaks. Pad the file to a multiple of 10 lines with lines of 94 nines.
Why this is risky
A single character in the wrong position, a miscalculated hash, or a missing leading zero will cause the entire file to be rejected by your bank. There is no partial success — the file either passes validation or it does not.
5. Common Conversion Errors
These are the mistakes that catch most people when manually converting Excel data to NACHA format:
Wrong Padding or Justification
Numeric fields must be right-justified and zero-padded. Alphanumeric fields must be left-justified and space-padded. Mixing these up — or having a field that is one character too short — shifts every subsequent field in the line.
Missing Routing Number Check Digits
ABA routing numbers are 9 digits, with the 9th being a check digit calculated from the first 8. If Excel has truncated a leading zero or you copied only 8 digits, the routing number will fail validation.
Entry Hash Calculation Errors
The entry hash is the sum of the first 8 digits of each routing number. A common mistake is using all 9 digits, or forgetting to truncate the result to 10 digits when the sum is large.
Incorrect Line Length
Every line must be exactly 94 characters. Editors that auto-trim trailing spaces, or spreadsheet exports that add commas or tabs, will produce invalid files.
Dollar vs. Cent Amounts
NACHA amounts are in cents with no decimal point. Forgetting to multiply by 100 means a $1,500 payment becomes a $15.00 payment.
6. Automated Alternatives
Given the complexity and fragility of manual conversion, most businesses use dedicated tools to generate NACHA files. The right tool eliminates padding errors, calculates hashes and control totals automatically, and validates the file before you download it.
BatchPay QB takes this a step further. Instead of starting from a spreadsheet, it connects directly to your QuickBooks Online account and pulls in your pending bills and invoices. You select the transactions you want to pay, confirm banking details, and download a fully valid NACHA file — no manual formatting, no Excel gymnastics.
For payments that are not in QuickBooks, BatchPay QB also lets you create ACH files from scratch with a guided form that validates every field as you go. Either way, the output is a properly blocked, properly hashed NACHA file ready for your bank.
Frequently Asked Questions
Can I convert an Excel file directly to NACHA format?
Not directly. Excel files must be reformatted into fixed-width, 94-character-per-line records that follow NACHA specifications. This requires padding fields, calculating control totals, and exporting as plain text.
What columns do I need in my Excel spreadsheet for a NACHA file?
At minimum you need: payee/receiver name, routing number (9 digits), account number, transaction amount, transaction type (credit or debit), and account type (checking or savings).
Why is my bank rejecting my NACHA file created from Excel?
Common reasons include incorrect line length (must be exactly 94 characters), invalid routing number check digits, miscalculated entry hash or batch totals, and missing blocking padding at the end of the file.
Is there an easier way than manually converting Excel to NACHA?
Yes. Tools like BatchPay QB let you enter payment details through a guided form or sync directly from QuickBooks Online, automatically generating valid NACHA files without manual formatting.