how to filter multiple rows in excel

Master Excel: Filtering Multiple Rows

By Richard Thomas
AD

Are you overwhelmed by massive Excel datasets, struggling to extract the information you need? Filtering can be your best ally, but dealing with multiple rows can be a challenge. Here's a comprehensive guide on how to filter multiple rows in Excel like a pro.

Before we dive in, let's assume you have a report with various sales figures, and you want to filter out rows where the 'Sales' column is above a certain amount and located in 'New York'.

Understanding Excel Filters

Excel filters allow you to show only the data that's relevant to your search, while hiding the rest. They're incredibly powerful when dealing with large datasets.

AD
How to Filter Multiple Rows in Excel: 11 Suitable Methods - ExcelDemy
How to Filter Multiple Rows in Excel: 11 Suitable Methods - ExcelDemy
An Easy Guide to Filtering Data in Excel
An Easy Guide to Filtering Data in Excel
Why Did My Excel Rows Disappear? Filter Fix for Beginners
Why Did My Excel Rows Disappear? Filter Fix for Beginners

To start filtering, select the header of the column you want to filter - in this case, 'City' and 'Sales'. Then, click on the 'Sort & Filter' button in the 'Home' tab (it looks like a funnel).

AutoFilter vs. Advanced Filter

AutoFilter is Excel's default filter, which is quick and easy to use but limited to simple criteria. It's ideal for basic data selection.

How to Filter Multiple Rows in Excel: 11 Suitable Methods - ExcelDemy
How to Filter Multiple Rows in Excel: 11 Suitable Methods - ExcelDemy
How to Filter Multiple Rows in Excel: 11 Suitable Methods - ExcelDemy
How to Filter Multiple Rows in Excel: 11 Suitable Methods - ExcelDemy

On the other hand, Advanced Filter is more flexible and allows you to set precise criteria down to individual cells. It's perfect for complex data filtering needs. We'll be focusing on AutoFilter here, but you can always explore Advanced Filter if you need more control.

Applying AutoFilters

After clicking on 'Sort & Filter', you'll see a dropdown with filter options for the selected column. Check 'Number Filters' for 'Sales' and 'Text Filters' for 'City'.

AD

Set your criteria, e.g., 'Sales' should be greater than 1000 and in the 'City' column, type 'New York'. Click 'OK'. Now, you should see only the rows that meet your criteria.

Managing Filtered Data

You can apply multiple filters by clicking on the dropdown in the header of each filtered column and adding more rules. To remove a filter, just click on the small icon at the top right of your filter (it looks like a funnel).

If your data is filtered but you need it to return to its original state, click on 'Clear' in the 'Sort & Filter' dropdown menu.

AD

Freezing and Sorting Panes

After filtering, you might want to sort, or 'freeze', certain panes to keep them static while you scroll through your data. To do this, right-click on the row below the one you want to freeze, then click 'Freeze Panes'.

To sort, click on the column heading, go to the 'Data' tab, and click 'Sort'. Here, you can change the sort order, apply custom sorts, or even sort by multiple columns.

There you have it! You're now equipped to filter through multiple rows in Excel with ease. So, the next time you're dealing with a mountain of data, remember - filtering is your friend. Happy filtering!

AD
AD