How to Filter in Excel

Hello there! Are you looking to learn how to filter in Excel? You’ve come to the right place! Filtering is an incredibly useful feature in Excel that allows you to quickly and easily sort through large amounts of data to find exactly what you’re looking for. In this article, we’ll explore:

• Basic filtering
• Filter by selected value
• Automatically subtotal the visible rows
• FILTER function

Let’s get to it!

Video

Narrative

Basic Filtering

To start, we will explore how to apply filters using checkboxes. First, select any cell within the range you want to filter.

Then, navigate to the data ribbon tab and select filter. This will activate the filter and sort controls. You will see drop-down menus for each column header.

To filter your data, you can use the checkboxes. For example, you can select to show everything in the Northeast or Southeast regions by checking the appropriate boxes. When you have made your selections, click “OK,” and the filtered data will be displayed.

Another option is to use the text filter options, which allow you to filter rows based on specific text criteria. You can choose to only show rows that equal, do not equal, begin with, end with, or contain specific text.

To clear the filters, simply click the filter button again, and all checkboxes and text filter options will be cleared.

There is also a shortcut to filter for specific text criteria. Simply right-click on the cell you want to filter and select “Filter by Selected Cell’s Value.” This will apply a filter to show only the data that matches the selected cell’s value.

Automatically compute subtotal of visible rows

Another way to filter data is by using tables. To do this, select any cell within the range you want to filter and click “Insert Table.” This will convert the range into a table and activate the filter and sort controls.

The benefit of using tables is that they offer additional properties such as the “Total Row.” This allows you to see the total for a column, and the total row will adjust automatically based on any filters that are applied. To activate the Total Row, simply select the table, go to the “Table Design” ribbon tab, and click “Total Row.”

As you apply filters, the total will update to reflect only the visible rows.

Copying Filtered Data with the FILTER function

Now we’ll use a formula to take a copy of the filtered data to another worksheet. Assuming the data is stored in a Table named Table1, we can enter the following formula in any cell on a new worksheet:

=FILTER(Table1,Table1[Region]="Midwest")

Alternatively, we could store the desired region in a cell, such as B7, and update the formula as follows:

=FILTER(Table1,Table1[Region]=B7)

The formula results:

The formula returns a dynamic subset of the Table data. If the data changes, the formula automatically updates accordingly.

In conclusion, filtering data in Excel is a powerful tool that allows you to analyze specific data within a larger dataset. By using filters, you can display only the data that is relevant to your analysis. I hope this article will help you filter data with checkboxes, tables, and formulas.

If you have any other filtering tips or tricks, please share by posting a comment below … thanks!

Sample File