MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table Search Bug


May 31, 2018 - by Bill Jelen

Pivot Table Search Bug

An interesting problem from Sam in Nashville: Sam wonders why his pivot table does not correctly refresh after he uses the Search box in the Filter drop-downs in his pivot table.

Say that you have a pivot table. You filter the Row field by using Date Filter, Label Filter, or Value Filter. Change the underlying data. Press Alt + F5 to Refresh the pivot table. The filters that you selected will be re-applied.

Filters will re-apply after a Refresh
Filters will re-apply after a Refresh

Note

Did you catch that hot keyboard shortcut? Alt + F5 is a fast way to refresh a pivot table. Ctrl + Alt + F5 will refresh all pivot tables. Thanks to Wendy in Row 3 of my Power Excel seminar in Calumet City for this awesome trick. Also thanks to MF Wong on Twitter for the Ctrl + Alt + F5 tip.

But the problem that Sam is encountering is when he uses the Search box to apply a filter. In the image below, use the Search box to find all customers with Wand in the name.


Search for all Wand customers
Search for all Wand customers

The resulting pivot table shows the correct customers.

Three customers with wand selected
Three customers with wand selected
Alt+F5 is a quick way to refresh a pivot table

Change the underlying data to add a new customer that should match the filter: The Wanderer.

This customer should match the filter.
This customer should match the filter.

Refresh the pivot table using Alt + F5. The new customer is not added to the pivot table.

As I note in the video below, the horizontal line between Value Filter and the Search box seems to be telling us something. Anything above the line is reflected in the Refresh. Anything below the line will not be re-applied during the Refresh.

The solution: Do not use the Search box if you plan on refreshing the pivot table. Instead, use Label Filters, Contains…

Use Contains to perform the Search
Use Contains to perform the Search

Specify wand as the search term:

Ask for all customers with 'wand' in the name.
Ask for all customers with 'wand' in the name.

Change the underlying data. Refresh the pivot table. The new customers appear.

After a refresh, the customers appear.
After a refresh, the customers appear.

Watch Video

Video Transcript

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.

Download Excel File

To download the excel file: pivot-table-search-bug.xlsx

It seems frustrating that Label filters will refresh, but the far-more-visible Search filters do not refresh. However, by using the Label filters and Contains, you can set up a rule which will persist through a Refresh.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"DATEDIF documentation is to Excel what Windows 8 was to anything"

Title Photo: NICK SELIVERSTOV on Unsplash


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.