How to Filter Pivot Tables with Timelines in Excel 2013
Excel 2013 introduces a new way to filter your data with its timeline feature. You can think of timelines as slicers designed specifically for date fields that enable you to filter data out of your pivot table that doesn’t fall within a particular period, thereby allowing you to see timing of trends in your data.
To create a timeline for your pivot table, select a cell in your pivot table and then click the Insert Timeline button in the Filter group on the Analyze contextual tab under the PivotTable Tools tab on the Ribbon.
Excel then displays an Insert Timelines dialog box displaying a list of pivot table fields that you can use in creating the new timeline. After selecting the check box for the date field you want to use in this dialog box, click OK.
Below, you see a timeline for the sample Employee Data list by selecting its Date Hired field in the Insert Timelines dialog box. As you can see, Excel created a floating Date Hired timeline with the years and months demarcated and a bar that indicates the time period selected.
By default, the timeline uses months as its units, but you can change this to years, quarters, or even days by clicking the MONTHS drop-down button and selecting the desired time unit.
You can use the timeline to select the period for which you want your pivot table data displayed. The sample pivot table has been filtered so it shows the salaries by department and location for only employees hired in the year 2000. This was done by dragging the timeline bar in the Date Hired timeline graphic so that it begins at Jan and extends up to and including Dec.
And to filter the pivot table salary data for other hiring periods, you can simply modify the start and stop times of the by dragging timeline bar in the Date Hired timeline.