Spreadsheets

Pivot chart for monthly spending by category in Excel

Make a stacked column pivot chart of monthly spending by category in Excel, hide the field buttons, and use a pie chart for a single month.

Editorial illustration for Pivot chart for monthly spending by category in Excel

Click inside a pivot table that has Month in Rows or Columns and Category in the other, then choose PivotTable Analyze, PivotChart, and pick Stacked Column. Each month becomes a column, and each category is a coloured slice of that column. Right-click a field button and choose Hide All Field Buttons on Chart for a cleaner look.

What should the pivot look like first?

The chart copies the pivot's layout, so set it up before you add the chart. Put Month in Rows, Category in Columns and Amount in Values as Sum of Amount. If your date field has not grouped into months, right-click a date and choose Group, then Months and Years. Limit categories to a manageable list; a stack with fifteen colours is hard to read, so consider grouping small ones under Other in the source data.

How do I insert the stacked column chart?

  1. Click any cell in the pivot table.
  2. Open PivotTable Analyze and choose PivotChart, or use Insert, PivotChart.
  3. In the dialog pick Column, then Stacked Column, and press OK.
  4. The chart appears with the months along the bottom and the categories as the stacked segments.
  5. If the stacks are the wrong way round, click Design, Switch Row/Column, or drag fields between the Rows and Columns boxes.
  6. Add a title by clicking the chart title and typing over it.

How do I remove the field buttons?

The grey buttons on the chart, such as Category and Month, are handy for filtering but clutter a dashboard. Right-click any of them and choose Hide All Field Buttons on Chart. You can still filter using the pivot table itself or slicers placed beside it. To bring the buttons back, right-click the chart and choose Show Field Buttons.

Chart jobChart typePivot layout
Spend by month, split by categoryStacked columnMonth in Rows, Category in Columns
Compare categories side by sideClustered columnCategory in Rows, Month in Columns
One month's mixPieCategory in Rows, one month chosen in Filters
Total trendLineMonth in Rows, no Columns field

How do I make a pie chart for one month?

  1. Make a second pivot with Category in Rows, Amount in Values, and Month in the Filters box.
  2. Use the filter drop-down to choose one month, for example June.
  3. Choose PivotChart, then Pie.
  4. Sort the pivot by Amount, largest first, so the biggest slices come first.
  5. Show data labels using Chart Design, Add Chart Element, Data Labels, and choose percentage.

Say a hypothetical June is ₹52,000 in total. Rent of ₹18,000 gives a slice of about 34.6 percent, calculated as 18,000 divided by 52,000. A pie works for one month with up to about six categories; beyond that, a bar chart reads better.

What goes wrong with pivot charts?

  • The chart moves or disappears after you rearrange the pivot, because it is tied to the pivot layout. Fix the layout first.
  • Totals show as a giant column. Remove Grand Total rows from the pivot using Design, Grand Totals, Off for Rows and Columns.
  • New months do not appear. Refresh the pivot; the chart follows.
  • Colours change when you filter. Set series colours by right-clicking a series and choosing Format Data Series.
  • Month names appear in the wrong order if they are text. Use real dates grouped by month.

What next?

Once the chart works, place it above the pivot on a dashboard sheet and add slicers for Category and Payment mode. If you would rather not maintain the chart, SpendixAI shows month-by-month spending and category breakdowns in its dashboard and reports once statements are imported, and you can download a CSV for a chart of your own.

  • Real dates grouped by month, with years if the data spans more than a year.
  • Stacked column for the trend, pie for one month only.
  • Field buttons hidden, grand totals off.
  • Refresh after adding rows.
SPENDIXAI JOURNAL

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

Explore your reports