How Finance Teams Clean a Monthly Bank Export in Excel

A monthly bank export is rarely something you can paste into a reconciliation template and start matching. The CSV looks fine in the email preview, you open it, and now half your dates are text strings, the amounts have leading apostrophes, there's a five-row banner of legal disclaimers at the top, and someone in Treasury Services left blank rows between months. Cleaning a typical bank export by hand takes a finance analyst 15 to 25 minutes a month per account.

This walks through the cleanup in the order it actually happens, using free macros where the manual fix is fiddly and Excel's built-in tools where they win.

TL;DR: the 6-step cleanup

• Strip the banner. Delete the disclaimer rows the bank's export script staples to the top of every CSV.
• Trim and clean the headers. Remove trailing spaces and weird punctuation so VLOOKUPs and PivotTables key cleanly.
• Fix the dates. Bank exports often deliver dates as text. One macro converts them to real Excel dates.
• Fix the amounts. A leading apostrophe forces a number to text. Strip it so the amounts SUM correctly.
• Drop the blank rows. Some banks insert empty rows between statement periods. They break sorts and filters.
• Verify the totals match. Compare the cleaned-data SUM to the bank's printed statement total before you reconcile.

The whole pass takes about 90 seconds with the right macros loaded. The same pass by hand: 15 to 25 minutes, with two or three places to introduce errors.

Why bank exports are this messy

Banks ship CSVs through legacy systems built before Excel was the universal endpoint. Many of them write the export from a mainframe, where dates are strings, amounts have type-coercion quirks, and the export header is a free-text disclaimer block, not a structured CSV header.

You can complain to your relationship manager. They'll nod, file a ticket, and three quarters later nothing will have changed. The faster path is to keep a cleanup workflow that runs in under two minutes and bake it into your month-end checklist.

Step 1: Delete the banner rows

Most bank CSVs start with 3 to 8 rows of "This statement is for…", "Branch:", "Account number:", and a blank line, before the actual data starts. Sometimes a footer too: "End of statement. For questions call…"

Just select those rows and hit Ctrl + Minus (Windows) or Cmd + Minus (Mac) to delete them. Then check the bottom of the file for a footer block and delete that too.

If your bank's export is consistent and you do this every month, record a one-line macro that deletes rows 1 through (whatever) and assign it a keyboard shortcut.

Step 2: Trim the headers

Bank export headers often have weird whitespace. "Transaction Date " with a trailing space, "Reference No" with a double space. PivotTable will treat "Date" and "Date " as different fields. VLOOKUP will fail silently.

Run Trim Whitespace From All Cells on the header row. It strips leading, trailing, and collapses internal multiples to a single space. Now your headers are clean and downstream lookups stop missing.

Step 3: Convert text dates to real dates

This is the big one. Many bank exports deliver dates as text strings: "08-MAR-2026" or "20260308" or "March 8, 2026." Excel doesn't see these as dates. Sorting by date sorts alphabetically (which means December comes before February). Date arithmetic returns errors.

Use Standardize Date Formats. Select your date column, run the macro, and it parses the most common bank-export formats and writes real Excel dates back to the cell. Sorts and filters work after that.

Alternative: Excel's Text to Columns wizard with the Date format option works for some but not all bank export formats. The macro is more permissive about input variations.

Step 4: Strip leading apostrophes from amounts

Some bank exports prefix every numeric cell with an apostrophe ('1234.56). The apostrophe is invisible in display but forces Excel to treat the cell as text. Sum the column and you get zero. Filter for amounts greater than 1000 and the filter shows nothing.

Run Convert Text to Numbers on the amounts column. The macro strips the leading apostrophes (and other invisible-character corruption like non-breaking spaces) and writes back a real number. Now SUM, AVERAGE, and filtering work.

Step 5: Remove the blank separator rows

Some banks insert a blank row between statement periods or between transaction types (debits, credits, fees). Blank rows break filters and shorten the range Excel auto-detects when you Ctrl+Shift+End.

Select your data range and run Remove Blank Rows. The macro walks the range bottom to top and deletes any row where every cell is empty. Clean continuous data, ready to filter.

Step 6: Verify totals before you reconcile

Before declaring the file ready, sum the amounts column and compare to the printed statement total at the bottom of the bank PDF. If they match, your cleanup didn't drop or duplicate anything. If they don't, something went wrong: a banner row that wasn't deleted, an apostrophe still hiding in one cell, a date column that got accidentally summed (yes, this happens).

This 30-second sanity check has caught more reconciliation errors than any other discipline I know.

The hot take: Power Query is overkill for one-shot reconciliations

Power Query is the modern answer everyone points to. For recurring cleanups where the same source format lands every week, it's the right tool. The transformation steps are reproducible, version-controlled inside the file, and self-document.

For a one-shot monthly bank reconciliation, Power Query is overkill. The learning curve is two afternoons. The query you save lives inside one workbook and doesn't carry to a fresh download. Junior analysts who inherit your model can't read M code without a course. Three saved macros plus your eyes finish the same job in 90 seconds and leave nothing to maintain.

This take will annoy a Power Query advocate. Some will argue that the recurring case (the same bank export every month) is exactly when Power Query pays off. They have a point if your downloaded CSVs really are byte-identical month over month. In practice, banks change export formats, columns shuffle order, and the query breaks anyway. Macros tolerate sloppy variance better.

Common pitfalls

• Including a totals row in your sum. If the bank export has a "Total Debits" row at the bottom, summing the amounts column will double-count.
• Treating reference numbers as numeric. Reference numbers often have leading zeros that are real ("00012345"). Convert text to numbers and the leading zeros vanish and the field is broken. Skip Step 4 for reference-number columns.
• Forgetting to compare against the bank's printed total. Half the value of cleanup is the verification step. Skip it and a silent error will show up at quarter-end.
• Cleaning the original file. Always work on a copy. The bank's CSV is your audit trail; the cleaned version is your worksheet.

Frequently asked questions

How long should the cleanup actually take?

About 90 seconds with macros loaded, including the totals verification step. By hand it's 15 to 25 minutes per account, and the variance is mostly time spent on the date column. If you're new to the macros, expect 3 to 5 minutes the first time. By month two, it's muscle memory.

What if my bank's export has a different format every month?

Skip the macro-recording approach for the banner step (the disclaimer length changes), and just delete rows manually. The dates, amounts, and blank rows steps are format-tolerant and run the same way. If headers shift columns, re-pin your downstream formulas to header names rather than column letters.

Can I do this in Power Query instead?

Yes, and for recurring cleanups against a stable bank export format, Power Query is a strong fit. The trade-off is the upfront learning time and the maintenance burden when the bank changes its export. For one-shot or low-frequency reconciliations, the macros above are faster end-to-end.

Does this work on credit-card export files too?

Mostly yes. Credit-card CSVs from major issuers have similar shape problems: text dates, leading apostrophes, header banners. Same five macros work. The main difference is the date format, which is usually US-style (MM/DD/YYYY) for US-issued cards and DD/MM/YYYY for non-US ones. The Standardize Dates macro handles both.

Will the macros work in Excel for Mac?

Yes. All the linked macros work in Excel 2016 and later on both Windows and Mac. The keyboard shortcuts differ slightly (Cmd instead of Ctrl) but the macro behavior is identical.

What to do next

Bookmark this page or save the linked macros to your Personal Macro Workbook so they're available in every Excel session. Run the cleanup on your next bank export end to end. If you discover a bank-specific quirk that none of the macros handle, the .bas source for each is on the tool page; read the code, tweak the relevant constant, save your variant.

Try the Standardize Date Formats macro on your next bank export. It solves the single most common export problem.