Don’t forget to share this post

There are multiple ways of filtering data in Microsoft Excel, including the built-in column filters in Excel tables. However, this changes the original data by hiding rows. Using the FILTER function, you can reproduce a filtered version of the original data. This allows you and your team to work with different filtered versions of the data without affecting others or creating isolated copies of the data. You can also combine the FILTER function with others, such as SUM or AVERAGE, to operate directly on the filtered data.

In this guide, you will learn how to use Microsoft Excel’s FILTER function. First, you will review the syntax and learn how to match values by exact text, numerical values, and dates. Second, you will learn how to filter arrays by multiple conditions using FILTER with the OR (+) and AND (*) symbols. Finally, you have examples of how to use FILTER with functions like UNIQUE and SORT.

FILTER Function Syntax

Excel’s FILTER function has two required parameters and one optional parameter.

=FILTER(array,include,if_empty*)
  • array: the array or range of cells you want to filter.
  • include: the conditions that values must meet to be included; a Boolean array with the same height or width as the array.
  • if_empty*: optional. You can specify the value to return if the filter returns nothing.

How to Use FILTER Function in Excel?

Using the FILTER function in Excel is easy: just select the array and the criteria you want to use to filter the values. Optionally, you can specify what the function should return if the filter finds nothing to return. Below, you have step-by-step instructions on how to filter by text, numerical value, and by date. In the next section, you will learn how to use it with multiple criteria or conditions and how to combine it with other functions.

FILTER by Text

Follow the steps below to get records matching an exact text value in one of the columns.

  1. 1. In an empty cell, type the FILTER function and select the array you want to filter as the first parameter.
Excel FILTER Function and Examples FILTER Array
Excel FILTER Function and Examples - FILTER & Array
  1. 2. Add a semicolon followed by the condition. In this case, the text in column E must equal “ThirdProduct”.
Excel FILTER Function and Examples Match Text Exactly
Excel FILTER Function and Examples - Match Text Exactly
  1. 3. Close the parenthesis and press ‘Enter’ to see the results.
Excel FILTER Function and Examples Filtered Results
Excel FILTER Function and Examples - Filtered Results

FILTER by Numerical Value

If you want to match values greater than or equal to a specific value, follow the steps below.

  1. 1. Type the FILTER function and select the array.
Excel FILTER Function and Examples FILTER Array 2
Excel FILTER Function and Examples - FILTER & Array
  1. 2. Add a semicolon and the condition you want to use. In this case, I want records with $750 or more in the ‘amount’ column.
Excel FILTER Function and Examples Value Equal to or Greater than
Excel FILTER Function and Examples - Value Equal to or Greater than
  1. 3. Close the parenthesis and press ‘Enter’.
Excel FILTER Function and Examples Results Filtered by Numerical Value
Excel FILTER Function and Examples - Results Filtered by Numerical Value

FILTER by Date

You can also combine the FILTER and DATE functions to filter by date.

  1. 1. In an empty cell, type the FILTER function and select the array.
Excel FILTER Function and Examples FILTER Array 3
Excel FILTER Function and Examples - FILTER & Array
  1. 2. After the semicolon, add the condition. In this example, records must be from before April 1st.
Excel FILTER Function and Examples Before Date
Excel FILTER Function and Examples - Before Date
  1. 3. Close the parenthesis and press ‘Enter’.
Excel FILTER Function and Examples Results Filtered by Date
Excel FILTER Function and Examples - Results Filtered by Date
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

Use The Excel FILTER Function with Multiple Conditions

The Excel FILTER function can also be used with multiple conditions. These can be OR conditions - where values can meet either of the conditions - or AND conditions - where values must meet both conditions.

Excel FILTER Multiple Conditions with OR

Follow the steps below to return records where the ‘product’ column matches “FirstProduct” or “ThirdProduct”.

  1. 1. In an empty cell, type the FILTER function and select the cells with the array.
Excel FILTER Function and Examples FILTER Array 4
Excel FILTER Function and Examples - FILTER & Array
  1. 2. Add the first condition between parentheses.
Excel FILTER Function and Examples First Condition
Excel FILTER Function and Examples - First Condition
  1. 3. Add an OR symbol (+) followed by the second condition between parentheses.
Excel FILTER Function and Examples Second Condition
Excel FILTER Function and Examples - Second Condition
  1. 4. Close the parentheses for FILTER and press ‘Enter’ to see the results.
Excel FILTER Function and Examples Results Filtered by Two Conditions
Excel FILTER Function and Examples - Results Filtered by Two Conditions

Excel FILTER Multiple Conditions with AND

Follow the instructions below to use multiple criteria and ensure that the records match all of them.

  1. 1. Type the FILTER function in an empty cell and select the array.
Excel FILTER Function and Examples FILTER Array 5
Excel FILTER Function and Examples - FILTER & Array
  1. 2. Add a semicolon and type the first condition between parentheses. In this case, I want records for FirstProduct.
Excel FILTER Function and Examples First Condition 2
Excel FILTER Function and Examples - First Condition
  1. 3. Add an AND (*) symbol and type the second condition between parentheses. In this example, I want only records for Agent1.
Excel FILTER Function and Examples Second Condition 2
Excel FILTER Function and Examples - Second Condition
  1. 4. Close the parentheses and press ‘Enter’.
Excel FILTER Function and Examples Filtered Results 2
Excel FILTER Function and Examples - Filtered Results
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

Use FILTER with Other Functions

As mentioned above, the FILTER function can be combined with other built-in functions to manipulate the data after filtering. Below, you have examples using UNIQUE and SORT.

Excel FILTER with UNIQUE

Follow the steps below to filter the results by the amount and get a list of unique customers.

  1. 1. Type the UNIQUE function followed by the FILTER function and select the array.
Excel FILTER Function and Examples UNIQUE FILTER
Excel FILTER Function and Examples - UNIQUE & FILTER
  1. 2. Add the condition. In this case, amounts must be larger than $1000.
Excel FILTER Function and Examples Add Condition
Excel FILTER Function and Examples - Add Condition
  1. 3. Close the parentheses and press ‘Enter’ to see the results.
Excel FILTER Function and Examples Filtered Results 3
Excel FILTER Function and Examples - Filtered Results

Excel FILTER with SORT

Finally, let’s see how to use the SORT function to sort the results returned by FILTER.

  1. 1. Type the SORT function, followed by the FILTER function, and select the array.
Excel FILTER Function and Examples SORT FILTER
Excel FILTER Function and Examples - SORT & FILTER
  1. 2. Add the condition and the closing parenthesis. In this case, I want records belonging to Agent1.
Excel FILTER Function and Examples Add Condition 2
Excel FILTER Function and Examples - Add Condition
  1. 3. Add the number of the column you want to sort by. In this example, I want to sort by the amount in the sixth column. After the semicolon, select the order you want: ascending (1) or descending (-1).
Excel FILTER Function and Examples Sort Options
Excel FILTER Function and Examples - Sort Options
  1. 4. Close the parenthesis and press ‘Enter’ to see the result.
Excel FILTER Function and Examples Filtered Sorted Results
Excel FILTER Function and Examples - Filtered & Sorted Results

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

  • Centralized Knowledge: Access your team’s collective wisdom with ease.
  • Collaborative Workspace: Foster engagement with flexible project tools.
  • Seamless Communication: Connect your entire organization effortlessly.
  • Preserve Ideas: Capture insights without losing them in chats or notifications.
  • Comprehensive Platform: Manage all content in one organized location.
  • Open Teamwork: Empower employees to contribute, share, and grow.
  • Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

The Microsoft Excel FILTER function is very flexible and easy to use. You can reproduce filtered versions of your array without affecting the original data. Since it remains linked, the filtered array will be updated with changes to the original as soon as the formula refreshes. You can use multiple conditions within the same formula and apply them to the same column or different ones. Additionally, you can combine FILTER with other functions to operate on or manipulate the results.

You now know how to use Excel’s FILTER function to filter records based on exact text matches, numerical comparisons, and dates. You also know how to combine multiple conditions by using the AND (*) and OR (+) symbols. Finally, you know how to combine FILTER with UNIQUE to get only unique records matching your criteria and how to combine it with SORT to get a filtered and sorted version of the array.

To learn more about Google Sheets or Microsoft Excel, check out our guides on:

Maria Del Olmo
Originally published Mar 22 2023