Sort and Filter Bank Statement Transactions in Excel

A transaction list has a date, a description and an amount for every row, and most reviews begin by putting it in date order, then looking at the biggest amounts or all rows for one merchant. Excel's sort and filter commands cover all three. Both depend on one thing: each column must hold a single kind of data.

Microsoft's sorting guidance recommends that the range you sort has column headings, and says that by default the value in the heading is not included in the sort operation. Keep one header row at the top, with Date, Description and Amount in their own columns.

Per Microsoft, to sort a column on Windows you select a cell in the column you want to sort, then on the Data tab, in the Sort & Filter group, choose a sort button. For dates the labels are Sort Oldest to Newest and Sort Newest to Oldest. For numbers, Microsoft lists smallest to largest and largest to smallest.

Here is a made-up sample of four rows, with the date column holding real dates and the amount column holding numbers:

Microsoft states that for Excel to sort dates and times correctly, all dates and times in a column must be stored as a date or time serial number. It adds that if the results are not what you expect, the column might contain dates or times stored as text instead of as dates or times, and that the same is true of numbers stored as text. In the article on sorting dates, Microsoft says that if Excel cannot recognise a value as a date or time, the value is stored as text.

A visible clue, according to Microsoft's page on converting text dates, is that dates formatted as text are left-aligned in a cell. That page describes converting them with the DATEVALUE function: in an empty cell whose format is General you enter =DATEVALUE( , click the text-date cell, close the bracket and press Enter, and the result is a serial number. You then copy those results, use Paste Special with Values over the original cells, apply a Date format, and delete the helper cells.

For filtering on Windows, Microsoft's steps for a range are: select any cell within the range, go to the Data tab and select Filter, click the arrow in the column header, choose Text Filters or Number Filters and then a comparison, enter your criteria and click OK. Microsoft's example for numbers is choosing Number Filters, then Less Than, and entering 6000.

For a keyword, apply a Text Filters comparison to the Description column and enter the merchant name or part of it. The Microsoft page lists a Contains option for rows that contain specific text in its macOS instructions, so check the exact filter menu in your own version.

One more rule from the filter page: do not mix data types in the same column. Only one type of filter command is available for each column, and when types are mixed, the command shown is for the data type that occurs the most. If most rows in a column of dates are really text, you will see Text Filters rather than Date Filters, which is a quick sign that the date column needs converting.

Every caveat above comes down to the same cause, values stored as text when they should be dates or numbers. Starting from a spreadsheet with each field in its own column reduces that clean-up. PDFtoRow converts bank statement PDFs to Excel or CSV and shows a free preview of the first 10 rows, so you can check how dates and amounts come out before you sort or filter them.