Skip to content

Excel 2013 Timelines: Filter Date Fields in PivotTables

    In previous versions of Excel there have been a number of different ways of filtering on dates within a PivotTable.  In Excel 2013, Microsoft have gone and added another: Timelines.

    Timelines provide a more graphical way of filtering your PivotTable when filtering based on a date field in your underlying data.  You can choose whether your Timeline shows years, quarters or months or days – depending on the level of detail you wish to filter on.  Although Timelines provides a nice graphical interface that is more intuitive to use than a normal date filter – they have their limitations as I discuss below. Please also note that the Timeline feature will not work on a normal range or Excel Table.

    Download the Featured File

    In the example below, my PivotTable includes row and column headings for products and branch location respectively. The values show a sum of sales for each branch.

    Excel 2013 PivotTable

    In my field list, you will see there is a date field – this is necessary if you are going to use a Timeline (kind of makes sense!)

    PivotTable Field List

    Creating your Timeline

    To create a Timeline, first click somewhere in your PivotTable and then select the ANALYZE tab on the Ribbon. The Insert Timeline button is found in the Filter group.

    Insert Timeline Button in Excel

    Once you have clicked the button, Excel then asks you to select the date field that you want your Timeline to filter.  Click on OK once you have checked the relevant field. I’ve only got one date field, but I still have to make the selection.

    Choose a Date Field for a Timeline

    Your Timeline can be configured to group dates by years, quarters, months or days.  Use the drop-down list, top right of the timeline to select your required grouping as shown below. In my example I have grouped the dates by years.

    Excel 2013 Timeline

    To apply a filter via the Timeline, adjust the blue bar by dragging the end handles as shown below.

    Adjusting a Timeline

    If I group by months, I am forced to use the scroll bar at the bottom of the timeline to view all the months.  Not a major problem, but just be aware that the scroll bar is there for when you have a lot of dates.

    Scrolling a Timeline

    You can control the visibility of the scroll bar and other elements in the Timeline window up on the OPTIONS tab on the Ribbon.  If the OPTIONS tab is not visible then you need to activate it by selecting your Timeline.

    What to show on a Timeline

    If you are not happy with your Timeline’s colour theme, then you can easily change it using Timeline styles, also up on the OPTIONS tab.

    Timeline Styles

    Timelines vs Slicers

    For those of you who have used Slicers before you may be wondering if the Timeline replaces Slicers for date fields. Well not quite: although using Slicers to filter on years, quarters and months takes a little bit more set up, they are more flexible as you can select non-contiguous items.

    Apart from taking more time to setup, Slicers also take up more space on your spreadsheet, so I guess Timelines do have their advantages.

    In order to create Slicers for each grouping of the dates, you will need to first of all group the dates in the PivotTable itself.  Once the grouping has been established, a field is created for month, quarter & month (or whichever groups you created). Slicers can then be created based on these fields.  If you are not sure how this is achieved, watch the video above.

    Leave a Reply