Tables Part 4: AutoFilter improvements: much more than just multi-select …

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

  • Enabled multi-select in AutoFilter, so you can select any number of items for your filter condition
  • Added the ability to sort and filter by colour 
  • Increased the limit of items in the AutoFilter dropdown from 1,000 items to 10,000 items

Additionally, we have

  • Added a “quick filter” feature that enables data-type-specific filtering
  • Added date grouping to date AutoFilters
  • Made it possible to re-apply a set of filters with one button click
  • Provided more UI to help users figure out what filters are applied to a range/table

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.

(Click to enlarge)

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.

(Click to enlarge)

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. 

(Click to enlarge)

In the spirit of completeness, Excel 12 also provides text-based filters for working with non-numeric columns.

(Click to enlarge)

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.

(Click to enlarge)

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.

(Click to enlarge)

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

As in previous versions, a sort or filter is only applied at the time the sort or filter is created (or a query is refreshed if the table is connected to external data).  This is necessary so that data doesn’t shift around or “disappear” as you are editing it.  Of course there will be times when a sort or filter becomes stale and needs to be reapplied, like after you have copied and pasted a bunch of new records to the bottom of a table or range.  Excel 12 makes it easy to reapply all sort and filter conditions on a table via a single click of a ribbon button.

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.