Advanced Filter in Excel

You might be familiar with Excel’s default Filters option. This helps you sort through data based on criteria that you specify. But did you know that Excel has an Advanced Filter too? These, as the name suggests, help you perform more complex analysis tasks. Let’s learn how to use Advanced Filter in Microsoft Excel.

How to Use Advanced Filter in Excel

Imagine a list of several countries, with some that repeat. You might want to generate a separate list containing only unique names. Of course, you could use Excel’s Remove Duplicates feature, but this actually removes values from your data. By using the Advanced Filter option, you can preserve your original data while still generating filtered lists.

To use Advanced Filter, begin by going to the Data tab on Excel’s ribbon. Near the middle, you’ll see the Advanced Filter button. Click on it, and Excel, opens the Advanced Filter window.

To generate a new, filtered list, click on Copy to Another Location at the top. Then, in the List Range section, click and drag your cursor to select the list of countries. Be sure to include the header in cell A1. Your range here is A1:A8.

In the Copy To box, click into any empty cell in your workbook. There, Excel will place your data after it has been processed by Advanced Filter. We’ll use cell C1 here. Click into it.

Finally, check the box next to Unique Records Only. This tells Excel to filter duplicate values out of your list. But remember, they won’t be removed from your original dataset.

Finally, click OK. Excel will run your data through the Advanced Filter, and return a list of the unique values in the spot you’ve chosen.

As you can see, Excel’s Advanced Filter is a powerful way to process data based on multiple criteria, while preserving the underlying data.