Download the practice workbook 👉 HERE and follow along.
Why Use Advanced Filter in Excel?
Excel’s Advanced Filter helps you filter data by multiple criteria and place the results on a different sheet.
Perfect for when your filters change often, or when you want to avoid messing with the main dataset.
How to Use Advanced Filter in Excel
Here’s a quick example:
We want to use an Excel Filter for multiple criteria and filter rows that:
- Mention “laptop” in the Item Description and
- Are from July 1st, 2024 or later.
Step 1: Set Up Your Criteria
First, you’ll set the conditions for what data you want to filter. Let’s say you’re filtering for records that mention “laptop” and are dated from July 1st, 2024 or later.
- Create a small table next to your main data with the relevant headers (like Date and Item Description).
- In the Date column, enter
>=7/1/2024to filter by records from that date onward. - In the Item Description column, enter
*laptop*. The asterisks act as wildcards to include any with “laptop” in it.
Step 2: Create Your Output Headers
Now, switch to the sheet where you want the filtered results to show up. This will be your destination sheet.
- In this new sheet, create a header row with only the columns you want to display.
- For example, if your main dataset includes columns like Transaction Number, Item Description, Quantity, and Sales, but you only want to see Transaction Number, Quantity, and Sales, set up a header row with those names.
💡 Pro Tip: You can also rearrange the order of these columns in any way that fits your needs.
Step 3: Start the Advanced Filter
- While on your destination sheet where the output headers are, go to the Data tab in the Excel ribbon.
- Click on Advanced under the “Sort & Filter” group.
Step 4: Define the Filter Criteria
- In the Advanced Filter window, select Copy to another location.
- In the List Range field, switch to the sheet where your main data is located. Select the full range (including the headers) you want to filter (e.g.,
Data!$A$1:$K$116). - In the Criteria Range field, select the range (including the headers) where you set your filter conditions (e.g.,
Data!$M$1:$N$2). - In the Copy To field, select the header row you created on the destination sheet (for example,
Report!$A$1:$C$1).
- Click OK to run the filter.
Step 5: Check Your Results
Once you click OK, Excel will return the filtered rows based on your criteria.
But here’s the trick: only the columns you specified in your output header will show up, and they will appear in the order you set.
❗Important Note: This Solution is Not Dynamic
Keep in mind that this method is not dynamic. If your data changes—such as new rows or updates—you’ll need to manually run the Advanced Filter again.
The results won’t automatically update when your dataset changes, making this trick best for static data.
Want a Dynamic Solution? Try Power Query
If your dataset frequently changes, you might prefer a more dynamic solution like Power Query.
Power Query automatically refreshes your data every time it’s updated, saving you from manually reapplying the filter.
With Power Query, you can automate your filters and manage large datasets more efficiently.
👉 Want to learn more about Power Query? Check out this guide on what Power Query is and why you should use it.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master how to filter in Excel with hands-on examples.
Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
