How to Create Slicer Drop Down in Excel (with Quick Steps)

Get FREE Advanced Excel Exercises with Solutions!

Excel slicer drop-down is a powerful data filtering tool in Microsoft Excel that allows users to easily filter data in pivot tables and charts. It provides a user-friendly interface to select and filter data by presenting a set of buttons or drop-down menus corresponding to specific data sets. With the slicer drop-down, you can quickly and easily filter data by selecting one or more items in the drop-down list, which instantly updates the pivot table or chart. Let’s check out our article about creating a slicer drop down in Excel.

Overview of excel slicer dropdown


What Is Slicer Drop Down in Excel?

A Slicer Drop Down in Excel is a user interface tool that allows users to filter and analyze data in a pivot table or pivot chart. Slicers provide a visual way to filter data by displaying buttons or drop-down lists that correspond to specific data sets. When a user selects an item in the slicer, it filters the pivot table or chart based on that selection, allowing for quick and easy data analysis.

Slicers were introduced in Excel 2010 and have become a popular feature in data analysis. They can be used with different types of data including numbers, dates, and text. Slicers are easy to use and can help users quickly identify trends, patterns, and outliers in large datasets. Additionally, slicers can be customized to fit the look and feel of the data analysis report, making them a useful tool for presenting data to others.


How to Create Slicer Drop Down in Excel: with Quick Steps

In this segment, we will discuss the stepwise procedures to create a slicer drop down in Excel. Firstly, we will create a pivot table with data. Then, will include the slicer in the pivot table. Let’s walk through the procedures.

1st Step: Prepare Dataset for Making Slicer Drop Down

  • Firstly, we need a dataset to prepare the pivot table. We have included a dataset containing Film, Director, Actor, Genre, Rating, Oscar, and Year columns.

Dataset for making slicer dropdown


2nd Step: Create Pivot Table from Data Table

  • Now, we have to create a pivot table. Select the whole data table including the header.
  • Then, select Insert > Pivot Table > From Table/Range.

Inserting pivot table from data table

  • Instantly, Pivot Table from table or range tab will appear. Fill up the input range which should be filled up automatically cause we have selected the whole table previously. Also, click on New Worksheet to create the table in a new worksheet.

Defining input and output range for pivot table


3rd Step: Use Normal Filter in Pivot Table

It’s time to prepare the pivot table and introduce normal filters in it. The normal filters are independent. Changing the selection in one filter won’t affect other ones.

  • For that, click on the pivot table object, and pivot table fields will appear on the right pane.
  • Afterward, put the fields ID and Film in the Row section, and put the Oscar field in the Values section. Also, take the fields Genre and Actor in the filter section.

Choosing fields for pivot table and adding filter drop down

  • As a result, we will see the pivot table with filters like in the image given below.

Pivot table with normal filter drop down

  • Now, select different items from Genre filter. Then, check Actor filter which is unaffected by the change in Genre filter selection.

Checking primary and secondary filter drop down


4th Step: Insert Slicer in Pivot Table

  • Lastly, we will include the slicer in the pivot table. This is quite simple.
  • Simply, click on the pivot table.
  • Then, select Insert > Slicer (from the filters section).
  • Also, select the item from the checklist to add as Slicer. We have selected Genre and Actor.

Inserting slicer drop down

  • Finally, you will get the slicer included in the pivot table sheet. Let’s select some items from the Genre slicer and see if the items from the Actor slicer adapted accordingly.

Included slicer drop down in pivot table


How to Customize Slicer Drop Down in Excel

Already, we inserted a slicer attached to the pivot table. There are many ways you can format a slicer in Excel. Now, let’s do some formatting on it.

1. Changing Slicer Style

Let’s change the style of the slicer. That means the fill-up color for checked or unchecked items in the slicer.

  • Simply, select the slicer. Then, select Slicer and pick your desired style for the slicer from the list.

Changing the style of slicer


2. Change Size and Properties of Slicer

For changing the size or properties of the slicer right click on it and select Size and Properties.

Changing slicer properties & size

  • Finally, change the size and properties of the slicer from the right pane according to your needs.

Selecting new size and properties for slicer

  • Finally, we will get a customized slicer like in the image below.

Final customized slicer

Read More: How to Custom Sort Slicer in Excel 


What Are the Advantages of Excel Pivot Table Slicer Over Normal Drop Down?

Slicers in Excel pivot tables offer several advantages over normal drop-down lists:

  • Visual Filtering: Slicers provide a visual representation of the data filters that are being applied to the pivot table. This makes it easier to see the filters that are currently in effect and to make changes as required.
  • Multiple Selections: Unlike drop-down lists (generally), slicers allow users to select multiple items at the same time. This can be very useful when analyzing large datasets with multiple categories.
  • Easy to Use: Slicers are very user-friendly and intuitive. Users can simply click on the items they want to filter and the pivot table will update automatically.
  • Customizable: Slicers can be customized to match the look and feel of the data analysis report, making it easier to create professional-looking reports.
  • Easy to Create and Manage: Slicers can be created and managed with just a few clicks in Excel, making them a quick and easy way to filter data.

Overall, slicers are a more powerful and flexible tool than normal drop-down lists when it comes to filtering and analyzing data in Excel pivot tables. They are especially useful for large datasets with multiple categories or when presenting data to others.


Frequently Asked Questions

  • What is the difference between a slicer and a filter in Excel?

Ans: A slicer is a visual filtering tool in Excel that is used to filter data in a pivot table or pivot chart. A filter is a more general term that can refer to any tool or technique that is used to narrow down data in Excel.

  • Can I use a slicer with non-pivot table data?

Ans: No, slicers are designed specifically for use with pivot tables and pivot charts in Excel.

  • How do I customize the appearance of a slicer in Excel?

Ans: To customize the appearance of a slicer in Excel, first select the slicer you want to modify. Then, go to the “Slicer Tools” tab and use the options in the “Slicer Styles” group to change the slicer’s color, font, size, and other formatting options.

  • How do I remove a slicer in Excel?

Ans: To remove a slicer in Excel, first select the slicer you want to remove. Then, go to the “Slicer Tools” tab and click on “Remove Slicer.”


Things to Remember

  • Change the field elements in the Row, Column, Values, and Filters section from the pivot table pane in the pivot table sheet according to your necessity.
  • We have included two slicers, you can create more if needed.
  • Don’t forget to include the header of the data table before creating the pivot table.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

We may conclude, a slicer is a great tool while analyzing data in an Excel pivot table. Also, it has some advantages over normal filters. Hopefully, this article will help you to figure out how to use the slicer drop down in the Excel pivot table. Feel free to comment if you have any suggestions or queries.


Related Articles


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo