Excel Timeline (Table of Contents)
Introduction to Timeline in Excel
- A timeline feature in Excel is specifically designed to filter data based on time. This feature doesn’t work at times.
- An excel timeline is basically used with Pivot tables to filter data based on dates. While working with Pivot Tables, you must have observed many times that with the default filters in the Pivot table, it’s very difficult to filter the data in months, quarters etc.
- The timeline filter provides you the best solution for this. This feature is only useful when a pivot table has a file that is formatted as a date.
How to Create a Timeline in Excel?
For adding the timeline feature in Excel, you must have a pivot table first. Let’s understand the working of creating timelines in excel with some examples.
Excel Timeline – Example #1
We have given a list of a number of products sold region wise and month wise.
Follow the below steps to do this:
- First, create a Pivot table for the given data.
- Click on any cell in the table and go to the Insert tab.
- Click on the Pivot Table option under the Tables section.
- Now the Pivot table is created as shown in below screenshot.
- Click anywhere in the Pivot table and go to Insert tab.
- Click on Timeline filter under the Filters section. Refer below screenshot.
- It will open an Insert Timelines dialog box. Refer below screenshot.
- Click on Month checkbox inside the dialog box and click on OK.
- It will show you the timeline and by default, it’s showing the month’s data year wise. Refer below screenshot.
- Now with the help of this excel timeline, you can arrange the records in the Pivot table by adjusting and clicking. Refer below screenshot.
- In the above screenshot as we can see that we have clicked on Feb month under the 2016 year it is displaying the data accordingly in the Pivot Table.
- If you want to see the data for a continuous month then you need to drag the bar across those months like below screenshot. We have selected the data for May & June 2016 month.
- We also can see the data day, quarter and year wise.
- For this click on the drop-down arrow on timeline box as shown in the below screenshot.
- Choose another date component like Quarters.
- It will show the quarter wise data like below:
- If you want to remove the timeline from the data, then click on the icon on the upper right corner like shown in the below screenshot. You also can use shortcut key ALT+C for this.
You also can customize an excel timeline according to your preference. Follow the below steps:
- Click on the Timeline window. Go to the Options tab.
- Click on any color option under Timeline Styles section as shown in the below screenshot.
- You also can change the size of the timeline window.
- You can change the name of the timeline window by clicking on Timeline Captions under the Timeline section.
Excel Timeline – Example #2
Let’s take another example of a grocery shop.
- Now we will create a table for the above data. Here, we are inserting the pivot table in the same sheet with the data set.
- Now repeat the same steps of example 1 for creating timelines here. It will show you the timeline. Refer below screenshot:
As we can see that we have clicked for 6 months result (from Jan – June) of the year 2017.
Hence, you can select the months accordingly by clicking on this timeline bar.
Things to Remember About Timeline in Excel
- If your Pivot Table doesn’t have a field formatted as a Date, Excel displays an error.
- An excel timeline is useful for filtering the data in the Pivot table very quickly.
This is a guide to Timeline in Excel. Here we discuss how to create a timeline in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
- Introduction to Excel Pivot Table
- Learn about Advanced Filter in Excel
- Using Excel’s Auto Format function
- Tips on using Advanced Filter
All in One Excel VBA Bundle (120+ Courses, 30+ Projects)
120+ Online Courses