Pivot Table Timeline

HomePivot TablePivot Table Timeline

Timeline feature was first introduced in Excel 2013 and the good news is you can use it with a pivot table as well. It can be a game changer for you to filter dates in a pivot table.

Because the problem with default filter option is that it’s very hard to filter with months, quarters and years if you have simple dates.

But with a timeline filter is you don’t need to open the filter, again and again, you just do it with a single click. That’s why Microsoft has introduced the timeline filter option.

Replace Normal Filter With Pivot Table Timeline

A timeline is just like a slicer, the difference is that the timeline only filters dates. So today in this post, I’d like to share with you simple steps to add a timeline filter to filter dates in a pivot table.

NOTE: Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.

Steps to Insert Timeline in Pivot Table

sample file

  1. Select any of the cells from your pivot table.
    nt/uploads/2016/05/select-pivot-table-to-add-pivot-table-timeline
  2. Go to Pivot Table Tools → Analyze → Filter → Insert Timeline.
    nt/uploads/2016/05/add-pivot-table-timeline
  3. Click on insert timeline and you’ll get a pop-up box.
  4. From the pop-up window, select the date columns which you have in your data.
  5. Click OK.
    nt/uploads/2016/05/select-date-to-insert-pivot-table-timeline

Now you have a timeline filter in your worksheet and you can filter your pivot table with this.

Pivot Table Timeline

Related: Excel Slicer

Components of a Timeline Filter

Following are some major components you can use to master timelines.

Component Pivot Table Timeline
  1. Clear Filters: You can use this button to clear all filters.
  2. Time Level: From the time level option you can use four different time levels. You have days, months, quarters, and years to use as time level.
  3. Timeline: Use the timeline to filter dates for the period you want. You can filter dates for days, months, quarters, and years. You also select more than a day, month, quarter & year but only in sequence.
  4. Scrollbar: Use the scrollbar to get to scroll through the days, months, quarters, and years.

Customizing a Timeline

You also have options to customize your timeline. You can make following changes in it.

1. Change Size

You can change the size of a pivot table timeline by using following steps.

  • Select your timeline.
  • Go To Timeline Tools → Options → Size.
  • Change height and width of your timeline.
Change Size Pivot Table Timeline

And another best way is to resize a timeline from its borders.

2. Change Caption Name

By default, it will show you the caption name same as your column name which you have selected to insert a timeline. But you can change it anytime by using following steps.

  • Select your timeline.
  • Go To Timeline Tools → Options → Timeline → Timeline Caption.
  • Enter new name.
  • Hit enter.
Change Caption Name Pivot Table Timeline

3. Apply Timeline Styles

Yes, you can use different styles to your timeline in the pivot table. There are total 12 different theme styles you can use for pivot table timeline.

Different Styles To Use In Pivot Table Timeline

You can also create a customized theme for your timeline by creating a new Timeline style.

Create A New Style For PivotTable Timeline

You have following options to customize in your timeline.

Customize New Pivot Table Timeline Style

4. Link a Timeline with more than one Pivot Table

Like a slicer, you can also link a timeline with more than one pivot table. Just follow these simple steps to do that.

  1. Select your timeline and right click on it.
  2. Now, click on Report Connections.
    nt/uploads/2016/05/click-report-connections-tolink-pivot-table-timeline
  3. Now, you’ll get a pop window with the list of pivot tables.
  4. Tick mark the pivot tables which you want to link with the timeline.
    nt/uploads/2016/05/tick-mark-all-pivot-table-to-link-pivot-tables-timeline
  5. And, click OK.

More on Pivot Tables

  1. Connect Slicers to Multiple Pivot Tables
  2. Create a Pivot Table with Multiple Workbooks
  3. How to Ranks in a Pivot Table in Excel
  4. Add Running Total in Pivot Table
  5. Automatically Update a Pivot Table Range

11 thoughts

Leave a Comment

Your email address will not be published.

  1. Hi,
    very interesting, however, the data doesnt appear in the downloaded file so it’s not possible to follow the steps, rgds, david

    Reply
  2. Hello Puneet:
    I find the How to use Pivot Table Timeline in Excel very interesting. However, I would like to recreate the Pivot Table from scratch using the source data. Is it at all possible to send me the source data worksheet? I would be very grateful.
    Thanks.

    Reply
  3. Hi Puneet,

    Update to previous Time Line post.

    I have been using TimeLines for nearly 3 years in an expanding dataset. (Date/Event/Other causal events associated with the entry.) The problem I have is that the TimeLine regularly disappears, and I will have to reinstate it. One of my datasets now has 60 Connections. The only event that appears to trigger it (but not always!) is a blank line at the bottom of the table. This also will ungroup pivot tables that have dates in them. Otherwise, the Time Line is a fantastic addition, and superb for this particular task.

    Do you have any experience of a disappearing TimeLine?

    Reply
  4. I must be missing something. I reconstructed your pivot table but when I clicked on “Insert Timeline” I got the message, “We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.” But neither does yours best I can tell. I even entered dates for the months in the column headers but it still didn’t work. So what’s the secret?

    Reply
  5. Timeline Use,

    Hi Puneet, I use a Timeline to control 32 pivot tables each with its own graph. very useful and impressive. The only snag is that it keeps disappearing every now and again so I have to keep re-inserting it.

    SteveT

    Reply
  6. I already use timelines with pilot tables, but this lesson learned me somre new tricks, thank you!

    Reply