Enhance your Microsoft Excel Pivottables with a Timeline

From previous article - slicer

In the past three articles in this series, we created a Pivot Table based on file extension and date information, added a Pivot Chart and a slicer. Slicers can be very useful to filter the presentation and focus the charts and tables. An example of a slicer is on the right.

However, If this approach to interactivity looks a little…well, wordy, then there is an additional way. But this is only available to those who have Excel 2013, 2016 and Office 365 only. If you have Office 2010 or earlier, then you might still be interested to see what you can get when you next upgrade.


Insert Timelines dialog box

In addition to slicers, the latest version of Excel introduce Timelines to PivotTables and PivotCharts. As before, go to PivotTable Tools (or PivotChart Tools), and then go to the Analyse tab and the Filter heading, but this click on Insert Timeline.

A new dialog appears. Unlike the previous one for Slicers, this Timeline dialog box is tailored for date fields only. I’ll select the Date field and click OK.

Timeline unfiltered

Now the timeline sub-window appears. While it is not immediately obvious, this contains all the dates in my Pivot Chart. The clue is in the scrollbar underneath; it’s only showing the right-hand most part of the timeline.

So what can new window do? It allows the user to restrict the date range of the PivotTable and PivotChart, by clicking on the relevant range. For instance, pressing the mouse button down on August 2009 and holding it down while moving across to January 2010 gives the following:

Timeline filtered with PivotTable and PivotChart

Now both the PivotTable and PivotChart reflect this reduced range.

Note that the top-right hand corner of this timeline, underneath the “remove all filters” icon, says “Months” with a drop-down box. This is known as the timeline’s granularity; in other words, if it says “Years”, you can’t focus on quarters or months (a more refined period).

This can be both good and bad; if you wanted to click on an entire year, you need one mouse click to do that when it shows “Years”.

However, if you use a quarter granularity, you can still filter on a complete year as four quarters, but it’s more designed to drill down to three-month periods.

Timeline filtered with PivotTable and PivotChart, by quarter

It may seem from these four articles that Pivot Tables, Pivot Charts and interactivity takes a lot of work; however, if you have your data already set up in a table-like form, then setting up a Pivot Table only takes half a dozen clicks, a Pivot Chart a couple more, and timelines and slicers a few more again. You can impress your colleagues and clients with not more than two dozen clicks.

What features would you like to see in future articles? Please let me know in the comments below.

Did you like this article? If so, why not master PivotTables with 5 HOURS of video – click here for more details.

Other articles are contained here.

Leave a Reply

Your email address will not be published. Required fields are marked *