Nazmus Sakib
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.
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.
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.
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.
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.
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
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.
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.
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
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.
