• Excel Book Excerpt

Excel Filter Only Some Columns

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

Filter Only Some Columns

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.

LE10000652.jpg 

Figure 629 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.

LE10000653.jpg 

Figure 630 One line of VBA can hide a filter dropdown.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: