Timelines provide viewers with an easy way to quickly and meaningfully filter Microsoft Excel data.
Microsoft Excels timeline object is a dynamic filtering option that filters pivot tables and pivot charts by date/time values. They’re like slicers, in that the timeline filters data, but filters specifically by date and time elements.
For example, you can use a timeline so that users can filter a sales chart by quarters, years, months, and even days. You can even control more than one pivot chart with the same timeline. In this article, I’ll show you how to use a timeline to filter two different pivot charts based on the same data. You can download the demo file†
I use Microsoft 365 desktop on a Windows 10 64-bit system, but you can use earlier versions up to and including Excel 2013. Excel for the web supports an existing timeline and filters it as expected. However, you cannot create the pivot chart online.
Insert the pivot tables
The table named Sales, displayed in Image A, tracks sales for five regions. Let’s assume we want to build a quick dashboard to track and compare sales over the year 2021. To do this, we create pivot charts and then connect the same timeline to both.
Image A
We base two pivot charts on this sheet. Each presents a different view of the data: one evaluates the data as a whole, while the second evaluates sales by region. To create the first pivot chart, click anywhere in the data and do the following:
- Click the Insert tab, and then click PivotChart in the Charts group. Choose PivotChart from the resulting drop-down list.
- In the resulting dialog box, click OK without making any changes. Excel inserts a blank frame for a PivotChart and a PivotTable in a new sheet.
- If the PivotChart Field List is not visible, right-click the chart and choose Show Field List from the submenu.
- In the list of fields, 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 pivot table, but the terminology focuses on the diagram.
Figure B
Figure C shows the resulting graph. I’ve tweaked it a bit by removing the title and hiding the field buttons. This is not necessary for this technique to work.
Figure C
This process may seem a little different if you have created the PivotTable first and then the PivotChart in the past. Excel now builds the table as you create the chart – for you. The pivot table and pivot chart are in the PT1 sheet in the .xlsx demonstration file.
Repeat the steps above to create a second pivot chart. Using Figure D define as a guideline the graph displayed in Figure E† This second chart is more specific; it shows sales by region. In the demonstration file, this pivot chart and pivot table are in the PT2 sheet.
Figure D
Figure E
Copy and paste both charts onto a blank sheet, which we will use to display a dashboard. With both charts on the same sheet, it’s time to insert a timeline that filters them both.
Insert a timeline in Microsoft Excel
Right now we have two pivot charts. They show a few months of sales. The second shows sales by region. To add a timeline, select either chart and then click the Contextual PivotChart Analysis tab. In the Filter group, click Insert Timeline. In the resulting dialog box, check the Date field — it is the only Date/Time field available in the source data and click OK.
Initially, the timeline represents only the selected chart and by default is the year 2021. As you can see in Figure FI increased the width quite a bit.
Figure F
Right-click on the timeline and choose Report Connections. Currently there are two pivot tables to connect to – check them both as shown in Figure G†
Figure G
You’re done! It is 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 once, choose a time element from the drop-down list in the top right corner. figure H shows quarters and I just clicked the Q1 button.
figure H
The large image shows individual sales for the first quarter. You can easily tell that January was a good month. The smaller chart shows that the Central region had the most sales in the first quarter. When you click the Q4 button, both charts are blank because there are no records for that quarter yet.
Let’s try another one. Choose Months from the date drop-down, then select March through May. As you can see in figure IMarch is definitely the best month, and again, the Central region is responsible for the bulk of the revenue for this three-month period.
figure I
Continue to change the date element and click specific buttons to see 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.
