• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Advanced Filter in Excel

September 6, 2021 by Andrew Childress
Advanced filters Excel

You might be familiar with Excel’s default Filters option. But did you know about Excel's Advanced Filter? Here's how to use 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.

Advanced filter in Excel

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.

Advanced filters Excel

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.

Excel advanced filters

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

Category: Excel FunctionsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Consolidate in ExcelConsolidate in Excel
Next Post:Add Notes in ExcelShow all notes

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top