Filtering Tricks in Excel

Excel’s Filtering feature (formally called AutoFilter) is quick and easy to use, but it doesn’t always provide enough solutions to your filtering needs.

In the data below we want to filter so that we only see the Sales Reps with any score of zero. John in row 4 is the only one that has more than zero in all scores.

Filter_1
Normal filtering won’t let you do that. You have two options.

  • Add a column with a formula that determines if the row should be shown
  • Use an Advanced Filter.

Add a Column

A formula for cell E2 (see below) that can be copied down is

=IF(COUNTIF(B2:D2,0)>0,"Show","Hide")

You can then use a filter on column E and filter for Show – see below. You can use Ctrl + Shift + L to add filter icons to the table.

Filter_2

Advanced Filter

Advanced filters can take a little practice to get used to. An Advanced Filter needs a criteria range that specifies the filter to apply. The criteria range can be in another sheet.

The image below shows the criteria range and criteria we need to achieve the result we want. This is in a separate sheet called AF.

Filter_3

When criteria are shown on separate rows, as ours are in rows 2, 3 and 4; it instructs Excel to perform an OR filter. This means that we want to see the rows that have a zero in Column B OR a zero in column C OR a zero in column D. When you apply a normal Filter you can only apply an AND filter which means a zero in Column B AND a zero in column C AND a zero in column D; which is not what we need.

To use the Advanced Filter option you click a cell within the data table, then click the Data ribbon tab and click the Advanced icon. (You can also access this dialog by pressing in sequence Alt a q).

Filter_4

The dialog below shows the criteria range defined.

Filter_5

After you click OK the data is filtered. The blue row numbers show that a filter is in place.

Filter_6

Advanced Filters are not dynamic and if you change the criteria in the criteria range you will need to re-apply the Advanced Filter. You can use a macro to automate the process and have it update immediately.

The Advanced Filter feature has many interesting tricks and techniques. If you need to frequently filter data it is worth your time investigating its many uses.

Filters_Before

Filters_After

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

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