Search box and clear filters button for Pivot table in Excel

Occasional Visitor

Hi all, 

 

I've been asked to create a dashboard to help my team to quickly search values in a big dataset. 

So far I've set up a pivot table and added filters and drop down menus on the side to make it easier to filter and sort through the results. 

The next step is to add a search box and a clear-all-filters button. The dashboard is in a shared folder and most of my team aren't exactly tech-savvy, so I want to make things as easy as possible for the end-users. This is to say that although macros could be an option, I want to avoid them as much as possible because my teammates don't all know how to use them. 

 

For the clear-all-filters button I added the feature on the quick access part but it only works on normal table, not with pivot tables. Is anybody else having the same issue? I know that some features aren't available/don't work properly on a Mac, is this perhaps one of these cases? 

 

The real issue has been setting up the search box. I've watched multiple videos and read articles and they all suggest either using macros for the search box or a series of formulas that I can't get to work on my spreadsheet.  

The formula I'm looking for should search through all of the pivot table (5 columns), filter and sort through the data and only display the relevant ones. It doesn't need to display a drop-down menu, that would a plus. So far, I've tried using SEARCH and LOOKUP formulas with little success. It also doesn't help that the values are words (sometimes long titles) instead of numbers. 

I also thought of the conditional formatting, which works fine. This could be a great starting point if I could only make the search dynamic. 

 

So my questions are, can I realistically expect to have a dynamic search box for a pivot table? What about a clear-all-filters button?

 

If either of them isn't but an oasi in the desert, can anybody help? I really don't know how to proceed. Any idea/suggestion would be highly appreciated. 

 

Rocki1060 

 

0 Replies