Excel Slicers and Timelines: Filter a Dashboard in One Click

Step-by-step

Slicers and timelines are Excel’s two visual filter tools, and you’ll find them in almost every good dashboard. Slicers filter by categories (such as country or product) with a click, and a timeline filters by dates. The example dashboard already has PivotTables and PivotCharts built from the workbook’s Data Model.

1. Insert a timeline

Go to Insert > Timeline. Excel asks which data to connect to: open the Data Model tab, choose This Workbook Data Model and click Open. Tick the date field (Order Date in the example) and click OK. Move the timeline under the PivotTable it belongs with.

2. Style it and connect it to everything

With the timeline selected, pick a style from the Timeline (Options) tab. Then the important step: click Report Connections and tick every PivotTable and PivotChart you want it to control. Otherwise it filters only one of them.

Test it: switch the timeline between years, quarters and months, and drag across a range of dates. The connected reports update instantly.

3. Insert slicers

Go to Insert > Slicer, connect to the workbook Data Model the same way, and tick the fields you want to filter by. The example uses Country and Product Category. Move the slicers into place and choose a style from the Slicer (Options) tab.

4. Connect the slicers too

Don’t skip this: right-click each slicer (or use the Slicer tab), choose Report Connections and tick all the PivotTables and charts, so one click filters the whole dashboard.

5. Add chart titles and test

Add clear titles above the charts (the example uses Sales per Product Category and Sales per Quarter) and format them. Finally, click through the slicers and timeline and check that every value changes as expected.