Validate ABA Routing Numbers in Excel: the 3-7-1 Checksum Formula

Before a vendor payment run goes out, thirty seconds of spreadsheet math can catch a mistyped routing number. Here is the exact formula, a worked example, and — just as important — what the checksum cannot tell you.

What the routing number checksum is

Every US bank routing number (ABA RTN) is exactly nine digits, and the ninth digit is a check digit. The rule: multiply the nine digits by the repeating weights 3, 7, 1 (so positions 1, 4, 7 get weight 3; positions 2, 5, 8 get weight 7; positions 3, 6, 9 get weight 1), add the products, and the total must be divisible by 10. If a data-entry error changes any single digit, the weighted sum stops landing on a multiple of 10 and the number fails — the same idea behind barcode and IBAN check digits, explained in depth in how check digits work.

For a one-off check, paste the number into the routing number validator. For auditing a whole payment file in a spreadsheet, read on.

The formulas: audit a payment file in three columns

Assume the routing numbers your vendor sent are in column A, starting at A2. We build three helper columns: a cleaned copy, the checksum verdict, and the prefix verdict.

Step 1 — clean the input and restore leading zeros (B2)

=TEXT(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ",""),"-",""),"000000000")

Reading from the inside out: TRIM strips stray spaces that ride along in CSV exports; the two SUBSTITUTE calls remove internal spaces and hyphens (people love typing 0210-0002-1); and TEXT(…,"000000000") pads the result back to nine digits. That last part is the lifesaver: if the column was ever formatted as General, Excel stored 021000021 as the number 21,000,021 and threw the leading zero away. Because a routing number is always nine digits, zero-padding reconstructs it exactly. A cell containing something that is not a number at all will fail the length test in step 2 and get flagged rather than silently pass.

Step 2 — the 3-7-1 checksum (C2)

=IF(LEN(B2)<>9,"BAD LENGTH",
  IF(MOD(3*(MID(B2,1,1)+MID(B2,4,1)+MID(B2,7,1))
        +7*(MID(B2,2,1)+MID(B2,5,1)+MID(B2,8,1))
        +1*(MID(B2,3,1)+MID(B2,6,1)+MID(B2,9,1)),10)=0,
     "CHECKSUM OK","CHECKSUM FAIL"))

Each MID(B2,n,1) pulls out digit n; the + arithmetic silently converts the text digits to numbers (this works identically in Excel and Google Sheets). Digits 1, 4, 7 are summed and tripled, digits 2, 5, 8 are summed and multiplied by 7, digits 3, 6, 9 are added as-is, and MOD(…,10)=0 is the pass/fail test. The LEN guard catches account numbers pasted into the wrong column and anything the cleanup in step 1 could not repair.

If you prefer one compact line in Excel, this is equivalent:

=MOD(SUMPRODUCT(MID(B2,{1,2,3,4,5,6,7,8,9},1)*{3,7,1,3,7,1,3,7,1}),10)=0

The long form above is the safer choice if the sheet has to open in every Excel version and in Sheets.

Step 3 — the Federal Reserve prefix (D2)

=IF(LEN(B2)<>9,"",
  LET(p,LEFT(B2,2)+0,
    IF(OR(AND(p>=1,p<=12),AND(p>=21,p<=32),AND(p>=61,p<=72),p=80),
       "PREFIX OK","PREFIX INVALID")))

LEFT(B2,2)+0 turns the first two characters into a number, and the OR tests it against the only ranges the Routing Number Administrative Board actually assigns. LET needs Excel 2021/365 or Google Sheets; on older Excel, put =LEFT(B2,2)+0 in its own column and test that.

First two digits	Meaning
00	US Government use only — should never appear in a vendor file
01–12	The twelve Federal Reserve districts (Boston 01 … San Francisco 12)
21–32	Thrift institutions: district number + 20
61–72	Electronic transaction identifiers: district number + 60
80	Traveler's checks
13–20, 33–60, 73–79, 81–99	Not assigned — reject even if the checksum passes

Worked example: 021000021

Take 021000021, JPMorgan Chase's New York routing number, and run the step 2 math by hand:

Position	1	2	3	4	5	6	7	8	9
Digit	0	2	1	0	0	0	0	2	1
Weight	3	7	1	3	7	1	3	7	1
Product	0	14	1	0	0	0	0	14	1

Sum: 0 + 14 + 1 + 0 + 0 + 0 + 0 + 14 + 1 = 30. MOD(30, 10) = 0, so the formula returns CHECKSUM OK, and the prefix 02 falls in the 01–12 range: PREFIX OK. Now corrupt the last digit to 021000022: the sum becomes 31, MOD is 1, and the cell flips to CHECKSUM FAIL. The scheme catches every single-digit typo and almost every adjacent transposition — the only swaps it misses are of two digits that differ by exactly 5, such as 27 typed as 72.

Edge cases that bite in real payment files

• Leading zeros are the number-one killer. About a quarter of US routing numbers start with 0, and one careless paste into a General-formatted column truncates all of them at once. Reformatting the column as Text after the fact does not restore the zeros — only re-padding with TEXT(A2,"000000000") does. The same disease infects barcodes; see Excel leading zeros and barcodes.
• Wrong length means wrong field. An 8-digit value is usually a zero-loss victim (step 1 repairs it); a 10-to-12-digit value is usually the account number pasted in the routing column. That is why the formula says BAD LENGTH instead of guessing.
• The fraction on the check is not the routing number. Paper checks show a fractional form (like 2-21/210) in the top corner; the nine-digit MICR number at the bottom is what ACH files need.
• Never "repair" a failing number. Given the first eight digits you can always compute a ninth that makes the checksum pass — but that fabricates a plausible-looking number, not the bank's actual one. If a row fails, go back to the vendor; the triage steps in fixing an invalid routing number cover what to check in which order.

Checksum-valid is not payment-valid: ACH vs wires

The 3-7-1 test proves internal consistency, nothing more. It cannot tell you whether the Federal Reserve has assigned the number to any bank, and it cannot tell you whether it is the right number for the rail you are paying on. Many large banks publish different routing numbers for ACH, domestic wires, and paper checks: Bank of America, for example, uses 026009593 for domestic wires while its ACH numbers vary by state (Texas is 111000025). Both pass the checksum — the sums are 110 and 30 — yet putting the ACH number on a wire instruction can get the payment rejected or delayed. Treat the spreadsheet audit as the cheap first gate, the routing number validator as the quick second opinion, and confirmation with the payee's bank as the final word. Our methodology page spells out exactly what each of our checks does and does not verify.

Validating at scale?

If routing numbers arrive by the thousands, the same 3-7-1 check runs as a JSON API — up to 100 numbers per call:

curl -X POST "https://codeclassify-api.rosariovitale0096.workers.dev/v1/aba/validate" \
  -H "X-Api-Key: YOUR_KEY" \
  -H "Content-Type: application/json" \
  -d '{"numbers":["021000021","021000022"]}'

Abridged response — note that failing numbers come back with the ninth digit that would have passed, which is useful for spotting zero-loss damage (but see the warning above about blindly "repairing"):

{"ok":true,"count":2,"valid":1,"results":[
  {"input":"021000021","routing_number":"021000021","valid":true},
  {"input":"021000022","routing_number":"021000022","valid":false,
   "expected_ninth_digit":1,"corrected":"021000021"}]}

The free tier includes 10 calls per month with no card required — get a key on the API page. For wiring a whole CSV through it, see bulk-validating a CSV via the API.

FAQ

Why does Excel show my routing number as 21000021 instead of 021000021?

Because the cell is formatted as General or Number, Excel stores the value as the number 21,000,021 and drops the leading zero. Reformatting the column as Text afterwards does not bring the zero back. Since every ABA routing number is exactly nine digits, rebuild it in a helper column with =TEXT(A2,"000000000"), which pads the value back to nine digits, then run the checksum on the helper column.

Can a routing number pass the checksum and still be wrong?

Yes, in two ways. First, the checksum only proves the nine digits are internally consistent; it cannot tell you whether the Federal Reserve has actually assigned that number to a bank. Second, many banks use different routing numbers for ACH, domestic wires, and paper checks, and all of them pass the same 3-7-1 test. A wire sent with an ACH-only routing number can be rejected or delayed, so always confirm the number and the payment rail with the recipient.

What do the first two digits of a routing number mean?

They identify the Federal Reserve routing region. Valid ranges are 01–12 for the twelve Federal Reserve districts, 21–32 for thrift institutions (the district number plus 20), 61–72 for electronic transaction identifiers (the district plus 60), and 80 for traveler's checks. 00 is reserved for United States government use, and every other prefix (13–20, 33–60, 73–79, 81–99) is unassigned, so a routing number starting with, say, 45 is invalid even if its checksum works out.

This guide is for general information only. The ABA checksum confirms that a routing number is internally consistent, not that it is assigned to a bank, active, or correct for a given payment type. Always confirm banking details directly with the payee or their bank before sending money, especially for wire transfers.