How to Create a Pivot Table Timeline in Excel?

We can use slicers with a pivot table to apply filters on the inserted pivot table. A pivot table timeline is also an important tool using which we can apply a type of filter to it.

Let us see how this works.

What is a Pivot Table Timeline?

The pivot table timeline lets us filter the pivot table based on the dates. We can filter the data lying in between a certain period of time. This period can be in days, months, quarters, or years based on your choice.

example of pivot table timeline in excel

How to Add a Pivot Table Timeline in Excel?

A Pivot table timeline can be added to a pivot table that has at least one field in date format to apply the timeline on. Let us suppose we have the

Let us suppose we have a pivot table containing sales for different types of food items for years 2017,2018,2019, and 2020 years as follows:-

example of pivot table timeline in excel raw data

To add a timeline to this pivot based on the date on which the order for the food item was placed, we need to follow the below-mentioned steps.

  • Click on any cell in the pivot table.
  • Go to the Pivot Table Analyze tab on the ribbon.
  • In the Filter Group, click on the Insert Timeline Button.
example of pivot table timeline in excel step 2
  • Choose the field name from the list of fields based on which you want to insert the timeline. Only the field having date format would be shown here.
example of pivot table timeline in excel step 3

This is going to insert a default timeline in the worksheet containing the pivot table.

example of pivot table timeline in excel result
infographics pivot table timeline

Working with Timeline

We have got a timeline inserted in the worksheet. Now we will learn about using a timeline.

Filtering Data for a Period of Years

We can find the sales of pizza from 2017 to 2018 and set the time period in the timeline.

example of pivot table timeline in excel step 4

This would automatically filter the pivot table to show the sales of orders placed in the years 2017 and 2018.

example of pivot table timeline in excel step 5

The top left corner displays the period for which data is filtered. Here it is 2017-2018. We can also filter the data based on months, quarters, or days.

example of pivot table timeline in excel step 6

If we choose months, then we would be able to filter the data based on months.

example of pivot table timeline in excel step 7

However, you can use the scrollbar at the bottom of the timeline to scroll to the months on the left or right. Since we filtered the data for the years 2017 and 2018 ( from the yearly timeline ), the monthly timeline would appear with the filter applied for 2017 and 2018.

example of pivot table timeline in excel step 8

Removing the Filter from Pivot Table

You can remove the filter applied by the timeline using the clear filter button at the top of the timeline.

clear filter button on timeline

Moreover, you can use the shortcut to set the timeline to default is the Alt + C key. This is also going to remove the filter on the pivot table.

Formatting a Pivot Table Timeline

We can customize the appearance of a timeline in the following way.

  • Select the timeline and go to the Timeline tab on the Pivot table.
  • In the Styles group, you can alter the Pivot table appearance.
customizing the Pivot table timeline
  • Thereafter, you can also use the following options in Show Group to either show or hide the Timeline components.
hiding the pivot table timeline components

Unchecking the checkbox would hide the respective timeline component.

components of pivot table timeline

Deleting the Pivot Table Timeline

To delete the timeline:-

  • Click on the Clear Filter Button on Timeline.
  • Select the timeline and press the delete key.

This brings us to the end of the blog.

Thank you for reading.

Leave a Comment