Enabling Filters by Default

Shishona wonders if there is a way for Excel to enable/set filters upon opening every workbook. She almost always uses 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 it 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).

You can also add the Filter tool to the Quick Access Toolbar. (Adding tools to the QAT has been covered in other issues of ExcelTips.) Click the Filter icon to toggle the filtering on and off.

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