Lesson 13


Create a Pivot Table Timeline; Practice Exercise 20

Practice Exercise 20: Create a timeline to filter a pivot table.

Continue to use data file, RANGE TO TABLE TO PIVOT TABLE2.

Figure 1

STEP 1: Select any pivot table cell.  Select the Pivot Table Analyze  tab from the Ribbon as shown in FIGURE 1 above.

STEP 2: Go to the Ribbon Filter section, and select, Insert Timeline icon (FIGURE 2).

Figure 2

Important Note:
You must have at least one field formatted as a Date field in order to use a Timeline Slicer. If you do not have a date field you will receive this error message shown below (FIGURE 3). 

Figure 3

​STEP 3: After selecting the Insert Timeline icon, you will see the Insert Timelines dialog box. Check the box, Date, which will illuminate the OK button (FIGURE 4).
STEP 4: Select the OK button.

Figure 4

STEP 5: The Timeline Slicer appears. You will see a small downward pointing arrowhead next to “Months” on the right side of the timeline. Select the Months arrowhead and you will see a drop-down menu with a choice of the timeline periods, i.e. years, quarters, months, and days (FIGURE 5).

Figure 5

STEP 6: ​Select the arrowhead next to Months and select Years from the drop-down menu (FIGURE 6).

Figure 6

STEP 7: The timeline is currently selected for Jan-Mar 2014. The filter icon up at the top right corner of the timeline illustrates that filtering is currently being used. The filter can be cleared with the mouse just like a regular slicer (FIGURE 7).

Figure 7

STEP 8: Click on the timeline and the Timeline tab will appear. Click on the Timeline tab and you will see the Timeline toolbar appear, as shown below in FIGURE 8.

Figure 8

STEP 9: In the Timeline section of the Timeline Tools, to change the Timeline Caption from “Date” to something else (i.e. “Period”), click mouse inside the Timeline Caption window and enter the new name.

Click mouse inside the Timeline Caption window and replace “SalesDate” (FIGURE 9) with “Date of Sale” (FIGURE 10).

Figure 9
Figure 10


Back to: Intro to Excel 2016 Pivot Tables > Module 4 – Intro to Excel 2016 Pivot Tables

Leave a Reply