How to Add a Search Box to a Slicer to Quickly Filter Pivot Tables and Charts + Video

Bottom line: Learn how to add a search box to a slicer to quickly filter your pivot tables, pivot charts, or Excel Tables.  Includes a video tutorial that explains the setup in detail.

Skill level: Intermediate

Video Tutorial: How to Add a Search Box to a Slicer

Watch on YouTube & Subscribe to our Channel

Download the File

Download the file to follow along.

Problem: It's Hard to Navigate Slicers with Lots of Items!

Kati asked a great question about adding a search box to a slicer.  She has a slicer with over 200 items (names) in it, and it takes a lot of time to scroll horizontally through the slicer to find a name.

Slicers With Large Number of Items Are Difficult to Scroll Through

How Can We Add a Search Box to the Slicer?

Unfortunately there is no built-in way to add a search box to a slicer in Excel.  So this solution is a bit of a workaround.

The good news is that it does NOT require any macros or coding.  This solution uses the filter drop-down menu in another connected pivot table, and it is pretty easy to implement.

Add a Search Box to the Slicer to Filter it Quickly

Note: The filter search box was introduced in Excel 2010 for Windows, so this solution will work in the 2010, 2013, or 2016 versions for Windows.  The solution will also work for the Mac 2016 version of Excel.

Step-by-Step Instructions

The video above explains how to add the search box to the slicer.  Here are the basic steps that I explain in the video.

  1. Insert a slicer on the worksheet.
  2. Make a copy of the pivot table and paste it next to the existing pivot table.
  3. The new pivot table will also be connected to the slicer.  The slicer is connected to both pivot tables.
  4. Remove all fields from the areas of the new pivot table.
  5. Add the slicer field to the Filters area of the new pivot table.
  6. Move the slicer on top of the cell that contains the filter drop-down button in the Filters area of the new pivot table.
  7. Adjust the column width so the filter button is just to the right of the slicer.
  8. Turn off the Autofit column widths option on the new pivot table. (Right-click pivot table > PivotTable Options…)Turn off Autofit column widths on update on 2nd pivot table
  9. Hide the column that contains the field name in the Filters area of the new pivot table.

Now you should be able to click the filter drop-down button and use the Search box to quickly find items in the field (slicer) and apply filters.

Keyboard Shortcuts

In the video I mention that you can press the letter “e” on the keyboard to place the text cursor in the search box.

Press the Letter e to Set Focus on the Search Box in the Filter Menu Excel

Checkout my article on 7 Keyboard Shortcuts for the Filter Drop Down Menus for more shortcuts for this menu.

You can also add a Hyperlink to the cell that contains the Filter Button.  Then press Alt+Down Arrow to open the menu and jump the cursor into the search box.

Add Hyperlink to Filter Cell and press Alt Down Arrow Keyboard Shortcut to Search

Those shortcuts will make it really fast to get to the search box to filter the slicer.  The hyperlink text also alerts your users that there is a search box available for the slicer.

Use the Slicer Search Boxes on Multiple Slicers in Dashboards

The slicer search boxes are great for dashboards and pivot charts too.  You can repeat the process to setup search boxes for all the slicers in your dashboard.

Multiple Slicer Search Boxes on Dashboards or Pivot Charts
Click to Enlarge

Checkout my free video series on pivot tables and dashboards to learn how to use pivot tables to create the dashboard in the image above.  It's easier than you think! 🙂

I also have an article that explains how to create a list of all the slicers in your workbook.

Alternate Solutions

Wow, there has been a lot of great feedback and questions about this technique!  Here are some alternate solutions to help navigate slicers with a lot of items.

I added a file in the download section above that contains these alternate solutions.

#1 – View the Name of the Filtered Item

Dennis had a great question about not being able to see the item that is selected in the slicer, after you perform the search.  Unfortunately, the slicer will not automatically scroll to the item that is selected in the search.

So you might type a search and apply a filter for a name that is way down in the list.  You won't be able to see that selection anywhere.

One quick workaround is to add the Sales Rep field to the Filters Area of the pivot table.  I recommend this as a best practice anyways because it helps you quickly see what filters are being applied to the pivot.

Move Slicer Below Filter Cell to Display Selected Item

If your slicer is on a dashboard that filters multiple pivots or charts, then you can simply move the slicer down one row to make the filter field visible.  That cell in the pivot table will display the name of the item that is being filtered.

#2 – What if Multiple Items are Applied to the Filter?

The technique above works great if there is only one item selected in the filter/slicer.  If there are multiple items then you will see (Multiple Items) in the Filters cell.

That doesn't help us very much!

Multiple Items Selected in Slicer or Filter of Pivot Table

There are a few ways to list out all items that are applied in the filter.

Rob Collie has an article that explains a simple way using the Rows area of another pivot table.

Another way is to use Power Pivot and the CUBERANKEDMEMBER function.  Rob Collie has another article that explains that in more detail.

You can also use VBA and Jan Karel Piertse has an article with a UDF (macro) to list the selected items.

#3 – Add a Parent Grouping to Filter the Slicer with Another Slicer

Another solution to this problem is to create a shorter slicer (with less items) that can filter down the long slicer.

In this example I added a slicer that contains the 1st Initial of the names in the Sales Rep field.

Filter or Sort Slicer with Another Slicer GIF

The 1st Initial slicer allows the user to first select the first initial of the name they are looking for.  The Sales Rep slicer will then display a list of the names that start with that initial at the top of the Sales Rep slicer.

This makes it much faster for the user to navigate through the list by narrowing down their selection with the 1st Initial field (slicer).

Checkout this blog post and video for instructions on how to setup the slicer to filter another slicer.

Will You Add Slicer Search Boxes to Your Pivot Tables and Charts?

Please leave a comment below with any questions or suggestions.

114 comments

Your email address will not be published. Required fields are marked *

  • Hi,

    First of all thanks for the tutorial it worked superbly. A problem I am encountering is that if I make a selection after searching an item. If I click into the pivot table itself to get the underlying details it doesn’t show that item and just give mes a full view of the entire data and not the item I am trying to hone in on.

    Thanks

  • The video was very nice. I would like to have the upper left hand box of the pivot table feed a cell elsewhere in excel. Any advice? thx Ward

  • I tried do this in excel for mac but it didn’t work. Just wanted to check if this works even if I have thousands of entires.

    Thanks
    Akshay

    • My brother recommended I might like this web site. He used to be entirely right. This put up truly made my day. You cann’t believe simply how much time I had spent for this information! Thank you!

    • I would like to thank you for the efforts you have put in penning this website. I really hope to check out the same high-grade content by you later on as well. In fact, your creative writing abilities has encouraged me to get my own, personal website now 😉

    • Very good info. Lucky me I recently found your blog by chance (stumbleupon). I have bookmarked it for later!

  • Hi, if I have two slicer, once you click on one option to filter second slicer will shrink with the available data, how do I shrink the pivot table drop down list?
    Thank you!!!
    Alexandra

  • Hi John – as always AWESOME. I do have one teensy question…I have tow pivots – 2 different years – but need to search an item for BOTH – can I use this and if so – HOW…I tried to connect the slicer to both – but did not work – any thoughts?

  • Hi Jon,

    This is a great tip. You might want to mention that the column widths in the pivot table need to be fixed, otherwise the slicer gets pushed if new content is wider than the column widths of the pivot table at the time the hidden pivot table was created.

  • I formatted the pivot table chart – line graph to smooth line but whenever I click the slicer – Unclear or unfilter button it goes the lines go back to pointed and not smooth. How do i fixed this?

  • Great tip Jon, thanks for sharing! I wonder how, once I have searched, selected and filtered, I can make sure that the filtered item is highlighted on top of the pivot slicer list? The filtering works on my pivot tables, but the searched/filtered entry remains in the position in the list and therefore it is not visible to the user unless it is one of the top 10 entries in the list…
    Looking forward to your reply. Thanks!

  • This is great but I cannot do it with a slicer based on just a regular table. Can I make a slicer based on a table also searchable?

  • Hello,
    It is a good solution.
    But it does not work for a time slicer.

    1) I have PivotTables and Slicer (linked to multiple PivotTables including the one I will duplicate)
    2) I duplicated one PivotTable, converted it to a Filter and assured that it is linked to the slicer, like in a video

    When I filter this filter-like PivotTable it does not affect neither my slicer nor any PivotTable linked to this slicer.

    Is there any way to create a drop-down list for a time slicer? Thanks in advance!

  • I found a newer tip that documents the same “overlap” technique you describe. It also includes a couple of techniques to do a better job of hiding the pivot displaying the search box
    .
    Search box in Slicer | Cross filter in two Pivot tables
    https://www.youtube.com/watch?v=Xeff9PbGh64 15min
    Feb 16, 2020 PK: An Excel Expert

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter