To remove repeated headers in Excel after converting a statement, turn on a filter, show only rows whose date cell reads "Date", delete those visible rows except the first, and clear the filter. Do the same for page footers. Keep the opening and closing balance lines, then check the row count and totals against the PDF.
Why do repeated headers show up in Excel after converting a statement?
Repeated headers show up because a bank prints the column titles at the top of every page, and a converter reads each page as its own table. When the pages are stacked into one sheet, the title row from page two, three and onward lands between transactions. The same thing happens with page footers and "continued" notes.
A three-page statement gives you three header rows where you want one. Those extra rows do real harm. A text value such as "Amount" in the middle of a numbers column breaks sorting by amount, sits inside pivot tables as its own item, and can stop a column being treated as numbers at all.
Which tools leave them in
Almost every free route leaves them: copy and paste from a PDF reader, Excel's own PDF import when you append pages, and open source extractors. Tabula and other open source PDF to Excel converters export one table per page, so the header repeats at every page break. The fix below works the same whichever tool produced the sheet.
Filtering repeated header rows out of the Excel sheet
The fastest way to remove repeated header rows is a filter on the date column. Show only the cells that contain the header text, delete those rows apart from the real header, then show everything again. The whole job takes under a minute on a statement of any length.
- Click any cell in the data and press Ctrl+Shift+L to turn on filters.
- Open the filter arrow on the Date column.
- Clear Select All, tick only the header text, for example "Date", and click OK.
- Select the visible rows below the real header row. Click the first row number and Shift-click the last.
- Right-click a selected row number and choose Delete Row. Excel deletes only the visible rows.
- Open the filter arrow again and choose Clear Filter.
When the header text varies
Some banks print "Date" on page one and "Date (cont.)" on later pages. Use Text Filters, then Begins With, and type Date. If the header is split over two rows, such as "Posting" above "Date", repeat the filter for each line.
The same steps in other spreadsheets
Google Sheets and LibreOffice Calc both have filters that work this way, with different menu names. Free spreadsheet software for bank statement data compares the options if you are not working in Excel.
Dropping page footers and continued lines from a statement sheet
Page footers repeat just like headers, and they are harder to spot because they look different on each page. "Page 1 of 3", "Page 2 of 3", a bank address and a "continued on next page" note all arrive as rows. A helper column flags them in one pass, so you can filter and delete them together.
In an empty column, enter a formula like this in row 2 and fill it down, changing the cell and words to match your sheet:
=OR(ISNUMBER(SEARCH("page ",B2)), ISNUMBER(SEARCH("continued",B2)), A2="Date")
It shows TRUE on any row whose description contains "page " or "continued", or whose date cell says Date. Filter the helper column for TRUE, check the visible rows by eye, delete them, then clear the filter and delete the helper column.
Look before you delete
A merchant name can contain one of your flag words. "PAGE ONE BOOKS" would match "page ". Scan the TRUE rows for anything with an amount in it before you delete. Footer rows almost never carry an amount.
Which balance lines stay when you remove repeated headers?
Keep the opening balance and the closing balance. They are the two numbers that prove the sheet is complete. Lines such as "Balance forward" or "Balance carried forward" at the top of each later page are repeats of the last balance on the page before, so you can remove them once you have checked they match.
Each balance line plays a different role:
- Opening balance. The starting point for the running total. Keep it as the first row under the header.
- Balance forward lines. Copies of the previous page's last balance, with no transaction behind them. Check each one equals the running balance just above it, then delete it.
- Closing balance. The finishing point. Keep it as the last row, or note it next to the table.
- Daily or subtotal lines. Some banks print a total per day or per section. Delete them from the transaction list, because they double the amounts if you sum the column.
Why not keep the forward lines?
If a balance forward line has its amount in the amount column, summing the column counts that balance as money in. Leaving it in is the most common way a cleaned statement ends up with a total that is far too high.
Proving the header cleanup in Excel deleted nothing real
Prove the cleanup with two checks: a row count and a balance check. Count the transactions left and compare with the PDF. Then confirm that the opening balance plus every amount equals the closing balance. If both match, nothing real went with the headers.
Here is a worked example for an example three-page statement. The numbers are made up.
| Row type | Before cleanup | After cleanup |
|---|---|---|
| Header row | 3 | 1 |
| Page footer rows | 3 | 0 |
| Balance forward rows | 2 | 0 |
| Opening and closing balance rows | 2 | 2 |
| Transaction rows | 48 | 48 |
| Total rows | 58 | 51 |
The transaction count is 48 in both columns. That is the number to compare with the PDF, by counting the lines on each page.
The balance check formula
With the opening balance in C2, the amounts in C3 to C50 as positive deposits and negative withdrawals, and the closing balance typed in C51, this returns TRUE when the sheet is complete:
=ROUND(C2+SUM(C3:C50),2)=C51
A FALSE result means a transaction row was deleted or a forward line was left in. The size of the difference usually tells you which.
Next steps for the clean sheet
Once the rows are clean, sorting and pivot tables behave. Charting spending by category in Excel picks up from here. If you are choosing an Excel to do this in, the cheapest Microsoft 365 plan for Excel compares the costs. To skip the cleanup on future statements, CheapBankConvert is getting ready to open. Its files have one header row and date, description, amount and balance columns, at early prices from $2.99 a statement.