How to Automatically Categorize Excel Transactions Using a Database in WPS Spreadsheets
Question details
The user wants to automatically classify transaction rows by matching text in descriptions against a category and keyword database.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing bank or card transactions and assigning categories such as Friends, Food, Fuel, Taxes, or Car based on names, stores, or keywords in the description.
- Observed behavior
- Transactions should be assigned automatically to the correct category when a description contains a known keyword or name.
Arrange the transaction data with Description and Category columns, and prepare a separate sheet or range where you will list each category and its matching keywords.
Create a keyword lookup database and categorize with SEARCH, ISNUMBER, and lookup formulas
Use this for a growing or changing category list because a separate keyword table is easier to maintain and update than rewriting long formulas.
A separate keyword table acts as a small database. The formula scans each transaction description, finds the first keyword match, and returns the related category without rigid nested IF conditions.
In WPS Spreadsheets, click the plus (+) icon near the sheet tabs to add a new sheet named Categories. Enter Category in A1 and Keyword in B1, then add rows such as A2: Food, B2: Supermarket; A3: Fuel, B3: Gas Station.
In the transaction sheet, click the first empty Category cell. Enter =IFERROR(INDEX(Categories!$A$2:$A$100,MATCH(TRUE,ISNUMBER(SEARCH(Categories!$B$2:$B$100,B2)),0)),"Uncategorized") and press Enter or Ctrl+Shift+Enter if required by your WPS Spreadsheets version.
Select the formula cell, then drag the fill handle down the Category column to apply the categorization to all transaction rows.
Type new Category and Keyword rows in the Categories sheet when needed. If you add rows beyond the original range, update the $A$2:$A$100 and $B$2:$B$100 references in the formula.
Use a nested IF formula for a small fixed category list
Choose this when you have only a few categories and prefer one self-contained formula without a separate lookup sheet.
Auto-Categorize Excel Transactions with a WPS Spreadsheets Keyword Database
WPS Spreadsheets gives you the formulas and lookup functions needed to build a category database in a separate sheet and automatically classify bank transactions. The workbook stays Excel-compatible, so you can keep using .xlsx files without switching to another office suite.
- 1. Open the transaction workbook: Launch WPS Office and open your .xlsx transaction file from the Start page, or double-click the file in File Explorer.
- 2. Create the category database sheet: Click the plus (+) button beside the sheet tabs, name the sheet Categories, and enter Category and Keyword column headers followed by your category and keyword rows.
- 3. Enter the categorization formula: Click the first empty Category cell in the transaction sheet and enter the SEARCH, ISNUMBER, and INDEX lookup formula that points to the Categories sheet.
- 4. Fill and review the results: Drag the fill handle down the Category column, then review Uncategorized rows and add missing keywords to the Categories sheet.
Frequently Asked Questions
Can I categorize Excel bank transactions automatically in WPS Spreadsheets?
Yes. Create a Categories sheet with Category and Keyword columns, then use a formula based on SEARCH, ISNUMBER, and INDEX or XLOOKUP to return the matching category for each transaction description.
Why does my SEARCH formula return #N/A?
The #N/A error normally means no keyword was found in the description. Wrap the formula in IFERROR to show a fallback such as Uncategorized, and check that the description cell and keyword list contain text values.
How do I add a new category or keyword later?
Add a new row in the Categories sheet, such as Category in column A and Keyword in column B. If your formula uses a fixed range, expand that range to include the new rows.
Does this categorization method work with Microsoft Excel files?
Yes. WPS Spreadsheets opens and saves .xlsx files, and the same formulas are compatible with Microsoft Excel, so you can share the categorized workbook without format conversion.
