# Pivot Table for Bank Transactions: Spending by Category

- Author: CheapBankConvert (https://cheapbankconvert.com)
- Published: 2026-10-17
- Category: Budgeting from your statements
- Canonical: https://cheapbankconvert.com/blog/pivot-table-for-bank-transactions

> Build a pivot table for bank transactions in Excel or Sheets: spending by category and month, filters for one payee, and a total check.
A pivot table for bank transactions turns a long list of statement rows into a grid of spending by category and month. Put your rows in one table with headers for date, description, amount, category and month. Insert a pivot table, drag Category to Rows, Month to Columns and Amount to Values, then check the total against the statement.

## Preparing bank transaction rows for a pivot table

A pivot table needs bank transaction rows in a plain table: one header row, one transaction per row, no blank rows and no merged cells. Each column should hold one kind of data. Amounts must be real numbers, not text, and you need a category and a month column before the pivot can group anything useful.

Microsoft's own guide to [creating a PivotTable in Excel](https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576) sets out the same rules: a single header row of unique, non-blank labels, data in a tabular format with no blank rows or columns, and one data type per column. Statement data broken across PDF pages tends to fail at least one of these, so a little preparation pays off.

The columns that work best are:

| Column | Example value | Notes |
| --- | --- | --- |
| Date | 2026-09-14 | A real date, not text |
| Description | KROGER #0412 COLUMBUS OH | As the bank printed it |
| Amount | -86.40 | Negative for money out, positive for money in |
| Category | Groceries | From a lookup or typed |
| Month | 2026-09 | `=TEXT(A2,"yyyy-mm")` |
| Payee | KROGER | Optional, a cleaned-up name for filtering |

Three preparation steps remove most problems:

1. **Delete repeated headers and page footers.** A statement copied page by page often repeats "Date Description Amount" every few dozen rows. Each repeat breaks the one-header rule.
2. **Check that amounts are numbers.** Click an amount. If it sits on the left of the cell, Excel is treating it as text. Data, Text to Columns, Finish usually converts a whole column in one step.
3. **Turn the range into an Excel table.** Select a cell and press Ctrl+T. A table grows when you paste more rows underneath, which matters later when you refresh.

The category column does the real work. A pivot can only group what you have labelled. If you have not built one yet, the post on [adding up spending by category with SUMIFS](/blog/sumifs-spending-by-category) includes a payee lookup you can reuse here.

## Building the bank transactions pivot table in Excel

To build the bank transactions pivot in Excel, select any cell in your table, choose Insert, PivotTable, and place it on a new sheet. In the field list, drag Category to Rows, Month to Columns and Amount to Values. Excel then shows each category as a row and each month as a column, with totals at the edges.

A few settings make the result easier to read:

- **Show spending as positive numbers.** Spending rows are negative, so the pivot shows negative totals. Either add a column called Spend with `=-C2` and use that in Values, or leave the sign and read negatives as money out.
- **Keep income out of the spending grid.** Drag Category to Filters as well, or filter the row labels, and untick Income and Transfers. That leaves a pure spending view.
- **Check the summary type.** Values should read "Sum of Amount". If they read "Count of Amount", some amounts are stored as text. Microsoft's guide notes that Excel shows a count when it interprets the data as text. Fix the source column, then change the field to Sum in Value Field Settings.
- **Format the numbers.** Right-click a value, choose Number Format and pick Currency or a two-decimal number. Formatting the source column does not carry into the pivot.

If you added a Month column as text, the months sort correctly because "2026-09" comes before "2026-10" alphabetically. If you used the Date column directly, newer Excel versions group dates into years, quarters and months on their own. Either works, but the text month is more predictable across versions.

## Can you build a bank transactions pivot table in Google Sheets?

Yes. Google Sheets builds a bank transactions pivot table in almost the same way. Select the data, choose Insert, Pivot table, and use the side panel to add Category under Rows, Month under Columns and Amount under Values as a SUM. The one big difference is refreshing, which Sheets handles for you.

Google's help page on [creating and using pivot tables in Google Sheets](https://support.google.com/docs/answer/1272900) says each column needs a header, and that the pivot table refreshes any time you change the source cells. That removes the most common Excel problem, a pivot that does not show new rows, as long as the new rows fall inside the selected range.

To make sure they do, select whole columns as the source, for example `Transactions!A:F`, rather than a fixed range like `A1:F240`. Blank rows at the bottom show up as a "(blank)" row label, which you can filter out in the side panel.

Two other differences matter for statement data:

- **Filters live in the side panel.** Click Add next to Filters, choose Category, and untick the values you want hidden.
- **Number formats.** Format the pivot's values from the Format menu after it is built.

Google Sheets is free with a personal Google account. If you are deciding whether you need a paid Workspace plan for this kind of analysis, the breakdown of [Google Sheets on a free account versus Workspace](/blog/google-sheets-free-vs-workspace) covers what changes and what stays the same.

## Worked example: a three-month spending pivot from example rows

The example below takes made-up statement rows from July to September 2026 and shows the pivot they produce. All payees and amounts are illustrative. It shows what the grid looks like with spending turned positive, income and transfers filtered out, and a grand total you can check.

Sample source rows (twelve of about 180):

| Date | Description | Amount | Category | Month |
| --- | --- | ---: | --- | --- |
| 2026-07-03 | KROGER #0412 | -94.20 | Groceries | 2026-07 |
| 2026-07-05 | SHELL OIL 57442 | -41.80 | Transport | 2026-07 |
| 2026-07-15 | PAYROLL ACME CO | 2,150.00 | Income | 2026-07 |
| 2026-07-19 | DOORDASH | -32.15 | Eating out | 2026-07 |
| 2026-08-02 | KROGER #0388 | -121.60 | Groceries | 2026-08 |
| 2026-08-11 | TRANSFER TO SAVINGS | -300.00 | Transfers | 2026-08 |
| 2026-08-14 | CITY WATER | -48.90 | Utilities | 2026-08 |
| 2026-08-22 | TARGET T-1123 | -67.44 | Shopping | 2026-08 |
| 2026-09-01 | PROPERTY MGMT ACH | -1,450.00 | Housing | 2026-09 |
| 2026-09-09 | SHELL OIL 57442 | -38.25 | Transport | 2026-09 |
| 2026-09-18 | NETFLIX.COM | -15.49 | Subscriptions | 2026-09 |
| 2026-09-27 | CHIPOTLE 2291 | -14.85 | Eating out | 2026-09 |

Resulting pivot, Sum of Spend, with Income and Transfers filtered out:

| Category | 2026-07 | 2026-08 | 2026-09 | Grand total |
| --- | ---: | ---: | ---: | ---: |
| Housing | 1,450.00 | 1,450.00 | 1,450.00 | 4,350.00 |
| Groceries | 588.30 | 641.75 | 603.10 | 1,833.15 |
| Eating out | 212.40 | 188.95 | 264.80 | 666.15 |
| Transport | 176.55 | 159.20 | 201.65 | 537.40 |
| Utilities | 241.60 | 266.38 | 229.14 | 737.12 |
| Shopping | 318.20 | 402.33 | 255.90 | 976.43 |
| Subscriptions | 61.47 | 61.47 | 76.96 | 199.90 |
| Grand total | 3,048.52 | 3,170.08 | 3,081.55 | 9,300.15 |

Reading across a row shows trend. Eating out jumped in September. Subscriptions rose by $15.49 in September, which matches the Netflix row in the sample and suggests a new or restarted plan. Reading down a column shows where one month's money went.

A grid like this is also the cleanest source for a chart. The walkthrough for [charting spending by category in Excel](/blog/chart-spending-by-category-in-excel) starts from a summary of exactly this shape.

## Filtering the transactions pivot table to one payee

To see spending at a single merchant, add a filter for payee or description to the transactions pivot table. In Excel, drag Payee into the Filters area and pick one name. In Google Sheets, add a filter on the Payee column in the side panel. The grid then shows only that merchant's rows, still split by month.

A cleaned Payee column makes this practical. Raw descriptions carry store numbers and dates, so "KROGER #0412" and "KROGER #0388" count as two values. A Payee column that holds just "KROGER" groups them. You can fill it with the same lookup you use for categories, returning a payee name instead.

Three filter tricks help with statement data:

- **Label filters in Excel.** On the row labels drop-down, choose Label Filters, Contains, and type a fragment such as "AMAZON". This works even without a Payee column.
- **Slicers.** In Excel, PivotTable Analyze, Insert Slicer, Payee gives you clickable buttons for each merchant. That is faster than reopening the filter list.
- **Top ten.** Put Payee on Rows instead of Category, then use Value Filters, Top 10 to list your largest merchants for the period.

A payee filter answers questions like "how much did we spend on food delivery this quarter" in seconds, without writing a formula.

## Why does the pivot table total not match the bank statement?

A pivot table total that does not match the bank statement usually means rows were filtered out, duplicated, or stored as text. Before trusting any category figure, check that the pivot's unfiltered grand total equals the sum of the Amount column, and that this sum equals the statement's closing balance minus its opening balance.

Run the check in this order:

1. **Clear every filter in the pivot.** Income, transfers and every category should be visible. Hidden rows are the most common cause of a mismatch.
2. **Compare the pivot grand total with `=SUM(C:C)` on the source.** If they differ, the pivot range does not cover all the rows, or some amounts are text and dropped out of the sum.
3. **Compare the source sum with the statement.** For one statement, the net of all rows should equal closing balance minus opening balance. A gap means a missing or duplicated row.
4. **Look for duplicate rows.** Overlapping downloads, for example two CSVs that both include the 15th, create duplicates. Sort by date and amount to spot them.
5. **Reapply your filters.** Once the unfiltered total matches, the filtered spending view is trustworthy.

This check takes two minutes and catches the problems that make a budget review misleading. It also explains a number that looks wrong: often the "wrong" total is right, and the surprise is a row you had forgotten.

## Pivot table refresh mistake when new statement rows arrive

The usual pivot table mistake with statements is pasting next month's rows and forgetting to refresh. In Excel, a pivot does not update itself when its source data changes. The new rows sit in the sheet while the pivot still shows last month's totals, and nothing on screen warns you.

Microsoft's guide says pivot tables built on a data source need to be refreshed after new data is added. Right-click inside the pivot and choose Refresh, or use Refresh All on the PivotTable Analyze tab if you have several.

Refreshing only helps if the new rows are inside the source range. Two ways to make sure they are:

- **Use an Excel table as the source.** A table created with Ctrl+T expands when you paste directly below it, and the pivot follows.
- **Check the source after pasting.** PivotTable Analyze, Change Data Source shows the range. If it ends above your new rows, extend it.

You can also tell Excel to refresh the pivot when the file opens, under PivotTable Options, Data, "Refresh data when opening the file". That covers the case where you paste, save and come back later.

Most of the effort in a statement pivot happens before the pivot exists, in getting clean rows. If your statements arrive as PDFs, CheapBankConvert converts each one to a CSV or Excel file with date, description, amount and balance columns, priced per statement with no subscription. It is in early access, and you can [get started](/get-started) to be emailed when conversions open.