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.

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].

, , ,

Leave a Reply

Your email address will not be published. Required fields are marked *