How to Add a Search Box to a Slicer to Quickly Filter Pivot Tables and Charts + Video - Excel Campus
96

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

Add a Search Box to a Slicer to Quickly Filter Pivot Tables

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

Watch video on YouTube (and hit the Like button!)

Download the File

Download the file to follow along.

Add A Search Box To A Slicer.xlsx (278.7 KB)

Add A Search Box To A Slicer - List Selected Items (366.4 KB)

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

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 96 comments
Binu - September 18, 2017

Hi Jon- I have been looking for a solution for the slice search. I tried macro but it did not work. This is very helpful. Thank you so much. Now I am facing one problem. I have 4 slicers and all are connected. If the searching “Text” is in the bottom of the Slice then the focus is not going to that “Text”. What I have to do is scroll down to that field manually. Is there any way the focus can directly set to that field based on the search text?

Thanks,
Binu

Reply
Uche Uche - September 9, 2017

Great excel Mentor, You are just Awesome, but humble in your method of teaching. How do I mean?
By allowing comments from others who often are your sought, not only in knowledge but in freely sharing their knowledge with others. In that way, you increase the knowledge of my like, as your unseen and distance students of excel.
I don’t know how many thanks would be enough to thank you. Nevertheless, I still say thanks our Guru sir.

Reply
Murthy - September 8, 2017

This is excellent.

Reply
Joe Teller - August 10, 2017

Hi Jon. Thank you for all you do. I’m new here and am learning a lot from you.

Question about the need to add the search box in conjunction with the slicer.

If you are adding the search box, I think you don’t really need the slicer at all. If you are going into a pivot table search box to select something, then what is the point of the slicer at all?

Maybe I am clueless, so maybe your answer will help me learn.

Thanks, Joe

Reply
vijay kumar - July 28, 2017

Mr. Jon You are awesome.

Reply
Andre - July 26, 2017

This is a wonderful solution for me; however, once I publish the workbook to SharePoint (2013), Excel Services 2013 removes the very search box part of the filter (due to it being an unsupported feature) that my end users really want and need. Is there any viable work-around for this?

Reply
Laura - July 21, 2017

Hi Jon,

I am having an issue with the search box on the second pivot table I have created for my slicer. It will not update the slicer when I make a selection in the search box. I have deleted the second pivot table and followed your steps to rebuild it but it is still not working properly. I did a web search to try to find a solution but I have not found any articles related to the search box not working properly. Can you assist?

Reply
    Jon Acampora - July 24, 2017

    Hi Laura,
    The slicer needs to be connected to all pivot tables that you want to slice/filter on. You can right-click the slicer and go to Report Connections (Pivot Table Connections) to connect the slicer to all the pivot tables. All pivot tables must all share the same source data range. I hope that helps.

    Reply
Allan - April 11, 2017

Great tip about the slicers. Can I use them for this example:
Let’s say I have 3 customers( A,B,C) and products: X,Y,Z.
Customer A has X,Y,X B has X,Y and C has X. If I have a slicer for products and select X I get customers A,B and C. Now how do I build a slicer when I select X it gives me just the customers that ONLY have X as a product? In this case customer C.

Thanks!

Reply
Meredith - March 17, 2017

Good morning John, Thank you for your fantastic videos everything is well explained. The only thing I am having a problem with is copying and pasting my new and updated slicer (with the search bar) to my dashboard.
Is there a specific way this done, to ensure that it works as per your video?

Warm regards
Meredith

Reply
    Jon Acampora - March 20, 2017

    Thank you Meredith! I would recommending inserting or copy/pasting the pivot to the dashboard sheet first. Then inserting the slicer on the dashboard sheet. You will then need to connect the slicer to all the pivot tables on the dashboard sheet. I hope that helps. Thanks again! 🙂

    Reply
Erik - March 9, 2017

Hi Jon – Sorry if I missed this in the comments, but is it possible to filter on multiple values in a pivot table using a search box in a splicer? Say I wanted to only view data for values A,B, and C in a data set that included A-Z. Would I be able to copy and past: “A, B, C” in the search box and have the splicer filter on those values?

Reply
    Jon Acampora - March 13, 2017

    Hi Erik,

    You can use the “Add current selection to filter” checkbox that appears at the top of the list when you type in the search box. This will keep the current filter and add to it with the new search. I don’t believe you can paste a list of values in the search box though.

    Reply
Lois Mathius - January 15, 2017

Hey Jon,

Thanks for the awesome video that’s really help me to complete my job quickly.

Reply
Laurent Dresse - November 30, 2016

Great Post! You made my day!
Thanks
Laurent

Reply
Siops - November 28, 2016

Hi Jon,

Is it possible to use the Slicer when filtering based on the Row Labels? Let’s say I have break down of dates and then per Month. If I choose a per Month slicer, it will only show the grand total per Month. I don’t know if this is possible. Hoping for your help. Thanks in advance.

Reply
    Jon Acampora - December 1, 2016

    Yes, you can add a slicer to any field in the field list. The field does not have to be in any area of the pivot table.

    Reply
Michael - November 24, 2016

Is there a way to apply a filter to a pivot table using multiple criteria, without looping through every item and making them visible or not? When a pivot table has 20,000 records it takes forever. The same with a slicer attached to the pivot table.

Reply
    Jon Acampora - December 1, 2016

    Hi Michael,

    If the field is in the Filters Area of the pivot table then you are pretty much limited to the checkbox list. If the field is in the Row or Columns Area then you can use some of the other filters to select between, begins with, contains, etc. to specify the filter criteria.

    You might also want to think about grouping the field or creating a lookup table and adding a column to the source data that groups the field for specific filter criteria. It will be easier to apply filters to a parent level field that groups the items. I will write an article on that in the future. I do have an article on how to group dates that might help. I hope that helps.

    Reply
Majid - November 22, 2016

Hi Jon,

Your textbox solution is not working on Sharepoint.

Reply
Manay - November 14, 2016

Hi Jon,

Slicer takes a lot of space. Is there anyway i can link a drop down to the slicer !! That’ll save a lot of space on the dashboard.

Reply
    Jon Acampora - November 14, 2016

    Hi Manay,
    The Filter Field that uses the search box in this example is the drop-down version of the slicer. It performs the same filtering as the slicer, and has a search box built in. I hope that helps.

    Reply
Anj - October 28, 2016

Hi there,

Is it possible, taking the example you gave, to filter on sales person and the product name search list only contains the items related to that person.

For example Tom sells apples and orange, Tim sells lemons and limes. If I select Tim the Product search list now only shows the lemons and lines and not all 4 options?

Reply
Thomas - October 18, 2016

Hi Jon,
great job – thanks for sharing! Do you have an idea on how to improve Slicer performance when it comes to pivot tables containing multiple measures which run complex calculations? The problem is that slicers evaluate ALL measures to decide which elements should be shown (due to selection “Hide elements with no data” – an option that users like a lot) even though it would be enough to evaluate it for one measure because all the others depend on this one.

Is there a way to achieve this or the decide which measure should be used to evaluate?

Thanks in advance!

Reply
    Jon Acampora - October 22, 2016

    Hi Thomas,
    I’m not sure of any way to improve the calculation performance like that. It’s a great question though. Let me know if you find any answers. Thanks!

    Reply
Klára - October 17, 2016

Hello Jon,

Your site is very interesting for me! Thank you for it. I would like to ask you for a little help.
I have created a simple table about a few rows and columns from which I have created a Stacked bar chart. But I am not able to unify the color for each item. Everytime I had been to set up the same color for each item (each bar) and then used the slicer tool to switch between the items for to change inputs for chart, the colors had been to somehow by itself changed back to original. Can you help me and explain what I am doing wrong?

Thanks a lot & regards!
Klara

Reply
    Jon Acampora - October 22, 2016

    Hi Klara,

    Thanks for the nice feedback. Here is a guest post I wrote on Peltier Tech that explains this problem of the pivot chart formatting changing, and a few solutions. I hope that helps.

    Reply
Louis - September 25, 2016

Hi John,
It is a valuable work-around – many thanks. Do you know how to set the slicer to automatically list (highlight) the items selected? Even better would be if it only displayed the items picked in the filter, especially if multiple items have been selected. This will then provide a point of reference when one inspect the results. I look forward to your answer. Thank you.

Reply
    Jon Acampora - September 27, 2016

    Hi Louis,
    Yes, you can do this in the slicer settings. Right-click the slicer and select Slicer Settings… There are a few checkboxes in that window that control what is displayed.

    Checking “Hide items with no data” will hide all the items that are not selected in the filter.
    Checking “Visually indicate items with no data” will display the additional slicer buttons as greyed out or disabled.

    I hope that helps.

    Reply
Mandava - September 1, 2016

I have the slicer with 10000 Loan Numbers. I want to create the search box to filter the required loan numbers in slicer. Is there any way we can achieve this Using VBA or Any other way.

Thank you

Reply
Sainadh - September 1, 2016

Hi John

I like Your Idea. How can we filter multiple of names at a time. like I want to enter multiple names in same time this is possible in this scenario.

Thank you

Reply
    Jon Acampora - September 7, 2016

    Hi Sainadh,
    I don’t believe you can enter multiple names at the same time. However, you can check the box at the top of the item list in the filter drop-down menu that says “Add current selection to filter”. Here is a screenshot.

    Add current selection to filter in Filter Drop-down Menu Excel Pivot Table

    You would click the checkbox after typing the search term to select other items in the list to add to the filter. I hope that helps.

    Reply
Rafeeq - July 12, 2016

Hi Jon,

Through your videos and blogs, have become a big fan of Excel now, specially pivot tables. Have a question on Slicers. How can I add cascading slicers so that selecting a value in one slicer should pop up corresponding values in second slicer. For Example, if I am creating two slicers one for Sales Rep and second for Region. Selecting a particular region should bring in all the associated Sales Rep present in that sales region in the Sales Rep slicer. Your response is highly appreciated.

Best Regards

Reply
    Jon Acampora - July 12, 2016

    Thank you Rafeeq! I’m stoked to hear you are learning so much about Excel. Pivot tables are an amazing tool.

    There is a setting in the Slicer Settings menu that will allow you to display the associated Sales Reps first in the slicer. You will want to check the box that says “Show items with no data last”. That will gray out/disable the items that are not included in the current filter context (when a region is selected).

    I have another blog post & video that discuss this in more detail. How to Filter a Slicer with Another Slicer

    That is a more advanced technique, but should help you understand the concept of how multiple slicers can work together. Thanks!

    Reply
Donna Martinez - June 22, 2016

Hello Jon,

This is a very nice work around. The users of my dashboard report have raise this issue and glad I found your site. I have questions with regards to the file size and memory resources. (1) Isn’t will increase the file size if we add another pivot table? Like possibly double the size. (2) With regards to resources, will it have difference in the processing time having additional pivot?

Thank you and will wait for your help 🙂

Reply
    Jon Acampora - June 22, 2016

    Hi Donna,

    Great question. The short answer is, No, the additional pivot will NOT increase file size.

    In this case the two pivot tables share the same data source and the same pivot cache. The pivot cache is saved in the file and stores the pivot table source data. Multiple pivot caches can lead to an increase in file size. Typically you will only have multiple pivot caches if you have pivot tables with DIFFERENT data sources. In this case, the source data range is the same for the main pivot and the search/filter pivot. Therefore the file size should not increase much at all.

    I hope that helps. Let me know if you have any questions. Thanks!

    Reply
Kshitij - May 19, 2016

I am not able to drag and move the filter. Is there something I am missing out on?

Reply
    Jon Acampora - May 21, 2016

    Hi Kshitij,
    You will first need to unhide all the columns of the pivot table. In the example file column E is hidden so the field name does not display. Unhide that column first, then you should be able to select and move the entire pivot table. Thanks!

    Reply
Thomas - May 6, 2016

Hi Jon,

Thanks for the tutorial. Each time i make a selection on the slicer the size of the cell where the filter box is placed changes and thus the dropdown gets hidden behind the slicer each time and I manually have to increase the width of the cell to show the dropdown again. Any ideas how i could fix this if anyone else is having the same issues?

thanks again.

Reply
    Jon Acampora - May 7, 2016

    Hi Thomas,
    Great question! There is a setting in the Pivot Table Options menu that needs to be turned off.

    1. Right-click any cell in the pivot table and select Pivot Table Options…
    2. Uncheck the box that says Autofit columns widths on update. This will prevent the cell from resizing when the pivot table is refreshed.
    3. There is a screenshot of this in step #8 in the article above. Please let me know if you have any questions.
      Thanks!

    Reply
Prasad - April 27, 2016

Thank you. Nice one.

Reply
Ryan Wilson - April 27, 2016

Very cool! It was just earlier this month a coworker asked me if it was possible to add a search box to a slicer and I responded “I’m sure you could, but you are most likely going to need to write some VBA.” I’m going to forward him this link. Thanks Jon!

Reply
Kinny - April 18, 2016

What a smart workaround Jon…ingenious! However, this approach seems to work only when you have a 1 slicer vs 1 pivot table situation. In my dashboard, I actually have 3 pivot tables with 5 slicers connected to all 3 of them. I’ve tested your approach in my dashboard with the 1st slicer and the 1st pivot table by creating a duplicate copy of the 1st pivot table then followed the rest of your suggested steps to create a search box in the duplicate pivot table then move it below the 1st slicer and sized it so that the filter box can be seen in the outer edge where I can then search a field name but this does not seem to synchronize with the other 4 slicers and the remaining 2 pivot tables which were and should remain connected. My situation might be a more complex one than the simple 1 to 1 situation used in your example. Could you think of a modified workaround for my specific situation?

Just to give you some context as to why I need to have 5 slicers all connected to 3 pivot tables in my dashboard – Initially there was only 1 slicer in my dashboard which was connected to 3 pivot tables each with different dimensions to meet the different needs of users. As the dashboard is being used by more users, many have started to request for additional slicers to be added in hope of easing them in getting the information for the specific dimensions they are interested in seeing from the 3 pivot tables. As many of the users are not super users of pivot tables, adding additional slicers appeared to be the most simple way to improve their experience with the dashboard. As time moved on, I ended up increasing the number of slicers from 1 to 5 which were all connected to the 3 pivot tables. Let me know if this explanation helps you understand my specific situation. I really hope that you can provide me with a simple workaround. Thanks Jon!

From: Kinny

Reply
    Jon Acampora - April 26, 2016

    Hi Kinny,
    My apologies for not responding sooner. It should work with multiple pivot tables and slicers. Do you have all the slicers connected to the new pivot table that you are using for the filtering? That might be the issue. If you want to send me your file I would be happy to take a quick look. jon@excelcampus.com.

    Reply
VK - April 10, 2016

Hi John,

Great tip!! It helps me so much at work.

Reply
Gayle - March 3, 2016

Hi Jon,

I love how you manage to come up with such clever workarounds! This one is going to be really useful to me. Thank you for sharing your knowledge 🙂

Reply
    Jon Acampora - March 7, 2016

    Thank you Gayle! I really appreciate the great feedback. I am also excited to hear that you will be able to use this. I love the challenge of trying to find solutions to our everyday Excel problems. 🙂

    Reply
Mynda - March 3, 2016

Ingenious! Wish I’d thought of that 😉

Reply
John F - February 24, 2016

Love your work Jon.
Your tips seem to pop up at just the right time to help with my dashboard projects.

Regards
JF

Reply
Mary Beth Riley - February 23, 2016

Hi Jon,

Great tip as always. I have attempted (and failed!) to find a similar solution for the value filter. Right now I have multiple pivot tables that I filter by a dollar range (typically greater than or equal to $5M). The data is structured by rolling up individual accounts and services into one parent row. Each individual account/service might fall into a lower range but rolling it all up it is greater than $5M. I can’t build the dollar range into data table or it won’t roll up correctly into the range.

Any thoughts on if you have seen a solution to this? My file is used by different people with different excel skills levels to knowing how to open a file to some advanced users (not too many in this bucket!).

Thanks for all your great tips. I really like this one as I have a long list of names that this will definitely assist with.

Reply
    Jon Acampora - March 7, 2016

    Hi Mary Beth,
    Sorry to not get back to you sooner. I missed your comment.

    That is a very interesting question. Do you want this in a slicer? To be able to filter by all accounts at different total amounts/groupings?

    I’m thinking that you could create a new column in the source data with a SUMIFS formula that would be used as a lookup value in a lookup table, to return the dollar group name. The SUMIFS formula would do the same calculation that the pivot table does to sum the account. The dollar group name field could then be added as a slicer. Let me give that one some thought. It’s late in the day and there might be an easier way to do it. 🙂 Thanks!

    Reply
Linda Kammerer - February 23, 2016

Is there a way to have the drop down list be sorted? I have quite a long list and it looks like it is working properly, however the items in the list are not sorting and that will make it more difficult to use.

Reply
    Jon Acampora - February 23, 2016

    Hi Linda,
    Great question! If the field was originally in the Rows or Columns area then it might have been sorted in a different order. Or a custom sort might have been applied to it.

    There are a few ways to fix this.
    1. You can move the field from the Filters area back to the Rows area, then click the filter drop-down for the field and select Sort A to Z.

    2. Another way is to find the field in the Pivot Table field list (right side of the screen). There will be a small triangle on the right side of the field name when you hove the mouse over it. Click that and it will bring up the filter drop-down menu. You can select the Sort A to Z on that menu.

    Let me know if that helps. Thanks!

    Reply
      Linda Kammerer - February 24, 2016

      Ye, I used #1 option and that worked. I put the items in the rows area, sorted them and brought them back to the filter section.

      Reply
Trish Gifford - February 18, 2016

As always, nice trick! Using all your resources. We have a similar problem with a data validation list that won’t allow us to type the first character to get to the all the suppliers that start with the first letter of “Z”. The list of suppliers is a query from our ERP system maintained in a named range and we want the user to find the right supplier name so we have concatenated their Address location to their name. However, if you have a supplier’s name that starts with Z you have to scroll for some time. Once the supplier name is selected, we have a vlookup that goes and retries the supplier address book # so we need the method to still allow this vlookup / retrieval somehow. Any suggestions?

Reply
    Jon Acampora - February 18, 2016

    Thanks Trish! Great question! There is nothing built into the application that allows you to scroll through validation lists like that. Let me give it some thought… Maybe someone else can chime in as well. Thanks again! 🙂

    Reply
      Trish Gifford - February 19, 2016

      Jon,
      I opened your file Add-a-Search-Box-to-a-Slicer-List-Selected-Items.xlsx and found that by showing the PivotTable search box, I was able to reference that cell in a separate cell and then vlookup the supplier # from the named range. We are going to try this on our real PO Requisition template. I think it will work and provide enhancement. Thanks!

      Reply
        Jon Acampora - February 19, 2016

        Awesome! That sounds like a great use for this technique. There has been a lot of feedback and questions, and I added that file with some alternative solutions. I will probably add another video or article with those solutions as well.. Thanks again Trish!

        Reply
Nadine - February 18, 2016

clever!

Reply
Kamil - February 18, 2016

Good idea but…it will not be working if we have on slicer connected to 10 pivots or more.

Reply
Dimitris - February 18, 2016

Cool idea Jon! 🙂

Reply
MF - February 18, 2016

Hi Jon,
What a nice workaround! As you said, the steps are not difficult, but the thinking is!
Thanks for sharing!
Cheers,

Reply
AHMED - February 18, 2016

hi jon,
it’s a great job, well done
but i have a small proplem when i try it
when i chose a a diffrent Sales Rep such as ” Chris Williams” it dosen’t light up on the slicer tool, it seems to me it just light for the very first 8 person only
i hope you can solve this problem in your next tip
good luck

Reply
    Jon Acampora - February 18, 2016

    Hi Ahmed,
    Great question! The item should be selected in the slicer, but the slicer will not automatically scroll to that selection. I don’t believe there is any way to program the slicer with VBA to scroll to the selected it.

    There are a few workarounds for this. One is to just move the slicer down below the cell that contains the filter. When you apply the filter the item name will be displayed in that cell. If there are multiple items selected in the filter/slicer then it will display the phrase (Multiple Items).

    The other option is to use another pivot table, put the slicer field in the rows area, and then use some formulas to create a concatenated list within a cell.

    I added a file to the downloads section above that contains both solutions. Let me know if you have questions or suggestions. Thanks!

    Reply
      Adam Kopeć - February 29, 2016

      It’s strange that when there are 2 Slicers and 1 Pivot Table, the filtered data appears at the top, and with 2 Pivot Tables and 1 Slicer I can’t get this effect to work.

      But it’s a great trick

      Reply
        Jon Acampora - March 1, 2016

        Hi Adam,
        Are both pivot tables connected to the slicer?

        Reply
          Adam Kopeć - March 1, 2016

          Exactly as in your example in the film.
          When there are 2 pivot tables connect to one slicer filtered values don’t appear on the top of the slicer

          Reply
David - February 17, 2016

Jon

Great & you present so well.

Best

David

Reply
Anika - February 17, 2016

Thank you, another great tip and tutorial!

Reply
Lenny Valentino - February 17, 2016

Perfect timing! I had a slicer with about 150 teachers names and my clients hated going through the long slicer list to find all the teachers of one department.
The addition of the search box made this so much easier! Thanks!

Reply
Joseph - February 17, 2016

WOW! Awesome tips Jon.

Reply
Peter Raiff - February 17, 2016

Great tip Jon! It is so simple, I don’t know why I didn’t think to do that before. I have already implemented it in many of my projects. Thanks!

Reply
    Jon Acampora - February 17, 2016

    Thanks Peter! I wish I had thought of it sooner too. Kati asked the question last week in a comment, and the idea came to me in a dream… Yes, I dream about Excel. Just don’t tell my wife 🙂

    Reply
T N Ramanathan - February 17, 2016

Hi Jon,

Another great tip… keep them coming.

Thank You

Reply
Bob Umlas - February 17, 2016

Sweet. Like!

Reply
Samantha - February 17, 2016

Hi Jon,

Another great tip… keep them coming.

Thank You

Reply
Jon Acampora - February 16, 2016

Please leave a comment with any questions about implementing this in your file. Thanks!

Reply

Leave a Reply: