Timeline in Excel

Published on :

21 Aug, 2024

Blog Author :

Edited by :

Reviewed by :

Timeline in Excel is a kind of SmartArt created to display the different timings of a particular process. It is mainly used for filtering the underlying datasets by date. Such datasets are in the form of pivot tables containing the date field.

The timeline was first introduced in the 2013 version of Excel.

What is the Timeline in Excel?

How to Create Timelines in Excel? (With Example)

In the following table, there are five columns, namely–Date, Branch, Product Category, Customer Type, and Revenue.

With the help of a pivot table and a pivot chart, let us create a timeline in Excel. The pivot table and pivot chart help summarize and analyze data.

Step #1 - Create table object

Initially, let us convert the data set into a table object with the help of the following steps:

Click inside the data set, go to the Insert tab, and select “table".
The “create table” popup appears which displays the data range and a checkbox for table headers. Click “Ok.”
Once the table object is created, the data appears in a tabulated form, as shown in the succeeding image.

Step #2 - Create a pivot table

Since we want to summarize the revenue data across different categories of the timeline, we create a pivot table.

The steps to create a pivot table are listed as follows:

Click on the data set within the table.
Go to the Insert tab, select “PivotTable,” and click “Ok.”

The “PivotTable Fields” pane appears in another sheet. Name this 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 - Create a pivot chart

We need to base a pivot chart on the pivot table that we have created. The steps to create a pivot chart are stated as follows:

Copy the previous sheet as “PivotChart_Timeline” or create another sheet with this name.
Click inside the pivot table on the sheet “PivotChart_Timeline.”
In the Home tab, go to “Analyze” and select “PivotChart.”

The “insert chart” popup window appears.
Select “stacked column chart” and click “Ok.”

The pivot chart appears as shown in the following image.

In the pivot chart, you can hide the “product category,” “branch,” and “sum of revenue.” To do this, right-click and select “hide legend field buttons on chart,” as shown in the succeeding screenshot.

The pivot chart appears without the legend buttons, as shown in the following image.

Step #4 - Insert a timeline in Excel

The steps to insert a timeline are mentioned as follows:

Copy the “PivotChart_Timeline” to other sheets with the “create a copy” option. The popup window shown in the succeeding image appears.
Right-click on the sheet name “PivotChart_Timeline” and name the sheet as “Insert_Timeline."

Click anywhere on the data set of the pivot table. Select the Analyze tab on the Excel ribbon and click on the “Insert Timeline” button in the Filter group.

The “insert timelines” pop-up window appears. It shows a checkbox with the date field. This is the filter of the timeline. Select the checkbox and click “Ok.”

Now, the timeline appears.

For the timeline, you can configure or select group dates by years, quarters, months or days with the help of the drop-down list.

We have selected “years” as shown in the following image.

How Does the Timeline Filter the Pivot Table?

Let us consider the previous example again.

We want the timeline to filter the pivot table with results of the year 2018. To do this, click on “2018” in the timeline slicer.

The revenue for the year 2018 with reference to the branch and product category appears.

Now, let us select “quarters” from the dropdown list. If quarterly data in the timeline is not visible, drag the blue-colored box towards the end.

Let us select the 2^nd quarter of 2016 to observe revenue across the different branches and product categories.

Top Timeline Tools in Excel

Timelines in Excel help filter the dates of the pivot tables. This is done with the help of various tools that assist in the working of the timeline.

The timeline tools appear to the right and the left of the Options tab, as shown in the following two images.

The major timeline tools are listed as follows:

#1 - Timeline slicer

The timeline slicer allows toggling between years, quarters, months, and days. In the dashboard, there is an option of combining the timeline with the slicer.

In comparison to the normal date filter, the timeline slicer is a more effective visual tool. This is because the latter provides a graphical representation that helps track critical milestones.

#2 - Scroll bar

It appears in the Options tab and helps select periods. It also allows scrolling through the years, quarters, months, and days.

#3 - Time level

This tool allows selecting from four different time levels based on choice. The four-time levels are, namely–years, quarters, months, and days.

#4 - Filter

This button helps clear all the “time” options like years, quarters, months or days.

#5 - Timeline header

It displays the heading or the title of the timeline, as shown in the following image.

#6 - Selection label

It displays the date range that is included in the filter.

#7 - Timeline window size

The height and width of the PivotTable timeline can be adjusted according to the requirement. It is also possible to resize the timeline window by dragging it from its borders.

#8 - Timeline caption

By default, the caption box shows the column name as the caption. This is the column that was selected while inserting the timeline.

#9 - Timeline style

There are various style options in Excel for the PivotTable timeline. The following screenshot shows 12 types of theme styles.

The style of the timeline can also be customized according to choice.

Frequently Asked Questions

#1 - What are slicer and timeline in Excel?

A slicer is an object that allows quick filtering of data. The slicer shows all the possible values of the column that are selected by the user. Each value appears as a button that can be used for toggling.

The slicer also displays the current filtering state, which lets the user know the exact values that are being displayed currently. A slicer can be used with a table and a pivot table.

A timeline allows the filtering of data specifically with date fields. The user can filter data by years, quarters, months, and days. The dates are displayed horizontally, beginning from the oldest to the newest, as one moves from left to right on a timeline.

A timeline can only be used with a pivot table having date fields.

#2 - How to use a timeline in Excel?

The timeline can be used with the help of the following features:

1) Timeline period–The timeline can use either a single period or multiple adjacent periods. To select a period, click on the first period and drag the cursor to the last period. Release the click to see the selected date range.
2) Date grouping–The “time level” feature allows grouping of dates in data. The dates can be grouped into years, quarters, months, and days.
3) Timeline handle or scroll bar–The scroll bar can be used to either increase or decrease the selected range of dates. This is done by dragging the scroll bar to the left or the right of the timeline range.
4) Timeline filters–Filters can be used to reset the timeline. For this, either select the filter icon or press Alt+C on the keyboard (with the timeline selected).

#3 - How to present a timeline in Excel?

The steps to present a timeline in Excel are listed as follows:

- Adjust the height and width of the timeline as per the requirement. To do this, select the timeline and go to “timeline tools,” click “options,” and select “size.”
- Enter an appropriate caption name in the “timeline caption” button.
Apply a theme to the timeline from the various “timeline styles” available.
- Link the timeline with more than one pivot table, in case of multiple PivotTables. To do this, select the timeline, right-click and select “report connections.” In the popup window, the pivot tables to be linked can be selected.

A timeline in excel is a kind of SmartArt that displays the different timings of a particular process.
The timeline was first introduced in Excel 2013.
The pivot table and the pivot chart help summarize and analyze data.
The user can select dates by years, quarters, months, or days with the help of the drop-down list of the timeline in excel.
The timeline excel tools like scroll bar, time level, filter, selection label, window size, etc., appear on the right and the left side of the Options tab.
The timeline slicer allows toggling between years, quarters, months, and days.
Since the timeline slicer helps track milestones, it is a more effective visual tool than the normal date filter.