What is Timeline in Excel?
In Microsoft Excel, a timeline is a visual representation of data that allows you to easily filter information within a specific time period. Timeline was first introduced with Excel 2013.
Creating a timeline in Excel can be a useful way to filter data in a pivot table or pivot chart, allowing you to interactively analyze time-based data over different time periods, such as project schedules, sales trends, or financial data.
They are especially useful when you have a large dataset with dates and you want to focus on specific time intervals without manually filtering the data every time.
Compared to simple date filters, Timeline Slicer is a more effective visual representation tool. This is because the latter provides a graphical representation that helps track important milestones.
Note: To create a timeline in Excel, make sure your data is organized with date or time columns. This column must contain a date or time value.
How to Insert a Timeline
Click anywhere on the PivotTable or PivotChart to which you want to add a timeline slicer.
- Go to the Insert tab on the Excel ribbon and click Slicer in the Filters group.
- Click anywhere on the PivotTable or PivotChart to which you want to add a timeline slicer.
- Select the Analyze tab on the Excel ribbon and click the Insert Timeline button in the Filter group.
- The Insert Timelines pop-up window appears. A checkbox appears with a date or time column in the Insert Timelines dialog box. Select the date or time field you want to use as a timeline.
- Click OK.
- Now, the timeline appears as below.
- For the timeline, you can configure or choose group dates by years, quarters, months, or days with the help of the drop-down list.
How Does the Timeline Filter to the Pivot Table?
- If you want Timeline to filter the Pivot Table with the results of April month. To do this, click on “April” in the timeline slicer.
- Sales for the month of April will appear or be filtered.
- Now, select Quarters from the dropdown list. If quarterly data is not visible in the timeline, drag the blue box towards the end.
- Let’s select Q2 2023 to track various Cust names and their sale.
- You can also do same for Years & Days filter for display data accordingly.
Customize the Timeline (Change the Timeline Styles)
- You can also change the Timeline Style, color, and other formatting options by following the below steps:
- Select Timeline, and then the Timeline Tool contextual tab will appear on the ribbon.
- On the Timeline Tools contextual tab, go to the Options.
- Use the Timeline Styles dropdown to apply a pre-defined style to your Timeline. These styles include various color schemes and designs.
- You can also customize the Timeline style as per your desire, you can do it by New Timeline Style…., That will open format timeline dialog box, where you can modify various formatting options, such as the Timeline’s font, border, border color, background color/effect/pattern, and so on.
Setting of Timeline Slicer
- Scroll bar : This option appears in the Options tab and helps select periods. It also allows scrolling through the years, quarters, months, and days.
- Time level : This option allows selecting from four different time levels depending on preference. The four time levels are, namely–years, quarters, months, and days.
- Timeline header : This option displays the heading or the title of the timeline.
- Selection label : This option displays the date range that is included in the filter.
- Clear Filter : This button helps to clear all the “time” options like years, quarters, months or days.
- Timeline window size : The height and width of the PivotTable timeline can be adjusted as per requirement. It is also possible to resize the timeline window by dragging it beyond its borders.
- 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.
Connect Timeline to multiple Pivot Tables and Charts
If you already have a Timeline on a pivot table, you can use the same Timeline to filter another pivot table.
Note: Timeline can only be connected to PivotTables that share the same data source.
- Right-click on the Timeline, and then click Report Connections
- Alternatively, select the Timeline you want to share with another pivot table. This displays in the Options under the Timeline Tools tab.
- In the dialog box, select the check box of the Pivot Table in which you want the Timeline to be available. Now both pivot tables will respond to the Timeline selection.
- You can use the same approach to disconnect a timeline from any pivot table.
FAQs:
- A slicer is an object that allows quick filtering of data. The slicer shows all the possible values of the column which are selected by the user. Each value appears as a button that can be used to toggle.
- The slicer also displays the current filtering state, letting the user know the exact values currently being displayed. A slicer can be used with table and a pivot table or Pivot Chart.
- If your data has a date field, you can insert a Timeline. this is a special type of slicer, which works only with date values.
- A timeline allows the filtering of data specifically with date fields. The user can filter data by years, quarters, months, and days. When one moves from left to right on the timeline, dates are displayed horizontally, from oldest to newest.
- Timeline can only be used with a pivot table that contains date fields.
The timeline can be used with the help of the following features:
- 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 view the selected date range.
- Date grouping–The Time level feature allows grouping of dates in the data. Dates can be grouped by years, quarters, months, and days.
- Timeline handle or scroll bar–The scroll bar can be used to 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.
- 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).
The steps to display a timeline in Excel:
- 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.
- In case of multiple PivotTables, you can link the timeline with more than one pivot table, To do this, select the timeline, then right-click and select Report connections. In the popup window, the pivot tables to be linked can be selected.