Spreadsheets

Add slicers to an expense pivot table in Excel

Add slicers to an Excel expense pivot table, connect them to two pivots and a chart with Report Connections, and build a one-sheet dashboard.

Editorial illustration for Add slicers to an expense pivot table in Excel

Click inside the pivot table, choose PivotTable Analyze, Insert Slicer, and tick Category and Payment mode. Then right-click each slicer, choose Report Connections, and tick every pivot table you want it to filter. One slicer then drives several pivots and any pivot chart built on them, which is the core of a simple expense dashboard.

What do I need before adding slicers?

  • An expense Table with columns such as Date, Category, Payment mode and Amount, formatted with Ctrl+T.
  • At least one pivot table built from that Table. For a dashboard, build two: one summarising by Category, another by Month.
  • Both pivots made from the same source, so that they can share slicers. Creating the second pivot from the same Table normally does this.
  • A clean Payment mode column with a short list of values such as UPI, Card, Cash and Net banking.

How do I insert the slicers?

  1. Click any cell inside the first pivot table.
  2. Open the PivotTable Analyze tab and choose Insert Slicer.
  3. Tick Category and Payment mode, then press OK. Two boxes of buttons appear on the sheet.
  4. Click a button to filter. Hold Ctrl or use the multi-select icon to choose several buttons at once.
  5. Use the small funnel icon at the top right of a slicer to clear its filter.
  6. Resize and place the slicers along the top of a Dashboard sheet, using the Slicer tab to set columns and colours.

How do I connect one slicer to several pivots?

A new slicer only filters the pivot it was created from. To link the others, right-click the slicer and choose Report Connections. A list of pivot tables and their sheets appears. Tick each pivot that should respond, then press OK. The same dialog is also where a pivot chart gets linked: because a pivot chart is tied to its pivot, filtering the pivot filters the chart as well.

If a pivot is missing from that list, it was built from a different data source. Delete it and create it again from the same Table, or use one data model for all of them.

Dashboard pieceBuilt fromReacts to
Category pivotCategory in Rows, Sum of AmountBoth slicers
Month pivotMonth in Rows, Sum of AmountBoth slicers
Column chartPivot chart on the Month pivotBoth slicers, through the pivot
Category slicerCategory fieldConnected to both pivots
Payment mode slicerPayment mode fieldConnected to both pivots

What does a worked example look like?

Say a hypothetical month has Groceries ₹9,400 (UPI ₹6,100, Card ₹3,300) and Dining ₹4,200 (UPI ₹1,200, Card ₹3,000). With no slicer selected, the Category pivot shows 13,600 in total. Click UPI in the Payment mode slicer and Groceries becomes 6,100, Dining 1,200, and the total 7,300. The Month pivot and chart change in step. Click the funnel icon to return to 13,600.

What can go wrong?

  • Slicer buttons do not update after you add new rows. Refresh the pivots first; slicers read from them.
  • Old items stay in the slicer. Open PivotTable Options, Data, and set the number of items to retain per field to None, then refresh.
  • A slicer filters only one pivot because Report Connections was not set.
  • Too many categories make a slicer huge. Set its Columns to 3 or 4 in the Slicer tab, or filter the list.
  • Slicers do not work in the same way in every Excel version, such as older Mac builds, so test on the device you use.

What next?

Add a third pivot for Top merchants, connect it as well, and place everything on one sheet with the data sheet hidden at the back. If building and refreshing this dashboard begins to feel like a part-time job, SpendixAI has a ready dashboard, insights and a library of reports from imported statements; use a pivot dashboard when you want a layout that is entirely your own.

SPENDIXAI JOURNAL

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

Explore your reports