In April, Bill Jelen and I put on the acclaimed DataPig Power Analyst Bootcamp in Chicago. I always love our live events. I get to practice my stand-up routines; Bill gets to pass out his surplus Mr. Excel crap, and the audience gets fresh new Excel techniques that make them look good. It’s a lot like Woodstock without the talent or the sex.
I inevitably come away from every event learning something new from our audience. At this event I learned about the “Filter on Selection” functionality in Excel.
Anyone who has used Access will be familiar with Filter on Selection. Essentially, it’s a functionality that allows you to apply a filter to your table using the value in the active cell as the filter criteria.
In Excel, you do have AutoFilter, but you must select your filter criteria from a dropdown list. There is no way to apply a filter automatically based on the value of the cell you are currently on – so I always thought.
It turns out there is Filter on Selection functionality in Excel. It’s just hidden very well. The key is to find the “AutoFilter” command.
Finding Filter on Selection in Excel 2007
In Excel 2007, click on your Quick Access Toolbar and choose “More Commands…”
In the ‘Choose commands from’ dropdown, select ‘Commands Not in the Ribbon’, and then find Autofilter. Add it to your Quick Access Toolbar.
Finding Filter on Selection in Excel 2003
In Excel 2003, Click Tools -> Customize.
Find the Autofilter command under the Data Category, then drag the command to any of the toolbars you have open at the top of the Excel window. Close the Customize Window.
Using Filter on Selection
Ok – so you’re thinking this command you just added to your toolbar is the same as the AutoFilter command you use every day in Excel. It’s not! That’s the why this feature is so hard to find. If this command were to have a name like…oh I don’t know…FILTER ON SELECTION, maybe more people would find it.
I personally refer to this as the Filter on Selection Icon.
Here’s how you use it. Click on any cell in a table, and then click the Filter on Selection icon in the toolbar. Excel will automatically apply a filter using the value in that cell.
In this screenshot, I click on a cell with 10-Feb-04 then click the Filter on Selection icon. Excel immediately applied a filter to only show those records with 10-Feb-04.
And just like Access, these filters are cumulative. In this screenshot, you’ll see that I added another filter simply placing my cursor in another cell and clicking the Filter on Selection icon again.
Rumor has it that this feature has been in Excel for so long without notice that the current Excel team didn’t know it was there until just recently. It only came to their attention when documenting features for Excel 2007.
RELATED STUFF
The feature’s there in Excel 2000.
As you say, odd that it’s not on the >Data >Filter menu at least.
Excellent tip – I especially like the fact that it’s cumulative
Note that Excel 2007 has done a better job than earlier versions in exposing this feature. Right-click the selection and choose Filter–>Filter by Selected Cell’s Value from the contextual menu.
Colin: Well I’ll be…I never noticed that. Great Tip.
…and don’t forget the equally useful ‘Show All’ just below in the command list there..! I have these two buttons on my ‘useful tools’ toolbar.
Now if only i could make it clearer which columns had filters active – rather than an easy-to-miss blue triangle!
“Now if only i could make it clearer which columns had filters active – rather than an easy-to-miss blue triangle!”
That’s probably why they’ve added a funnel icon next to the filter arrow in Excel 2007.
thanks Mike & Colin!
“..funnel icon next to the filter arrow in Excel 2007…”
Oh yes! I’m stuck on 2002 for the time being…
Like Colin say it is in 2007
See also this VBA tip
http://msdn.microsoft.com/en-us/library/cc952296(office.11).aspx
SWEEEEEEEEEEEEEEEEET!
Thanks again for the awesome/useful tip!
Since we’re on the topic of filters, and pertaining to date filters in Excel 2007, I don’t understand why there’s not a “Last xx years” or “Last xx months” filter. Virtually every project I work on that include historical numbers requires filtering the last 1 year, last 2 years, etc. I use this filter more than all of other date filters combined. I’m not too worried about this presently since I extract the relevant data via an SQL query or stored procedure. However, with Gemini looming, it would be useful to select this filter from a drop-down list.
Another thing – the stupid Year-to-Date filter doesn’t have an option to exclude the current month. If I generate a year to date report on July 2nd, what point would there be to include the first two days of July?
@ Mike:
Thanks for this. Truly awesome.
@Colin:
I’m not positive, but I think that you can work pivot tables to more easily filter the dates. I also could be very wrong.
The date filters are the same for PivotTables.
@Colin:
I tried to remember where I have seen this done, and found this http://blog.contextures.com/archives/2009/06/08/filter-a-pivot-table-for-a-date-range/ but it wasn’t really what you are looking for.
One thing you could do is at the top of your pivot table add a few cells that let you input beginning date and end date of the period. Back in your source data add a column that determines if the date is within the desired period. After that you now have a new column you can use to filter, and you can change the range easily by changing the date range and refreshing the pivot table.
Would that work for you?
Jayson, I simply code the last xx years (or months) into the criteria expression of an SQL script. The advantage is that I bring in only the data I need into Excel.
However, you’ve given me an idea for times where filtering a larger data set might be useful. I can create a calculated field in the SQL query (or in an Excel Table) that puts a Yes/No Boolean in the field if the date in the date field lies between the month prior to the current date and 12 months before that (Yes indicates that the date lies within the required range). This way the field will adjust automatically as the date changes. In a PivotTable, the calculated field will be used as a page field, filtered for “Yes.” Refreshing the PivotTable will automatically filter the appropriate data.
Thanks for the suggestion.
Under Insert, Illustrations and Charts is not accessible! (its greyed out) All other ribbon functions are working. Help!
Alex you are god’s gift to excel world
Hi,
In Excel 2003, this trick does not work when the range is converted to a List (Ctrl+L). In fact when the range is converted to a list and you apply the trick mentieond above, the auto filter icons on the list goes away.
Therefore, this may answer someone’s question of how can i remove the auto filter icon when i have converted a range to a list in Excel 2003.
Hi,
Here is a VBA code that does excatly the same (AutoFilter from selection) that you can asociate to a Button on a sheet or a shortcut as any other macro.
Sub Auto_Filter()
Dim FieldN As Integer
FieldN = ActiveCell.Column
Selection.AutoFilter Field:=FieldN, Criteria1:=ActiveCell.Value
End Sub
Best Regards
GjayjayG : Very nice. Thanks for this.
Hi again,
Don’t forget to specify that the filter have to start in the Column A else you would have to decrement FiledN with the offset number from wich column the filter start from.
ie: if your filter start from column C (offset of -2)
FieldN = ActiveCell.Column – 2
Best Regards
‘Filter on Active Cell’ may be a more accurate name as this command won’t filter on more than one cell at a time. I’ve been using this trick definitely as far back as 2000, maybe even 97.
In 2007, I have added 5 filtering tools – Clear, Reapply, Filter, Advanced, AutoFilter (i.e. filter on selection) to my QAT (below the Ribbon is my preference). Oh, and don’t forget to add the Select Current Region (CTRL+SHIFT+8) and Select Visible Cells (ALT+;) commands also. They make working with lists soooo much easier.
The Filter and AutoFilter commands look identical, however the Filter command just applies the AutoFilter arrows to the current region. If the cell you have selected is in the first row of your list, the AutoFilter command acts just like the Filter command and simply adds the AutoFilter arrows to the current selection. But using the AutoFilter command for instant ‘filter on active cell’ is a must if you work with lists.
GjayaG,
Rather than subtract a harcoded value, be dynamic
FieldN = ActiveCell.Column – Selection.Cells(1,1).Column + 1
Thanks a lot for sharing this with all of us you actually know what you are talking about! Bookmarked. Kindly also visit my website =). We could have a link exchange agreement between us!