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.
Sorting and filtering are two of the most important types of basic analysis that you can do with data. In Excel 12, we have improved sort and filter functionality to better expose common tasks, to make key tasks simple, and to enable scenarios that were not possible in earlier versions. We have done work in AutoFilter, our sort functionality (Data|Sort), and in PivotTables. I will cover the first two (AutoFilter and Sort) in posts this week and PivotTables in a few weeks when I review all our PivotTable work. Today, I will focus on AutoFilter.
Several of our goals for AutoFilter were driven by a couple of our top customer requests. Specifically, in Excel 12 we have
Additionally, we have
Let’s take a closer look. The first thing we tried to do was to make it easier to turn on AutoFilter by making it part of the “Sort & Filter” commands on the Sheet tab in the Excel ribbon (the tab that is shown by default).
Sort & Filter Chunk
If you are a user of AutoFilter today, one of the first things you will notice is that we are now referring to this functionality as “Filter”. (You may also notice that "Sort Descending" should say "Sort Z to A" - that's a bug in current builds.) We did a lot of usability work in this area, and we determined that users that had used AutoFilter before had no trouble figuring out the new name, while users that had never use AutoFilter before were much more likely to understand and try the feature when it was referred to as “Filter”. (For the duration of this post, I will refer to the feature as the Filter feature). Once you have turned on Filter, the next thing that a current user of AutoFilter will notice is that the interface has been completely revamped.
(Click to enlarge)
Sort options remain at the top of the dropdown, but we have updated the text to reflect the data being filtered (“smallest to largest” for numbers, “oldest to newest” for dates, etc.). We have added the ability to sort by colour (more on that later). We’ve added a way to quickly and easily remove all filter conditions from a single column. Below that we have some filter options (more on that in a moment) and finally we have the filter items themselves.
Let’s look at filtering in more detail, starting with multi-select. In previous versions of Excel, if you wanted to multiple-select items, you needed to use the Custom dialog, and that limited you to two choices. In Excel 12, you can simply select the items you want to see included in your filter and press OK. This is much faster, easier to discover, and supports as many items as you want.
Excel 12 makes it possible to express more complex filtering conditions than just clicking individual items. It does this by providing filtering options based on the data type of your column – we are currently referring to this as “quick filters”, but we may come up with a better name by the time we ship. Say, for example, I’m looking at a record of sales for the past couple years and I want to see how much revenue I made last month and which sales brought in the most money. By clicking on the filter dropdown on my date column I’m presented with a large list of date filters, among which is the option to filter records to last month.
As you can see from the picture above, Excel offers an array of date filters that make filtering by different date ranges a snap. What makes these filters special is that they are based on the system clock so my “last month” filter will always filter to the previous month when the filter is reapplied. Setting up these types of dynamic filters were not easily possible in previous versions.
We have also added “quick filters” for numeric data. For example, after I filter by date, I can then filter my profit column to only show me sales that were above average.
In the spirit of completeness, Excel 12 also provides text-based filters for working with non-numeric columns.
Another feature for date-based columns is that the filter dropdown groups dates by day/month/year rather than displaying a flat list of dates so that it’s easier to drill-down and pick a specific series of dates. If you wanted to select all of the dates in a particular month for instance, you can do so in two clicks.
In Excel 12, we have gone beyond the ability to filter on values in cells. We now also support the ability to filter by colour color. For example, if I have a table of data I’m working with and I’m using orange and red fill as a way to mark rows that need special attention, I may want to just filter to see everything I have marked red. To set that up is just a few clicks.
Filter by colour allows you to filter by font colour as well as cell fill colour. In addition, it also recognizes conditional formats on cells including regular formatting, gradient fills, and conditional formatting icon sets (which I discussed in previous posts here and here).
In the same way that we have enabled filtering by colour, we have enabled sorting by colour which, predictably enough, allows you to sort your data based on cell or background colour. I will run through an example of that in the next post.
Excel 12 makes it a bit easier to notice when a table has been filtered or sorted as well as easier to figure out what the sort or filter is. For any column that has a filter set, Excel changes the filter dropdown icon to denote its filter state. The same thing happens, although a different icon is used, for columns that have been sorted. In the screenshot below, you can spot which columns were used to sort and filter the table. In addition, you can get more information by hovering the mouse over the icon ... Excel will show a tooltop that describes the sort and filter state of the column.
Filter buttons and tooltip
Reapply Command on the Ribbon
As you can see from the screenshot above, it also takes a single click to remove all filters from all columns in a table.
The last thing I want to mention before closing is that everything I have discussed here can be used without the table feature, however there are certain advantages to using them in conjunction with tables. Each table has its own AutoFilter whereas the sheet can only have a single AutoFilter, so if you need to filter more than one dataset on a sheet, than tables are your only option. Similarly, tables also remember their own sort conditions, so if you need the ability to maintain multiple sort states across a sheet than tables will do the trick.
Next time, more on sorting.
Comments: (16) Collapse
This should be useful.
There's one small part of this that strikes me as counterintuitive: using a square in the filter items to mean partial selection when a check mark means selected and an empty box means not selected. I understand interfaces change, but a light grey checkbox background with a dark grey checkmark would be more in keeping with the current interface. The new square fills in more of the checkbox than the checkmark, so it seems odd that it means less of a selection.
David,
Does the act of a user selecting a filter trigger any events in excel 12?
This is great stuff, especially the possibility
|to filter more than one dataset on a sheet,..
I was wondering how will it work if 2 different datasets share the same row numbers? It would not make sense that they mutually filtered each other by hiding rows. So how will this example behave?
I find it practical to use the subtotal function on filtered rows. For example, in conjunction with counta function, I can calculate the % of visible records. Will this still be possible on filtered table?
Will custom views work with table filter?
When you have different users that can have a different look at the same data, custom views are very useful. Meanwhile, if there are many different users you can end up having many different custom views that are more user’s specific. Would it be possible to assign a “category” at the creation of a custom view. You could then flag (filter) witch categories of custom views you want to see in the drop down list.
Thank's
Jean
Will there be a quick method of detecting (and deleting) duplicates. Often I have merged worksheets from different sources, and then add a countif in a parallel column to count the number of occurences, then sort on the count, so that duplicates appear with a count of 2 or more are at the top of the list. Then can manually delete the duplicates. Alternatively a simple macro does the job? A "find duplicates" (with optional delete) duplicates in one or more columns
Possibly some of the most useful features yet for the average user, excellent stuff! I would also like to echo Jean's query about Table conflicts in row filtering.
Also, in the table reference keywords/tokens is there a subset of ther #Data reference like #Visible or #Hidden? This would really beef up the scope of some of the functions and resulting formulas?
Presently the AutoFilter cannot cope with recognizing a CustomNumberFormat when building a list in the custom filter dialog. I mean by that that the number 12345678 in a cell with the CustomNumberFormat "#,,.000" appears in the filterlist dropdown as 12.346 . If I then ask it to filter numbers greater than this amount then it appears to interpret this value as text and not a number thus only filtering out text values in the list. Has this been fixed?
"filter dropdown groups dates by day/month/year" - no weeks ? Being able to filter on the two first weeks of a month, by example, would be useful.
Also, please, please, please add the ability to filter on styles !
Jean-Marc
I sure Advanced Filter did not disappear, David, but I didn't see an access point in any of the screen shots, unless it's under the 'Custom' selection. Has work been done on Advanced Filter?
I like what you did with date fields - rather than list every date in the drop down you display 'bands' by month/year. It has always bothered me that number fields like 'Sales' list each individual amount when it's clear you'd never filter by, say, $1,234.56.
Of course it's only 'clear' when you know what the data in that field represents. If a number field represents a month then you would want to be able to filter by it. So maybe there is no way to know when it makes sense to list each unique item and when not. But if there are 1000 rows and 997 are unique that's a clue. Maybe some ratio of uniques to record count?
So when this ratio is low, so that you're confident you have a 'Sales' like field, what do you do instead of listing each item? Some kind of banding like with dates perhaps. There is nothing quite as obvious as 'month' to band by but ranges could be constructed I suppose. Or maybe even nothing (that's easy!), knowing that Custom is available if you really did want to filter by a specific amount.
Wow, Dave. This is great stuff! I love having all those date, text and number filters.
These additions will be very useful.
I have one suggestion though, an inverse selction option...When working with this select-in/select-out checkbox structure in PivotTables, I often find that I forget to uncheck the select all box before I start scrolling down through the list. So when I finally scroll down to the one thing that I really want to zero in on I realize I forgot to un-check everything and thus have to start over. It would be very useful to have a right-click option that allowed me to "Select only" at each checkbox.
This would apply to Filters and PivotTables
Hi folks – thanks for the comments and suggestions.
Graham - We haven’t added any events for Excel 12, so you’ll only get the events that you did in Excel 2003 when you filter (only a calculation event if cells depend upon the filtered out rows).
Jean – If two tables share the same rows, things will work as it does in Excel 2003 - the last filter applied to set of rows wins. So if I have two tables – Table1 and Table2 – that share rows and I apply a filter to Table1, when I apply a filter to Table2, the result is the same as if I had not filtered Table1 to begin with. Yes, calculating percent visible will still work, as will custom views. We have not made any changes to custom views, but thanks for the suggestion.
SteveA – You bet. Remove duplicates post coming later this week.
Nigel – We did not add a Visible or Hidden keyword, but it is an interesting idea. We have not made any changes to the Custom Number Format behaviour you describe.
Jean-Marc – Thanks for the feedback. In the next few days I will expand on table styles – I would love to hear your thoughts about filtering after that post.
Jim – No, Advanced Filter did not disappear (nor has it changed). The access point is on the Data ribbon – at some point, probably not for a while till things are more stable in our UI, I will have a post on each ribbon and where the commands are. Also, we considered having grouping strategies for other types of values as well (which would address the problem of showing every unique value in a column that is basically all unique values), but we decided to only include date grouping for this version. Thanks for the suggestion.
Something I just thought of when there are multiple tables with filters in the same worksheet. If Table1 begins in row 1 and Table2 begins in row 5, a filter is applied to Table1 which hides row 5. Presumably this would make it impossible to filter Table2 until row 5 becomes visible again. Or would XL12 be smart enough to unhide Table2's header row when the user moves the ActiveCell into Table2? Yes, that is what I would consider the most sensible behavior when there are multiple tables in the same worksheet.
Hi Harlan - the header row is indeed hidden. I suspect that most users will have one table per sheet, but that is currently just my thinking - beta feedback will help here.
I like the idea that active filters should be related to the ActiveCell. If a
| user moves the ActiveCell into Table2
the filters that were last applied to Table2 should automatically reappear. This would remove the other filters temporally. Meanwhile, if we move the ActiveCell where there is no filters associated, we should not lose the last applied filters.
Double-clicking a checked item should inverse the state of all check boxes, making it much easier to filter everything but xxx
Comments: (loading) Collapse