Lesson 7 of 8 · 7 min · Pivot tables and charts
Slicers, timelines and pivot charts
What you will learn
- Add slicers and timelines to filter pivots with a click
- Connect one slicer to several pivots
- Create and format pivot charts
- Pick a sensible chart type for each question
- Assemble a small interactive dashboard
In simple words
Pivot tables are powerful but filtering them with drop-down lists is slow for someone who just wants to look at the numbers. Slicers are big friendly buttons: click "Hyderabad" and every table and chart on the page changes to show Hyderabad. A pivot chart is a chart that is wired to a pivot table, so it redraws whenever the pivot changes.
Words to know
- Slicer: a set of buttons that filter one or more pivot tables.
- Timeline: a slider that filters a pivot by date range.
- Pivot chart: a chart based on a pivot table, which changes with it.
- Dashboard: one sheet that shows the key numbers and charts together.
Slicers
Click inside a pivot, then PivotTable Analyze > Insert Slicer, and tick the fields you want, such as City and Product. Each becomes a panel of buttons.
- Click a button to filter; Ctrl+click (or the multi-select button) to choose several; the small clear-filter icon at the top right resets.
- Dark buttons are selected; light grey ones have no data for the current selection.
- Use Slicer tab settings to set the number of columns, button size and style, and to arrange them neatly.
A slicer is attached to the pivot you inserted it from. To make one slicer control several pivots on the same page, right-click the slicer, choose Report Connections (or PivotTable Connections) and tick every pivot that should respond. All pivots must share the same data source (the same cache): create the second pivot by copying the first or by choosing the same Table, not by importing again.
Timelines
Click inside a pivot with a date field, choose Insert Timeline, and pick the date field. A timeline shows a scrolling bar of months, and you can switch the level to Days, Months, Quarters or Years. Drag the ends of the selected range to filter. It is the quickest way to answer "what about only the last quarter?". Timelines need a real date field with no blank or text values.
Pivot charts
Click inside a pivot and choose PivotTable Analyze > PivotChart (or Insert > PivotChart). The chart has the same fields: Axis (Categories), Legend (Series), Values and Filters. Filtering the chart filters the pivot and the reverse. Slicers control both.
Choose the chart for the question:
| Question | Chart |
|---|---|
| Compare categories | clustered column or bar (bars when the labels are long) |
| Change over time | line (or columns for a few periods) |
| Parts of a whole | stacked column or a pie with only a few slices |
| Distribution | column chart of grouped bins |
| Two measures with different scales | combo chart with a secondary axis, used carefully |
Format for reading, not decoration:
- Delete gridlines and legends you do not need, and write a clear title that states the point ("Hyderabad led sales every month").
- Sort bars from largest to smallest unless the order is natural (months).
- Start the vertical axis of a column chart at zero.
- Keep colours few; highlight the one series that matters.
- Hide the field buttons on the chart (PivotChart Analyze > Field Buttons > Hide All) for a cleaner look.
A one-page dashboard
- Make three pivots from the same Table: sales by month, sales by city, sales by product.
- Create a pivot chart for each: a line for month, a bar for city, a column for product.
- Put the three charts and the slicers (City, Product, Status) and a timeline on a new sheet called
Dashboard. - Connect every slicer and the timeline to all three pivots with Report Connections.
- Add a few big numbers at the top, such as total sales (
=GETPIVOTDATA(...)or aSUMover a Table) and the number of orders. - Hide the sheets that hold the pivots, or move them to the right, and protect the layout.
- Test: click each slicer and confirm that every chart changes, and compare a total with
=SUM(Sales[Amount])to check nothing is dropped.
Before sharing, Refresh All so the numbers are current, and consider saving as a PDF if the audience does not need to interact.
Common mistakes
- Slicers connected to only one pivot, so the dashboard shows mixed filters.
- Pivots built from different copies of the data, so Report Connections cannot link them.
- Too many slicers and charts, hiding the message.
- Pie charts with ten slices or 3-D charts that distort size.
- Forgetting to refresh before sending.
Try it yourself
- Add slicers for City and Status to a pivot and click through them.
- Insert a timeline and filter to one quarter.
- Create a pivot chart, give it a title that states the finding, and tidy the axis and colours.
- Build the three-chart dashboard and connect the slicers to all of them.
Check yourself
How do you make one slicer filter several pivot tables?
Right-click the slicer, choose Report Connections, and tick each pivot; they must share the same data source.
What is a timeline?
A date slider that filters a pivot by range, switchable between days, months, quarters and years.
What happens if you filter a pivot chart?
The linked pivot table filters the same way, because they share the same data.
Why start a column chart's vertical axis at zero?
A truncated axis exaggerates differences between bars and misleads readers.
Interview questions
How would you build an interactive sales dashboard in Excel?
Keep clean data in a Table, build pivots and pivot charts from it, add slicers and a timeline connected to all pivots, lay them out on one sheet, hide the working sheets and refresh before use.
What is the difference between a normal chart and a pivot chart?
A normal chart plots fixed cell ranges. A pivot chart is tied to a pivot table and changes whenever the pivot's fields or filters change.
How do you choose between a bar chart and a pie chart?
Use bars to compare many categories or exact sizes; use a pie only for a few parts of a whole where the share is the message.
Summary
- Slicers and timelines turn pivots into click-to-filter tools; connect them to several pivots with Report Connections.
- Pivot charts follow their pivot; pick the chart that fits the question.
- Format for clarity: sorted, few colours, honest axes, a title with the point.
- A dashboard is pivots, charts, slicers and a refresh habit on one page.
Going deeper
- Use slicers to filter data: the official guide.
- Create a PivotTable timeline to filter dates: timeline setup.
- Create a PivotChart: charts from pivots.
See how much of this you can explain
A free mock interview asks you new questions on Excel for analysts for Data Analyst and scores every answer, with what to fix.