DEV Community

thienkrik - small shop
thienkrik - small shop

Posted on

Build a one-click monthly profit report in Excel with a PivotTable (no formulas)

Every month-end I used to add up income and expenses by hand, category by category. It took an evening and the totals never quite matched. A PivotTable does the same job in one click — and it updates itself when new rows come in.

Here is the setup, using a small shop's November numbers as the example. The raw sheet is just one list of transactions: date, category, type (income or expense), and amount.

  1. Click anywhere inside the list, then Insert → PivotTable → New Worksheet.
  2. Drag Month to Rows, Type to Filters, Category to Columns, and Amount to Values.
  3. Set the Type filter to Income: November income is 40,640,000 VND.
  4. Switch the filter to Expense: November expenses are 29,880,000 VND. The biggest lines are stock purchases 15,100,000 VND (50.5%), salaries 5,500,000 VND (18.4%), and rent 5,000,000 VND (16.7%).
  5. Profit is the difference: 40,640,000 − 29,880,000 = 10,760,000 VND, a 26.5% margin — visible the moment the table refreshes.

Two habits make this stick. First, keep the raw list clean: one row per transaction, no merged cells, dates in a real date format. Second, right-click the PivotTable and hit Refresh after adding new rows — the report rebuilds itself, no formula edits needed.

The whole point: the report is a view of your data, not a second copy of it. One list in, one report out, every month.

Disclosure: this post was drafted with AI assistance and reviewed by the author.

Top comments (0)