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.

## 📁 Download Excel File

Download the following Excel workbook to realize the topic more clearly.

## 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**

**FILTER(B6:D12, C6:C12=G5, “No results”)**

👉 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.

**📕 Read More: 7 Ways to Filter Data Based on Cell Value in Excel**

**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.

## 📝 Takeaways from This Article

📌 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.

## Conclusion

That concludes the discussion for today. These are some convenient methods to filter data using formula in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website **ExcelDen** to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.