What it does
Uses picker prompt to help expedite common filter actions (turn filtering on, apply favorite filter/sort criteria, unfilter column/all columns, filter to current cell value, filter to/exclude zeroes and/or blanks, etc.).  See the full list of options below in the Instructions section.

When to use it
When working with tabular data and you want to filter on the data, this is an easy way to quickly apply common filter criteria.

Why to use it
It’s easier and quicker than using the mouse, combining several clicks into one step, and providing options that extend the native capabilities of Excel.

Default shortcut
Ctrl+Shift+F

Other Details

• Category: Filter and Sort / Filter
• Difficulty: 2/5
• Usage/frequency: 5/5
• Automation factor: 3/5 (estimated 5 seconds saved each time used)
• Type: Shortcut
• Date added: 11/15/2017
• Tags: Filter, sort, slicer, picker

Related Macros
[A] Filter Freeze
[X] Unfilter Column
[U] Unfilter
[T] Filter This
[O] Filter Out
[P / N] Filter Previous Next
[C] Filter to Clipboard Contents
[ZX] Filter Non Zero
[Z] Filter Zero
[BX] Filter Non Blank
[B] Filter Blank
[BZX] Filter Non Blank Zero
[R] Filter To Errors
[E] Extend Filter Range
[D] Apply Filter Sort Size
[L] Insert Slicer
[K] Bulk Filter
[S] Quick Sort
[WH] Hide AutoFilter Arrows
[WS] Show AutoFilter Arrows
Copy Special Picker
Assign Macro Defaults

Note: most of the related filter macros above will be covered in the instructions below.  The picker option code is displayed in the [brackets].

Other Articles
None

None

Prerequisites
Options A, S, E, and D: Select the entire range that should have the filter or sort applied.
Options X, U, T, O, P, N, C, Z, ZX, B, BX, BZX, R, and L: Select one or more cell(s) within a range that has AutoFilter applied.
Options WH / WS: select one or more cells within the columns for which you want to hide/show the AutoFilter arrows.
Option C: Copy the value you want to filter to (multiple values must be separated with the pipe “|” character)

Instructions
With cells selected per the prerequisites above, run the Filter Picker macro.  It will present several common actions to run for filtering and sorting.  For the action you run most often, you can set your default so you don’t have to enter it (just launch the picker and press Enter).  Note that the default is highlighted when the picker is launched, so to change the action code, there is no need to press the backspace first.  Further instructions for the individual action codes are described below.

• [A] This will activate AutoFilter (or change the range it is applied to, if was already activated), freeze panes for the top selected row, and prompt the user whether to auto-fit the selected column widths (default is Yes).
• [D] This will display a prompt where you can select one of five custom settings of filter, sort, and column width values for data sets where you apply those settings often.  You can even pick the default custom option (1/2/3/4/5).  To save a favorite, set the filter criteria, sort criteria, and column widths manually, run the Filter Picker – option D, press Enter, and follow the instructions in the next prompt when applying option X.  See the video below for this in action around the XX:XX mark.  
• [X] This will clear the filter criteria in the selected column only.
• [U] This will clear the filter criteria in all columns of the AutoFilter range.
• [P] This will filter to the previous value in the list of unique potential filter values.  If multiple filter values are in place, it is based on the value of the top selected cell in the column.  So if unique values are apple, banana, coconut, and dragonfruit and banana is the selected value, the values would filter to “apple.”
• [T] This will filter to one or more selected values in a column.  If multiple columns are selected, the first column’s value(s) are used.
• [TX] This will filter just like option [T] above, but will also clear the existing filters in all columns beforehand.
• [O] This will filter out one or more selected values in a column.  If multiple columns are selected, the first column’s value(s) are used.  This can save a lot of clicking if there are many unique values in the list.
• [N] This will filter to the next value in the list of unique potential filter values.  If multiple filter values are in place, it is based on the value of the top selected cell in the column.  So if unique values are apple, banana, coconut, and dragonfruit and banana is the selected value, the values would filter to “coconut.”
• [M] This will filter numbers or dates based on the operators and values you supply.   Valid operators are = (equals), <> (does not equal), < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), and B (between).  To use this option, enter M, then a space, then the operator, then the value (i.e. “M >5000” would filter to all values greater than 5000).  For option B (between), enter M, then a space, then the low value, then a < symbol, then the high value (i.e. “B 1000<5000” would filter to all values between 1000 and 5000).
• [C] This will filter values in the selected column to value(s) in the clipboard.  Multiple clipboard must be separated by the pipe “|” character.  To easily copy unformatted values to the clipboard, use the Copy Special Picker, option D and add the pipe “|” character.
• [Z] This will filter the selected column to values of zero.
• [ZX] This will filter the selected column to values that are not zero.
• [B] This will filter the selected column to values that are blank.
• [BX] This will filter the selected column to values that are not blank.
• [BZX] This will filter the selected column to values that are not zero or blank.
• [F] This will filter the selected column to cells with the same fill color (will only fill to one fill color at a time). 
• [E] This will extend the filter range to include additional columns and/or rows.  It will present a prompt giving options of extending columns, rows, or both, including the existing range address and the proposed new range addresses.
• [R] This will filter to value errors such as #N/A, #VALUE, and #NAME.
• [S] This will launch the prompt to quickly apply one or more sort levels on the AutoFilter range with a quick enter method, as well as apply five favorite sort criteria for commonly applied sorting values.  For quick entry, enter the column letter, an underscore, and A (for ascending) or Z (for descending).  For multiple sort levels, separate them with a comma.  For example, “D_A,E_Z,A_A” would sort column D ascending, then column E descending, then column A ascending.  To save a favorite sort, after that string, add a space and the word “DEFAULT” then a 1/2/3/4/5 for the favorite shortcut number.  For example “D_A,E_Z,A_A DEFAULT1” would save that as favorite sort #1.
• [L] This will add a slicer for the first selected column.  Slicers provide a nice visual representation of the filter(s) applied on data.  They are very useful with PivotTables and when presenting data to ensure people understand what they are seeing.  Note that slicers require the data to be in a table (or PivotTable), so if the AutoFilter range is not a table, you will be prompted to convert the data range to a table.
• [K] This is based on a settings sheet and can apply multiple different filter criteria across multiple sheets.  See the [Bulk Filter] support page for instructions on how to use it.
• [W] This will hide [WH] or show [WS] the AutoFilter arrows for the columns that are selected (or cells within those columns) when it is run.  Generally users will hide AutoFilter arrows when they want to copy the range as an image into an email, PowerPoint file, etc. but not display the arrows.

Like most of the picker macros, you can set the default option to apply so you don’t even have to enter the letter/number – just the Enter key.  Just enter your preferred default letter(s), then click the default checkbox, and those letter(s) becomes the new default.