by Allen Wyatt
(last updated November 27, 2017)
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Filtering is a great tool when dealing with large data sets. Knowing how to apply a filter, though, can be a bit tricky ...Discover More
Using the fill handle is a great way to quickly fill a range of cells with values. Sometimes, however, the way to fill ...Discover More
When you use Excel to input and store information, you need to be concerned with whether the information meets your ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.