How to clean up and categorize bank transactions in Excel

Once a statement is in a spreadsheet, the real work starts: making descriptions readable, spotting duplicates and putting every row in a category. This workflow uses only standard Excel features and works the same in Google Sheets.

By Muhammad Sharif · October 2026 · 10 min read

Raw bank descriptions are written for the bank's systems, not for you: POS DEBIT 0312 STARBUCKS STORE 04421 SEATTLE WA and CHECKCARD 0313 STARBUCKS #1187 PORTLAND OR are the same merchant. To answer questions such as "how much did I spend on coffee" or "which vendors need a 1099", you need to standardise, categorise and summarise. This guide builds that process one layer at a time, so that you can stop at whichever level you need.

It assumes you already have a spreadsheet with Date, Description and Amount columns, and that you have run the balance check from our reconciliation guide. Cleaning data that does not reconcile just makes the errors prettier.

Step 1: work on a copy and set up an Excel Table

Keep the original converted sheet untouched in one tab and do your work in a second tab or workbook. Click inside the data and press Ctrl+T to turn it into an Excel Table. Tables auto-expand formulas when you add rows, make filtering simple, and let you refer to columns by name, such as [@Amount].

Step 2: tidy the descriptions

Add a helper column called Clean and apply a few stacked formulas. The aim is not perfection, just enough consistency for matching.

• Uppercase and trim: =TRIM(UPPER([@Description])) removes stray spaces and removes case differences.
• Remove noise words: nested SUBSTITUTE calls strip repeated prefixes such as "POS DEBIT", "CHECKCARD", "PURCHASE AUTHORIZED ON" or "ACH". Put the noise words in a small list and apply them with Find and Replace for a one-off job.
• Remove long digit strings: store numbers and card suffixes break matching. Power Query's "Replace values" or a regular expression in a spreadsheet script does it well; in plain Excel, Flash Fill (Ctrl+E) will learn the pattern from one or two examples.

Keep the original Description column. You will want the full text later for audit questions.

Step 3: find duplicates before you add anything

Duplicates arise when statement periods overlap, when a page break repeats a row, or when you merge several downloads. Use a key built from the fields that should be unique together:

=[@Date]&"|"&[@Amount]&"|"&[@Clean]

Then add a column =COUNTIF([Key],[@Key]) and filter for values above 1. Do not delete automatically. Two genuine coffees on the same day for the same price are legitimate, so look at each pair against the statement. A running-balance check will tell you: removing a real transaction breaks the balance, removing a true duplicate fixes it.

Step 4: build a category rule table

On a separate sheet, create a two-column table: Keyword and Category.

Then, in the main table, look for any keyword that appears inside the description. In current Excel versions, a compact formula is:

=IFERROR(INDEX(Rules[Category], MATCH(TRUE, ISNUMBER(SEARCH(Rules[Keyword],[@Clean])), 0)),"Uncategorised")

In older versions of Excel, enter it as an array formula with Ctrl+Shift+Enter. The first matching keyword wins, so order your rules from specific to general. After the first pass, filter for "Uncategorised", add the most common missing merchants to the rules table, and recalculate. Two or three rounds usually gets 90 percent or more of a typical account categorised, and the rule table then carries over to every month you do afterwards. That reusability is where the time is saved.

Step 5: summarise with a pivot table

1. Click inside the table and choose Insert → PivotTable.
2. Drag Category to Rows, Amount to Values (as Sum).
3. Drag a month field to Columns. If you only have dates, right-click a date in the pivot and choose Group → Months and Years.
4. Filter out the "Transfer" category so that moving money between your own accounts does not appear as spending or income.

The result is a one-glance table of spending by category by month, suitable for a budget, a tax workpaper or a conversation with a client.

Step 6: reconcile the summary back to the statement

Finish by checking that the pivot's grand total equals the sum of your Amount column, which equals the statement's closing balance minus the opening balance. This takes seconds, and it protects you from the classic spreadsheet failure where a filter or a hidden row quietly removes data from a report.

Working in Google Sheets instead

The same steps work in Sheets. Replace Excel Tables with plain ranges (or named ranges), use ARRAYFORMULA to fill a formula down a column, use REGEXMATCH for keyword rules, and use Insert → Pivot table. Our Google Sheets guide covers the import side.

Habits that keep it maintainable

• Never overwrite the original description or amount; add columns instead.
• Colour or flag rows you changed by hand, and say why in a Notes column.
• Keep the rules table in a shared workbook so everyone categorises the same way.
• Save a dated copy at month end, with the PDF.
• Talk to your accountant before choosing categories for tax reporting, since the categories that matter depend on your jurisdiction and business type.

Frequently asked questions

Related guides

Written by Muhammad Sharif
Founder, StatementHarbor · Last updated October 2026