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

Wrapping Text Around a Text Box or Frame

Text boxes and frames can be used for all sorts of information and objects in a document. You can wrap text around the text ...

Discover More

Deriving a Secant and Cosecant

Two rather common trigonometric functions are secants and cosecants. Excel doesn't provide functions to calculate these, but ...

Discover More

Automatically Entering a Data Entry Time

Excel worksheets can be used to keep track of all sorts of information. You may want to use it, for instance, to track ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Performing Calculations while Filtering

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

Discover More

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

Using a Filtered Value in a Formula

Accessing filtering criteria for use in a formula can be a real need for some worksheet designs. Getting to that information ...

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. 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 six less than 6?

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.