How to categorize bank transactions in Excel?
Managing personal or business finances often involves reviewing a detailed list of monthly bank transactions. These transaction records may include a broad array of descriptions, from restaurants to stores, utilities, or services. Tracking and analyzing spending becomes much clearer when you can place each transaction into a distinct category—such as "Takeout", "Groceries", "Utilities", or "Family fee". By automating the classification process in Excel according to certain keywords found in transaction descriptions, you can gain specific insight into your costs and spending patterns each month.
As illustrated in the screenshot below, suppose you have raw data with vendor or service details in column B and want a simplified set of categories (e.g., any transaction containing "Mc Donalds" should be identified as "Takeout", while "Walmart" is classified as "Family fee") according to your personal or organizational rules. This step-by-step tutorial explores several practical solutions including formula-based and advanced automation methods.
Contents:
Categorize bank transactions with formula in Excel
VBA Code - Automate categorization with a macro based on a predefined list
Other Built-in Excel Methods - Use Power Query with conditional column logic
Categorize bank transactions with formula in Excel
If you want a straightforward, formula-based approach to categorize bank transactions in Excel, follow these steps. This method is ideal for users who need quick results and are comfortable maintaining a small lookup table of keywords and categories.
First, create two "helper columns" outside your main transaction list—one column with keywords (corresponding to possible content in the transaction description), and the second column with the category you want linked to each keyword.
1. In this example, list all keywords to match (such as "Mc Donalds", "Walmart", etc.) in column A from row 30 to 41, and the corresponding category names ("Takeout", "Family fee", and so on) in column B from row 30 to 41.
If your keyword set or category set is larger or changes frequently, simply adjust the ranges to cover all your need. See screenshot:
2. Next, click the first cell in your desired output column (for example, F3 next to the last column of your data), then enter this array formula. Press Ctrl + Shift + Enter (not just Enter) to confirm since it's an array formula. This will look for the first keyword found in the description and return the matching category.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),"Other")
Once you have the formula working for the first row, drag the AutoFill Handle down the column to apply the categorization formula to all remaining transaction rows.
Parameter explanations:
Pros and cons analysis: This formula-driven approach allows rapid setup and low maintenance if your categorization rules do not change often. However, if your list of keywords grows more complicated or you need to frequently update categories, then managing helper columns and formulas can become tedious, and you may consider automation via VBA or Power Query.
Practical tip: If multiple keywords match in a description, only the first one found in your list will determine the category. To prioritize a particular keyword, place it earlier in your lookup column.
Common troubleshooting: If you receive unexpected results, double-check the lookup range and ensure that your keywords are spelled consistently and completely. Also, check for extra spaces or formatting differences in your transaction descriptions.
VBA Code - Automate categorization with a macro that matches transaction descriptions to categories based on a predefined list
This solution uses a VBA macro to automate the process of matching transactions to categories with enhanced control and scalability. It's especially beneficial for users with large transaction volumes or if you want to make the keyword-to-category reference dynamic and minimize manual formula management.
Applicable scenario: When the transaction list is long, updates are frequent, or you want to avoid maintaining manual formulas, VBA macros can process each description and assign the proper category more efficiently, with customizable logic and prompts.
Operational steps:
- Prepare a keyword-to-category list, similar to the helper columns used in the formula method (for example, in columns A and B from row 30 downward).
- Press Alt + F11 to open the Visual Basic for Applications editor. In the VBA window, click Insert > Module to add a new module.
Copy and paste the following code into the module:
Sub CategorizeTransactions()
Dim lastRow As Long
Dim i As Long
Dim descCell As Range
Dim kwRow As Long
Dim kwRange As Range
Dim catRange As Range
Dim kwCount As Long
Dim catResult As String
Dim matched As Boolean
On Error Resume Next
xTitleId = "KutoolsforExcel"
kwCount = Cells(Rows.Count, "A").End(xlUp).Row - 29
Set kwRange = Range("A30:A" & 29 + kwCount)
Set catRange = Range("B30:B" & 29 + kwCount)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To lastRow
Set descCell = Cells(i, "B")
catResult = "Other"
matched = False
For kwRow = 1 To kwCount
If InStr(1, descCell.Value, kwRange.Cells(kwRow, 1).Value, vbTextCompare) > 0 Then
catResult = catRange.Cells(kwRow, 1).Value
matched = True
Exit For
End If
Next kwRow
Cells(i, "F").Value = catResult
Next i
End SubHow to use:
- Click the
Run button or press F5 in the VBA editor to execute the macro. The macro will process each transaction description in column B, compare it to the keyword list, and write the matching category (or "Other" if no match is found) to column F of the same row. - You can adjust row numbers/ranges inside the macro to fit your data layout—such as changing where the transaction descriptions start or the helper list location. Ensure your keyword list does not have blank cells, and categories are clear and unique for easy referencing.
Run button or press F5 in the VBA editor to execute the macro. The macro will process each transaction description in column B, compare it to the keyword list, and write the matching category (or "Other" if no match is found) to column F of the same row.Pros and cons analysis: The VBA solution is adaptable to more complex rules, can be rerun any time after updating your keyword/category list, and removes the need for array formulas. However, macros require enabling automated operations in Excel, which may not suit all users or environments.
Practical tip: Store your VBA macro in a reusable file, and always back up data before running macros in case accidental overwriting occurs.
Troubleshooting suggestions: If categories are not updating as expected, check that your keywords and category lists align and that there are no hidden characters in the cells. VBA is case insensitive if you use "vbTextCompare", but mismatches can still happen due to formatting.
Other Built-in Excel Methods - Use Power Query to set up rule-based categorization via conditional column logic
If you prefer a modern, no-code automation approach, Power Query offers a robust way to categorize your bank transactions. This method is ideal when importing transaction data from CSV, online sources, or when managing dynamic and evolving categorization rules since it centralizes logic and provides easy updates without complex formulas or VBA scripts.
Operational steps:
- First, ensure your transactions are formatted as a table or clear data range. Select any cell in your table, then go to Data > From Table/Range to load the data into Power Query.
- In the Power Query Editor, click on Add Column > Conditional Column to create a new rule-based column for categories.
- Define your matching rules, such as:
- If Description contains "Mc Donalds", then output "Takeout"
- If Description contains "Walmart", then output "Family fee"
- Otherwise, output "Other"
- Click OK to create the conditional column, then select Close & Load to return your categorized results to Excel.
Pros and cons analysis: Power Query offers flexibility for rule changes, integrates well with external data imports, and can quickly refresh results when transaction data or rules are updated. It's also more efficient at handling very large datasets than manual formulas. However, Power Query requires basic setup and familiarity with its interface, which might be new to some users.
Practical tips: Arrange your rules in priority order (top to bottom) inside the Conditional Column dialog; the first matching rule will apply. You can easily edit, add, or delete rules in Power Query without altering your main Excel worksheet formulas.
Error reminders: If you import new transactions and the result does not refresh as expected, click Refresh in Excel's Data tab. Pay attention to spelling and exact keyword matches in Power Query, as partial mismatches may prevent the correct category from being assigned.
Troubleshooting suggestion: If your categories are not appearing correctly, review your conditional column rules for conflicts or missing cases. It's useful to test a few sample entries before applying changes across the full dataset.
In summary, whether you choose array formulas, the flexibility of a VBA macro, or the streamlined automation of Power Query, Excel offers several practical ways to categorize your bank transactions efficiently. Always review your categorization rules and result consistency as transaction data or categorization criteria evolve.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in