Timeline

The timeline is a new tool added for the pivot table. With it, you can easily select a period of dates in your pivot table.

Presentation

To illustrate use of timelines, let’s say you are managing a big database with Excel in a company of house rentals.

Excel_2013_Slicer_Example

This example is interesting because you can have a lot of different selections like:

  • The type of goods (apartment, house, maisonnette, penthouse….)
  • The location (London, Paris, New York, ….)
  • The country (France, Spain, Canada, Great Britain, China, …)
  • And also the date.

Because the possibility of selections are huge, we are going to make our selection with the slicer and the timeline.

Slicers

Slicers were introduced in Excel 2010. When you create a PivotTable, you can use slicers to filter your data. But now, in Excel 2013, you can use also slicers for your tables.

Insert a Slicer

When you create a PivotTable, you have a new ANALYZE menu on the ribbon, which appears when you select a cell on a PivotTable.

Excel_2013_Slicer_2

 

When you click on the Insert Slicer button a dialog box will display all fields of your PivotTable. In our example, we select 2 fields (thus we get 2 slicers), the country and the city.

Voluntarily, we have selected a field, the country, which is not in our PivotTable.

Excel_2013_Slicer_3

When you click OK, the 2 slicers appear on your spreadsheet.

Excel_2013_Slicer_4

Format of the slicer

As you can see on the above picture, the presentation of slicers is a nice visual way to filter your data and you can change the color as well as the number of columns easily.

First, your can change colors of your slicers by clicking in the OPTIONS tab.

Excel_2013_Slicer_5

And the result is pretty cool isn’t it? -)

Excel_2013_Slicer_6


You can also change the number of columns of your slicers by changing the value in the ribbon.

Start by selecting the slicer you want change the number of columns (here, the slicer Cities) and change the value in the ribbon.

Excel_2013_Slicer_7

 Filtered on a value

The main reason to have slicers in your spreadsheet is to easily filter your PivotTable. Let’s say that you have too much data in your PivotTable and you only want to present the results for only one country. In your slicer Country, you click on the value United States

Excel_2013_Slicer_8

Automatically, only the data corresponding to your selection are displayed in your PivotTable. Moreover, the second slicer displays only the cities of the country which aren’t filtered out, as you can see by the shaded items. The slicers are linked to the same PivotTable :)

Try using the slicers yourself on the following spreadsheet.

 

If you want to select more than one value in your slicers, click on the Ctrl key 

 

 The timeline

Exactly on the same principle of slicers, Excel 2013 provides a filter for your PivotTable (and only PivotTable’s this time) on a specific period with the new tool, timeline.

With your PivotTable selected, on the ANALYZE ribbon tab, select the option Insert Timeline

Excel_2013_Slicer_9

A new dialog box shows only the fields corresponding to a date format. In our table only one field has data in date format. Dates stored as text will not be recognized for timelines.

Excel_2013_Slicer_10

A new box is now inserted  in your spreadsheet. This is a timeline control.

Excel_2013_Slicer_11


When you select a range of time only these values are display in your PivotTable.

Excel_2013_Slicer_12

 

You can also change the date split by clicking on the arrow next to the default value Month.

Excel_2013_Slicer_13

 

We selected DAYS this time.

Excel_2013_Slicer_14

In this second Excel Web App, change the value of the timeline to see the impact on the Pivot Table.

3 comments

  1. Jerry Norbury

    Is there a way to connect a single Timeline to 2 Pivot tables with different data sources?

  2. Siobhan

    Hi,

    I’m hoping you can help.
    I have Excel 2013.
    There is no “Analyze” option on the ribbon and there is only a “slicer” filter, none for the timeline.

    Please please assist.

    Thanks in advance.

    1. Frédéric LE GUEN

      Hi,
      Weird :( And I have never heard anything like this before.
      Have you install some add-ins ?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>