Bank Statement Analysis in Excel (Workflow)

Excel is a capable home for bank statement analysis. Once your transactions sit in rows and columns, you can group spending, total income, and watch how the numbers move month to month. This is a workflow rather than a fixed file — the same steps work whether you analyze one account or several, and they hold up as the data grows. For the wider picture of why this matters, see the overview of bank statement analysis.

Step 1: Get the transactions into rows

Analysis starts with clean, structured data. Each transaction should be one row, with separate columns for the values you want to slice by. A workable layout:

• Date — formatted as a real date, not text
• Description — the raw text from the statement
• Amount — the transaction value
• Type — debit or credit, or a single signed amount column
• Category — added in the next step

If your statement is a PDF, convert it before you analyze it. The companion guide on how to convert a bank statement to Excel walks through manual entry, copy-paste, and automated extraction. For repeat work, the bank statement converter returns rows you can paste straight into a worksheet.

Once the data is in, convert the range to an Excel Table. Tables give you named columns, keep formulas filling down automatically as you add rows, and make later formulas easier to read.

Step 2: Categorize the transactions

Raw descriptions are hard to total, so the next step is tagging each row with a category — groceries, rent, salary, transfers, utilities, and so on. A few approaches, from simplest up:

• Manual — type the category in by hand. Fine for a short statement.
• Lookup — keep a second sheet that maps keywords to categories, then use a lookup function to match part of the description. This scales because you only maintain the keyword list, not every row.
• Helper column — combine text functions to flag patterns (for example, descriptions containing "PAYROLL" or "INTEREST") and assign categories from those flags.

Whichever you choose, keep the category list short and consistent. Twenty tidy categories analyze far better than two hundred near-duplicates. A drop-down list (data validation) on the category column prevents typos that would otherwise split one category into several.

Step 3: Summarize income and expenses with SUMIFS

With categories in place, conditional sum functions do the heavy lifting. SUMIF totals a column where one condition is met; SUMIFS handles several conditions at once. Common summaries:

• Total spent in a category: sum the amount column where category equals "Groceries".
• Total income: sum the amount column where the type is a credit.
• Spending in a date range: sum where the date falls between two values, which is how you isolate a single month.

Pair these with COUNTIFS to see how many transactions fall in each bucket, and AVERAGEIFS for the typical size of a transaction. Build a small summary block — categories down the side, totals beside them — and it updates the moment you add rows to the table.

Step 4: Build a PivotTable for flexible views

SUMIFS is precise but rigid; a PivotTable is the opposite. Point a PivotTable at your transaction table and you can drag categories, dates, and amounts into rows, columns, and values without writing a formula. From the same data you can produce:

• Spending by category, sorted from largest to smallest
• Income and expense totals side by side
• A month-by-month grid, with dates grouped into months or quarters

Grouping the date field by month turns a long transaction list into a compact monthly view. When you change the underlying data, refresh the PivotTable to pull the new rows in. Because a PivotTable rebuilds on demand, it suits exploration — try a layout, change it, and try another without disturbing the source data.

Step 5: Track monthly trends

Cash flow is a story over time, so add a time dimension. With a PivotTable grouped by month, or with a SUMIFS block that has one row per category and one column per month, you get a table that shows where each category is drifting. Watching the trend matters more than any single month: a subscription that creeps upward, a seasonal utility spike, or income that dips in a quiet quarter all show up across the row.

A running total column — each month's net added to the prior balance — turns the same data into a simple cash-position view, so you can see whether the account is building up or drawing down over the period.

Step 6: Add a few charts

Numbers tell you what; charts tell you at a glance. A handful is usually enough:

• A column chart of monthly income versus expenses to compare the two side by side
• A pie or bar chart of spending by category for one period
• A line chart of a category over several months to show its trend

Charts built from a PivotTable update with it, so once the link is set the visuals stay current as you add statements. Keep them plain — a chart that needs a paragraph of explanation is doing less than a clean table.

Keeping the workflow repeatable

The value of an Excel workflow is in reuse. Keep the column layout and category list stable so that each new month's transactions drop into the same structure and the summaries, PivotTables, and charts all flow through without rework. Convert the statement, categorize the rows, refresh, and read the result.

When you are ready to go deeper into ratios, anomalies, and what to look for across a full account history, the broader bank statement analysis guide covers the next layer.