To illustrate use of timelines, let’s say you are managing a big database with Excel in a company of house rentals.
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 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.
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.
When you click OK, the 2 slicers appear on your spreadsheet.
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.
And the result is pretty cool isn’t it? -)
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.
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
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
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
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.
A new box is now inserted in your spreadsheet. This is a timeline control.
When you select a range of time only these values are display in your PivotTable.
You can also change the date split by clicking on the arrow next to the default value Month.
We selected DAYS this time.
In this second Excel Web App, change the value of the timeline to see the impact on the Pivot Table.