If you’re dealing with large datasets in Excel and need to quickly pull specific information, the advanced filter tool is your new best friend. Instead of sifting through rows and columns manually or using basic filters, the advanced filter allows you to extract data based on specific criteria and copy it to a new location—within the same worksheet or onto another tab.

In today’s tutorial, we’ll walk through a clear step-by-step guide for using the advanced filter in Excel, helping you sort through massive amounts of data quickly and efficiently.

Watch the full video tutorial here:

What Is the Advanced Filter in Excel?

The advanced filter in Excel is a powerful feature that lets you filter data based on a specified list of criteria. It’s especially useful if you’re working with a large dataset involving multiple columns and rows. Unlike Excel’s ordinary filter tool, the advanced filter gives you more control over how you filter your data and allows you to copy the results to another location for further manipulation.

For this example, we’ll work through a dataset containing company codes, locations, invoice numbers, customer details, and product purchases. We want to extract specific information that meets these criteria:

  • The date is between January 1st and June 30th.
  • The product description contains the word “apple”.

Setting Up Your Data in Excel

First, before diving into the advanced filter, you need to ensure your data is set up correctly. In our example, the data consists of things like invoice numbers, product descriptions, dates, and amounts. Here’s a quick guide on how to set up your data:

  1. Prepare your dataset: Add your data to a tab labeled something like “Sales Data” if you want to keep things neat.
  2. Create a results sheet: This can be within the same workbook or a new tab. Name it “Results” to make things easier to manage.

Make sure your data headers are clearly labeled, as these will be key for filtering.

Setting Up Criteria for the Advanced Filter in Excel

Excel’s advanced filter works by looking at the dataset based on certain criteria. In our example, we want to find sales of apples that happened between January 1st and June 30th.

Here’s what you need to do:

  1. Set up a criteria range: This will be where Excel knows what to look for. In the example, we set this up in columns M and N. You can do this within the same sheet or on a different tab, whichever makes sense for your workflow.
  2. Copy headers exactly: When entering your criteria, copy the column headers (like “Date” and “Item Description”) exactly from your data. This avoids typos that might confuse Excel.
  3. Enter your criteria:
    • For the date, enter <= 6/30/2020. This will make sure Excel selects dates that are June 30th or earlier.
    • For the product description that contains “apple,” use the asterisk (*) symbol. Type *apple* in the description field to tell Excel to look for any text that includes “apple,” whether it’s at the beginning, middle, or end of the description.

Running the Advanced Filter

Now that your criteria are in place, it’s time to run the advanced filter in Excel. Here’s how to do it:

  1. Go to the Data tab: In your Excel toolbar, find the Data tab.
  2. Select Advanced Filter: You’ll see an option that says Advanced under the “Sort & Filter” section.
  3. Choose to copy the filter to another location: You can either choose to filter the data in-place or copy it to another location. For this example, choose to Copy to another location.
  4. Define your range:
    • List range: This is your dataset from the sales data tab. Highlight your entire dataset, including all rows and columns (e.g. A1:K200).
    • Criteria range: Highlight the criteria that you entered in columns M and N.
    • Copy to: Select the cell where you want the filtered data to show up, such as A1 in your results tab.
  5. Click OK: Excel will now extract and copy all rows that meet your criteria (sales of apples between January and June) to the results tab.

Refining the Filter Results

At this point, you might want to narrow down the columns you’re extracting. Let’s say you don’t need all the data, just the most relevant columns. You can choose to copy only specific columns like Invoice Number, Date, Item Code, Description, Quantity, and Amount.

Here’s how you can filter only the selected columns:

  1. Manually copy the headers of the columns you want: Copy the headers for the specific data points you want to the results tab.
  2. Run the advanced filter again: Use the same steps as before, but this time, when selecting your Copy to range, highlight the cells under your newly copied headers.
  3. Press OK: Excel will then pull only the columns you selected. You’ll get a cleaner, more focused set of data with exactly what you asked for.

Key Notes and Troubleshooting

One issue you might come across when using the advanced filter in Excel is an error that pops up when starting from line 2. This happens if your criteria range is in the second row of your sheet. To avoid this:

  • Always start the query on line 3. Either leave line 2 blank or fill it with some other data that Excel can ignore. This resolves the error and ensures the filter works correctly.

Using this technique, you can easily handle large datasets and sort out exactly what you’re looking for—without digging through everything manually.

Conclusion

Excel’s advanced filter is a great tool for anyone working with large amounts of data. Instead of endlessly sorting and filtering data, using this filter allows you to focus on just the information you need and copy it to another location for further analysis. Whether you’re pulling sales reports for a specific time period or looking for a product, this tool boosts your productivity.

Take your data filtering to the next level, and don’t forget to watch the full video tutorial linked above for a detailed walkthrough.

Be sure to like, subscribe, and get notified for more useful Excel tutorials like this!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR