Instead of playing around with filters to show dates, you can now use a PivotTable timeline. It’s a box you can add to your PivotTable that lets you filter by time, and zoom in on the period you want. Click Analyze > Insert Timeline to call it up.
Much like a slicer you create to filter data, you can insert a timeline once and keep it with your PivotTable to change the time period on the fly. Here’s how:
- Click anywhere in a PivotTable to show the PivotTable Tools.
- Click Analyze > Insert Timeline.
- In the Insert Timelines dialog box, check the boxes of the date fields you want, and click OK.
Use a timeline to filter by time period
With your timeline in place, you’re ready to filter by a time period in one of four time levels (years, quarters, months, or days).
- Click the arrow next to the time level shown, and pick the one you want.
- Drag the timeline scroll bar to the time period you want to analyze.
- In the timespan control, click a period tile and drag to include additional tiles to select the date range you want. Use the timespan handles to adjust the date range on either side.
- To clear a timeline, click the Clear Filter button .
Tip If you want to combine slicers with a timeline to filter the same date field, you can do that by checking the Allow multiple filters per field box in the PivotTable Options dialog box (PivotTable Tools > Analyze > Options > Totals & Filters tab).
Customize a timeline
When a timeline covers your PivotTable data, you can move it to a better location and change its size. You can also change the timeline style, which may be useful if you have more than one timeline.
- To move the timeline, simply drag it to the location you want.
- To change the size of the timeline, click it, and then drag the sizing handles to the size you want.
- To change the style of the timeline, click it to display the Timeline Tools, and then pick the style you want on the Options tab.
More information about PivotTables