Skip to content
Tutorials Center
My learning

Topics

Lesson 63 of 93 ยท PivotTables and Data Analysis

Slicers, Timelines and PivotCharts

Filter PivotTables with slicers and timelines, connect one slicer to several reports, build PivotCharts and tune layout options, with a pandas stand-in.

Filters in the Filters area work, but they hide their state behind a dropdown. Slicers and timelines are visual filters: big buttons or a date bar that show at a glance what is selected. A PivotChart turns the pivot into a chart that follows every filter and rearrangement. Together they make a report people can use without knowing PivotTables.

Excel cannot run on this site, so the steps are text and screens are described in words. The TryIts use pandas filtering and grouping as a stand-in for what a slicer does to the data; they are not Excel's implementation. New to pivots? Read PivotTable Basics first.

Visual filters

Slicer Buttons for any field.
Timeline A bar for dates.
Connections One filter, many pivots.
1 / 2

Slicers

Click inside a PivotTable and choose PivotTable Analyze > Insert Slicer. Tick one or more fields, such as Region and Category, and press OK. Each field gets a floating box of buttons.

  • Click a button to show only that item. Ctrl+click adds items; Shift+click selects a range; drag across buttons to select several.
  • The funnel icon with a red cross (top right) clears the filter.
  • Items with no data in the current selection turn grey, so you see what the other filters leave out.
  • Slicers are objects on the sheet: move and resize them, and use the Slicer tab on the ribbon to change style, the Columns count (a long list fits better in several columns), button size and Slicer Settings such as sorting and hiding items without data.

A slicer is not limited to the field on the screen: you can slice by a field that is not in Rows, Columns or Values at all, as long as it exists in the data source. Slicers also work on Excel Tables (Table Design > Insert Slicer).

Timelines

A timeline is a slicer made for dates. Choose PivotTable Analyze > Insert Timeline and tick a date field. It needs a real date column (no text dates, no blanks). Use the dropdown at the top right to switch between Years, Quarters, Months and Days, and click or drag the bar to pick a range. Pick a range of months and the pivot shows only those months, without any grouping on your side. Shift-drag the handles to extend or shrink the range.

Report connections

By default a slicer filters the pivot you inserted it from. To control several pivots, right-click the slicer > Report Connections (or Slicer > Report Connections), and tick each PivotTable. A pivot appears in the list only if it shares a pivot cache, which means you created it from the same source. The easiest way to guarantee that is to copy and paste a pivot instead of rebuilding it from scratch, or create each pivot with the same Table as source and Excel will reuse the cache in most cases. If the connection list is empty, the pivots probably have separate caches; rebuild them with a copy of the first one.

A dashboard with one Year timeline and one Region slicer, wired to five pivots and five charts, is the classic use. See Building an Interactive Dashboard.

Run it: a slicer is a filter on the source

When you click "North" in a slicer, the pivot is recalculated from only the matching rows. Several pivots connected to the same slicer all filter by the same rows.

Python One slicer driving two pivots

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

PivotCharts

Select a cell in the pivot and choose PivotTable Analyze > PivotChart (or Insert > PivotChart). Pick a chart type; the chart appears with field buttons on it. Each button is a dropdown filter for that field. Rules:

  • Changes are two-way. Moving a field in the pane changes the chart, and changing the chart's filters changes the pivot.
  • The chart shows what the pivot shows. Hide a row in the pivot and it disappears from the chart.
  • Some chart types are not available, such as XY scatter, bubble and stock charts. For those, copy the numbers or use GETPIVOTDATA formulas as the data source.
  • Formatting is lost after some changes, for instance when the pivot layout shifts. Keep Preserve cell formatting on update and, in the chart's Format Data Series pane, avoid colors for single points; style the whole series.
  • Pick the right chart. Clustered column or bar compares categories; line shows time; stacked column shows parts of a whole. See Choosing the Right Chart Type.

To clean up for a dashboard, right-click a field button > Hide All Field Buttons on Chart. Remove the legend if there is just one series, and set a clear chart title. Charts can be moved to another sheet with Cut and Paste (their link to the pivot stays).

Layout options that matter

On PivotTable Design:

  • Report Layout: Compact (default, all row fields in one column), Outline, and Tabular (each field in its own column; best when copying results or building lookups). Repeat All Item Labels fills in repeated labels, which helps when you feed the result into another formula.
  • Subtotals: show at the top or bottom of each group, or off.
  • Grand Totals: on for rows, for columns, both or neither.
  • Blank Rows: insert a blank line after each item.

In PivotTable Options (right-click > PivotTable Options): For error values show, For empty cells show (type 0), Autofit column widths on update (turn it off if widths keep resetting), Preserve cell formatting on update, and on the Data tab Refresh data when opening the file.

Sort and filter: click the arrow on row labels to Sort A to Z, More Sort Options (by a value field) and Value Filters (Top 10, greater than, ...). A label filter hides items by name. A value filter hides them by a number.

Run it: top N like a Value Filter

Python Top 3 products and a share of the whole

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main difference between a timeline and a regular slicer?

    A timeline is made for real date fields and lets you drag across a period. Slicers show buttons for the items of any field.
  2. 2. A slicer's Report Connections list does not show your second PivotTable. What is the likely cause?

    Only pivots that share a cache can be connected. Create the second pivot by copying the first one.
  3. 3. Which chart type cannot be used as a PivotChart?

    PivotCharts do not support XY scatter, bubble or stock charts.
  4. 4. How do you clear a slicer's filter?

    The Clear Filter button in the top-right corner of the slicer resets it to show all items.

Summary

  • Slicers are buttons that filter a PivotTable (or a Table) and show the filter state at a glance; Ctrl+click selects several items.
  • A timeline filters on a date field and switches between years, quarters, months and days.
  • Report Connections let one slicer control several pivots, which must share a cache.
  • A PivotChart follows its pivot in both directions, excludes scatter, bubble and stock types, and looks cleaner with field buttons hidden.
  • Tabular layout, subtotal and total settings and PivotTable Options tune the report.

Next: The Data Model, Power Pivot and DAX Overview.