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.

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.

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.

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.

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.

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

How to Filter Multiple Rows in Excel: 11 Suitable Approaches

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.

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.

📌 Step 2:

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

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

📌 Step 3:

➤ Now select Desktop from the Device menu.

➤ Press OK & you’re done.

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.

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.

📌 Step 1:

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

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

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

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.

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

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

Read More: How to Filter with Multiple Criteria 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.

📌 Steps:

➤ Select the output Cell B30 & type:

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

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:

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

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:

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

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:

➤ Press Enter & you’re done.

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

Read More: How to Filter Unique Values in Excel

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:

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

Read More: [Fixed!] Excel Filter Stops at Blank Row

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:

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

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

Read More: How to Filter Data Based on Cell Value in Excel

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 desktops made in the USA.

📌 Steps:

➤ Select the output Cell E28 & type:

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

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:

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

Read More: How to Apply Multiple Filters 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:

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

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.

Read More: How to Filter Multiple Columns Independently in Excel

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:

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

Download Practice Workbook

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

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.

Related Articles