How to use a timeline to filter multiple PivotCharts in Microsoft Excel | TechRepublic (2024)

Microsoft Excel’s timeline object is a dynamic filter option that filters PivotTables and PivotCharts by Date/Time values. They’re like slicers, in that the timeline filters data, but it specifically filters by date and time elements.

For instance, you might use a timeline to allow users to filter a sales chart by quarters, years, months and even days. You can even control more than one PivotChart with the same timeline. In this article, I’ll show you how to use a timeline to filter two different PivotCharts based on the same data. You can download the demo file.

I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use earlier versions down to Excel 2013. Excel for the web supports an existing timeline and it will filter as expected. However, you can’t create the PivotChart online.

How to insert the PivotTables

The Table named Sales, shown in Figure A, tracks sales for five regions. Let’s suppose we want to build a quick dashboard to track and compare sales over the year 2021. To do so, we’ll create PivotCharts and then connect the same timeline to both.

Figure A

We’ll base two PivotCharts on this sheet. Each will provide a different view of the data: One will evaluate the data as a whole while the second evaluates the sales by regions. To create the first PivotChart, click anywhere inside the data and do the following:

1. Click the Insert tab and then click PivotChart in the Charts group. Choose PivotChart from the resulting dropdown.
2. In the resulting dialog, click OK without making any changes. Excel will insert a blank frame for a PivotChart and a PivotTable into a new sheet.
3. If the PivotChart Field list isn’t visible, right-click the chart and choose Show Field List from the submenu.
4. In the field list, drag Region to the Legend section, Amount to the Values section, and Date to the Axis section (Figure B). This process is similar to creating a PivotTable, but the terminology is geared toward the chart.

Figure B

Figure C shows the resulting chart. I’ve modified it a bit by removing the title and hiding the field buttons. Doing so isn’t necessary for this technique to work.

Figure C

This process might seem a bit different if in the past, you’ve built the PivotTable first and then built the PivotChart. Excel now builds the table as you create the chart — for you. The PivotTable and PivotChart are in the PT1 sheet in the demonstration .xlsx file.

Repeat the steps above to create a second PivotChart. Using Figure D as a guide, define the chart shown in Figure E. This second chart is more specific; it displays sales by region. In the demonstration file, this PivotChart and PivotTable are in the PT2 sheet.

Figure D

Figure E

Copy and paste both charts to a blank sheet, which we’ll use to represent a dashboard. With both charts on the same sheet, it’s time to insert a timeline that filters them both.

How to insert a timeline in Microsoft Excel

Right now, we have two PivotCharts. One displays a few months worth of sales. The second displays sales by the region. To add a timeline, select either chart and then click the contextual PivotChart Analyze tab. In the Filter group, click Insert Timeline. In the resulting dialog, check the Date field — it’s the only Date/Time field available in the source data and click OK.

At first, the timeline represents only the select chart and defaults to the year of 2021. As you can see in Figure F, I increased the width quite a bit.

Figure F

Right-click the timeline and choose Report Connections. Currently, there are two PivotTables to connect to — check them both as shown in Figure G.

Figure G

You’re done! It’s extremely simple and the only rule is that the charts must be based on the same data.

To use the timeline to filter both charts at the time, choose a time element from the dropdown in the top-right corner. Figure H shows quarters and I clicked only the Q1 button.

Figure H

The big-picture chart shows individual sales for the first quarter. You can easily see that January was a good month. The smaller chart shows that the Central region had the most sales in the first quarter. If you click the Q4 button, both charts are blank because there are no records yet for that quarter.

Let’s try another one. Choose Months from the date dropdown and then select March through May. As you can see in Figure I, March is definitely the best month, and again, the Central region is responsible for most of the income for this three-month period.

Figure I

Continue to change the date element and click specific buttons to watch both charts update. Using a timeline to filter multiple charts by date elements is a great way to share a lot of information about the underlying data. And you’re not limited to two charts; try creating a new chart and adding it to the dashboard.