How to create timeline in Excel to filter pivot tables and charts (2024)

What is a timeline in Excel pivot tables and charts?

Timeline in an Excel PivotTable or PivotChart is an interactive tool that lets you quickly filter data by time periods using a slider control. Timelines are available for pivot tables and pivot charts that have a date field in the rows or columns area. They can be used to filter data by years, quarters, months, or days, and can be formatted to match the style of your workbook.

Picture this: you've got your pivot table or chart set up, but instead of scrolling through endless rows of dates or fiddling with filters, you've got this neat little slider right in front of you. That's your timeline! This nifty tool lets you zoom in and out of specific time periods with just a few clicks, making it super easy to focus on the data that matters most. They are fully dynamic, meaning you can adjust the timeframe on the fly and your pivot table and graph will instantly update to reflect the changes.

How to create a timeline for Excel pivot table

It's very quick to make a pivot table timeline in Excel. Just follow these steps:

1. Click anywhere in the pivot table to activate its ribbon tabs.
2. On the PivotTable Analyze tab, in the Filter group, click the Insert Timeline button.
3. In the dialog box that pops up, select the date field you want to include.
4. Once you've made your selection, hit the OK button.

And just like that, you've successfully added a timeline to your Excel pivot table! Now you can easily filter and examine your data by time periods, revealing useful insights with no trouble.

How to make a timeline for Excel pivot chart

When you make a timeline for an Excel pivot table, it gets automatically connected to all pivot charts based on that table. If you wish to incorporate a timeline directly into the pivot chart area, follow these steps:

1. Click on your pivot chart to ensure it's selected.
2. Navigate to the PivotChart Analyze tab and click Insert Timeline.
3. Select the target date field and click OK. This will insert the timeline in the worksheet.
4. Resize the chart area as needed by dragging the borders. You can make the chart area bigger and the plot area smaller to accommodate the timeline.
5. Drag the timeline into the empty space at the bottom of the chart area.

The result might look something like this:

How to use a timeline in Excel

Once you have set up your timeline, you can use it to filter your pivot table and chart by time period.

• Select time level – utilize the Time Level dropdown menu to choose the desired time, whether it's years, quarters, months, or days.
• Pick the period – Use the timespan slider to select your preferred time interval. To choose a single period, simply click on the corresponding tile. For selecting multiple days, months, quarters, or years in sequence, click on one tile, then drag the timespan handles to adjust the date range on either or both sides.
• Scroll through periods - navigate through different time periods by dragging the scrollbar to the desired position.

Tip. To prevent pivot table columns from resizing when using a timeline, turn off the Autofit column widths on update feature.

How to customize Excel timeline

To make your data analysis even better, you have the flexibility to fine-tune the default timeline created by Excel. By changing its appearance, position, and size, you can get the dynamic filter to perfectly complement your pivot table or chart.

Move a timeline

To relocate the timeline to a more suitable position, simply drag it as you would any other object in the sheet:

1. Hover the cursor over the upper part of the timeline until the four-pointed arrow appears, indicating that the timeline is ready to be moved.
2. Click and drag the timeline to your desired location.

Resize a timeline

To quickly change the size of the timeline, click it, and then drag the sizing handles to the size you want.

Alternatively, you can set the desired height and width in this way:

1. Select the timeline.
2. On the Timeline tab, in the Size group, set the height and width in the corresponding boxes.

How to lock the timeline position in a sheet

To ensure that the position of a timeline remains fixed within a sheet, follow these steps:

1. Right-click on the timeline, and then select Size and Properties… from the context menu.
2. In the Format Timeline pane, navigate to the Properties section, and check the Don't move or size with cells option.

This way, your dynamic filter will stay in place even as you add or delete rows and columns, adjust fields in the pivot table, or make other modifications.

How to connect a timeline to multiple pivot tables

If you have several PivotTables based on the same data source, you can use a single timeline to filter those multiple tables simultaneously. To link a timeline to more than one table, follow these steps:

1. Select the timeline by clicking on it.
2. Go to the Timeline tab and click Report Connections. Alternatively, right-clink the timeline and choose Report Connections from the context menu.
3. In the Report Connections dialog box, select the PivotTables you want to filter.

To disconnect the timeline from a specific table, uncheck the box next to its name.

Tip. If you wish to filter the same date field using both a timeline and slicers, you can do so by enabling the Allow multiple filters per field feature. To access this option, right-click on the pivot table and select PivotTable Options from the context menu. In the dialog box that opens, switch to the Totals & Filters tab and check the Allow multiple filters per field box.

How to clear timeline filter

To clear a timeline, click the Clear Filter button or press the Alt + C shortcut. This will remove all applied filters and reset the timeline, allowing for a fresh analysis of the data.

How to remove a timeline

After using your filter slider for a while, you may not want it anymore. To delete the timeline from your Excel sheet, you can do any of the following:

• Right-click the timeline and choose Remove Timeline from the context menu.
• Select the timeline and press the Delete key on the keyboard.

Either way, the timeline will be promptly removed from your work sheet, letting you focus on other aspects of your analysis.

Tip. If you accidentally deleted the timeline, don't worry. You can easily bring it back using the Undo feature. Simply press Ctrl + Z or use the Undo button to revert the deletion.

Excel pivot table timeline not working

Encountering an error while trying to insert a timeline for a PivotTable or PivotChart in Excel? You might see a message stating: "We can't create a Timeline for this report because it doesn't have a field formatted as Date" even though your dataset does have a date column.

There are two potential reasons for a pivot table timeline not working:

• Your source dataset lacks a date column altogether.
• Your dataset does have a date column, but the values are stored as text strings that only resemble dates.

To resolve the error, you must either include a column of actual dates or convert the existing text values into dates. Here are some tips to differentiate between normal Excel dates and text-dates, along with quick methods to convert text to date format.

Practice workbook for download

Excel Timeline - examples (.xlsx file)