Problem: I don't want to offer filter dropdowns for Quantity and Revenue. It confuses the people who use my worksheet. I only want the filters to be available on columns A:D.
Strategy: You normally apply a filter by selection 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.
Instead, select cells A1:D1 before selecting the Filter icon. This will add the dropdowns to only those columns. Of course, if you filter by column A, it will only show you the filtered rows for all of the columns.
- Filter only the text columns.
Gotcha: This trick only works on a contiguous section of the data set. If you wanted dropdowns only on Column A, B, and D, you would have to use VBA to hide the dropdown on column C. To hide the dropdown for column C, follow these steps:
- Alt+F11 for VBA.
- Ctrl+G for immediate window.
- Type range("C1").AutoFilter Field:=3, VisibleDropDown:=False
To adapt for another column, change both the "C1" and the 3 for another column. Column J would have a Field:=10.
- One line of VBA can hide a filter dropdown.