Bank Statement Pivot Table in Excel: Analyze Spending

TL;DR Start with a clean table of transactions: one row per transaction, a real date, a signed amount, a category and an account. Insert a pivot table from that table, put category in rows, date (grouped by month) in columns, and amount in values. Filter out transfers, add slicers for account and year, and check that the pivot's grand total equals the sum of your source data. From there you can answer most questions about where money went in a few clicks.

A pivot table is the quickest way to turn a long list of bank transactions into something you can actually read. A year of statements might be two thousand rows; a pivot table summarizes them into a grid of categories and months that fits on one screen. It doesn't need formulas, it updates when you add data, and you can rearrange it in seconds to answer a different question.

This guide walks through building one from bank statement data, the settings that make it reliable, and a handful of layouts that answer common questions. It uses Excel, but Google Sheets pivot tables work much the same way.

Start with good source data

Pivot tables are only as good as the data under them. Before you insert one, make sure your transactions look like this:

The rules:

• One row per transaction. No subtotal rows, no blank rows, no repeated headers from each page of the statement.
• Real dates. =ISNUMBER(A2) should return TRUE. Text dates won't group by month.
• Real, signed numbers. Money in positive, money out negative, in a single column. Text amounts or separate debit and credit columns make the pivot much harder to use.
• A category for every row, even if it's Uncategorized. Blanks show up as "(blank)" and make totals harder to read.
• An account column if the data comes from more than one account.

If you're starting from PDF statements, convert them first. SheetStatement's bank statement to Excel converter produces exactly this shape, with dates and amounts as real values, and checks each statement's transactions against its opening and closing balances so you know the data is complete. For cleanup tips on data you already have, see how to clean bank statement data in Excel.

Make it a table

Select the data and press Ctrl+T (or Insert, Table), and confirm it has headers. Give it a name on the Table Design tab, such as Transactions. This matters because a pivot table built on a table picks up new rows automatically when you refresh. A pivot built on a fixed range like A1:E2000 does not, and you'll eventually miss data.

Add categories first

If you haven't categorized yet, do it before building the pivot. A rules table with XLOOKUP or a SEARCH-based formula handles most recurring payees; our guide to Excel formulas for categorizing bank transactions has formulas you can copy. Include a Transfer category for moves between your own accounts and card payments, so you can exclude them.

Build the basic pivot

1. Click anywhere in the Transactions table.
2. Go to Insert, PivotTable. Confirm the source is the table name and choose a new worksheet.
3. In the field list, drag Category to Rows.
4. Drag Date to Columns. Excel usually groups dates automatically into years, quarters and months. If it doesn't, right-click a date in the pivot, choose Group, and select Months and Years.
5. Drag Amount to Values. Make sure it says "Sum of Amount", not "Count of Amount". If it says Count, the amount column has text in it somewhere; go back and fix the source.

You now have a grid: categories down the side, months across the top, totals in each cell.

Exclude transfers

Drag Category into Filters as well, or use the drop-down arrow on the Row Labels to uncheck Transfer. Transfers aren't income or spending, and leaving them in doubles the apparent flow of money.

Format the numbers

Right-click a value, choose Number Format (not Format Cells, which only affects the selected cells), and pick a currency or number format with two decimal places and thousands separators. Using Number Format keeps the format when the pivot refreshes.

Check the pivot ties out

Before you trust the pivot, confirm it adds up.

1. Temporarily clear the Transfer filter so every row is included.
2. Compare the pivot's grand total with =SUM(Transactions[Amount]).
3. They should be identical.

If they're not, you have filters or blank values hiding data. Common causes: a date that isn't a real date (so it falls outside the grouping), a category that's blank, or a row filter left on. Fix the source and refresh.

If your data comes from statements, you can go one step further: for each statement month and account, the pivot's net total should equal the closing balance minus the opening balance. That's a useful cross-check when you've combined many statements.

Useful layouts

Once the basic pivot works, rearranging fields answers different questions. Here are layouts that come up again and again.

Spending by category, by month

Rows: Category. Columns: Months. Values: Sum of Amount. Filter: exclude Transfer and income categories.

This is the standard budget view. Read across a row to see a category over time; read down a column to see where a month's money went.

Top payees

Rows: Description (or a cleaned Payee column). Values: Sum of Amount. Sort ascending (largest outflows first, since they're negative), and use the Row Labels filter, Value Filters, Top 10.

This often surprises people: small subscriptions and frequent purchases at the same place add up faster than expected.

Income by source

Rows: Category (filtered to income categories) or a Source column. Columns: Months. Values: Sum of Amount.

Useful for freelancers and anyone preparing proof of income. Our guide to self-employed proof of income from bank statements explains how lenders typically look at this.

Account comparison

Rows: Category. Columns: Account. Values: Sum of Amount.

Shows which account carries which spending. Handy if you're trying to move all business costs onto one card.

Average monthly spending per category

Rows: Category. Values: Sum of Amount. Add a second copy of Amount to Values and change it to show Average, with Columns set to months; or calculate totals divided by the number of months in a cell beside the pivot. Pivot averages are averages per transaction by default, which isn't the same thing, so be careful what "average" means.

Count of transactions

Drag Amount into Values a second time and change it to Count. It tells you how many transactions are behind each total, which helps when one category looks large because of a single unusual purchase.

Show values as percentages and running totals

The Show Values As option (right-click a value, Show Values As) changes what each cell displays without changing the source data. Three settings are particularly useful with bank data.

% of Column Total. Each category's share of the month's total. Useful for seeing whether housing, food or subscriptions are taking a larger share over time, even when total spending changes.

% of Grand Total. Each cell's share of the whole year. Good for a one-glance view of which categories matter and which are noise.

Running Total In (Date). A cumulative total across months. For a spending category, it shows how much you've spent year to date at each month end. For a savings or income category, it shows how the total builds. Pair it with a pivot chart for a simple year-to-date line.

You can add Amount to Values more than once and show each copy differently: one as the plain sum, one as a percentage. Rename each value field (click the field, Value Field Settings, Custom Name) so it's clear which is which, for example "Total" and "Share of month".

Grouping categories and payees

Raw categories are often too detailed for a summary. If you have separate categories for groceries, restaurants and coffee, you may want a single "Food" line in a high-level view.

Two approaches:

1. Group in the pivot. Select the category rows you want to combine, right-click, Group, and rename the group. Quick, but the grouping lives only in that pivot.
2. Add a parent column in the source. Add a Group column to your table, filled from your rules table with a second lookup. Put Group and Category both in Rows, and collapse or expand as needed. This is better if you use several pivots, because every pivot can use the same grouping.

The same trick works for payees. A Payee column with cleaned names (one version of each merchant rather than ten slightly different descriptions) makes a top-payees pivot much more accurate. Bank descriptions often include store numbers, locations and reference codes that split one merchant into many rows.

Slicers and timelines

Slicers are buttons that filter the pivot visually.

1. Click in the pivot, go to PivotTable Analyze, Insert Slicer.
2. Choose Account and Category.
3. Click a slicer button to filter; Ctrl+click to choose several.

A Timeline (PivotTable Analyze, Insert Timeline) does the same for dates, with a draggable bar for months, quarters or years. Slicers and timelines make a pivot easy to use for someone who doesn't know how pivot tables work, such as a partner or a client.

Pivot charts

With the pivot selected, PivotTable Analyze, PivotChart, and choose a chart type. A stacked column chart of spending categories by month is a good default. Keep the number of categories small (the top six or eight, with the rest grouped as "Other") or the chart becomes unreadable. You can create an "Other" group by selecting several categories in the pivot, right-clicking and choosing Group.

Refreshing with new months

When you add next month's transactions to the Transactions table, the pivot doesn't update until you refresh it: right-click the pivot and choose Refresh, or use Data, Refresh All. Because the pivot is built on a table, the new rows are included automatically.

A monthly routine:

1. Convert the new statement and check it balances.
2. Paste the rows at the bottom of the Transactions table.
3. Fill in categories (most will be handled by your rules).
4. Refresh the pivot.
5. Check the grand total against the table sum.

If you'd like this as part of a fuller monthly process, our expense tracking system builds on the same table.

Common pivot table mistakes with bank data

Counting instead of summing. If Values shows "Count of Amount", at least one amount cell contains text. Fix it at the source; changing the pivot to Sum won't help, because text values are ignored.

Double counting transfers. A payment from checking to your credit card appears once as money out of checking and once as money into the card account. Leaving both in makes spending look larger than it is. Categorize both sides as Transfer and exclude them.

Mixing signs between accounts. If card files show purchases as positive and bank files show them as negative, the pivot will net spending against itself. Normalize signs before combining.

Grouping fails on dates. If Excel won't group dates, some of them are text. Find them with a filter on the date column or =ISNUMBER() and fix them.

Refunds look like income. A refund is a positive amount in a spending category. That's fine; it correctly reduces that category's total. Just make sure refunds are categorized to the original spending category, not to income.

Stale data. If the numbers don't change after adding rows, you forgot to refresh, or the pivot was built on a fixed range instead of a table.

A worked example

A small online shop wants to understand its costs for the year. The owner has twelve checking statements and twelve credit card statements.

1. Convert and check. All twenty-four statements are converted; each one's transactions add up to its closing balance.
2. Combine. Everything goes into one table with an Account column. Card amounts are flipped so spending is negative everywhere.
3. Categorize. About twenty rules cover most transactions: payment processor payouts as Sales, shipping labels, packaging supplier, marketplace fees, advertising, software, and Transfer for card payments.
4. Build the pivot. Category in rows, months in columns, amount in values, Transfer filtered out.
5. Tie out. With the filter cleared, the grand total matches the table sum exactly.
6. Read it. Shipping is the largest cost after inventory. Advertising spikes in November and December. One software subscription that the owner thought had been cancelled is still charging every month.
7. Add a slicer for Account so the owner can see how much runs through the card versus checking.

The owner used the pivot to plan next year's ad budget and cancel the forgotten subscription. Her accountant used the same table, with the categories, to check the books.

Do pivot tables replace accounting software?

Not for a business that needs proper books, invoices and tax reporting. A pivot table is an analysis tool: it summarizes what's in the table. It doesn't handle accruals, unpaid bills, depreciation or reconciliation. For a business, the usual setup is to keep the books in accounting software and use pivot tables for extra analysis. Our guides to importing bank statements into QuickBooks Online and the Xero CSV import format cover getting the same converted data into those tools.

For personal finances, a well-maintained table with a pivot on top is often all you need.

Try it with your own statements

Upload your statements to SheetStatement, confirm each one balances, and download Excel. Paste the rows into a table, add categories, and build the pivot. The free plan covers 3 pages a month; paid plans are $9, $19 or $39 a month if you're processing a full year at once.

FAQ

How do I make a pivot table from a bank statement?

Convert the statement to a clean table with a date, description, signed amount and category. Then insert a pivot table with category in rows, date grouped by month in columns and amount in values.

Why does my pivot table show Count instead of Sum?

At least one value in the amount column is text. Convert all amounts to numbers in the source data, then refresh.

How do I exclude transfers from a pivot table?

Give transfers their own category, then filter that category out in the pivot. Do this for both sides of the transfer.

Why won't Excel group my dates by month?

Some dates are stored as text. Convert them to real dates in the source table, refresh, and grouping will work.

How do I add new months to an existing pivot table?

Build the pivot on an Excel table, paste new rows at the bottom of the table, and refresh the pivot.

Can I do the same in Google Sheets?

Yes. Google Sheets pivot tables work similarly: rows, columns, values and filters. Date grouping uses the "Create pivot date group" option on the row or column field.