Part of the free Module 10: Pivot Tables · Lesson 11 of 15 · Full Excel course
A timeline in a pivot table is a date-only slicer: a horizontal bar you drag across to filter the report by days, months, quarters or years. In this lesson you will learn how to insert a timeline in a pivot table in Excel, switch its time level, select a range of periods, connect it to several pivot tables and clear it, so date filtering takes one click instead of a dialog.
What a timeline does and when to use it
Slicers list every item as a button, which becomes unusable for a date field with hundreds of individual days. The timeline solves this by presenting dates as a continuous band grouped at the level you choose. Click one month to filter to that month, or drag the selection handles to cover a quarter, a half-year or any custom span. The pivot table, and every pivot chart built on it, updates instantly. Timelines require a true date column in the source data and are available in Excel 2013 and every later version, including Excel 365.
How to insert a timeline in a pivot table
- Click anywhere inside the pivot table.
- On the PivotTable Analyze tab click Insert Timeline in the Filter group. It is also available under Insert > Filters > Timeline.
- The Insert Timelines dialog lists every date field in the data source. Our sample data has a single field, Date. Tick it and click OK. If a date column is missing here, it is stored as text and must be converted first.
- The timeline appears on the worksheet, grouped by Months by default, with the year labels above the month blocks and a caption showing the current selection.
- To change the time level, click the drop-down at the top right of the timeline and choose Years, Quarters, Months or Days.
- Click a year, quarter or month block to filter the pivot table to that period. Drag the handles at either end of the highlighted band to extend the selection across several periods. The caption above the bar confirms the range, for example “Jan – Mar 2018”.
Connect, clear and format the timeline
- Clear the filter with the funnel icon at the top right of the timeline or the shortcut Alt+C while the timeline is selected.
- Connect to other pivot tables: right-click the timeline, choose Report Connections and tick the pivots to control, exactly as for a slicer.
- Show or hide parts: on the Timeline tab untick Header, Scrollbar, Selection Label or Time Level to simplify the control for a dashboard.
- Style it with the Timeline Styles gallery, or create a custom style that matches your dashboard colours.
- Scroll with the bar at the bottom when the data spans many years.
Tips and common mistakes
- Timeline greyed out. The source has no genuine date column. Use DATEVALUE or Text to Columns to convert text dates, then refresh the pivot.
- Timeline and grouped dates. A timeline works independently of date grouping in the Rows area; you can group by Months in the pivot and still filter by Quarter in the timeline.
- Fiscal years. Timelines follow calendar years only. For a fiscal calendar add a Fiscal Year column to the source and use a slicer instead.
- Do not mix with a report filter on the same field. Choose one method per field to avoid confusing readers.
Practice and real-world use
Insert a timeline and a pivot chart on the same sheet, hide the chart’s field buttons and set the timeline to Quarters. Drag across two quarters and compare the result with a single quarter. Sales trend dashboards, monthly MIS reports and year-to-date reviews all use a timeline as their primary date control.
Related lessons
Frequently asked questions
Why is Insert Timeline greyed out in my pivot table?
The data source has no column that Excel recognises as dates. Convert text dates to real dates in the source, refresh the pivot table and the command becomes available.
What is the difference between a slicer and a timeline?
A slicer shows every item as a button and works with any field type. A timeline works only with date fields and presents them as a draggable band grouped by years, quarters, months or days.
Can one timeline filter several pivot tables?
Yes. Right-click the timeline, choose Report Connections and tick each pivot table that shares the same data source. All connected pivots and their charts update together.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.