Excel Slicer And Timeline – Tutorial With Examples

This tutorial explains how to create and apply Excel Slicer and Timeline to single or multiple Pivot tables to filter the table and get the desired results:

Filters in general are very important for any report or tables as they help us in displaying only the desired details at a given point in time. Excel Slicer and timelines are such filters that give us the desired results in an effective and simple way.

In this tutorial, we will learn how to apply Slicers and Timelines to the pivot table, how to filter the table, and get the desired results. We will also learn to use one filter for multiple tables.

=> See ALL Excel Tutorials Here

What Is An Excel Slicer

Slicers are a set of clickable buttons that will allow you to perform quick filtering on the pivot tables. Slicers display the active filter thereby making it easy to understand what data is being displayed currently.

For example, let’s say you are searching for Quarter 1 sales by specific product in a particular region.

By using a usual filter, you have to click multiple times or filter in multiple rows/columns to get one result. But with slicers, it can be achieved in a matter of clicks.

Slicers were first introduced in Excel 2010. Hence Excel 2010 and later versions will have Slicers for pivot tables. Excel 2013 and later versions will have slicers for both pivot tables and regular Excel tables.

Earlier versions of Excel won’t support the Slicer feature. So if you create a file in Excel 2010 or later version with slicers and send that to someone who is using an older version of Excel, say Excel 2007, an empty box will be shown instead of the slicer.

Data Source Sample File:

We shall use the below-mentioned sample Excel file as our data source. Click on the link to download it.

Sample_Data Excel Slicer

How To Apply A Slicer To A Pivot Table

There are 2 ways to insert a Slicer in a pivot table.

#1) Using PivotTable Fields

• Right-click on the pivot table field in which you wish to apply slicer, for example Region.
• Choose “add as slicer” from the context menu.

#2) From PivotTable Tools

• Click anywhere on the pivot table to open PivotTable Tools.
• Click on Analyze -> Insert slicer.

• Select the field you want to apply slicer from the dialog box.

Both the methods will insert a slicer and will be displayed on the workbook.

Let’s filter the table based only on the South Region. To achieve this you just have to click on South in the slicer. The resulting table is shown below.

Multiple slicers can be added to a pivot report. Based on the selection from multiple slicers, the appropriate subset of data will be shown in the pivot report.

Let’s create 2 more slicers for Product Name and City. So there are 3 slicers.

Click on Seattle city and Chocolate product. The pivot table will just show the result related to the selection.

Clear Slicer Filters

If you want to clear the applied filter, you need to just click on the filter with a cross mark on the top right corner of the slicer.

Let’s clear the product name filter and the pivot table will update automatically.

Multi-Select

If you enable the Multi-Select icon, then you can select multiple items inside the slicer without holding the Ctrl Key. Alternatively use the shortcut (Alt+S).

If you click on this icon, then all the items you click will be selected and the pivot table will be updated accordingly.

Slicer Toolbar In Excel

Once you click on the slicer, the Slicer Tools will be displayed in the menu bar.

It contains the following options like Slicer Caption, Settings, Report Connections, Styles, etc.

#1) Slicer Caption

You can enter the name of the slicer in this text box and that name will be displayed on top of the slicer. For example, I will enter the name as Region Slicer.

#2) Slicer Settings

Settings dialog which will allow us to Sort and filter will be displayed.

• Name: This is the name that is used in case you are using this slicer in the formula.
• Caption: Alternatively you can enter the Slicer name here also.
• Sorting: You can sort the items in the Slicer in ascending or descending order.
• Filter: You can decide to hide or show the items that have no result after applying the filter.

#3) Report Connections

This option is used to connect one slicer to multiple pivot tables. We will discuss this in detail later in this tutorial.

#4) Slicer Styles

This is a gallery with pre-defined styles that you can use to decide the style of the slicer. You can change the color of the items in it. You can also define your custom format with the New Slicer Style option.

If you create a custom format, that will be shown in the gallery as shown below.

#5) Arrange

Bring Forward

If you have 2 or more Slicers that are almost overlapping then:

1. Select the Slicer you want to bring in front.
2. Click on the Bring Forward option to bring the Slicer one step forward.

Bring to Front

This option will bring your slicer above all the other slicers.

Send Backward

If you have 2 or more Slicers that are almost overlapping then:

1. Select the Slicer you want to send back.
2. Click on the send backward option to send the Slicer one level back.

Send To Back

This is used to send the selected slicer back of all the other slicers.

Selection Pane

You can decide the visibility of the slicer using the selection pane. This pane shows you all the slicers available and you can click on the eye icon to decide whether that particular slicer should be visible on the worksheet or not.

If you click on the eye icon next to any of the fields, then that slicer won’t be visible on the worksheet. When you click again, it will be displayed back.

Align

If we have multiple slicers in the sheet, then the default alignment overlaps all the slicers, which is not useful as you can’t see all the items available inside the slicer. Hence there are multiple options available to help us reduce extra effort to arrange them. While aligning, one of the Slicer is taken as the reference point.

• Align Left: Aligns all slicers (select one slicer and press CTRL A) to the left of the referenced slicer.
• Align Center: Aligns all slicers (select one slicer and press CTRL A) to the center of the referenced slicer.
• Align Right: Aligns all slicers to the right of the referenced slicer.
• Align Top: Aligns all slicers to the top of the referenced slicer, one of top of the other.
• Align Middle: Aligns all slicers to the middle of the referenced slicer.
• Align Bottom: Aligns all slicers to the bottom of the referenced slicer.

Distribute Horizontally: You can arrange all the slicers one after the other horizontally. Drag one slicer and place it at the position where you wish your last slicer to be placed. For example, drag one slicer to the cell O, then Ctrl+A, and click Align -> Distribute horizontally.

Distribute Vertically: You can arrange all the slicer one below the other vertically. Drag one slicer and place it at the position where you wish your bottom slicer to be placed. For example, drag one slicer to cell 32, then Ctrl+A and click Align -> Distribute Vertically.

• Snap to Grid: This is used to align shapes to the closet intersection in the grid.
• Snap to Shape: This is used to align shapes to the grid lines.
• View Gridlines: This will show/hide the grid lines on the sheet.

Group: You can select the slicers to group them together. This will be helpful in the selection page where you can hide or show all the grouped slicers in a single click.

It also has the options to Ungroup and Regroup. As the name suggests, ungroup will be discarded, and regroup will group them back.

#6) Buttons

These options are specific to the items/ buttons inside the slicer.

Columns: By default, all the slicer will have one column which will show all the items. If you wish to show the items in multiple columns, then you can change the number of columns in the slicer.
Height: You can increase or decrease the height of the button present inside the slicer. Select the slicer and change the height.
Width: You can increase or decrease the width of the button present inside the slicer. Select the slicer and change the width.

Example: In the city slicer, in order to see all the items in it, you have to drag it down. But instead, you can make 2 columns and can adjust the height and width of the button.

Before

After

#7) Size

These options are used to format the slicer itself.

Height: You can decrease or increase the height of the slicer. Select the slicer and change the height.

Width: You can decrease or increase the width of the slicer. Select the slicer and change the width.

If you click on the pop-out icon on the bottom, you will get advanced options where you can select the position, layout, and additional properties.

Slicer To Multiple Pivot Table

We can apply a single slicer to multiple tables and control both the tables simultaneously.

Suppose you have created a pivot table and also created slicers related to that pivot table. A new requirement comes from the manager that you have to create another similar pivot table but instead of Sum of Total, you have to choose the Count of Total in the new table.

This can be easily achieved by copy and paste instead of creating a new table.

To copy a pivot table:

• Click anywhere on the pivot table.
• Analyze ->  Select -> Entire Table
• Press Ctrl+C
• Place the cursor in the cell where you want to paste the pivot table.
• Press Ctrl+V
• You can change the value field setting from Sum of Total to Count of Total.

Notice that the slicers are not copied. But if you make any selection in the slicer then both the tables will be updated based on the selection criteria.

Report Connection

In the previous example, we saw how a slicer is linked to 2 pivot tables when it is copied and pasted. But what if you create 2 or more pivot tables and want to link slicers to all the tables. Report connection will help you to achieve this.

Report connections can be used to connect the slicer to multiple pivot tables. They help us to filter multiple tables simultaneously using a single slicer.

You can open Report Connection in two ways as shown below.

#1) From Slicer Tools Option

• Click on any Slicer.
• Go to the Slicer Tools -> Report Connections.

#2) Utilizing Context Menu

• Right-click on the slicer.
• Click Report Connections.

Connecting Pivot Table

From the Report Connections dialog, you can select the pivot tables that you wish to connect with that particular slicer.

Report connections dialog gives us the information that Region is the slicer that should be used to filter both the PivotTable1 and PivotTable2

As an example, we have created 2 pivot tables where Region is common as shown below.

Create a slicer for Region and connect it to both the tables using report connection. Now click on East on the slicer. The resulting table is shown below where both the tables are showing the details of only the East region.

Delete Slicer

You can delete a slicer from the right-click context menu or select the slicer and hit the delete key on the keyboard.

Excel Timeline

Timelines work much like slicers but will help to filter only based on the date/time field. It also allows you to zoom in and out on a particular period with the help of controllers.

Let’s create a sample pivot table as shown below to understand how Timelines work.

How To Create Timeline In Excel

• Click anywhere on the Pivot table, and the PivotTable Tools ribbon will be displayed.
• Go to PivotTable Tools -> Analyze -> Insert Timelines.

• Click on Insert Timeline.
• Insert Timeline dialog which will only show the date column of your table will appear. Select the date checkbox.

• Click OK

The timeline will be displayed on the worksheet as shown below.

Timeline Filters

• You can filter your table based on Years, Quarters, Months, and Days from the drop-down available as shown below.

• You can use the scroll bar to navigate to the desired month, years, days, etc.

• You can use the timeline just to filter by one month, year, or quarter by using the slider. You can also select more than one month but the selection should be in sequence only.

Timeline Tools

Like Slicers, timelines also have various options to make timelines more intuitive to use. Given below are the options available for the Timeline.

#1) Caption Name

You can enter the name of the timeline in this caption text box and the timeline name will be updated as shown below.

#2) Timeline Styles

You can decide the style or the color of the timeline filter. Timeline Styles has different colors light and dark from where you can make the selection.

Similar to Slicer, you can also create a custom style from the New Timeline Style.

#3) Arrange

Just like slicer, timelines also have similar options for aligning multiple timelines. All the options work exactly the same way for both Slicer and Timelines.

#4) Show

Show has 4 options as shown below.

• Header: If this option is unchecked then the name of the timeline won’t be displayed. Notice in the below picture that the Timeline name is not displayed as the Header is not checked.

• Selection Label: This will not display the selection Label which is the selection made by you.

• Scrollbar: This option will not display the scroll bar present at the bottom of the timeline.

• Timeline: If unchecked this won’t show the filter available at the right for years, months, days, etc.

#5) Clear Filter

If you want to clear the applied filter, then you just need to click on the filter with a cross mark on the top right corner of the slicer or alternatively Alt+C.

#6) Report Connection

Similar to Slicer, You can link a single timeline to multiple pivot tables and any filter applied in the timeline will affect both tables.

Available under Timeline Tools, Options -> Report Connections

Note: If you try to insert a timeline that does not have a date column then an error message will be displayed as shown below.

Delete Timeline

You can delete a Timeline either by right click and select Remove Timeline from the context menu or Select Timeline and hit the Delete key on the keyboard. Make sure to clear the filter from the timeline before deleting the same.

Frequently Asked Questions

Q #1) What is a slicer in the Excel Pivot table?

Answer: Slicers are filters with buttons that will allow us to perform quick filtering on Excel tables or Pivot tables and get the desired output in fewer clicks.

Q #2) How does slicer work in Excel?

Answer: For the Excel Pivot table you can insert the slicer from Analyze -> Insert Slicer.

For regular excel tables, you have to go to Insert -> Slicer

Q #3) How do I connect slicer pivot tables?

Answer:

To connect a slicer to pivot tables:

• Click on the Slicer you want to connect to the tables.
• Go to Slicer Tools -> Report Connections.
• From the dialog, you can choose the tables which are to be connected with that particular slicer.

Q #4) How do I make a horizontal slicer in Excel?

Answer: The default setting of a slicer is by columns, and you can change it using the Slicer Tools Options.

Select the slicer, Go to Slicer Tools -> Options.

Under the Buttons section, you can change the number of columns, their height, and their width. This way you can customize the slicer to display as horizontal, vertical, or any other format as you desire.

Q #5) What is the difference between Slicer and Timeline?

Answer: Slicer lets you filter the pivot table or regular table effectively. You can filter based on different fields.

Timelines are only used to filter based on the date field in a table like a month, year quarter, etc. If you don’t have a date field in a table then you will get an error.

Q #6) How do I create a timeline slicer in Excel?

Answer: For the Excel Pivot table you can insert the timeline from Analyze -> Insert Timelines.

For regular Excel tables, you have to go to Insert -> Timeline.

Conclusion

In this tutorial, we have learned about Excel Slicers and Timelines that can be used for quick filtering of tables in Excel. We have seen how to apply single or multiple slicers to a table and change its style. We have also discussed how to connect a single slicer to multiple pivot tables.

We saw how to use timelines that help to filter tables by date. You can also use timelines in the dashboard and present the report in just a matter of clicks.

We also discussed the various options available for Slicers and Timelines that help in customizing and aligning them.

=> Visit Here To Read Through ALL Excel Tutorials