Auto filter - custom filter options

Hello Amy

Thank you for your question.

As far as I know, you can't save something like this as part of the filter dropdown list options itself.

I can make two suggestions:

1. Once you have filtered your data using the custom filter, save the results as a custom view (note you can also save things like header/footer settings, print settings as part of your custom view as well, if that is useful). To do this View > Custom Views > click Add and enter a name for your custom view > click OK.

Then for easy access, add a dropdown window that allows you to select your custom view to a toolbar. To do this: Tools > Customise > Commands tab, select View from Categories on the left. Select Custom views with the dropdown window from the right under Commands. Drag and drop the dropdown window onto a toolbar at the top of the screen. Then the custom view can be selected from the dropdown arrow.

2. Create a macro, where you are recording yourself applying the filtered; which can be 'played' whenever you like to apply the filter. To create a macro: Tools > Macro > Record New Macro. Enter a name for the macro (with no spaces in the name). Click OK. Apply the filter; then Tools > Macro > Stop Recording.

You can create a button to play the macro using the Forms toolbar (View > Toolbars > Forms); select the button icon, click and drag on your spreadsheet to draw a button. Then select the macro to attach to the button. Highlight the default text on the button and type your own wording onto it.

We cover both Custom Views and Macros as part of our Excel Advanced course.

I hope this helps.
Amanda

Kind regards
Amanda