A list of bank transactions answers “what happened?” A monthly spending report answers “what changed, and what should we look at next?”
You can build that report in Excel with a small, reviewed transaction table and a PivotTable. The important work is deciding which movements count as spending before drawing the chart.
This example turns £630 of purchases and a £30 refund into £600 of net spending, while keeping a £400 savings transfer out of the spending total. It is designed for a UK household, but the same reporting structure can help a small business examine cash-paid costs. It is not a profit-and-loss or tax calculation.
Reviewed on 1 November 2026 against BankSync and Microsoft documentation. All figures are illustrative.
Start with a test set you can explain
| Date | Category | Bank amount | Include in spending |
|---|---|---|---|
| 3 September | Groceries | −£120 | Yes |
| 8 September | Transport | −£60 | Yes |
| 11 September | Home | −£450 | Yes |
| 15 September | Groceries refund | +£30 | Yes, reduce groceries |
| 18 September | Savings transfer | −£400 | No |
Define spending before building the PivotTable
Use a signed source amount: money out is negative and money in is positive for this worked example. Check your own mapping rather than assuming that convention.
Create a reviewed reporting amount called Net spend. For included purchases and refunds, it is the negative of the bank amount: a −£120 purchase becomes £120 of spending; a +£30 refund becomes −£30 of spending. For excluded movements, use zero.
This produces groceries of £90, transport of £60 and home spending of £450. Total net spend is £600. The transfer still belongs in an account reconciliation; excluding it here does not delete it.
Keep a separate Include in spending decision and an explanation for exceptions. A positive bank amount could be wages, a refund, a loan or an internal transfer. The sign alone cannot classify it.
Prepare a stable report source
BankSync's Excel integration can deliver bank records to a selected OneDrive workbook and worksheet. Keep those incoming columns intact and build the reviewed reporting fields alongside them or in a separate maintained analysis table.
For UK feeds, start with posted transactions unless you have a specific pending-review process. The UK bank documentation explains why pending and posted records can be separate.
Use one currency per spending report. A pound and a euro are different units; formatting both as pounds does not convert them. If you later introduce currency conversion, document the rate and date convention separately.
Suggested reporting fields
| Field | Example | Reason |
|---|---|---|
| Transaction key | Account plus source identity, where available | Trace the row back to its source |
| Date | 3 September 2026 | Choose a consistent period |
| Month | 1 September 2026 | Sort months chronologically |
| Review category | Groceries | Group comparable costs |
| Include in spending | Yes | Make scope explicit |
| Net spend | 120 | Sum purchases less refunds |
| Currency | GBP | Prevent mixed-unit totals |
Build the monthly view
Prepare the source
Use unique column headers and clean rows; ensure dates and amounts have appropriate types.
Create the PivotTable
Select the reporting table or range, choose Insert → PivotTable and put the result on a separate worksheet.
Place the fields
Put Review category in Rows, Month in Columns and Net spend in Values.
Check the calculation
Use Sum of Net spend, not a count of transactions. Filter to the intended currency and period.
Refresh after delivery
Check that new rows are inside the source and refresh the PivotTable before reviewing it.
Microsoft's PivotTable guide documents the creation and refresh process. Its source-data guide covers changing the underlying range. A successful bank sync and a refreshed report are separate checks.
For your own Month field, use a real first-of-month date rather than a label such as “Sep” without a year. That keeps September 2026 distinct from September 2027. You can format the date to display a shorter label.
Expected result for the example
| Category | September net spend | Interpretation |
|---|---|---|
| Groceries | £90 | £120 purchase less £30 refund |
| Transport | £60 | One included purchase |
| Home | £450 | One larger purchase |
| Total | £600 | Savings transfer excluded |
Turn the report into a useful conversation
Start with the largest change you can explain, not the brightest colour in the chart. A £450 home purchase may be a planned replacement rather than a broken monthly budget.
Compare complete months with complete months. If you are reviewing on the tenth, label the current period “month to date”. Otherwise a partial month can appear artificially cheap beside a full one.
Keep uncategorised transactions visible as their own group. Hiding that group makes the chart cleaner while making the report less complete. Review it before drawing conclusions about which category increased.
For visual presentation, a simple horizontal bar chart of categories is often enough. Keep the table beside it so the household can see exact amounts. Use the same colours consistently and avoid presenting an illustrative target as if it were a bank-supplied fact.
Close the loop each month
Check a sample purchase, refund and transfer against the raw rows. Confirm the new month is included, review uncategorised items and record the date of the review.
If the total looks wrong, investigate scope, duplicated records, currency, sign convention and source range before editing individual amounts. A report should be explainable from its records.
For the related transfer problem, see avoiding double-counted credit-card payments. For the account-level control, use opening-to-closing balance reconciliation.