How To Use Excel FILTER Function With Multiple Criteria & Return Only the Columns You Need

Summary

In this tutorial, the speaker demonstrates how to use Excel's FILTER function to filter data based on multiple criteria, such as department and division, with both 'AND' and 'OR' conditions. The tutorial covers extracting specific columns using the CHOOSECOLS function, sorting results by salary, and troubleshooting common mistakes like missing parentheses. The speaker also provides helpful tips for applying the FILTER function to real-world scenarios, such as filtering employees by salary and department. The video is perfect for users who want to enhance their data analysis skills in Excel.

Takeaways

• 😀 Learn how to use Excel's FILTER function to check multiple criteria across different columns.
• 😀 The FILTER function can be used to extract data based on specific conditions, such as matching multiple departments or divisions.
• 😀 Use the multiplication method in the FILTER function for 'AND' conditions to combine multiple criteria.
• 😀 To check for OR conditions, replace the multiplication with a plus (+) to include multiple possible values.
• 😀 Use the IF-empty argument to return nothing when no matching data is found, preventing errors in your result.
• 😀 You can filter data on multiple columns, like 'Division' and 'Department', to return only the relevant entries.
• 😀 The CHOOSECOLS function allows you to select specific columns to return, rather than all columns, improving your results' relevance.
• 😀 Sorting data using the SORT function can help you organize the output, for instance, by salary in descending order.
• 😀 Understanding how to combine multiple conditions, including salary and department checks, allows for more targeted filtering.
• 😀 When working with OR conditions across the same column (e.g., 'Game' or 'Utility'), ensure you properly use brackets for multiple criteria.
• 😀 Combining AND and OR conditions is possible in the FILTER function, allowing complex queries like filtering by division and salary at the same time.

Q & A

What is the main focus of this tutorial?

The tutorial focuses on using Excel's FILTER function with multiple criteria to filter data based on conditions from different columns or the same column.

How can you filter data based on two different columns in Excel?

You can filter data based on two different columns by using the FILTER function and applying logical conditions using multiplication for 'AND' operations or addition for 'OR' operations. For example, multiplying the results of two conditions ensures both must be true.

Why is the multiplication of logical conditions used in the FILTER function?

Multiplication of logical conditions is used because it turns the TRUE/FALSE values into 1/0. When using the 'AND' condition, only rows where both conditions are true (1 * 1) will return data, while others will return 0 (false).

What happens if you want to use an 'OR' condition instead of 'AND' in the FILTER function?

To use an 'OR' condition, you replace the multiplication with addition. This allows the function to return rows where at least one condition is true, such as either the division is 'Productivity' or the department is 'Finance'.

What is the role of the CHOOSECOLS function in this tutorial?

The CHOOSECOLS function is used to return specific columns from the filtered data. This allows users to display only the columns they need, such as 'Name' and 'Yearly Salary', instead of all the columns in the dataset.

How can you sort the filtered data by salary in descending order?

You can sort the filtered data by using the SORT function, specifying the column index for 'Yearly Salary' as the sort index and setting the sort order to descending.

What is the issue with the original formula when trying to filter the 'Game' and 'Utility' divisions?

The issue with the original formula is the lack of brackets around the multiple criteria in the 'include' argument. Brackets are essential when combining multiple conditions to ensure they are evaluated correctly.

How do you include a salary condition in the filter function along with division or department criteria?

To include a salary condition, you can combine the division/department criteria with the salary condition by multiplying the results, ensuring that both conditions are met (for example, salary greater than 80,000). Proper use of brackets is important to avoid errors.

What was the problem when trying to use the salary condition and multiple criteria in the filter function?

The problem was that the salary condition was not properly enclosed in brackets, causing incorrect results. The correct approach is to group the division/department criteria and salary condition within separate brackets.

How does the FILTER function help solve common data filtering challenges in Excel?

The FILTER function allows users to filter data based on multiple criteria from one or more columns. It simplifies tasks like retrieving specific rows of data based on conditions, which would otherwise require more complex filtering or manual sorting.