Excel Slicers with search functionality provide a user-friendly way to filter and navigate data in Excel. By incorporating a search feature, users can quickly find and select specific items within the slicer, saving time and effort. This powerful tool enhances data analysis and decision-making by simplifying the process of filtering large datasets. In this article, I am sharing with you how to make a slicer with the search feature in Excel. Stay tuned!
In the following, you will find an overview of Excel Slicer with a search option.
Download Practice Workbook
You can download our practice workbook from here for free!
How to Make Excel Slicer with Search Box (with Easy Steps)
In the following, I have described three simple and easy steps to create a search option with a slicer in Excel.
Suppose we have a dataset of Employee Name, Region, and Total Sales in a spreadsheet. Now, we will insert a slicer with a search option to filter specific items quickly. To insert a slicer, we have to insert a PivotTable.
1st Step: Create PivotTable
- To make a slicer with a search bar, I have to create a PivotTable for the given dataset. To do so, choose the whole table and click PivotTable from the Insert tab.
- Second, choose New Worksheet and hit OK.
- Then, from the PivotTable Fields pane, drag the Region table to the Rows section and the Total Sales table to the Values section.
- Now, you will get a PivotTable inside your worksheet. For working advantages, we will make a copy of the PivotTable by selecting the table and pressing CTRL+C to copy.
- Choose a cell (E4) and paste the PivotTable.
- Thereafter, choose any cell from the PivotTable and click Slicer from the Insert tab to add a slicer inside the worksheet.
2nd Step: Insert Slicer and Check Connections
- A window will pop-up named Insert Slicers. From the window, check box before the Employee Name option and press OK.
- In order to check whether the slicer is working properly or not, visit the Report Connections feature and select the slicer.
- Checkmark both the PivotTable and hit OK.
- Now, we don’t need the copied PivotTable anymore. Thus, we will delete it by using the DELETE button on the keyboard.
3rd Step: Add Filter Option
- In this final step, I will add the filter option by visiting the Field List from the PivotTable Analyze tab.
- From the PivotTable Fields pane, drag the Employee Name table to the Filters section.
- Thus, we will get a PivotTable with Employee Name filter.
- To check the filter, simply click the drop-down menu, and you will see all the employee names in the list.
- Let’s start rearranging by hiding the column which has the Employee Name.
- Then, put the slicer over the newly created filter.
- Finally, we have created our Excel slicer with a search bar.
- Simply click the filter icon and choose a name from the list.
- As a result, you will see the output for the chosen employee name. Simple, isn’t it?
Things to Remember
- While creating the search option, make sure the slicer is linked to the appropriate column or fields.
- In order to avoid overlapping important data, choose a suitable design and layout for the slicer.
Frequently Asked Questions
1. Can I reset the slicer search and show all options again?
Yes, you can clear the search box within the slicer to reset the search and display all available options.
2. Can I apply multiple search terms in Excel slicers?
No, the search functionality in Excel slicers typically allows you to search for one term at a time.
3. Does the search feature in Excel slicers support wildcard characters?
Yes, the search functionality in Excel slicers supports wildcard characters such as asterisks (*) and question marks (?).
Conclusion
In conclusion, Excel slicers with search functionality enhance data filtering by providing a quick and efficient way to locate and select specific items. This feature saves time and improves data analysis by eliminating the need for manual scrolling through lengthy lists. By leveraging the search capability, users can easily navigate and explore their data to make informed decisions. Take a tour of the practice workbook and download the file to practice by yourself. 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.