10 steps to adding a timeline to an Excel 2013 PivotTable

NOTE: If you want to buy office 2013 Pro key (just $45.99) ,welcome to my store :http://www.mskeyoffer.com/

Excel 2013 includes a new timeline tool for PivotTables. A timeline lets you filter records by dates, similar to a slicer. In this 10-step article, we'll generate a simple PivotTable, add a timeline, and explore the different ways to use it to filter data. Combining PivotTables, timelines, and slicers is a great start to an interesting and easy-to-use dashboard.

1: Insert the PivotTable framework

PivotTables are great tools for filtering and summarizing data. The sheet in Figure A lists nearly three dozen records that span three years. A few of the dates occur more than once, but most of them don't. The timeline tool handles duplicates with ease.

Figure A

This data lends itself to a PivotTable.

To execute the PivotTable wizard, click anywhere inside the data and then do the following:

1. Click the Insert tab.
2. Click PivotTable in the Tables group.
3. In the resulting dialog, choose Existing Workbook from the location options and click cell F1, as shown in Figure B.
4. Click OK.

Figure B

You can insert a PivotTable into a new sheet, but insert this one in the active sheet.

2: Arrange the data

Now you're ready to arrange the data by dragging fields to the appropriate areas. Figure Cshows the example table's setup.

Figure C

Arrange the table's data for filtering.

3: Filter as is

The date filter lets you filter the table by specific dates. You can even select multiple dates as follows:

1. Click the All drop-down arrow and check the Select Multiple Items option. Doing so will select all the dates.
2. Uncheck the (All) option at the time and select 2/28/2010 and 5/31/2010, as shown inFigure D.
3. Click OK to see the results in Figure E.

Figure D

Select multiple dates.

Figure E

The PivotTable displays records for 2/28/2010 and 5/31/2010.

To specify a time span, select all the times involved. For instance, displaying dates for 2010 would require selecting all 12 2010 records, but you could do it. Unfortunately, filtering by the day, month, year, or quarter would require specialized knowledge. It's not something your average user could do.

4: Insert a timeline 

Now, let's add a new timeline tool. To do so, click inside the PivotTable and click the contextual Analyze tab. In the Filter group, click Insert Timeline. The resulting dialog will display the date field. Check it, as shown in Figure F, and click OK. The results are shown in Figure G.

Figure F

Select the date field.

Figure G

The default timeline displays months for each year.

5: Use the timeline as is

To update records in the PivotTable using the new timeline, simply adjust the timeline by clicking a tile. Figure H shows the records for February 2010. The scroll bar below lets you browse all the tiles. You can also increase the width of a timeline to expose all its tiles.

Figure H

Click tiles to update the PivotTable.

6: Select a time span

In step 3, you had to select several dates to accommodate a time span. Using the timeline, you have a lot fewer clicks. Just drag across several tiles. Figure I shows the result of dragging across Jan, Feb, and March 2010, to show the first quarter of 2010.

Figure I

Display time spans by dragging across the appropriate time components.

7: Try a second option

We've been filtering by months because they're the default. You can also filter by day, year, and quarter. To do so, click the Months drop-down and choose another date component, as shown in Figure J. Figure K shows a second way to filter by quarters.

Figure J

Choose another date component from the dropdown.

Figure K

A single click displays data for the first quarter of 2011.

8: Add a slicer

You can use slicers with the timeline for more specific filtering. For instance, you can add a personnel slicer to filter the date data as follows:

1. Click inside the PivotTable and click the contextual Analyze tab
2. In the Filter group, click Insert Slicer.
3. In the resulting dialog, choose a field to filter by. In Figure L, I've chosen Personnel.
4. Click OK.

Figure L

You can use a slicer to filter the PivotTable, with or without the timeline.

9: Use the slicer and the timeline together

With the new slicer, you can quickly filter the data by personnel and dates. To see how this works, check Martha in the slicer. Then, click the Q1 tile for 2011. Figure M shows the results -- a single record for just Martha even though two other records exist for Q1. You could add a slicer for each field, if doing so makes sense.

Figure M

Combine slicers to further hone the PivotTable's displayed records.

10: Use the slicer alone

You might want to disregard the timeline and use only the slicer to filter the PivotTable. As long as any timeline tile or tiles are selected, the slicer will reflect the values for only those time periods. To remove the timeline from the selection, click the Clear Filter option in the timeline's top-right corner. Figure N shows all records for Luke after clicking the timeline's Clear Filter option. Slicers also have Clear Filter options.