How to Categorize Credit Card Transactions in Excel

Month-end, you export the business card statement to sort expenses for your books, and the merchant column looks like a ransom note: SQ *BLUE BOTTLE #1180, TST* THE DELI, AMZN MKTP US*2H4G8. You know exactly what those are, but Excel sees three unreadable strings, and there's no way to total your coffee spend when every coffee shop is spelled differently. To categorize credit card transactions in Excel without going row by row, the move most guides skip is to fix the merchant names first, then let a lookup do the sorting.

This is the bookkeeping version aimed at small-business owners, using a free macro for the messy merchant cleanup and Excel's built-in tools for the categorizing.

TL;DR: categorize credit card transactions in Excel

• Fix the structure. Turn text dates and text amounts into real dates and numbers.
• Normalize the merchant names. Strip the SQ * and TST* junk so the same shop reads the same every time.
• Assign categories with a lookup. One VLOOKUP against a merchant-to-category table tags every row.
• Total by category. A PivotTable sums your spend per category in seconds.

With the macros loaded, the cleanup is a couple of minutes. The payoff is that next month's statement categorizes itself against the same lookup table.

Why categorizing never sticks: the merchant names

Here's the real problem. Payment processors staple their own prefixes onto every charge. Square adds SQ *, Toast adds TST*, and plenty of charges carry a store number or city on the end. So one coffee shop shows up as SQ *BLUE BOTTLE #1180, BLUE BOTTLE COFFEE, and BLUEBOTTLE SF across three months.

To Excel, those are three different merchants. You can't group them, you can't total them, and a VLOOKUP for "Blue Bottle" matches none of them. Every categorizing method downstream, filter-and-assign, lookup, PivotTable, depends on the same merchant reading the same way. Fix that once and the rest is easy.

Step 1: Fix the dates and amounts

Pasted statements usually land with dates and amounts as text. Select the date column and run Standardize Date Formats, telling it whether your dates are US, UK, or ISO. Then select the amount column and run Convert Text to Numbers so the values actually sum. A quick pass with Trim Whitespace From All Cells clears stray spaces. New to running macros? The 30-second setup gets you going.

Now the columns behave like data instead of text, and a total won't silently come out as zero.

Step 2: Normalize the merchant names in bulk

This is the step that makes everything else work. On a spare sheet, build a two-column table: the junk fragment your card uses in the first column, the clean name in the second. So SQ *BLUE BOTTLE maps to Blue Bottle, TST* maps to nothing, AMZN MKTP maps to Amazon.

Select your description column and run Bulk Find and Replace From Table. Point it at your two-column table, and it applies every find-and-replace pair in one pass, so all your Blue Bottle variants collapse to Blue Bottle at once. It matches on partial text and ignores case, so you're stripping the recurring noise, not hunting one cell at a time. It handles the recurring 80% of charges. The occasional one-off store still needs a manual touch, but the merchants you hit every month are now consistent.

Step 3: Assign a category with a lookup

With clean, consistent merchant names, categorizing is a lookup, not a chore. On another sheet, list each merchant and its category: Blue Bottle to Meals, Amazon to Supplies, Uber to Travel. Then in a Category column next to your transactions, use the VLOOKUP function:

=VLOOKUP(C2,MerchantCategories,2,FALSE)

where C2 is the cleaned merchant and MerchantCategories is your lookup table. Fill it down and every recognized merchant gets its category instantly. Anything that returns #N/A is a merchant you haven't mapped yet, so add it to the table and it's covered from then on.

Step 4: Total your spending by category

Now the analysis is trivial. Select your data, insert a PivotTable, drag Category to Rows and the amount to Values, and you have total spend per category. That's the number your accountant wants and the one that tells you where the money actually went. Refresh it next month and it updates against the same clean data.

The hot take: build the lookup once, never categorize by hand again

Most people re-categorize their statement from scratch every single month, eyeballing each row. That's the actual waste, not the typing. The merchant-to-category table you build in Step 3 is reusable. Next month, you clean the new statement, run the same VLOOKUP, and everything you've seen before is already tagged.

So the first month costs you twenty minutes and every month after costs you two. Categorizing transaction by transaction feels productive and is a treadmill. A lookup table is an asset that pays you back every close.

Common pitfalls

• Over-normalizing. Don't collapse two genuinely different merchants into one name just because they look similar. You'll misfile the spend.
• Forgetting refunds and payments. Credits and card payments are negative or separate rows. Decide whether they belong in your spend totals before you pivot.
• Partial matches leaving tails. The replace is partial, so Blue Bottle #1180 may keep its store number. Map the specific recurring string, or accept a short manual cleanup on the tail.
• Editing the only copy. Work on a copy of the export so the raw statement stays as your record.

Frequently asked questions

How do I categorize credit card transactions in Excel?

Clean the merchant names first so the same shop always reads the same, then use a VLOOKUP against a merchant-to-category table to tag every row. Finish with a PivotTable to total spend per category. The one-time work is building the lookup table; after that, each month's statement categorizes automatically.

How do I clean up messy merchant names from a card statement?

Build a two-column table mapping the junk fragments your card adds (like SQ * or TST*) to clean names, then run the Bulk Find and Replace From Table macro on your description column. It applies every mapping in one pass, so all the variants of a merchant collapse to a single consistent name you can group and look up.

Can Excel categorize my transactions automatically?

Yes, once the merchant names are consistent. A VLOOKUP formula matches each cleaned merchant to a category from your lookup table and fills the category in for you. It only categorizes merchants you've mapped, so new ones show #N/A until you add them, which keeps you in control of how each is filed.

Why does my expense total come out wrong or zero?

Usually the amounts are stored as text after pasting from a statement, and text values sum to zero in a PivotTable without any error. Run the Convert Text to Numbers macro on the amount column first. Left-aligned numbers and a small green triangle in the corner are the giveaway that a value is text, not a number.

Do these macros work on Excel for Mac?

Yes. The bulk-replace, date, number, and trim macros all run on Excel 2016 and later on Windows and Mac. They avoid Windows-only features, so a business owner switching between a laptop and a desktop gets the same result on either machine.

What to do next

Clean the merchant column once, build the lookup, and expense categorizing stops being a monthly slog.

To categorize credit card transactions in Excel on your own statement, browse the full set of Excel tools for small business owners, or start with Bulk Find and Replace From Table to tidy the merchant names. Pair it with reconciling those charges against your invoices for a full monthly close.