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.
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.
- 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”.
- 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.
- Simply, just single click your desired “Year”, “Product Category”, and “Products” from the slicers and the dataset will visualize data according to it.
- 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?
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.
- 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.
- 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.
- Now, choose any category from the list to open multiple slicers.
- Press OK to continue.
- 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.
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
- How to Convert Table to List in Excel (3 Quick Ways)
- [Fixed] Report Connections Slicer Not Showing All Pivot Tables
- Excel Slicer for Multiple Pivot Tables (Connection and Usage)
- How to Resize a Slicer in Excel (With Quick Steps)
- Connect Slicer to Multiple Pivot Tables from Different Data Source