Advanced Filter – Smartly Extract Only the Data You Need

When a simple filter makes it difficult to extract the data you need, Excel’s Advanced Filter feature lets you freely set even AND/OR criteria.

✅ Advanced Filter Basics

1. Set up the data range and criteria range separately.
2. Click Data > Advanced.
3. Specify the List range, Criteria range, and Copy to location.
4. You can use the “Unique records only” option.

✅ Setting Up a Criteria Range

• The criteria range must include column headers.
• Multiple criteria in the same column → OR criteria
• Criteria across different columns → AND criteria

✅ Practical Example 1: Sales of 100 or More & Region Is Seoul

Criteria range:
Region     Sales
Seoul      >=100

→ Extracts only records for Seoul with sales of 100 or more.

✅ Practical Example 2: View Revenue for a Specific Period

Criteria range:
Date
>=2025-01-01
<=2025-01-31

→ Displays revenue for January 2025 only.

✅ Practical Example 3: Use It to Remove Duplicates

Remove duplicates from a customer name list.

1. In Advanced Filter, select “Copy to another location.”
2. Select “Unique records only.”

✅ Copy Results to Another Worksheet

You can automatically copy only the data that meets your criteria to another report worksheet.

📌 Summary

• Use Advanced Filter to search with complex criteria.
• Copy results to another location to separate reports.
• Clean data with the duplicate removal feature.
• Use criteria range cells to minimize repetitive work.

🙋 Frequently Asked Questions (FAQ)

Advanced Filter helps you smartly extract only the data you need, significantly improving report creation speed 🚀