# 9 Easy Examples to Filter Data Using Formula in Excel

We need to filter data in Excel to perform myriad operations. In this article, we will learn how to filter data using formula with various useful functions in Excel with the help of 9 convenient examples.

## Learn to Filter Data in Excel Using Formula with These 9 Easy Examples

In this article, we will learn how to filter data using formula in Excel. We will use the following sample dataset to achieve our goal.

Example

### 1. Filter Columns Based on Particular Condition

In the first example, we will show how to filter data based on a particular condition by using the FILTER function. Here, we want to filter data by Western Conference.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell F8 and type in the following formula, and press Enter:

`=FILTER(B6:D12,C6:C12=G5,"No results")`

• Momentarily, your data will be filtered just like in the above screenshot.
• As this is an array function, after pressing Enter you will notice a second bracket appearing in the Formula Bar.
• You will notice a thin blue border around the filtered data if you select any of the filtered cells.

🔨 Formula Breakdown

👉  The FILTER function will find the value stored in cell G5 which is Western from cells C6 to C12 and based on that it will filter all the data from cells B6 to D12. If the FILTER function does not find Western in cells C6 to C12, it will return No Results.

Example

### 2. Filter Columns Based on Multiple Conditions

In our next example, we will use multiple conditions to filter data. We want to filter data by Eastern Conference and two Percentile conditions.

⬇️⬇️ STEPS ⬇️⬇️

• First, Enter the following formula in cell F10:

`=FILTER(B6:D12,(C6:C12=F6)*((D6:D12>=G6)+(D6:D12<=G7)),"Not Available")`

• Done! Your data is filtered according to the conditions given.

Note:

The Number Format may not always remain the same as the main data. Here, in the Percentile column, we used the Number format up to 3 decimal places. But the filtered data is showing Percentile in General format.

🔨 Formula Breakdown

FILTER(B6:D12,(C6:C12=F6)*((D6:D12>=G6)+(D6:D12<=G7)),”Not Available”)

👉  Here, we have used the combination of the AND (*) along with OR (+) logic.

👉  The FILTER function will find data where the values in cells D6 to D12 are greater or equal to the value stored in cell G6 (7) or less or equal to the value stored in cell G7 (0.45).

👉  Then, the FILTER function will find Eastern which is stored in cell F6 from cells C6 to C12.

👉  The intersected data will be shown as filtered data in cells F10:H12.

Example

### 3. Filter Columns Using Dates

We will now learn to filter data using formula between two given dates.

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, insert the formula in cell G10 and press Enter:

`=FILTER(B6:E12,(E6:E12>=H6)*(E6:E12<=H7),"Not Available")`

• Excel has filtered your data within the dates mentioned.

🔨 Formula Breakdown

FILTER(B6:E12,(E6:E12>=H6)*(E6:E12<=H7),”Not Available”)

👉  Here, we have used the AND (*) logic inside the FILTER function.

👉  The FILTER function will return data between the dates stored in cells H6 and H7. If it does not find any data between the dates, the function will return Not Available.

Example

### 4. Filter Duplicate Rows from Multiple Columns

We will filter duplicate rows using the FILTER and COUNTIFS functions.

⬇️⬇️ STEPS ⬇️⬇️

• First, Enter the formula in cell F8:

`=FILTER(B6:D12,COUNTIFS(B6:B12,B6:B12,C6:C12,C6:C12,D6:D12,D6:D12)>1,"Not Available")`

• Voila! All the duplicate rows are filtered.

🔨 Formula Breakdown

FILTER(B6:D12,COUNTIFS(B6:B12,B6:B12,C6:C12,C6:C12,D6:D12,D6:D12)>1,”Not Available”)

👉  The COUNTIFS function will return how many times it found the same Team name from cells B6 to B12 and it will do the same for the other two columns.

👉  If the COUNTIFS function returns a value greater than 1, the FILTER function will filter those data. Otherwise, it will show Not Available.

Example

### 5. Filter Non-Empty Cells

In this example, we will filter the non-empty cells using the FILTER function.

⬇️⬇️ STEPS ⬇️⬇️

• Select cell F8 and Enter the formula below:

`=FILTER(B6:D12, (B6:B12<>"")*(C6:C12<>"")*(D6:D12<>""),"Not Available")`

• All the non-empty cells are filtered.

🔨 Formula Breakdown

FILTER(B6:D12, (B6:B12<>””)*(C6:C12<>””)*(D6:D12<>””),”Not Available”)

👉  We used the AND (*) logic here inside the FILTER

👉  If the FILTER function finds empty cells in either of the three columns, it will not show those rows.

Example

### 6. Filter Columns If Cell Contains Specific Text

We will use the FILTER, ISNUMBER, and SEARCH functions if a cell contains a specific text to filter the dataset.

⬇️⬇️ STEPS ⬇️⬇️

• First, Enter the following formula in cell F8:

`=FILTER(B6:D12,ISNUMBER(SEARCH(G5,C6:C12)),"Not Available")`

• The data is filtered according to the condition.

🔨 Formula Breakdown

FILTER(B6:D12,ISNUMBER(SEARCH(G5,C6:C12)),”Not Available”)

👉  The SEARCH function will search east from cells C6 to C12. The SEARCH function is not a case-sensitive function.

👉  The ISNUMBER function will return TRUE if the SEARCH function finds east.

👉  The FILTER function will filter the dataset using the condition.

Example

### 7. Filter Columns and Return Specific Columns

We will use two FILTER functions to filter data and return which columns we would like to show.

⬇️⬇️ STEPS ⬇️⬇️

• First, Enter the formula below in cell F8:

`=FILTER(FILTER(B6:D12,C6:C12=G5),{TRUE,FALSE,TRUE})`

• Your filtered dataset is shown in cells F8:G10.

🔨 Formula Breakdown

FILTER(FILTER(B6:D12,C6:C12=G5),{TRUE,FALSE,TRUE})

👉  The second FILTER function takes the value stored in cell G5 (Western) and filters the dataset accordingly.

👉  However, the first FILTER function will return the first and the last column of the dataset as we have written the condition {TRUE,FALSE,TRUE}. It denotes we don’t want to show the second We could write {1,0,1} instead.

Example

### 8. Filter Desired Number of Rows

Now, we will filter data and show as many rows as we want using the FILTER and the INDEX functions.

⬇️⬇️ STEPS ⬇️⬇️

• Enter the following formula in cell F8:

`=IFERROR(INDEX(FILTER(B6:D12,C6:C12=G5),{1;2;3},{1,2,3}),"Not Available")`

• Filtering the dataset is complete!

🔨 Formula Breakdown

IFERROR(INDEX(FILTER(B6:D12,C6:C12=G5),{1;2;3},{1,2,3}),”Not Available”)

👉  The FILTER function will filter data according to the value stored in cell G5.

👉  The INDEX function will show the first 3 rows as we wrote {1;2;3}. Similarly, we wrote {1,2,3} to show the 3 columns.

👉  If there is an error in the formula, the IFERROR function will return Not Available. Otherwise, it will show the value returned by the INDEX and FILTER

Example

### 9. Filter by Merging INDEX, SMALL, IF, and ROW Functions

In our last example, we will use different functions to filter our dataset.

⬇️⬇️ STEPS ⬇️⬇️

• Select cell F9 and insert the formula below and press Ctrl+Shift+Enter concurrently:

`=IFERROR(INDEX(B:B,SMALL(IF(\$F\$6=C:C,ROW(C:C),""),ROW()-ROW(\$F\$8))),"")`

• Next, use the Fill Handle icon to get the rest of the values matching the given conditions just like in the following photo.

🔨 Formula Breakdown

IFERROR(INDEX(B:B,SMALL(IF(\$F\$6=C:C,ROW(C:C),””),ROW()-ROW(\$F\$8))),””)

👉  The ROW function will return the number of rows where the IF function finds Eastern.

👉  From the array returned by the IF function, the SMALL function will return a row index. This will be used as the row number for the INDEX function.

👉  The ROW function refers to the number of rows where the formula is entered. ROW()-ROW(\$F\$8) will return 1 as we have entered the formula on cell F9. This will be the column number for the INDEX function.

👉  The IFERROR function will return an empty Otherwise, it will show the value returned by the INDEX function.

📌  Firstly, we learned to filter based on specific criteria using the FILTER function.

📌  Secondly, we showed how to employ multiple conditions in the FILTER function.

📌  Thirdly, we demonstrated filtering data between a start and an end date.

📌  Next, we filtered duplicate rows using the FILTER and COUNTIFS functions.

📌  Afterward, we filtered non-empty rows using the AND logic inside the FILTER function.

📌  Later, we showed how to filter data if a cell contains specific text with the help of the FILTER, ISNUMBER, and SEARCH functions.

📌  Next, we learned to filter and return specific columns using the nested FILTER function.

📌  Then, we showed how to filter and return the desired number of rows using the INDEX and FILTER functions.

📌  Finally, we coalesced INDEX, SMALL, IF, and ROW functions to filter our dataset.