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

Shifting Margins Evident in Word 2002

When you open a document in one version of Word and compare it to what you see for the same document in a different version ...

Discover More

Triple-Spacing Your Document

Print your document with lots of space between each line—triple space it! Here's some quick and easy steps for getting ...

Discover More

Creating a String

Need to use a macro to create a text string? One easy way to do it is to use the String function, described in this tip.

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)

Copying the Results of Filtering

Filtering is a great asset when you need to get a handle on a subset of your data. Excel even makes it easy to copy the ...

Discover More

Counting Filtered Rows

The filtering capabilities of Excel are indispensable when working with large sets of data. When you create a filtered list, ...

Discover More

Skipping Rows when Filling

Using the fill handle is a great way to quickly fill a range of cells with values. Sometimes, however, the way to fill cells ...

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 for this tip:

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. 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 two more than 9?

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.

Links and Sharing