You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
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.
>We received a lot of great internal feedback
Only internal feedback ? Didn't you get any good feedback from Technical Preview users ?
I also liked the fact that when you have more than 10000 unique items, the Filter drop down gives a helpful message which says "Not all items showing"
The fact that Excel has removed the limit of 8192 areas is also a big plus for handling large data.
Its small things like this that give us hope...that the excel team is not completely controlled by the Office UI astronauts
@ Mike Walsh
We have definitely received tons of great feedback from Technical Preview users. For this particular feature we were already re-designing at the time Technical Preview released to external users.
I hope you find the feature easy and useful.
Regarding Filters, It would be a better option if you make it similar to the page field of a pivot.
So if I want to currently filter and see one product It takes 3 clicks (Filter, Unselect All, Select the one to filter)
If you make it similar to the page field(all check boxes unticked), I can filter a single item with fewer clicks)
If I colour the items in the Pivot table report, Can I filter all those items. eg. similar colour filtering features in the excel table.
We definitely considered that design but decided that the ability to find matches, hit enter and have the filter applied was a quicker experience overall.
At this time filtering by color is not available for PivotTables.
Do you plan to apply these filtering/sorting/searching features to the pivot sliders at some point? I was disappointed to find that they're more limited than pivot filters, row or column labels.
It is normal that in excel 2007, the filter drop down list show 32000 items and in excel 2010, it will show only 10000 ?
None of the images on this blog are getting displayed! Please fix.
Images did not open. Site was of little value without them.