Table of Contents
What is the Timeline in Excel?
- Timeline in excelare mainly used for date filtering in your underlying datasets which contains date field in a pivot table. This option was first introduced in Excel 2013 Version.
- When we use a normal filter in excel, it is very difficult to filter months, quarters and years. This issue is better resolved with the timeline filter in excel with a single click.
- Timeline filters in excel work only on date values & your pivot table should contain a date field.
- Click on Insert Tab to find the “timeline” tool.
How to Create Timelines in Excel?
Below is an example of how the Timeline works in Excel in Pivot Tables and Pivot Charts
In the below-mentioned table, I have a tabular data containing Date, Branch, Product Category, Customer Type & Revenue columns.
With the help of Pivot table & Chart, let create a timeline in excel where you can easily summarize & perform data analysis based on Branch, Product Category, Customer Type & Revenue data.
Step #1 – Create Table Object
Initially, Data set is converted to a table object, it can be done by below-mentioned steps
- Click inside the data set, click the Insert tab, Select Table.
- A create table popup appears, where it shows data range & headers, and click OK.
- Once the table object is created, it appears as below mentioned.
Step #2 – Pivot Table
In the end, we need to summarize revenue data for Branch, Product Category, Customer Type by timeline, Therefore, we need to create the PivotTable
To create a pivot table, below mentioned steps are followed:
- In the Tables object, click inside the data set, click the Insert tab, Select Pivot table and click ok, PivotTable Fields pane appears in another sheet. (You can name the sheet as “PIVOTTABLE_TIMELINE” )
- In the PivotTable Fields pane, drag branch to the Rows section, product category to the Columns section, and revenue to the Values section.
Step #3 – Pivot Chart
Here, I need to base a PivotChart on created pivot tables in that sheet, therefore copy that sheet “PivotChart_Timeline” or create another copy, with a “PivotChart_Timeline” as sheet name.
- In the “PivotChart_Timeline” sheet, click inside the PivotTable, under the Analyze tab in a home, select PivotChart.
- Insert chart popup window appears, in that Select column, under that select stacked column chart. Click ok.
- Pivot chart appears.
Here you can hide the product category, branch & sum of revenue in pivot chart by right click, and select hide “legend field buttons on a chart”, so that those three fields will not appear on the chart (Below mentioned screenshot)
Step #4 – Insert Timeline in Excel
You can copy this “PivotChart_Timeline” to other sheets by create a copy option, you have to select right click on a mouse, on the sheet name “PivotChart_Timeline”, below mentioned window popup appears. You can name that sheet as “Insert_Timeline”.
- To create a Timeline in excel, you can click anywhere on a dataset in your PivotTable and then select the Analyze tab on the Ribbon. Click on Insert Timeline button in the Filter group.
- Once you have click on Insert Timeline button, Insert Timeline popup window appears, it asks you to select the date field which you want in your Timeline to filter. Click on OK.
- Now the Timeline windows appear,
- For your Timeline in excel, you can configure or select group dates by years, quarters, months or days with the help of the drop-down list.
- Here I have selected year in below-mentioned e.g.Years.
Suppose I want the Timeline filtering a PivotTable to show only results from the year 2018, you can click on 2018 in timeline slicer. You will get the revenue for the year 2018 with reference to region or branch and product category.
Now let’s check out quarterly time data, it can be done by selecting, in the dropdown list, where you can change it to quarters instead of a year. If quarterly data in the timeline is not visible then you can drag blue colored box towards each end. In the below-mentioned example, I selected the 2nd quarter of 2016 to check the revenue reference to region or branch and product category.
List of Top Timeline Tools in Excel
Timelines is a special form of excel for PivotTables to filter dates. Timeline slicer in excel enables you or lets you filter or toggle between year, quarter, month, or day.
#1 – Timeline Tools on Right-side of Option Tab
#2 – Timeline Tools on Left side of Option Tab
#3 – Scroll Bar
It is one of the timeline tool options under the Show tab, which is clicky-draggy thingy to select which periods you want and also it indicates you, which periods you’ve selected. a scrollbar is used to scroll through the days, months, quarters and years.
#4 – Time Level
In time level option you can select four different time levels based on your choice. Different options in the time levels are years, quarters, months & days.
#5 – Clear Filters
This button can be used to clear all Timeline options in excel (It may be either days or months or quarters or years)
#6 – Timeline Header:
It displays the heading or title of a timeline.
#7 – Selection Label:
It indicates or displays the date range included in the filter.
#8 – Other Various Customize Options in Timeline – Timeline Window Size
You can change the size of a pivot table timeline i.e. you can adjust the height & width of your timeline, You can also resize a timeline window from its borders, by dragging it.
#9 -Timeline Caption
By default, In the timeline caption box, it will show you the caption name which you entered in the column name which you have selected to insert a timeline in excel.
#10 – Timeline Styles
You have a various option to use different styles to your timeline in the pivot table.
In the below-mentioned screenshot, you can observe 12 different types of theme styles, apart from this, you can customize with new timeline style also based on your choice.
Things To Remember About The Timeline in Excel
- In the dashboard, you have the option to combine timeline with Slicer.
- Your timeline can be configured to group dates by years, quarters, months or day.
- When compared to normal date filter, Timeline slicer is more effective and it is a better visual tool which provides a graphical representation & helps out to track critical milestones.
You can download this Create Timeline Excel Template here – Timeline Excel Template
This has been a guide to Timeline in Excel. Here we discuss how to use timeline tools in excel to create Timeline in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –