Enabling Filters by Default

by Allen Wyatt
(last updated March 29, 2014)

4

Shishona wonders if there is a way for Excel to enable/set filters upon opening every workbook. She almost always use filters for her worksheets and doesn't believe there would be any harm in having filters on for workbooks where she doesn't use filters. Rather then enable filters each time she opens a workbook, she'd like them to already be on.

The answer, as you might guess, depends on exactly what is meant by "default." Excel won't allow you to have filters turned on if a worksheet contains no data (since there is nothing to filter), so "default" cannot mean new, blank workbooks.

Since filters can only be enabled on worksheets that contain data, it is possible to create a "default workbook" that contains data and has the filters turned on. Then save that workbook in a special place on your system so it can be used as the default template for any new workbooks you create. How you do this has been covered in other issues of ExcelTips.)

Of course, taking that approach may be more trouble than it is worth, because you would probably need to delete the default filtered data after creating a new workbook, which means that the filtering would be removed anyway. It is best, therefore, to not mess with the default workbook approach.

Instead, you may mean that you want the filtering enabled, automatically, when you open an existing workbook. There is no way to do this automatically in Excel, but you may not need to. The reason is because when you save a workbook, Excel remembers which of your data has filters applied to it. Thus, if you create a worksheet and turn on filtering, and then save the workbook, the next time you open it, the filters on that data will still be turned on.

You could also make sure the data in your worksheets is defined as a formal data table. All you need to do is select a cell in the data and press Ctrl+T or display the Insert tab of the ribbon and click the Table tool. Once the data table is set up, Excel keeps the filters for that data table turned on unless you convert it back from a data table to regular data.

If you are worried that a filter will get turned off (or not turned on at all) when saving the workbook, you can add a macro to the mix to step through the worksheets and turn on filtering. The following example does that, ignoring any worksheets that may already have filtering turned on.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
  Cancel As Boolean)
    Dim ws As Worksheet

    On Error Resume Next ' Handles worksheets with no data
    For Each ws In ThisWorkbook.Worksheets
        If ws.AutoFilter Is Nothing Then
            ' Only affect worksheets with filtering turned off
            ws.Rows(ws.UsedRange.Row).AutoFilter
        End If
    Next ws
    On Error GoTo 0
End Sub

This macro should be added to the ThisWorkbook object in the Visual Basic Editor. It will execute every time the workbook is closed, thereby ensuring that filtering is turned on for every worksheet. Since the filtering state is saved with the workbook, the filters will be in place the next time the workbook is opened.

Of course, you might not want a macro to be associated with each of your workbooks. In such a situation may simply be easier to remember a quick little keyboard shortcut: Ctrl+Shift+L. This shortcut toggles the state of the filtering on the data surrounding the currently selected cell. Thus, you could select a cell, press Ctrl+Shift+L, and filtering is turned on. Press it again, and filtering is turned off. The shortcut is extremely fast and allows you to easily filter your data (or not).

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13037) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Quickly Customizing the Keyboard

Want a quick way to change the shortcut key associated with a tool available on a ribbon, toolbar, or menu? Here's one ...

Discover More

Deleting a File in a Macro

Macros give you a great deal of control over creating, finding, renaming, and deleting files. This tip focuses on this last ...

Discover More

Finding Quoted Text in VBA

Macros are created for all sorts of purposes in creating, editing, and processing documents. You might want to use a macro to ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Filtering for Purchases within a Given Month

Filtering is a great tool when dealing with large data sets. Knowing how to apply a filter, though, can be a bit tricky at ...

Discover More

Performing Calculations while Filtering

The advanced filtering capabilities of Excel allow you to easily perform comparisons and calculations while doing the ...

Discover More

Column Formatting Based On a Filter

When working with filtered data, you may want to specially format a column that has a filter applied to it. Here are a couple ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 0 + 7?

2014-03-31 08:48:00

Michael (Micky) Avidan

This is exactly a situation the "Quick access Toolbar" was designed for.
Add the 'Filter' icon to that QAT and you are all set.
(The icon toggles the auto-Filter On & Off).
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2014-03-31 02:31:30

Rudra Sharma

If you are using Excel 2007 or above, Ctrl + Shift + L does the work.

With Regards
Rudra


2014-03-30 13:48:05

Greg

If I have a large amount of data to which I have applied filters in multiple columns, I know I'll "never get back there" so I create a Custom View. One Normal view showing everything and then Custom views using the checkbox to retain filters hidden rows and columns. Then it's easy to get back (two weeks later)) to the results.


2014-03-29 07:25:02

Colin

1. Create a Defined Name (say "MyFilters") that refers to the row of headings in the data table to be filtered.
2. Add the following macro to the ThisWorkbook object of the project in the Visual Basic Editor:

Private Sub Workbook_Open()
' This macro turns off any existing AutoFilters and then applies new autofilters to the specified range.

Dim rng As Range
Set rng = Range("MyFilters")

With rng.parent
.AutoFilterMode = False
.rng.AutoFilter
End With

End Sub


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.