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.
- 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
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.
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.
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).
The dialog below shows the criteria range defined.
After you click OK the data is filtered. The blue row numbers show that a filter is in place.
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.