Filter Only Some Columns

September 13, 2022 - by Bill Jelen

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.

Only some columns in the data set have Drop-Down icons. — Figure 683. 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:

1. Alt+F11 for VBA.
2. Ctrl+G for immediate window.
3. 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.

After running the VBA to hide the filter on column C, you see drop-downs on A, B, and D. — Figure 684. One line of VBA can hide a filter dropdown.

This article is an excerpt from Power Excel With MrExcel

Title photo by Michael Held on Unsplash