How to Filter Multiple Rows in Excel (11 Suitable Approaches)

In our practical scenario with Microsoft Excel, we have to filter columns as well as rows from a large range of data for analysis. In this article, you’ll get to learn about the methods of how you can filter multiple columns as well as rows under different criteria in Excel.

filter multiple rows in excel overview

The above screenshot is an overview of the article which represents an example of filtering multiple columns & rows in Excel. You’ll learn more about the dataset as well as the methods and functions to filter Excel data based on different criteria in the following sections of this article.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

In Excel, though it is not exactly possible to filter multiple rows, but if you copy the entire dataset & paste it with the Transpose option, then this data will be converted into columns. And thereby you’ll be able to filter those columns efficiently. Here I’m showing you a simple example first of how you can transpose a random dataset or table.

The following chart has all headers in Column B & the related data in the 2nd, 3rd, 4th & 5th rows. To transpose this table, you have to select the entire table first & then copy it by pressing CTRL+C.

transposing data before filter multiple rows in excel

Now Paste this data or table in another cell or area with the Transpose option. You’ll be shown a preview of the return values.

transposing data before filter multiple rows in excel

After pressing Enter, you’ll find that the headers along with all other columns have been transposed to rows now. This is how you can transpose a dataset & then move to filter the table based on your criteria.

transposing data before filter multiple rows in excel

So, here we go for the methods & functions to filter our table or a range of data.


11 Suitable Approaches to Filter Multiple Rows in Excel

1. Using Sort & Filter Command to Filter Multiple Rows

Let’s get introduced to our original dataset now. We can assume that our dataset is already transposed. There are some random names of computer brands, device types, model names, countries of origin, date of release & device prices in 6 columns (B, C, D, E, F & G). We’ll filter these data based on different criteria & by using different methods.

sort and filter command to sort multiple rows

First, we want to know the prices of desktops made in the USA only. So we have to filter the table.

📌 Step 1:

➤ Select the headers of the table.

➤ Under the Home tab, select the Filter command from the Editing and Sort & Filter drop-down. You’ll see the filter buttons in your table headers now.

sort and filter command to sort multiple rows

📌 Step 2:

➤ From the Country of Origin options, select USA only.

sort and filter command to sort multiple rows

Press Enter & you’ll find all the devices originated in the USA.

sort and filter command to sort multiple rows

📌 Step 3:

➤ Now select Desktop from the Device menu.

➤ Press OK & you’re done.

sort and filter command to sort multiple rows

You’ll be displayed all the columns based on two selected criteria at once. So you’ve just got all the available data for desktop devices made in the USA.

sort and filter command to sort multiple rows


2. Applying Advanced Filter for Multiple Rows

You can also use Advanced Filter for sorting & filtering a table. In the picture below, 2 criteria have been added beside our original dataset. That means we’re going to filter the table based on those 2 criteria now with the Advanced Filter option. We’ll find out the computer devices made by Japan that cost under $900.

advanced filter multiple rows

📌 Step 1:

➤ From the Data ribbon, select the Advanced command from the Sort & Filter group of commands. A dialogue box will appear.

advanced filter multiple rows

📌 Step 2:

➤ Select the entire table or the array- B4:G23 for the List Range.

➤ Select Criteria table or the range of Cells- I12:J13 for Criteria Range.

➤ Now press OK & you’re done.

advanced filter multiple rows

Like in the picture below, you’ll be shown the filtered table based on the selected criteria.

advanced filter multiple rows


3. Using Autofilter to Customize Filter for Multiple Rows

By using AutoFilter, you can add multiple AND-OR criteria without typing formulas. Based on our dataset, we’ll determine the devices that cost equal to or between $600 & $900.

📌 Step 1:

➤ Assign the Filter buttons to all headers.

➤ From the Price menu, select the Custom Filter option from the Number Filter drop-down.

autofilter to filter multiple rows in excel

📌 Step 2:

➤ In the AutoFilter dialogue box, select the 1st price criteria as ‘Is greater than or equal to’ & then type 600 as the value for this criteria.

➤ Similarly, select the 2nd price criteria as ‘Is less than or equal to’ & input the value as 900 for this criteria.

➤ Press OK & you’re done.

autofilter to filter multiple rows in excel

You’ll get the following result with the Price column filtered.

autofilter to filter multiple rows in excel


4. Inserting FILTER Function to Filter Multiple Rows with Criteria

Before getting down to the uses of the FILTER function, we can have a look at how this function works.

  • The Objective of the Function:

Filter a range or an array.

  • Syntax:

=FILTER(array, include, [if_empty])

  • Arguments:

array- Array or range of cells that has to be filtered.

include- Criteria for the function.

[if_empty]- It’s optional. If the function finds nothing from the data then the message will be shown based on the texts inputted here.

4.1 Filtering Multiple Rows with AND Criteria

Based on our dataset, we’ll filter the devices & origin countries only. We’re adding two different criteria from two different columns here.

filter function multiple rows and logic criteria in excel

📌 Steps:

➤ Select the output Cell B30 & type:

=FILTER(B5:G23,(E5:E23=E26)*(C5:C23=E27),"Not Available")

➤ Press Enter & you’ll get the resultant array for desktops made in the USA only.

filter function multiple rows and logic criteria in excel

In this function, you have to add two or more criteria by using Asterisk(*) among them in the 2nd argument.


4.2 Filtering Multiple Rows with OR Criteria

Now we’ll add two different criteria for the same column. We’ll find out all the available data from the table for two origin countries: Japan and Taiwan.

📌 Steps:

➤ Select Cell B30 & type:

=FILTER(B5:G23,(E5:E23=E26)+(E5:E23=E27),"Not Available")

➤ Press Enter & you’ll get the filtered array right away.

filter function multiple rows or logic criteria in excel

To add multiple OR logic, you have to use the Plus(+) symbol between two criteria in the 2nd argument.


4.3 Filtering Multiple Rows with AND-OR Criteria

We can also combine both the AND and OR logic for multiple criteria with the FILTER function. Assuming that we want to know all data available in the table for notebooks made in Japan & Taiwan.

📌 Steps:

➤ In Cell B31, the related formula will be:

=FILTER(B5:G23,(C5:C23=E28)*((E5:E23=E26)+(E5:E23=E27)),"Not Available")

➤ Press Enter & you’ll get the return values as well.

filter function multiple rows and or logic criteria in excel


5. Filtering Duplicates from Multiple Rows

By using FILTER and COUNTIFS functions you can also find the duplicate rows in your table or chart. From the following dataset, we’ll find out the duplicate rows & they’ll be shown in another table at the bottom.

📌 Steps:

➤ Select Cell B26 & type:

=FILTER(B5:E23,COUNTIFS(B5:B23,B5:B23,C5:C23,C5:C23, D5:D23,D5:D23,E5:E23,E5:E23)>1,"No Duplicates")

➤ Press Enter & you’re done.

filter function multiple rows to find duplicates in excel

🔎 How Does This Formula Work?

➤ Here the COUNTIFS function searches for all duplicates & then counts those findings.

FILTER function then searches for the counts that are more than 1 & accordingly shows the data from the original table.


6. Filtering Out Rows Containing Blank Cells

In the following dataset, we have some blank cells now. We want to filter out the rows containing blank cells.

📌 Steps:

➤ In Cell B26, the related formula will be:

=FILTER(B5:E23,(B5:B23<>"")*(C5:C23<>"")* (D5:D23<>"")*(E5:E23<>""))

➤ After pressing Enter, you’ll get the filtered result at once.

filter function multiple rows to filter out blanks in excel


7. Filtering Multiple Rows to Find Specific Text

By using FILTER, ISNUMBER & SEARCH functions together, you can find data based on a specific string of text. Assuming, we want to find out the data from the table by searching for the texts- ”oc” in the column of brand names.

📌 Steps:

➤ Select the output Cell B26 & type:

=FILTER(B5:G23,ISNUMBER(SEARCH("oc",D5:D23)),"Not Found")

➤ Press Enter & you’ll get all the data for the selected brand names with specific texts “oc”.

filter function multiple rows to find specific text in excel

🔎 How Does This Formula Work?

SEARCH function searches for the text “oc” in Column B & returns with ‘1’ for each finding.

ISNUMBER identifies the numbers or all 1’s found from the SEARCH results & returns with the logical values- TRUE or FALSE.

➤ Finally, the FILTER function shows all the available data from the table based on the row numbers of the logical values- TRUE found from the previous step.


8. Filtering Multiple Rows for Calculation

By inserting the FILTER function within another related function, you can easily calculate the data found from the filtered results. For example, here we want to know the average price for the desktops made in the USA.

📌 Steps:

➤ Select the output Cell E28 & type:

=AVERAGE(FILTER(G5:G23,(E5:E23=E26)*(C5:C23=E27),"No Result"))

➤ Press Enter & you’ll be shown the evaluated result based on the filtered data at once.

filter function multiple rows for calculation in excel


9. FILTER function for Case-sensitive Text Strings from Multiple Rows

By using the EXACT function inside the FILTER function, you can find the case-sensitive texts as well. Based on our dataset, we’re going to find the available data for the brand name ‘Bytec’ only as if you look carefully at the bottom in Column B, there are similar names with lowercases- ‘bytec’ too.

📌 Steps:

➤ The related formula in Cell B28 will be:

=FILTER(B5:G23,EXACT(B5:B23,E25))

➤ Press Enter & the resultant array with Bytec only will be displayed.

filter function multiple rows to find case sensitive text in excel


10. Filtering Multiple Rows for Specific Columns

From a large range of columns, we can also find a specific number of columns by using the FILTER function twice. For example, from our dataset, we want to see the available data for Bytec notebooks with the columns of brand names, model names & prices.

📌 Steps:

➤ In Cell I14, the related formula will be:

=FILTER(FILTER(B5:G23,(B5:B23=J10)*(C5:C23=J11)),{1,0,1,0,0,1})

➤ Press Enter & you’ll be shown the specific columns only with selected criteria.

filter function multiple rows to show specific columns in excel

What’s happening here is that the outer FILTER function shows the specific columns based on the presence of 1 in the array of {1,0,1,0,0,1} in the 2nd or ‘include’ argument.


11. Showing Specific Number of Rows from Multiple Rows with FILTER Function

By using the INDEX function outside the FILTER function and assigning rows as well as column numbers, you can find the specific rows & columns. So from our dataset, we want to see the available data for brand names, model names & prices only based on the selected criteria of USA-produced desktops. And we also want to see 1st 3 findings only or rows as well.

📌 Steps:

➤ Select the output Cell I14 & type:

=INDEX(FILTER(B5:G23,(C5:C23=J10)*(E5:E23=J11)),{1;2;3},{1,3,6})

➤ Press Enter & you’ll find your customized table of data right away.

filter function multiple rows to show specific rows in excel


Concluding Words

I hope all of these methods mentioned above will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

Filter Multiple Criteria in Excel (4 Easy Ways)

How to Filter Data in Excel using Formula

Excel Filter Data Based on Cell Value (6 Efficient Ways)

How to Use Sort and Filter with Excel Table

Excel Advanced Filter [Multiple Columns & Criteria, Using Formula & with Wildcards]

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo