Business

Build a merchant-by-month expense report with a pivot table (US)

Build a custom expense report that groups spending by merchant and month in a pivot table. Dollar example for a small online seller, plus data cleanup tips.

Editorial illustration for Build a merchant-by-month expense report with a pivot table (US)

A pivot table is the standard way to see spending by merchant and by month: merchants go down the side, months across the top, and each cell shows the sum. It takes about two minutes once your transactions sit in one tidy table. The real work is cleaning the merchant names first, so the same vendor does not appear three times.

What does a merchant-by-month report answer?

It answers questions such as "which suppliers did I pay most often?" and "did that software bill creep up?" Picture a small online seller who pays for shipping labels, packaging, a storefront subscription and a bookkeeping tool. Standard category reports would lump all of that under "business", which hides exactly what she wants to see: which vendor grew month over month.

How do I prepare the data?

  • One row per transaction, with headers in the first row: Date, Merchant, Amount.
  • No blank rows or merged cells inside the table.
  • Real dates in the Date column and real numbers in Amount (no currency symbols typed as text).
  • One spelling per merchant. If your statement shows "PACKCO 4471" one week and "PACKCO 5120" the next, add a clean Merchant column with a single name.

How do I build the pivot?

  1. Select any cell in the table and choose Insert, then PivotTable.
  2. Drag Merchant into Rows.
  3. Drag Date into Columns. In Excel it will often group by months automatically; if not, right-click a date and choose Group, then Months. In Google Sheets, right-click a date header and choose Create pivot date group, then Year-Month.
  4. Drag Amount into Values and make sure it says Sum.
  5. Turn on grand totals for rows and columns so you can check the whole thing at a glance.

What does the finished report look like?

MerchantJanuaryFebruaryMarchTotal
Shipping labels$184.20$211.60$246.05$641.85
Packaging supplier$96.00$0.00$132.40$228.40
Storefront plan$39.00$39.00$39.00$117.00
Bookkeeping tool$25.00$25.00$25.00$75.00

The January column adds up to 184.20 + 96.00 + 39.00 + 25.00 = $344.20, February to 211.60 + 0.00 + 39.00 + 25.00 = $275.60 and March to 246.05 + 132.40 + 39.00 + 25.00 = $442.45. Grand total: 344.20 + 275.60 + 442.45 = $1,062.25, which matches the right-hand column, 641.85 + 228.40 + 117.00 + 75.00 = $1,062.25. All figures are invented for the example.

Why is one merchant split into two rows?

Almost always a spelling difference: a trailing space, capital letters, or a store number. Fix it in the source data, not in the pivot. A quick helper column with =TRIM(B2) removes stray spaces, and =PROPER(TRIM(B2)) tidies the capitals. For store numbers, use a lookup table that maps each variation to a single name.

Can an app build this report instead?

If you import statements into SpendixAI, its report builder can filter and group, you can save the report you built, and export it to CSV. It is built around India and INR, so we are not saying it fits a US seller; it is useful to know what a report builder should do: group, filter, save and export.

Adding a trend you can see

Once the pivot works, add a line chart with months along the bottom and one line per merchant. Spikes appear immediately: a shipping cost that rises while order volume stays flat is worth a conversation with the carrier. If you want a number instead of a picture, add a column that subtracts the first month from the last for each merchant. In the example, shipping labels move from 184.20 to 246.05, a rise of 61.85 dollars, while the storefront plan stays at 39.00. That contrast tells you where to look.

Before you share it

  • Check the grand total against the sum of the raw Amount column.
  • Note the date range and what was excluded, such as personal spending on the same card.
  • Save the pivot on its own tab so the raw data stays untouched.

If the report is for tax preparation, ask your accountant which grouping they prefer. That differs by person and changes over time.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports