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?
You can create an Advanced filter in Excel in four easy steps.
Make sure your data is well-organized with column headers, and you know the specific criteria you want to use for filtering.
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.
Go to the "Data" tab in the Excel ribbon. In the "Sort & Filter" group, click on "Advanced."
In the Excel Advanced Filter dialog box that appears:
In the criteria range you specified:
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.
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:
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:
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:
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 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.
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.