Prepare the Transaction Table
A PivotTable summarises rows, so the source needs a clean layout. Microsoft's guidance for creating a PivotTable is that your source cells should have a single header row. Keep one transaction per row, with columns such as Date, Description, Category and Amount.
Excel does not add a Category column for you; you add it yourself (for example, "Groceries", "Salary", "Transport"). Here is a small synthetic table:
Date Description Category Amount
2026-01-03 Sample Market Groceries -62.30
2026-01-10 Sample Payroll Salary 2000.00
2026-01-18 Sample Transit Transport -25.00
2026-02-04 Sample Market Groceries -48.20
2026-02-10 Sample Payroll Salary 2000.00Microsoft notes that if Excel reads a value field as text it counts instead of summing, so avoid mixing data types in the Amount column. Amounts stored as text need converting to numbers first.
Create the PivotTable
On Excel for Windows, Microsoft documents this sequence: select your source cells, choose Insert > PivotTable, pick New Worksheet or Existing Worksheet, and select OK. Microsoft also documents Insert > Recommended PivotTable, which suggests layouts, but notes that it is available only to Microsoft 365 subscribers.
The PivotTable field list has Rows, Columns and Values areas. By default, Microsoft says non-numeric fields go to Rows, date and time fields go to Columns, and numeric fields go to Values. You can drag fields between areas or clear their check boxes.
Arrange Fields for Month by Category
One practical arrangement is Category in Rows, Date in Columns, and Amount in Values. Numeric fields in Values are summed by default, so each cell shows the sum of the Amount for that category and period. To change the calculation, Microsoft points to Value Field Settings (Windows) and Summarize Values By.
With the synthetic table above, Groceries for January would show -62.30 and for February -48.20; the Salary row would show 2000.00 in both months. Check your own totals against the source rows, because the PivotTable reflects whatever signs and categories are in your data.
Group Dates into Months
If the date field shows individual days, Microsoft documents grouping: right-click a value in the PivotTable and choose Group, adjust Starting at and Ending at if needed, then under By pick a time period such as months, and click OK. Microsoft says this can group an unwieldy list of date and time fields into quarters and months.
To reverse it, right-click any item in the group and select Ungroup.
Limits to Keep in Mind
The PivotTable only analyses the rows you give it, and the categories are your own labelling. If a statement starts as a PDF, the transactions need to be extracted into rows before any of this applies. PDFtoRow converts bank statement PDFs to Excel or CSV and shows a free preview of the first 10 rows.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
Microsoft says that if Excel reads a field as text it uses COUNT. Make sure the Amount column contains true numbers and no mixed data types, then rebuild or refresh the PivotTable.
Right-click a date value in the PivotTable, choose Group, and under By pick a time period such as months, then click OK. Microsoft documents Ungroup to reverse it.
Yes. Microsoft documents Value Field Settings on Windows (Field Settings on Mac) with Summarize Values By.
Primary Evidence & Official References
- Create a PivotTable to analyze worksheet data — support.microsoft.com (Verified 2026-10-29). Official source documentation →
- Group or ungroup data in a PivotTable — support.microsoft.com (Verified 2026-10-29). Official source documentation →