Click any cell in the pivot table, go to PivotTable Analyze → Insert Timeline, tick the date field and click OK. In Excel 2013 and 2016 the path is PivotTable Tools → Analyze → Insert Timeline, and Insert → Timeline works too. Timelines need Excel 2013 or later.
Choose the period size from the drop-down on the right of the timeline: Years, Quarters, Months or Days. Click a tile to filter to that period, or drag across tiles to select a run of consecutive periods. The Clear Filter button in the top-right corner removes the filter.
To make one timeline control several pivot tables, right-click it, choose Report Connections and tick each pivot table. Only pivot tables that share the same source data appear in that list.
Change the caption, size and style from the Timeline tab that appears when you select the timeline (Timeline Tools → Options in Excel 2013 and 2016).
If Excel says We can't create a Timeline for this report because it doesn't have a field formatted as Date., the date column holds at least one text value, usually a date typed or imported as text. Changing the cell format to Date does not fix it: convert those cells to real dates, refresh the pivot table and insert the timeline again. Timelines work only with pivot tables, so use a slicer for a plain Excel table.
The Timeline feature was first introduced in Excel 2013, and the good news is that it works with pivot tables. It can be a game changer for you to filter dates in a pivot table.
Because the problem with the default filter option is that it’s very hard to filter by months, quarters and years if you have simple dates.
But with a timeline filter, 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.
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
- Select any of the cells from your pivot table.
- Go to PivotTable Analyze → Filter → Insert Timeline (in Excel 2013 and 2016, it’s PivotTable Tools → Analyze).
- Click on insert timeline and you’ll get a pop-up box.
- From the pop-up window, select the date column which you have in your data.
- Click OK.
Now you have a timeline filter in your worksheet and you can filter your pivot table with this.
Related: Excel Slicer
Components of a Timeline Filter
Following are some major components you can use to master timelines.
- Clear Filters: You can use this button to clear all filters.
- 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.
- Timeline: Use the timeline to filter dates for the period you want. You can filter dates for days, months, quarters, and years. You can also select more than one day, month, quarter or year, but only in sequence (drag across them or Shift + click).
- Scrollbar: Use the scrollbar to scroll through the days, months, quarters, and years.
Customizing a Timeline
You also have options to customize your timeline. You can make the following changes in it.
1. Change Size
You can change the size of a pivot table timeline by using the following steps.
- Select your timeline.
- Go to the Timeline tab → Size (Timeline Tools → Options → Size in Excel 2013 and 2016).
- Change the height and width of your timeline.
Another easy way is to resize a timeline by dragging its borders.
2. Change Caption Name
By default, the caption is the same as the name of the column you selected to insert the timeline. But you can change it anytime by using the following steps.
- Select your timeline.
- Go to the Timeline tab → Timeline → Timeline Caption (Timeline Tools → Options in Excel 2013 and 2016).
- Enter a new name.
- Hit enter.
3. Apply Timeline Styles
Yes, you can use different styles for your timeline in the pivot table. There are a total of 12 different built-in styles (6 light and 6 dark) you can use for the pivot table timeline.
You can also create a customized theme for your timeline by creating a new Timeline style.
You have the following options to customize in your timeline.
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.
- Select your timeline and right click on it.
- Now, click on Report Connections.
- Now, you’ll get a pop-up window with the list of pivot tables.
- Tick mark the pivot tables which you want to link with the timeline.
- And, click OK.
The Report Connections window only lists pivot tables that use the same data source (pivot cache) as the one the timeline was made for.
What You Need to Use a Timeline
- A pivot table: a timeline only works with pivot tables (and pivot charts). For an Excel table, use a slicer instead.
- A real date column: the source data needs a column with dates stored as dates. If the dates are text, Excel shows a message that the pivot table doesn’t have any fields formatted as Date.
- A new file format: Insert Timeline is greyed out in compatibility mode (an .xls file). Save the file as .xlsx first.
Remove a Timeline
To remove the date filter but keep the timeline, click the Clear Filter button at the top-right corner of the timeline. And to remove the timeline itself, select it and press the Delete key. Deleting the timeline also removes the filter it applied to the pivot table.
Frequently Asked Questions
Can a timeline filter by weeks?
No. A timeline only has four time levels: years, quarters, months and days. To filter by week, add a week number column to your data and use a slicer for it.
What is the difference between a timeline and a slicer?
A slicer filters any field by its items, while a timeline only filters a date field, but it lets you pick a period (like Q1 to Q3) by dragging. You can use both on the same pivot table.
Can one timeline control pivot tables on different sheets?
Yes, as long as those pivot tables use the same data source. Connect them with Report Connections as shown above.
Hi,
very interesting, however, the data doesnt appear in the downloaded file so it’s not possible to follow the steps, rgds, david
New feature explained very well.
Thanks
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.
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?
this is really awesome sir!!!
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?
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
Love the timeline slicer but I hate that you can’t protect the worksheet and use the time level – help rock the vote and let’s get this FIXED!!! – https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10765209-enable-the-time-level-functionality-of-the-timelin
Done
I already use timelines with pilot tables, but this lesson learned me somre new tricks, thank you!
Thanks for your words