How to Use Slicers to Filter Data in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you are working with a huge amount of data in your workbook and you want to filter them in a better way without any hesitation then “Slicer” will stand side by side with you to solve your problems. Slicer is a filtering feature with multiple choices in one button that can filter single or multiple data smartly with just one click. In order to filter data, users love to use slicers as it’s very comfortable to use. Today, in this article, I will share with you how to use slicers to filter data in Excel.


How to Use Slicers to Filter Data in Excel: 2 Easy Methods

In the following article, I have shared 2 simple and easy methods to use the slicer to filter data in excel.

Suppose we have a dataset of a Company’s Sales Information combining Year, Product Category, Product, Price, and Review. Now we will use slicers to filter data in this worksheet. Stay tuned!


1. Use Slicers to Filter Data from Worksheet

In order to use slicers to filter data, you have to convert the dataset into a table. Follow the steps to insert slicers in your worksheet.

Steps:

  • Above all, select the whole dataset and choose the “Table” option from the “Insert” feature.

Use Slicers to Filter Data from Worksheet

  • Thereafter, your dataset will be converted to a table. If you want you can change the background color for your working advantage. Here selecting all cells I have changed the background color to “White”.

  • Hence, selecting any cell from the table go to the “Insert” option and press “Slicer”.

Use Slicers to Filter Data from Worksheet

  • After that, from the new window, choose your desired filtering header and press OK.

  • Within a blink of an eye, you will get all the slicers inside the worksheet. Now, let’s filter the data using the slicers.

Use Slicers to Filter Data from Worksheet

  • Simply, just single click your desired “Year”, “Product Category”, and “Products” from the slicers, and the dataset will visualize data according to it.

Use Slicers to Filter Data from Worksheet

  • Although, you can also choose multiple filtering options from the slicers by clicking the “Multiple Selection” option at the top of any slicers.

  • Just click the “Multiple Selection” feature and choose various selections similar to the below screenshot. Simple isn’t it?

Use Slicers to Filter Data from Worksheet

Read More: How to Make Excel Slicer with Search Box


2. Apply Slicers to Filter Data in Pivot Table

If you want you can also apply slicers inside a pivot table to filter data more effectively. Follow the instructions below to learn.

Steps:

  • First, select the whole data from the table and choose “Pivot Table” from the “Insert” option.

Apply Slicers to Filter Data in Pivot Table

  • Second, from the new window click the “Existing Worksheet” and choose a cell according to your choice in the worksheet.
  • Gently, press OK to continue.

Apply Slicers to Filter Data in Pivot Table

  • After that, a right pane will pop up. From the right pane drag your headings to different locations to rearrange your pivot table.

  • Hereafter, choose any cell from the pivot table and click the “Insert Slicer” option from the “PivotTable Analyze” feature.

Apply Slicers to Filter Data in Pivot Table

  • Now, choose any category from the list to open multiple slicers.
  • Press OK to continue.

Apply Slicers to Filter Data in Pivot Table

  • In summary, you will get the chosen slicers for the pivot table.

  • Check out the following screenshot. Here I wanted to see the “Price” for the “Home Appliance” item sold in “2018” with a review of “4”. Clicking the filters from multiple slicers we have the final output in our hands.

Apply Slicers to Filter Data in Pivot Table


Things to Remember

  • If you want to enjoy the amazing features of slicers to filter data, you have to insert a table which can be a normal or pivot table. Otherwise, it won’t work.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have tried to cover all the methods to use slicers to filter data in excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience.

Stay tuned and keep learning.


Further Readings


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.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo