Study smart | Excel superstars
Slicers and Timelines
Discover how to use the perfect accessories to drill into your data
Words Traci Williams, excelace.co.uk
In the last two articles, we explored PivotTables and PivotCharts and how they can summarise and display data visually.
Slicers and Timelines were introduced to Excel in 2010 and 2013 respectively and are the perfect accessories for tables*, PivotTables and PivotCharts as they provide visual filters to enable the user to drill into the data quickly and easily.
* Tables can only be connected to Slicers; Timelines can only be connected to PivotTables.
This article will show you how to add and use Slicers and Timelines to provide ultimate control over your data.
What are Slicers?
I like to think of Slicers as powerful filters, as they provide that functionality but they are far simpler to use. They are visual and you can simply press the buttons for what you need.
They also provide a visual display of the selections made and will also drive subsequent Slicers to inform your next selection.
What are Timelines?
Timelines are like Slicers but only connect to date fields. They are also simple to amend to show the data by dates/months/quarter or years, without the need for any grouping or formulae.
Like Slicers, they also provide a clear display of the selections made.
How do you insert a Slicer?
Once you have created your PivotTable(s), go to the ‘PivotTable Analyze’ [1] ribbon and select ‘Insert Slicer’ [2]:
You will then see a list of fields [3] from the PivotTable and you can select as many as you need, then click ‘OK’. Here I have selected three Slicers:
A Slicer will appear for each of your selections, and you can then move and resize them all to whatever position you require:
How do you insert a Timeline?
In a similar way to inserting Slicers, go to the ‘PivotTable Analyze’ ribbon but select ‘Insert Timeline’ [2]. You will then be presented with a list of date fields that you can select from:
Select the relevant field(s) and click ‘OK’, and the timeline will appear. It can be moved or resized as required:
Slicer and Timeline features
With both Slicers and Timelines, you can literally click on the option you want to view.
In this example, I’ve selected ‘Customer A’ from the Slicer and ‘May 2024’ from the Timeline:
Notice how the top ‘Sales Person’ chart has changed to reflect these selections and also how the subsequent Slicers have altered to ‘grey out’ items that are no longer relevant given the selections already made. This helps to guide the user as to their next selections.
It is also possible to select multiple criteria on a Slicer by clicking on the ‘Multi-Select’ option [1]. When it is indented, it is switched on and you can then select multiple options:
This can also be achieved by holding down ‘Ctrl’ (for non-consecutive items) and ‘Shift’ (for consecutive items).
The filter connections on a Slicer (and Timeline) can be cleared by clicking the red ‘X’ on the funnel, indicated in the above image [2]. This is also useful as an indicator as to which Slicers have been filtered.
On a Timeline, it is possible to switch the time period by clicking on ‘Months’ and simply selecting ‘Days’, ‘Months’, ‘Quarters’ or Years’. This eliminates the need for any grouping in PivotTables, or including additional fields in the raw data:
You can also click on the three dots on the left (or right) of the bar and drag across to include multiple time periods (the selected time period will appear in the top left of the Timeline, providing clear visibility of the selection):
Report connections
In the above example, making changes to the Slicers and Timelines is only impacting the first PivotChart, so we will need to connect the Slicer to other PivotTables.
Select a Slicer or Timeline, right-click, select ‘Report Connections’ and this screen will appear:
Here you can select the PivotTables that you want the selected Slicer or Timeline to control. Unfortunately, this needs to be done individually for every Slicer and Timeline, but once they are connected they will stay connected.
Formatting and customisation
When you select a Slicer or Timeline, you will notice a new ribbon appears at the top right of the screen:
Slicer
Here, you can find all of the options to adjust the appearance of the Slicer, including styles and colours, which is really useful for communicating to others (i.e. “on the green Slicer”).
With Slicers, you can also amend the number of columns that will appear, which enables you to have a horizontal list instead of a vertical, or simply reduce the length to better fit using the height and width settings.
Timeline
The Timeline options are similar to Slicers in terms of styles and colours, and also height and width, but there are also options at the end for including (or excluding) headers, scrollbar, selection label and time level. These are simple to toggle on and off to find your preference:
Limitations and considerations
There are a couple of things to watch out for with Slicers and Timelines, though:
- Timelines only work with proper date fields in PivotTables, so make sure dates are always entered as dd/mm/yy.
- Slicers cannot filter charts unless they are based on PivotTables or tables.
- Too many Slicers can slow down large files.
- You cannot link Slicers directly to standard Excel ranges (non-tables).
Conclusion
Slicers and Timelines provide simple, visual and intuitive filtering, resulting in professional dashboards and extremely user-friendly reports.
They are so simple that end users can experiment with them to drill into the specific information they need. This eliminates the need for building multiple reports at the outset.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
1) Which of the following statements about Slicers is TRUE?
2
What is the main difference between a Slicer and a Timeline in Excel?
Note: Timelines are a special kind of Slicer meant for date fields. They allow users to filter by periods such as years, quarters, months and days.
3
Which of the following is required for a Timeline to work properly?
Note: Timelines only work when there’s a proper date field in the PivotTable. Without this, Excel won’t allow you to insert a Timeline.
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).