Excel: Filter Only Some Columns

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

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

  2. One line of VBA can hide a filter dropdown.