Spreadsheets

Build an expense dashboard in Excel with pivot charts and slicers

Build an Excel expense dashboard on one sheet: pivot charts for category, month and payment mode, shared slicers and a print-ready layout.

Editorial illustration for Build an expense dashboard in Excel with pivot charts and slicers

An Excel expense dashboard is a single sheet of pivot charts that all read from one Excel Table, with slicers that filter them together. Convert your transactions to a Table, build three pivots, turn each into a chart, then connect one set of slicers to all of them. Add a print area at the end so it fits a page.

Start with a clean Table

Keep one row per transaction with these columns: Date, Merchant, Category, Mode, Amount. Mode is UPI, Card, Cash or NetBanking, whatever you actually use. Click any cell and press Ctrl+T, tick My table has headers, and name the table tblExp on the Table Design tab. Add a dropdown list to the Category column through Data > Data Validation, because a pivot treats Food and Food with a trailing space as two separate items. A Table grows by itself when you add rows, so pivots built on it never miss new data after a refresh.

How do you build the three pivots?

Create a new sheet called Calc. Choose Insert > PivotTable, pick tblExp as the source, and place it at A3. Build the other two from the same table, or copy and paste the first pivot, so that all three share one pivot cache. That sharing is what lets one slicer control all of them.

PivotRowsValuesChart type
CategoryCategorySum of AmountBar chart, sorted largest first
MonthDate, grouped by Months and YearsSum of AmountColumn chart
Payment modeModeSum of AmountDoughnut or bar chart

If the Date field does not group on its own, right-click a date in the pivot, choose Group, and select Months and Years. Select a cell in each pivot and choose PivotTable Analyze > PivotChart, then cut each chart and paste it on a sheet named Dashboard.

How do slicers filter every chart at once?

  1. Click a chart on the Dashboard, then choose PivotTable Analyze > Insert Slicer and tick Category and Mode.
  2. Right-click a slicer and choose Report Connections, then tick all three pivots. Without this step the slicer moves only one chart.
  3. Use Insert > Timeline on the Date field to pick a month or quarter by dragging, if your Excel version offers it.
  4. Select the slicer, open the Slicer tab, and set Columns to 3 or 4 so the buttons form a neat strip.

Hold Ctrl while clicking to select several categories, and use the small clear-filter icon on the slicer to reset it.

How can you show totals that respect the slicers?

A cell such as =SUM(tblExp[Amount]) ignores slicers, so a headline total built on it would not change when you filter. Read the total from a pivot instead. Type = in a blank cell, click the Grand Total of the Category pivot, and Excel writes a GETPIVOTDATA formula, for example =GETPIVOTDATA("Amount",Calc!$A$3). That cell now follows every slicer click. Format it large and place it above the charts.

Make it print on one page

  • On the View tab, untick Gridlines so the sheet looks like a report.
  • Select the dashboard area and use Page Layout > Print Area > Set Print Area.
  • Choose Landscape, and under Scale to Fit set Width to 1 page and Height to 1 page.
  • Use Print Preview to confirm that slicers and charts do not straddle a page edge, then save as PDF if you want to share it.

A short monthly routine

After each month, paste the new rows under the Table, refresh, scan the category chart for anything odd, and save a copy named with the month. Once your statements run to years of rows and typing categories becomes the slow part, SpendixAI can import the statements and categorise them, with reports and a CSV download to keep working in Excel.

SPENDIXAI JOURNAL

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

Explore your reports