How to Categorize Bank Transactions in Excel

Start with transaction rows and a separate category column

To categorize bank transactions in Excel, arrange one transaction per row, keep Date, Description, and signed Amount unchanged, and add a Category column. For recurring merchants, use a lookup table to suggest categories. Review those suggestions before summarizing the final labels in a PivotTable.

Use the bank's transaction download when it covers the period you need. If the source is a PDF, convert it to rows first and compare the result with the issued statement. Categorization cannot recover missing transactions or correct a wrong amount.

The method below uses synthetic examples and Excel tables named Transactions and MerchantMap. Select each data range, choose Insert > Table, confirm its headers, and name the table. These are working tables you create in Excel, not extra fields automatically supplied by StatementMint.

Create a small merchant map

Create MerchantMap with two headers: MerchantKey and Category. Enter one reviewed key per merchant. Keep keys unique and category cells filled because a lookup returns a matching row, not an accounting judgment.

Choose labels that suit the report you need. A spending analysis might use Software, Office supplies, and Bank fees. A bookkeeping handoff should use the categories your bookkeeper expects. A merchant name alone does not establish business purpose or tax treatment.

Normalize a helper key without changing the description

Add MerchantKey to Transactions beside the source columns. For the first pass, assign the reviewed key yourself: HARBOR OFFICE SUPPLY 0482 becomes HARBOR OFFICE SUPPLY only after you confirm the merchant. Preserve the full original description for receipts, disputes, and later review.

If the description already contains only the merchant name, =UPPER(TRIM([@Description])) can standardize case and ordinary spaces. It does not remove every reference number, nonbreaking space, card suffix, or payment-processor prefix. Use it only where that transformation fits the source.

Do not strip all digits or match a short word across every description. Different merchants can share a prefix, and a marketplace can sell items belonging to many categories. Leave unclear keys blank and review the receipt instead of making a broad rule.

Suggest categories with an exact lookup

Add SuggestedCategory to Transactions and enter =IF([@MerchantKey]="","Review",XLOOKUP([@MerchantKey],MerchantMap[MerchantKey],MerchantMap[Category],"Review",0)). The blank-key check keeps empty cells in review; the final 0 requests an exact match.

Microsoft documents that XLOOKUP returns the first match. Before using the map, filter it for duplicate MerchantKey values and resolve conflicts. Exact matching still needs unique keys. XLOOKUP is available in newer Excel versions, but not Excel 2016 or Excel 2019.

For older Excel, with MerchantKey in column D and the two-column map in Map!A2:B100, use =IF(D2="","Review",IFNA(VLOOKUP(D2,Map!$A$2:$B$100,2,FALSE),"Review")). Extend the map range when you add rules. FALSE requests an exact match; the map key must be the first column.

Keep review decisions separate from formulas

Add OverrideCategory and FinalCategory. Leave the override blank unless a transaction needs a different label. In FinalCategory use =IF([@OverrideCategory]<>"",[@OverrideCategory],[@SuggestedCategory]). This retains both the original suggestion and the reviewer decision.

Filter SuggestedCategory for Review, then inspect the remaining suggestions too. A familiar merchant can still represent a different purpose. If one receipt spans categories, keep the source transaction once and use a separate allocation sheet whose amounts sum to the original.

Treat transfers and card payments carefully when combining accounts. If the underlying purchases already appear in your spending report, counting their payment again inflates spending. Keep refunds signed and link them to the relevant purchase category when supported by the records.

Build a PivotTable and check its totals

Select a cell in Transactions and choose Insert > PivotTable. Place FinalCategory in Rows and Amount in Values. Set Values to Sum of Amount. Microsoft notes that Excel may use Count when amounts are interpreted as text; fix the numeric source values before relying on the total.

For the five rows above, the full signed total is -650.00. Keeping Transfer visible makes it easy to account for all source rows. If you exclude transfers for a spending view, the remaining signed total is -150.00, or $150 of net outflow. The $20 refund reduces Office supplies from $120 to $100.

Refresh the PivotTable after editing categories or adding transactions. Check that its source still covers the table, include Review rows until resolved, and compare the unfiltered grand total with the original Amount total. Do not combine different currencies into one total without a separate documented conversion method.

Begin with a clean statement export

StatementMint exports the statement pages available under your allowance into Date, Description, and signed Amount in CSV or formatted Excel. It does not assign categories. Download the transaction rows, compare them with the PDF, then add the helper columns and map described here.

The Excel conversion guide covers the PDF starting point. The date-import guide helps with CSV date problems, and the spreadsheet-combination guide covers multiple statements. Categorization comes after those source checks so the category report remains traceable to the original transactions.

Frequently asked questions