Excel 2010: New Search Filter

Exploring large data sets and creating filters has never been easier than with Excel 2010. Tables, PivotTables and PivotCharts now have a new search functionality that easily enables you to find what you need, filter and repeat for a faster more efficient way of navigating those enormous data sets.

Filtering FAST!

In Excel 2010 when you click on the dropdown for autofilters, tables, PivotTables and PivotCharts you will now find a new search interface that enable swift navigation of extremely large data sets.

Below I will be navigating the product catalog of Contoso an online retailer that stocks over 20,000 items. Suppose that what I am looking to see of a specific product I care about, let’s say a replacement for my tablet pen (always losing those) is in stock. I simply type in “tablet pen” and my PivotTable will show me all items that match “tablet pen” available the catalog and how many Contoso has of them in stock.

That was pretty easy! Only 3 mouse clicks!

Ok, so that’s great I want the grey one so I can go ahead and uncheck all the others in the filter drop down.

I also remembered that I wanted to look for car power adapters so I go back to searching the catalog and look for “car power adapter”. Once again we see the drop down has been populated with all the matches. If I went ahead and pressed [OK] my original selection would be replaced by the new items. I actually want to keep my old items so I am going to go ahead and make sure that “Add current selection to filter” is checked.

And my end results after removing the non-grey power adaptors:

More ways to use search

Let’s say I am interested in looking for LCDs. Since I am not sure exactly what I am looking for I can simply type in “LCD” search and narrow down my list to about 150. That’s a good start but still a big set. Let’s clear the filter (click on the drop down and select “Clear Filter from XXXX”) and give it another shot, this time using one of the wildcard characters in my search. The “*” character can be used as a wild card for one or more characters before , in between or after any search terms entered into the search. In this case I will use “*LCD*HDTV*” and as expected I get all LCD with HDTV capability in my search results, hitting [OK] applies the filter for me.

When looking at the results above you can see that the first three entries are for portable LCD HDTV’s which I am not really interested in. So I am going to use search to remove these. Clicking on the drop down I type in “portable” into the search box which will bring all results that contain portable in them (Note: The search is conducted on the entire data set not just the items that have been filtered on) . I click on “Add to current filter” and de-select “Select all search results” effectively removing all items that contain portable from my current filter list.

By applying this filter my end results no longer contains any portable LCDs with HDTV capability.

That was pretty quick and easy wasn’t it? The new search functionality allows you to search for virtually any character/s in your data set quickly and efficiently. The creation of meaningful filters has really never been easier.

Note for those currently testing Excel 2010 – Technical Preview

You’re not crazy, we changed the design and behavior of search after the Technical Preview was shipped so most of what I have shown today cannot be done with your current version of Excel 2010.  We received a lot of great internal feedback during the launch and wanted search to feel more like the search you find in products such as web search engines and email search functions. When searching for “Apple” and then searching again for “Pear” it makes sense that the default behavior is that the final result is all matches to “Pear” instead of all matches for “Apple” and all matches for “Pear” together. We agreed that the feature was not as intuitive and easy as it could be and therefore took the feature back to the drawing board and improved the user experience. The new search will be shipped in the next Beta release as well as in the final product.