Timeline in Excel

What is the Timeline in Excel?

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.

How to Create Timelines in Excel? (With Example)

You can download this Timeline Excel Template here – Timeline Excel Template

In the following table, there are five columns, namely–Date, Branch, Product Category, Customer Type, and RevenueRevenueRevenue is the amount of money that a business can earn in its normal course of business by selling its goods and services. In the case of the federal government, it refers to the total amount of income generated from taxes, which remains unfiltered from any deductions.read more.

Timeline Example 1

With the help of a pivot table and a pivot chart, let us create a timeline in Excel. The pivot table and pivot chartPivot ChartIn Excel, a pivot chart is a built-in feature that allows you to summarize selected rows and columns of data in a spreadsheet. It is a visual representation of a pivot table that helps in the summarization and analysis of datasets, patterns, and trends.read more 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:

  1. Click inside the data set, go to the Insert tabInsert TabIn excel “INSERT” tab plays an important role in analyzing the data. Like all the other tabs in the ribbon INSERT tab offers its own features and tools. Under Insert Tab we have several other groups including tables, illustration, add-ins, charts, Power map, sparklines, filters, etc.read more, and select “table.”

    Timeline-Example-1-1

  2. The “create table” popup appears which displays the data range and a checkbox for table headers. Click “Ok.”

    Timeline-Example-1-2

  3. Once the table object is created, the data appears in a tabulated form, as shown in the succeeding image.

    Timeline-Example-1-3

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.”
Timeline Example 1-4
  • 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.
Timeline Example 1-5
Timeline Example 1-6

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.”
Timeline Example 1-7
Timeline Example 1-8
  • The pivot chart appears as shown in the following image.
Timeline Example 1-9
  • 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.
Timeline Example 1-10
  • The pivot chart appears without the legend buttons, as shown in the following image.
Timeline Example 1-11

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.”
Timeline Example 1-12
Timeline Example 1-13
  • 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.”
Timeline Example 1-14
  • Now, the timeline appears.
Timeline Example 1-15
  • For the timeline, you can configure or select group dates by years, quarters, months or days with the help of the drop-down list.
Timeline Example 1-16
  • We have selected “years” as shown in the following image.
Timeline Example 1-17

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.

Timeline Example 1-18

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 2nd quarter of 2016 to observe revenue across the different branches and product categories.

Timeline Example 1-19

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.

Timeline Tools left hand
Timeline Tools Right hand

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.

Timeline Slicer 1

#2 – Scroll bar

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

Timeline scrollbar
Timeline scrollbar 1

#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.

timelevel
level

#4 – Filter

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

Filter

#5 – Timeline header

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

Header

#6 – Selection label

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

Selection label

#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.

Window
Window Size

#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.

Caption

#9 – Timeline style

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

options /styles

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

New styles

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.

Key Takeaways
  • 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.

Recommended Articles

This has been a guide to Timeline in Excel. Here we discuss how to use timeline tools in Excel to create a Timeline in Excel along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>