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 C shows 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 in Figure 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.

Figure N

Click Clear Filter to disregard the date field.