How to Add a Search Box to a Slicer in Excel

welcome to Excel campus my name is John and in this video I'm going to show you how to add a search box to your slicer so what I have here to the right of my slicer is this little button and when I click on it it basically brings up the filter menu for this particular field and allows you to type a search to filter down your search criteria and if you ever if you have a slicer like this that has a lot of items in it this particular slicer has 200 names in it it's very difficult to scroll through this and find the name you're looking for so it makes it very easy if you can just click a button and start typing a search for a name and then hit enter and of course that will apply those filters to the slicer and also filter the connected pivot tables now excel does not have a search box feature built into the slicer so this is a bit of a workaround and it basically just requires us to create another pivot table put it in the background and put an item here in the filters area that allows us to use this filter drop-down so there's no coding necessary it's very easy to set up and in this video I'm going to show you how to do it all right so we're going to go ahead and start from scratch here I just have a pivot table on my sheet the first thing I want to do is insert a slicer so I'm going to go to the analyze or the options tab in the ribbon and select the insert slicer button that'll bring up the list of fields of my pivot table and I'm going to choose the sales rep field and then press ok so that's added a slicer right here into my sheet and basically again you could just select any item in the slicer and that's going to filter down the pivot table so the next thing I want to do is make a duplicate copy of this pivot table right here so I'm just going to select the entire pivot table if your pivot table is really large and you have a hard time selecting at all you can just select one cell inside of it go to the analyze or the options tab choose select and then entire pivot table that'll select your entire pivot table then I'm just going to hit ctrl C on the keyboard to copy it and I'm going to just paste it right here over and here next to my existing pivot table so ctrl V right there to paste and now basically what why the reason why I made a duplicate copy here is so that both of these pivot tables are connected to this slicer so basically if I was to select any item here in the slicer you could see that both pivot tables are being sliced or filtered and you can also see that if you just right-click on the slicer and go to report connections or pivot table connections you can see here that both of these pivot tables on this sheet are connected to the slicer so that's a very important step you want to make sure that both of your pivot tables are connected to the slicer the slicer is connected to all your pivot tables so I'm going to go ahead and hit OK there and now all we need to do is modify this pivot table so we don't need any of these fields here I'm just going to clear them out I'll just uncheck the boxes so we can start fresh with the blank pivot table and all I'm going to do is take the sales rep field because again our slicer is for the sales rep field and take the sales rep field and and drag it into the filters area of the pivot table so now I have my sales rep off field basically right here in the pivot table I can move this down just select both these cells and you can move this down to line it up with the pivot table something like that and so now basically what we have here is our search box or our our filter menu that you can see as I press this button right here that'll bring up our filter menu with our search box so now all we need to do is some formatting to get this lined up and looking good so just going to make this column a little wider and then basically I'm going to overlay the slicer over that cell there so f3 and just kind of bring this in a little bit so basically all I have is a button here to the right of my slicer and then column a we don't really need this column so we can just right click and hide it so now I have basically my slicer with a filter button right next to it and that clicking that filter button will open the filter menu that contains the search box for basically the same items that are in the slicer and one little keyboard shortcut here is if you hit the letter e on the keyboard that will jump your text cursor right here into the search box the letter e does that after you press this button here and then you can just start typing your search for whoever you want to find in the list I'll just hit type Adam hit enter and that will automatically apply the filter to our pivot table here and you can see it's also applied to the slicer as well Adam is selected in the slicer and again that's because this pivot this slicer is connected to both pivot tables so that's that's the magic make sure that if you hit report connections that both of these items are checked right here and that will basically allow you to set up this search box and that your users will love this if you have a basically a slicer here with a lot of items in it because it's much easier to just be able to go in here hit the letter e you can start typing your search find whoever you're looking for and hit enter and that will filter down your pivot table or your report for that particular person and you can also use this on dashboards it's great I have an example here where I have it on multiple slicers so you can set this up with multiple slicers it's the same basic thing and just create multiple pivot tables with the slicer filled in the filters area of the pivot table and then you can have basically search boxes right here for each of the slicers in your dashboard so it's a great tool that to add to any dashboard make it a little more interactive and a little easier for your users to use so of course you can download these files on the page there'll be a link below the video you can download these files and check it out and see how to apply it to your own work and please leave a comment below the video with any questions or suggestions and I'll be happy to help if you enjoyed that video there are a few simple things you can do to help me out if you are watching this video on youtube click the like button below the video and leave a comment with any questions or feedback and please don't forget to subscribe to my free email newsletter to get more tips and tricks that will help you learn Excel thanks again for watching and I'll see you soon

Channel: Excel Campus - Jon

Views: 271,880

Rating: 4.9382238 out of 5

Keywords: excel, pivot tables, slicers, search box, filter, dashboards

Id: 95_dIb8PMpc

Length: 6min 37sec (397 seconds)

Published: Wed Feb 17 2016