Spreadsheets

Remove footer totals and disclaimer lines from a statement sheet

Find the last real transaction row in a statement sheet and remove totals, summaries and disclaimer text below it, using filters or Power Query.

Editorial illustration for Remove footer totals and disclaimer lines from a statement sheet

Find the last real transaction row, delete everything below it, and use a filter on the date column to catch stray summary lines in between. Before you delete a totals row, read its numbers, because they are the cheapest way to check that the rows above are complete.

What gets printed under the transactions?

Statements often end with a block of text: totals of debits and credits, a closing balance, an end-of-statement marker and a paragraph of notices. None of it is a transaction. If it stays in the sheet, a SUM over the amount column adds a total on top of the things it is totalling, and a pivot will show strange categories made from sentence fragments.

Totals and notices also vary by bank and by month, so do not assume that the footer you cleaned last month looks the same this month. Whatever the wording, the test is the same: a transaction has a date and an amount, and a footer line has neither.

How do you find where the data ends?

  1. Click any cell in the date column and press Ctrl+Down repeatedly, or press Ctrl+End to jump to the last used cell of the sheet.
  2. Scroll up to the last row that has a real date and a real amount. Note its row number.
  3. Select from the row after it down to the last used row, right-click the row numbers and choose Delete.

If the text is scattered or the data has gaps, delete nothing yet and move to the next section.

Can a filter isolate the rows that are not transactions?

Yes. Add a helper column next to the data, say column G, and in G2 enter =ISNUMBER(A2), then fill down. A real date is stored as a number, so TRUE marks rows with a valid date and FALSE marks everything else. Filter column G for FALSE, look through what appears, and delete those rows if they are all footers. This only works when your dates are true dates; if the whole column returns FALSE, the dates are text, and you should convert them first.

To find rows by wording instead, use the filter on the narration column: Text Filters, Contains, then try words such as total, summary or statement, whatever your own file uses. Filtering to a word and deleting visible rows removes only the visible ones.

Which Power Query steps remove footers?

There are two routes. If the footer always has the same number of lines, choose Home, Remove Rows, Remove Bottom Rows and enter the count. If the count varies, set the date column's type to Date first, which turns text such as "Closing balance" into error cells, then choose Remove Rows, Remove Errors. Order matters: do the type change on the date column only after the header row is promoted, and keep the Remove Errors step after it.

What does the totals row tell you before you delete it?

Say the footer states total debits of ₹86,400. Before deleting it, enter =SUM(C2:C300) over your debit column, with the footer excluded. If you also get ₹86,400, the rows above are complete. If you get ₹81,400, ₹5,000 is missing, perhaps a row hidden by a filter or lost while pasting. These numbers are invented, but the habit of comparing is a good one. Compare the credit total and the closing balance too, then delete.

A final pass

  • The last row of the sheet is a transaction, not a sentence.
  • The helper column has no FALSE left, or only rows you checked.
  • Debit and credit totals agree with the footer's own figures.
  • A copy of the untouched file still exists.

If you would rather skip the clean-up for one-off files, SpendixAI accepts statements as PDF, CSV or Excel uploads and lists the rows for your review before anything is saved.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports