Bank Statement Pivot Table in Excel: Summarize Spending by Category

Last updated July 2026.

A pivot table is the fastest way to turn a year of bank transactions into a summary you can actually act on. Once your statement data sits in Excel as clean rows, a pivot table totals spending by category, by month, or by vendor in about thirty seconds, with no formulas to write. The catch is that pivot tables are unforgiving about input formatting, and statement exports are full of the exact problems that break them.

Here is the short version: put your transactions in a single table with one header row and columns for Date, Description, Amount, and Category, make sure Excel sees the dates as real dates and the amounts as real numbers, then insert a pivot table with Category in Rows and Amount in Values. Everything below is the detail that makes that work on real statement data.

What your data needs to look like first

A pivot table reads a flat table, not a formatted report. That means one header row at the very top, no blank rows in the middle, no subtotal rows, no merged cells, and no bank logo or account summary block sitting above the transactions. If you exported straight from a PDF statement, strip the header block and the closing balance summary before you start.

You need four columns at minimum:

• Date, formatted as a real date value, not text
• Description, the merchant or payee text
• Amount, a single numeric column where money out is negative and money in is positive
• Category, the label you want to group by

The running balance column is useful for reconciliation but leave it out of the pivot. Summing a running balance produces a meaningless number, and it is a common reason a first pivot table looks wrong.

Building the pivot table

Select any cell inside your data, then go to Insert and choose PivotTable. Excel should detect the whole range automatically; if it does not, that usually means there is a blank row or column breaking the block. Put the pivot on a new worksheet.

In the field list on the right, drag Category into Rows and Amount into Values. Excel defaults to Sum for numeric fields, which is what you want. You now have total spend per category. Drag Date into Columns and Excel will offer to group it; choose Months, and you get a category by month grid, which is the view most people actually wanted.

To see the biggest individual vendors instead, replace Category with Description in Rows, then click the row label dropdown and sort by Sum of Amount ascending. Because spending is negative, ascending puts your largest outflows at the top.

The four formatting problems that break it

Almost every broken pivot table on statement data comes down to one of these.

Dates are text, not dates. If your dates are left-aligned by default in the cell, Excel is treating them as text, and the Group by Months option will be greyed out or will throw an error. Select the column, use Data then Text to Columns, click through to the final step, and set the column data format to Date with the order that matches your file, usually MDY for US statements. That converts the whole column in one pass.

Amounts are text. Currency symbols, thousands separators, and trailing spaces all turn numbers into text, and a pivot table will either count them instead of summing them or return zero. If your Values field shows Count of Amount rather than Sum of Amount, this is why. Strip the dollar signs and commas, then confirm the column right-aligns.

Debits and credits sit in two separate columns. Many statements export money out in one column and money in in another. A pivot table cannot sum across two columns sensibly. Add a single Amount column with a formula that subtracts the debit from the credit, so outflows come through negative, then pivot on that.

Negatives arrived as positives. Some conversions lose the minus sign, or represent it with a trailing sign or parentheses that Excel does not read. If every number is positive, your totals will be nonsense. Fix the sign before pivoting, not after.

All four are formatting problems rather than data problems, which is why it pays to start from a converted file that already has separate, correctly typed columns. If you are pulling from a PDF, the PDF bank statement to Excel converter outputs date, description, debit, credit, and running balance as distinct columns with real date and number types, which removes most of this cleanup.

Where the Category column comes from

Bank statements do not include categories. The description field gives you a merchant string like SQ *BLUE BOTTLE COFFEE or ACH DEBIT VERIZON WIRELESS, and you have to map that to something meaningful before a pivot table is any use.

For a one-off analysis, sorting by Description and hand labelling the repeat vendors gets you most of the way, because a typical small business has forty to sixty recurring payees that cover the large majority of transactions. For anything recurring, hand labelling stops being viable by the second month. Automatic transaction categorization from a bank statement assigns categories during conversion, so the Category column is already populated when the file reaches Excel. If categorizing spend is an ongoing job rather than a one-time cleanup, it is usually worth moving it into a dedicated expense management workflow that keeps the rules in one place instead of rebuilding them each month.

How do I summarize a bank statement in Excel?

Convert the statement to a clean table with Date, Description, Amount, and Category columns, then insert a pivot table with Category in Rows and Sum of Amount in Values. That gives you total spending per category in one step. Add Date to Columns and group by month to see how each category moves across the year.

Can you make a pivot table from a bank statement PDF?

Not directly. Excel cannot pivot a PDF, so the statement has to become spreadsheet rows first. Copying and pasting from a PDF usually produces merged columns and text-formatted numbers that break the pivot. Converting the PDF into structured columns first, then pivoting, is the reliable path and takes less time than fixing a bad paste.

Why is my pivot table counting instead of summing?

Excel counts rather than sums when it does not recognize the field as numeric, which almost always means the Amount column is stored as text. Look for left-aligned values, currency symbols, or stray spaces. Clean the column so it right-aligns, then refresh the pivot, or click the Values field and change Count to Sum manually.

How do I group bank transactions by month in a pivot table?

Drag the Date field into Rows or Columns, right click any date inside the pivot, choose Group, and select Months. If Months is unavailable, the dates are text rather than real date values. Convert them with Text to Columns first, then refresh the pivot and grouping will work. Add Years alongside Months if your data spans more than one calendar year.

A practical monthly workflow

Once the structure is right, this becomes a short monthly job. Convert the new statement into the same column layout, paste the rows onto the bottom of your existing data table, and refresh the pivot. Because the pivot points at the whole table, the new month appears automatically. Formatting the source range as an Excel Table with Ctrl plus T before you build the pivot makes this cleaner, since the table expands to include pasted rows without you re-pointing the pivot range.

From there the same data supports more than spend analysis. The category totals feed straight into a profit and loss view, which is covered on the bank statement to profit and loss page, and the broader mechanics of working with statement data in a spreadsheet are covered in the guide to analyzing bank statements in Excel.

Getting the underlying data right

Every problem in this article traces back to input quality. A pivot table is only as good as the columns underneath it, and the difference between a five minute analysis and a two hour cleanup is almost entirely decided before Excel opens. Starting from a bank statement converter that preserves column structure, date types, and transaction signs means the pivot works on the first try. If you want to check the conversion before you rely on it, the note on bank statement converter accuracy covers the balance check that catches missed rows.