Using Timeline Slicers in Excel 2013 Pivot Tables

More notes from “The Office Guy”…

Excel has always had a lot of robust tools that allow users to analyze their data. Among the most popular tools is the ability to create and work with data in Pivot tables. This tool has been a part of Excel for a long time and like many other features in Excel it has been improved with each subsequent release.

So, this blog is about a feature that was added to Pivot Table tools in the 2013 version – Timeline Slicers.

First, let’s back up a bit to the 2010 version, where Slicers were first introduced. So, what is a slicer? Simply put, it is a tool used to filter data that is more visually oriented and easier to use than the traditional data filters that have been a part of Pivot Tables since day one. These filters were based on menus that had to be accessed from drop down menus as illustrated below:

So, after the user had selected the drop down menu, the check boxes in the filter menu could be used to narrow down which data values were then displayed in the Pivot table. While these were easy enough to use, it wasn’t obvious what criteria was being used after the filter was applied.

So, in 2010 Slicers were introduced.

After you assembled a Pivot Table in the 2010 version, you could then add Slicers that made it easier and more intuitive to filter data. All you had to do was click the Insert Slicer command from the Pivot Table Tools/Analyze Tab, select the fields you wished to filter as illustrated below:

Once the slicer was visible, all you needed to do was click the various buttons in the slicer to only display the values for the categories desired. The buttons that represented the chosen categories would then remain colored to make it easy to understand which categories of data were visible as illustrated below:

Needless to say, Slicers have become quite popular because of their ease of use and visibility.

So, what are Timeline slicers? They are slicers that allow a user to quickly filter dates based on a range. This is a big advantage, because in many data sources that Pivot Tables are created from, there is a lot of variability in date related fields. In a field where there is not a lot of variability, there will only be a few buttons to choose from in order to filter down to the desired categories. But if the data source has several hundreds or thousands of rows, there could be several hundreds or thousands of individual dates that would be visible in a slicer as separate buttons. So, let’s look at an example:

In the source data, there are 1,000 rows of information about sales data that spans an entire year:

When this date field is put into a Pivot Table, the resulting table will have several hundred columns of dates – one for each unique value in the data set:

If the user then wanted to view specific Months or Quarters of data, a regular slicer would require that each unique date button for the period was selected. This would be cumbersome and impractical. So, enter the Timeline Slicer. After the desired Pivot Table is created, click the Insert Timeline button on the Pivot Table Tools/Analyze Tab, and select any date related field to display the Timeline slicer:

Once the Timeline Slicer is displayed, you can then choose what interval you want to aggregate the dates by, in this example, you want to view data at the Quarterly interval:

So, if you now wanted to view data for dates in a particular Quarter, all you’d need to do is move along the Timeline in the slicer and click on the Quarter you wished to display, and you would only see the data from the desired Quarter, and the slicer would show graphically which Quarter of data you were viewing:

For those of you who use Pivot Tables that contain lots of date related data, this will make your life much easier! Learn more about Pivot Tables in Directions’ Excel 2013 Advanced course.