Video advanced filter details

Try-it!Transcript



You can use the Advanced Filter to create more powerful filters, such as filtering for unique records and using operators such as OR.

Remove a filter from a column

Click the Filter icon button next to the column heading, and then click Clear Filter from <"Column Name">.

Remove all the filters in a worksheet

Click DATA > Clear.

Want more?

Sort by dates

Sort data using a custom list

Filter data in a range or table

We filtered with the AutoFilter in the AutoFilter details video. It is easy to use and has many options.

You can use the Advanced Filter to create more powerful filters, such as filtering for unique records and using operators such as OR.

Tea is in the Product name column twice. To filter so that tea displays only once, on the DATA tab, click Advanced.

In the Advanced Filter dialog box, click the List range.

On the worksheet, select the column you want to use to filter for unique records.

In this case, the Product name column. Click Unique records only. Click OK, and now Tea appears only once.

You could also use the Remove Duplicates button on the DATA tab, depending on what you want to achieve, because this will delete duplicate records, not just filter them.

To use the Advanced Filter to filter with multiple criteria, you need to create a criteria range.

In this example, I created the criteria in cells G1 through H2.

The top cell in each criteria column must be the same as the headers in the columns you want to filter by.

The criteria in the Category column is equal to Drinks, and the Last order column is equal to 3/28/2013.

Click Advanced on the DATA tab. In the Advanced Filter dialog, select the list you want to filter. Click in Criteria range.

Select the criteria, in this case G1 through H2. Click OK, and the range is filtered using the criteria.

To make criteria equal to Drinks OR equal to 3/28/2013, move the Last order date down a row.

Click Advanced, reset the Criteria range, click OK, and the rows where the Category column is equal to Drinks OR the Last order column is equal to 3/28/2013 are displayed.

To remove the filter, click Clear on the DATA tab.

You can add more complex criteria to your criteria range, such as greater than instead of the default equal to.

Type a greater than sign before a value, click Advanced, reset the Criteria range, click OK, and the range is filtered using the criteria.

You can use the asterisk and question mark wild card characters in your criteria.

Left arrow, right arrow, asterisk s will return rows where the text in the category column does not end in s.

In the Advanced Filter dialog, set the Criteria range. I am using just G1 through G2.

To copy the results to a different location on the worksheet, click Copy to another location, click in Copy to, select a cell to copy to, click OK, and the filtered results are copied to the location.

Now, you have a pretty good idea about how to create sort and filter data. Of course, there is always more to learn.

So check out the course summary at the end, and best of all, explore Excel 2013 on your own.