Filtering Filters

How to apply filters selectively in Microsoft Excel.
Bill JelenNovember 15, 2012

Q: I don’t want to offer filter dropdowns for quantity and revenue in my customer sales sheet, because they confuse the people who use the worksheet. I want the filters to be available only on columns A:D. What’s the best way to do that?

A: You normally apply a filter by selecting the entire data set, or one cell in the data set, or the cell to the right of the last heading. Any of these methods will apply the filter dropdowns to all cells.

Drive Business Strategy and Growth

Drive Business Strategy and Growth

Learn how NetSuite Financial Management allows you to quickly and easily model what-if scenarios and generate reports.

To solve your problem, however, select cells A1:D1 before selecting the Filter icon. This will add the dropdowns to only those columns (Figure 1). Of course, if you filter by column A, it will show you only the filtered rows for all of the columns.

Caution: This trick works only on a contiguous section of the data set. If you wanted dropdowns only on columns A, B, and D, you would have to use VBA to hide the dropdown on column C (Figure 2). To hide the dropdown for column C, follow these steps:

1. Alt+F11 for VBA.
2. Ctrl+G for immediate window.
3. Type range(“C1”).AutoFilter Field:=3, VisibleDropDown:=False

It’s true: one line of VBA can hide a filter dropdown.

If you would like to submit a question to Bill “MrExcel” Jelen, e-mail him at [email protected].