Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 25

Interactive Filters: Slicers & Timelines

Recommended resources

Hello! Welcome back.

In our last lesson, we focused on summarizing large datasets by grouping data into categories like months, quarters, or numeric ranges. This helped us turn raw data into meaningful summaries. Today, we'll make those summaries dynamic and user-friendly.

The goal of this lesson is to learn how to use Slicers and Timelines to create interactive filters for a PivotTable. Instead of you, the analyst, having to manually filter a report for every request, you can build a report that allows your stakeholders—be it a manager or a client—to filter and explore the data themselves. This is a core skill for building modern MIS reports and dashboards in Excel.

From Static Filters to Interactive Dashboards

You've already used the "Filter" area in a PivotTable. It's functional, but it has two main drawbacks: it's not very intuitive for non-Excel users, and it's not immediately obvious what filter is currently applied.

Slicers and Timelines solve this. They are visual, clickable filters that float on your worksheet.

Excel PivotTable with Timeline and Slicer for Interactive Data Filtering
This is what we're aiming for. A clean PivotTable where anyone can easily see and change the active filters using the 'Timeline' for dates and the 'Slicer' for categories like 'Salesperson'.

1. Adding Slicers for Categorical Data

A Slicer provides a set of buttons that let you easily filter the data in a PivotTable. They are ideal for fields with text-based categories, like 'Region', 'Product Name', or, in your past work, 'Incident Status'.

Let's watch a video that gives a complete overview of what Slicers are and how to use them.

Excel Slicers, EVERYTHING You Need to Know - includes workbook with step by step instructions

This video from MyOnlineTrainingHub is a comprehensive guide to Slicers. We'll start with the basics of what they are and how to insert them.

Please watch the first three and a half minutes of the video (0:00 - 3:30). Focus on: The benefits of Slicers over traditional filters (0:00 - 2:03). The two methods for inserting a Slicer (2:03 - 3:30).

As the video showed, inserting a Slicer is straightforward:

  1. Click anywhere inside your PivotTable.
  2. Go to the PivotTable Analyze tab on the ribbon.
  3. Click Insert Slicer.
  4. In the dialog box that appears, check the box for each field you want to create a filter for.
Inserting a Slicer in Excel PivotTable
This image shows the exact moment of choosing which field to use for a Slicer. In a recruitment tracker, you might select 'Recruiter' or 'Job Status' here.

2. Adding a Timeline for Date Filtering

While you can create a Slicer for a date field, Excel provides a much better tool for this: the Timeline. It's a special type of Slicer designed specifically for filtering by date ranges. This is a natural extension of the date grouping you learned in the previous lesson.

The process is nearly identical to inserting a Slicer.

Create and share a Dashboard with Excel

This article from the official Microsoft Support site provides a clear, step-by-step guide. We'll focus on the section about adding Slicers and a Timeline.

Please read the section titled 'Add Slicers and a Timeline'. Pay close attention to the instructions for inserting a Timeline and notice how similar it is to inserting a Slicer.

A Timeline gives you an intuitive slider to select years, quarters, months, or even specific days, making it perfect for the trend analysis that is common in MIS reporting.

3. The Most Important Step: Report Connections

So far, so good. But here's the trick that turns a few separate PivotTables into a connected, interactive report. By default, a Slicer or Timeline only controls the single PivotTable you used to create it. To build a dashboard where one click filters everything, you must connect your Slicers and Timelines to all relevant PivotTables.

This is done using Report Connections.

The following video demonstrates this concept perfectly. It shows what happens when the connections are missing (only one chart updates) and how to fix it so that the whole dashboard responds to a single filter click.

📊 How to Build Excel Interactive Dashboards

In this video from Kevin Stratvert, you'll see how to bring an entire dashboard to life by connecting the filters to multiple charts and tables. This is the key to making your reports truly interactive.

Watch from 14:43 to 16:05. Observe how he right-clicks the Slicer, selects 'Report Connections', and then checks the boxes for all the other PivotTables in the dashboard. This is the single most important technique in this lesson.

To summarize the steps for connecting a Slicer or Timeline:

  1. Right-click on the Slicer/Timeline.
  2. Select Report Connections....
  3. In the dialog box, check the boxes for all the PivotTables you want this filter to control.
  4. Click OK.

It's good practice to give your PivotTables meaningful names (via PivotTable Analyze > PivotTable Name) so you can easily identify them in the Report Connections window.

4. Formatting and Professional Polish

Finally, to make your report look clean and professional, you can customize the appearance of your Slicers and Timelines. You can change colors, adjust the number of columns, and align them neatly on your worksheet.

Excel Slicers, EVERYTHING You Need to Know - includes workbook with step by step instructions

Let's revisit the first video to explore the formatting options that will make your reports look polished and professional.

Please watch these two short segments: Formatting Slicers (4:33 - 5:53): Focus on how to change styles, adjust the number of columns to create a horizontal button layout, and align multiple slicers. Slicer Settings (10:31 - 12:36): Pay attention to the option to 'Hide items with no data'. This is very useful for keeping your filters clean and relevant.

Your Turn: Build an Interactive Report

Now it's time to put all these pieces together. Please use the sample-data-for-pivot-table.xlsx file.

Task:

  1. On a new sheet, create two separate PivotTables from the source data:
    • PivotTable 1: Category in Rows, Sales (Sum) in Values.
    • PivotTable 2: Region in Rows, Sales (Sum) in Values.
  2. Click inside PivotTable 1 and insert a Slicer for the Salesperson field.
  3. Click inside PivotTable 2 and insert a Timeline for the OrderDate field.
  4. Now, connect them. Right-click your new Salesperson Slicer, go to Report Connections, and check the box for both PivotTables. Do the same for the OrderDate Timeline.
  5. Test your work! Click a salesperson's name on the Slicer. Did both PivotTables update? Now, adjust the Timeline to a single quarter. Did both tables filter correctly?
  6. (Optional) Change the Slicer to have multiple columns and a different color style to match your preference.

Completing this exercise demonstrates you can create the foundation of an interactive dashboard.

Conclusion

Excellent work! You've moved beyond static reports and learned how to give users control over the data they see. This is a fundamental shift in how you can present information and a highly valued skill for any MIS professional.

Key Takeaways:

  • Slicers are user-friendly, visual filters for categorical data (like text or categories).
  • Timelines are specialized slicers for filtering date fields, allowing users to easily select time ranges.
  • The Report Connections feature is the key to making a single Slicer or Timeline control multiple PivotTables, creating a truly interactive experience.
  • Formatting your Slicers and Timelines makes your report look more professional and easier to use.

In our next lesson, we will dive deeper into customizing PivotTables by creating Calculated Fields and Calculated Items. This will allow you to add your own formulas directly into the PivotTable, such as calculating a profit margin or a commission, further enhancing your analytical capabilities.