How to automatically categorize your expenses in Excel

You can’t beat the simplicity of setting up your own tracking spreadsheet. Sure, budgeting apps provide some automation, but building your spreadsheets allows you to customize a system for your own needs.

Two of the biggest pain paints people face in spreadsheet tracking are bulk importing transactions and manually categorizing every transaction. If these sound like problems you face, then this article is for you.

Bulk importing transactions

If you aren’t downloading your transactions straight from the bank, you need to start now! It’s a huge time saver compared to manually typing things over from your monthly statements. Most banks provide easy downloads of your debit/credit card transactions in CSV (comma separated values) format.

If you need any help, check out these articles for a walkthrough for major banks.

Use lookup tables to categorize expenses based on keyword

By using spreadsheet functions, you can create custom rules that sort expenses based on keywords. For example, if the cell contains “Starbucks”, categorize the expense as “Coffee”. Or if the cell contains “Uber”, categorize the expense as “Rideshare”.

First, create a new sheet called “Categories” with two columns, Keyword and Category. Create a list of keywords and categories as below. For the keywords, it doesn’t matter the casing you use (i.e. COSTCO and Costco are the same).

Then, paste this function into a new Category column in your transactions sheet. The function will go through the keywords from your list — if one is in your transaction description, it will assign it. Otherwise, it’ll write “Other”.

=IFERROR(INDEX(Categories!B$2:B$8,MATCH(TRUE,ISNUMBER(SEARCH(Categories!A$2:A$8,B2)),0)),"Other")

Then, use the AutoFill Handle (drag the bottom right corner of the cell) to apply the formula to all transactions in your sheet.

How this works:

  • Categories! finds data from the new sheet with the keywords list. Categories!A$2:A$8 is the list of keywords andCategories!B$2:B$8 is the list of categories.
  • If you add more categories, you’ll have to change the value ofA$8 and B$8

Use Unscatter to automatically categorize expenses

Although spreadsheets allow custom rules, setting them up is cumbersome and expenses often fall through the cracks. You can use that method in conjunction with an automated tool like Unscatter. After you have CSV files for your transactions, simply drop them into the Unscatter page here.

Unscatter allows you to choose the categories you want to sort into. Make sure you save a copy of your category list for using it next time.

Then, Unscatter will automatically infer categories based on transaction descriptions.

Download and use the built in tracking spreadsheet, or load the transactions into your own spreadsheet.