Pivot Table Search Bug

Learn Excel from MrExcel Podcast, Episode 2212: Pivot Table Search Bug.

Okay, welcome back to the MrExcel netcast, I'm Bill Jelen. Oh, boy, today's question or comment or something, is from Sam in Nashville. He says, "Hey, when you use search in a pivot table, it will cause missing data in the refresh." Alright. And, so, what we're talking about here, is we're talking about the date filters, the value filters, and search.

Alright, so, first thing, let's take a look at the date filters. And I have to admit, I never really use these, so if I would ask for date filters of today-- alright, so I'm recording this on May 9th and that works great. The big question is, if I come back tomorrow and refresh that pivot table, will it change to 5/10? Is it remembering the concept of today?

And I suppose-- I suppose-- we could test this, by saying we're going to look for date filters after-- let's put 5/11-- after 5/11, click OK. And we had 5/12 and 5/13. Alright, beautiful. Now, let's come back to the original data here and we're going to change some stuff. I'm going to take this data from 5/8 and change it to be 5/14/18-- so, now that's after 5/11, right? So the big question is... Let's make it big so we can see it, 9999. If we come back here to the filter, and now refresh the pivot table, it does-- it picks up 5/14. So the date filter seems to remember what the setting was and reapply itself.

But let's come here to the search filter. Alright. Now, in the search filter, we're going to choose some word here like "wand", alright? I'm going to search for anything that has "wand" in it, and I get "Reports Wand". Alright, that's beautiful. Now, here's what Sam says: Sam says the next day, he gets more data and-- type here, "magic wand", and, again, make this large, 999999, beautiful-- and then Sam comes back to the pivot table and refreshes. Now, this pivot table in Sam's mind is set up to have anything with the word "wand", and when he refreshes-- doesn't work. Alright? So, Sam is pointing out that the search box-- filters set up by the search box-- are not being remembered like values set up with the date filters. Certainly.

Alright, now let's try it another way. Let's clear this filter-- and this time, we should have two things that say "wand"-- so we'll say Customer Label Filters contains the word "wand", click OK. Alright. And now we get “Reports Wand” and “Magic Wand”-- beautiful. Let's come back to the original data and I'll change something else to "obi wand", and I know it's "obi-wan"-- I know that, I know that, I'm just being funny, alright-- we'll come back here to the search and we'll refresh and it does pick it up. Alright. So, that means that filters that we create with the label filters or with the date filters, they seem to be remembered and they work in a refresh. But if you try and use the search, it's really just kind of a one-time thing-- one-time thing-- and it doesn't-- it's not going to remember, it's not going to redo this search when you refresh the data. So, I guess Sam has a great point. Yeah, that seems to be buggy. I'm sure the Excel team would say, "Well, of course, we would never do that." But let's just.. If you were expecting the search to work like these other things would work here, it is not going to work. So, hey, thanks to Sam in Nashville for that awesome trick.

This book, MrExcel LIVe, The 54 Greatest Tips of All Time, it covers Pivot Tables, although not this trick-- I just learned that from Sam in Nashville-- but it covers everything else. So click that "I" in the top right-hand corner.

Oh, you know, now that I'm thinking about this a bit, I'm sure the Excel team's argument's going to be that they added that search box just to make it easier for us to go through, and choose, and un-choose here. Alright? I'm sure that's what they're saying. For example, like when I chose Wand in the filters, it doesn't bother to change this; like, everything is still checked, right? This- that line there-- that line underneath, between value filters and search, I bet that line is the Excel team saying, "Hey, this search box is completely separate from those filters. You shouldn't expect them to behave like the filters." And I don't know why I'm defending them.

Let's just wrap-up. Alright. Sam in Nashville, filtering a PivotTable: If you used the date filters or label filters or value filters, Excel remembers the setting and will reapply the filter after a refresh; but if you're using this search box to filter, Excel will not reapply the search to refresh-- instead use the label filter for "contains", for example.

Now, hey, if you want to work along, download the workbook from today's video-- visit that URL down there in the YouTube description.

I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.