How to Use Advanced Filters in Excel

If you use Excel for data analysis, then you are very familiar with the AutoFilter, even though you may not recognize that particular name. The AutoFilter is the standard filters that you add by hitting Data > Filter and then the friendly filter icons are added to your column headers.

But what happens when you click on the Excel Advanced filter? Well, it's a way more powerful option! AutoFilter is a basic search tool, but when you have intricate questions such as "Show me all the blue items that cost less than $10 and were bought in July," AutoFilter falls short. 

The Advanced Filter in Excel, on the other hand, handles these complex tasks with precision. It lets you apply multiple criteria and get specific results tailored to your choices. Let’s walk through how it works! Also, is it the best option out there for advanced filtering?
‍

How to create an Advanced filter in Excel?

You can create an Advanced filter in Excel in four easy steps. 

Step 1: Prepare Your Data.

Make sure your data is well-organized with column headers, and you know the specific criteria you want to use for filtering.

Step 2: Select the Data Range.

Click anywhere within the dataset you want to filter. Excel will automatically detect the boundaries of your data. Or, you can manually select the data range by clicking and dragging over the cells.

Step 3: Access the Advanced Filter Dialog Box.

Go to the "Data" tab in the Excel ribbon. In the "Sort & Filter" group, click on "Advanced."

Step 4: Define the Criteria Range

In the Excel Advanced Filter dialog box that appears:

• Choose whether you want to filter the data in place (replace the original data with the filtered data) or copy it to another location. If you choose to filter in place, make sure you have enough empty rows below your data to accommodate the filtered results.

• Specify the "List range," which is the range of cells containing your data.
• Specify the "Criteria range," which is a range of cells where you will define your filtering criteria.

Step 5: Define Filtering Criteria

In the criteria range you specified:

• Create column headers that match the headers in your data.
• Under each column header, specify the filtering criteria. You can use comparison operators like "=," "<>," "<, ",”>," "<=," ">”, “=" or logical operators like "AND" and "OR" to define complex criteria.
• Leave cells blank for columns where you don't want to apply any criteria.

‍

Step 6: View the Filtered Data

• Excel will apply the advanced filter based on your criteria.
• If you choose to filter in place, you'll see the filtered results in your original data range. If you choose to copy the data, it will appear in the specified location.

Step 7: Clear the Filter (Optional)

• To remove the filter and restore your original data, go back to the "Data" tab, click on "Clear" in the "Sort & Filter" group, and select "Clear Filters."

Easier Options to Try: Advanced Filtering in Gigasheet

The Advanced filter in Excel filters out your desired rows and columns for sure, but it took me around 1 hour to understand the function and then finally use it. On the other hand, Gigasheet, the big data spreadsheet, takes 2 minutes to do the same.

Data in Gigasheet is already ready to be filtered. Unlike the AutoFilter in Excel, Gigasheet is ready to be filtered. Just click on the Filter button and begin building a complicated set of conditions.

Gigasheet has drop-down menus to select various rows and columns. Also, you can use as many filters as you like.

It took me 30 seconds to filter out 2 conditions in Gigasheet.

Criteria Ranges in the Excel Advanced Filter

Comparison operators only work for filtering out numbers. But you must also filter text and other specific columns while analyzing a large dataset.  

Let’s understand how you can filter out specific columns using the Advanced filter in Excel:

1. Using logical operators to filter out text values 

Use the table below to filter out specific data in the text format:

For example, I want to filter out “logging” from the column “NAICS description” that has a total first quarter payroll greater than or equal to 18. This is how I provide the criteria:

See how the advanced filter in Excel filters out specific columns as per my criteria:

2. Harnessing Wildcards 

Sometimes, the desired data is more complex than a single text or numeric value. Use wildcards for such criteria. 

Here’s how wildcards work:

For example, to filter all business niches that have “building” in their names and have employees greater than or equal to 100, I added this criterion:

This is the result: 

‍

3. Advanced Filtering with AND or OR Logic

There are three ways you can use AND or OR logic in the excel advanced filter.

Using AND, you satisfy both conditions present in the criteria. For example, here, Excel’s advanced filter will share construction business with mid-March employees greater than or equal to 6000.

‍Using OR, you filter cells that have either of the two conditions. For example, this criteria will filter all cells that either contain “construction” or “Woodford County.”

‍Using both AND and OR together: you can filter more than two conditions. For example, this criteria will filter all cells that either contain “construction” or “Woodford County” and have a total number of business establishments greater than or equal to 15. 

Let’s try advanced filtering in Gigasheet.

Let’s use Gigasheet to filter out businesses that have “building” in their names. In Excel, I had to create criteria with a specific formula for the same. 

But Gigasheet’s drop-down menu has the “contains” operator, and I can select a single word - “building” in this case - to filter out from the value section.

I also added another condition to filter out companies with greater than or equal to 100 employees.

‍

Within seconds, I got the exact same result that took me half an hour in Excel. 

Advanced Filter in Excel is Good, but Gigasheet is Better.

The Advanced filter in Excel filters out whatever you want. But it’s slow, and the learning curve is significantly high, especially for those who don’t come from an analytics background.

It continues with formulas, and the number of steps only increases with the complexity of the filter. Gigasheet, on the other hand, is seamless. The process stays short and sweet no matter how complex your filter gets. You get results within seconds.

Using Gigasheet for data analysis becomes a no-brainer. Get started for free today.