Timeline is conceptually similar to a Slicer, but Timeline is specially designed to simplify time-based filtering in a pivot table. You will use the Timeline feature only when your pivot table has a field formatted as a date. This feature does not work with time. To add a Timeline to your worksheet, select a cell in a pivot table and choose Insert ➪ Filter ➪ Timeline. A dialog box will appear listing all date-based fields. Excel will display an error if your pivot table doesn’t have a field formatted as a date.
What Is Pivot Table Timeline?
The Pivot table timeline is an interactive filter that provides you with a platform to filter pivot tables. Using these timelines, you can easily see the pivot table for the specific time period. This timeline is quite similar to a slicer where you can insert a timeline and keep this with a pivot table for a longer period of time. The timeline was first placed in Excel 2013 version.
What Is Timeline Slicer in Pivot Table?
The timeline slicer in the pivot table helps to filter the pivot table in terms of date. If you don’t have the date in the pivot table, then you will get an error. In the PivotTable Analyze tab, you will see an option called a slicer. Here, you can filter the pivot table by using different fields. The main difference between a timeline slicer and a slicer is that in a timeline slicer, you have to have dates in the pivot table. Then, the timeline slicer will filter the pivot table based on dates like years, quarters, months, and days format. In the following timeline slicer, the pivot table is filtered quarterly in the timeline slicer.
Insert Timeline with Slicer to Filter Pivot Table: Step-by-Step Procedure
To insert the timeline with a slicer to filter the pivot table, we have shown a step-by-step procedure to do the job. Here, first, we need to create a table with the dataset. Then, convert the table into the pivot table. After that, we need to insert the timeline slicer to filter the pivot table.
Step 1: Convert Dataset into Table
We would like to create a table with the dataset in this step. You can directly convert the dataset into a pivot table. But if you have a large dataset, then it is quite tedious to select all of them. So, it is useful to convert the dataset into a table and then do other work.
- First, select the entire dataset to create a table.
- Then, go to the Insert tab on the ribbon.
- Select Table from Tables group.
- Then, the Create Table dialog box will appear.
- As you select your dataset to convert into a table, the range will appear there.
- Check on My table has headers.
- Finally, click on OK.
- As a result, you will see your dataset is converted into a table. See the screenshot.
Step 2: Convert Table into Pivot Table
In the next step, we would like to convert the table into a pivot table. To do this, you need to select the table and convert it into a pivot table. Follow the steps carefully.
- First, go to the Insert tab on the ribbon.
- Then, select PivotTable from the Tables group.
- As a result, the PivotTable from table or range dialog box will appear.
- Then, select Table1 in the Table/Range section.
- After that, select New Worksheet to place the Pivot Table.
- Finally, click on OK.
- Then, the Pivot Table Fields dialog box will appear.
- After that, select Customer, Product, and Total options from there.
- Then, drag the Customer option in the Column section, the Product option in the Rows section, and the Sum of Total in the Values section.
- Finally, we will get the following pivot table. See the screenshot.
Step 3: Insert Timeline Slicer to Filter Pivot Table
After converting the table into a pivot table, we need to turn our attention to inserting a timeline slicer to filter. The timelines slicer in the pivot table helps to filter the pivot table in terms of date. Follow the steps to do it.
- First, click on the pivot table.
- It will open up the Pivot Table Analyze tab on the ribbon.
- Then, go to the Pivot Table Analyze tab on the ribbon.
- Select Insert Timeline from the Filter group.
- Then, the Insert Timelines dialog box will appear.
- Check on the Order Date.
- After that, click on OK.
- As a result, we will get the following pivot table timelines slicer.
- In this timeline, we will get the required date range from the year 2009 to 2012.
- By default, Excel set the timeline on a monthly basis.
- Then, change the date range from a monthly basis to quarters by clicking the down arrow sign.
- Then, if you want to see the pivot table for the year 2009, you need to select all four quarters of 2009.
- After that, you will see the filter values of 2009 in the pivot table. See the screenshot.
How to Clear Filter from Timeline in Excel
As we used the filter to get the values of the year 2009 in the pivot table, we can also clear this filter and go back to the initial stage. To do this, you need to click on the Clear Filter option at the top right corner of the timeline.
As a result, it will go back to its initial stage where it shows all the values from the year 2009 to 2012.
How to Remove Pivot Table Timeline in Excel
After using the timeline slicer in the pivot table, we may think there is no need to have it now. In that case, you can remove the timeline from your Excel. To do that, right-click on the timeline. As a result, a Context Menu will appear. From there, select Remove Timeline. As a result, it will remove the timeline from the Excel.
Things to Remember
- Before inserting the Timeline, you need to ensure whether there is any date column in the dataset. Otherwise, it returns an error. Because the timeline only takes value when there is a field formatted as a date.
- The timeline is not available before the 2013 Excel edition.
Download Practice Workbook
Download the practice workbook below.
To Insert the pivot table timeline in Excel, we have shown a step-by-step procedure through which you can have complete knowledge about it. In this article, we also discussed how to clear the filter from the timelines and how to remove the timelines after filtering the pivot table. I hope we covered all possible areas regarding the topic. If you have further questions, feel free to ask in the comment box.