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

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.


Download Practice Workbook

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


2 Simple Methods to Use Slicers to Filter Data in Excel

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 use slicers in your worksheet. You can also check this link to learn more.

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


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. For further information check this link too.

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. In 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 your 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.

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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Further Readings

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo