An Easy Guide to Filtering Data in Excel

Key Takeaways

• To easily filter data in Excel, you can create a custom filter based on different criteria (e.g., text, numbers, and dates).
• If you want to filter the data based on multiple conditions, you can use an advanced filter.
• You can also use a Slicer to make filtering easier and interactive.

An Excel filter selectively displays rows that meet specific criteria, making it easier to analyze large datasets. We’ll show you an easy way to do that, as well as how to use advanced filters and Slicers.

How to Filter Data in a Range

Let’s start by looking at filtering data in a range (a group of neighboring cells). First, highlight the headers of the columns you want to filter in the range. Then, select the Data tab and click Filter in the Sort & Filter group to create a custom filter.

A down arrow button will appear in the headers. You can then filter data based on criteria like text, numbers, and dates, depending on the format of the data in the column.

For example, to filter based on numbers (column A in our screenshot), click the down arrow button in the header of the column you want to use as a filter (A1 for us) and select Number Filter in the menu. Then, select one of the comparisons in the menu. In our example, we will use the Greater Than Or Equal To comparison.

A dialog box will appear, asking you to enter specific criteria to filter the data with. Once you enter it, click OK to apply the custom filter.

Based on the filter you picked and the criteria you set, the range will be filtered to show the rows that meet the conditions you specified.

How to Filter Data in a Table

When you create a table in Excel, the headers will already have filters added to them, making things a little easier. Otherwise, the process is identical to filtering data in ranges. For instance, let’s filter the data based on text (column B).

To do that, click the down arrow button in the header of the column you want to use as a filter. Then, select Filter and pick a comparison. We will use the Equals comparison in our example.

Specify the criteria for the comparison in the dialog box and then click the OK button to filter the table.

Using Advanced Filtering

Excel has an advanced filter you can use to create complex filters that combine multiple criteria. This filter helps in scenarios where you need to filter data based on multiple conditions.

If you want to combine the criteria, you can use Excel’s logical operators like the ones in the table below:

Operator	Type	Description
=	Comparison	Equal to
<>	Comparison	Not equal to
>	Comparison	Greater than
<	Comparison	Less than
AND	Logical	All conditions must be true (it has to be in the same row)
OR	Logical	At least one condition must be true (it must be in different rows)

Let’s practice by filtering the range below to only show people who are above 30 years old and work in the HR department. You can copy and paste the range in cell A5 to follow along:

Name	Age	Department
John	30	HR
Sarah	25	IT
David	30	Finance
Emily	35	HR
Michael	35	HR
Jessica	24	HR
Jordan	40	Sales

To use the advanced filter, you first need to create a criteria range on a different part of the sheet, ensuring that it has the same headers as the range with the data. We are going to place the criteria above our initial range, as in the screenshot below.

You can copy and paste the below criteria range in cell A1 to follow along:

Name	Age	Department
	>30	=”HR”

Putting the criteria in the same row satisfies the AND condition. If it was in a separate row, it would satisfy the OR condition.

Next, follow the steps below:

1. In the Data tab, click Advanced in the Sort & Filter group of the ribbon.
2. Click inside the List range text box and select the range with the data (A5:C12).
3. Click the Criteria range text box and select the criteria range (A1:C2).
4. Click OK to apply the filter.

Once the filter is applied, only two rows should be displayed; these will be people who are above 30 and work in the HR department.

Use Slicers for Interactive Filtering

You can add Slicers to a table in Excel to filter table data interactively. This allows you to quickly view specific subsets of data without creating complex filters.

To add a Slicer, click anywhere on a table, select the Insert tab, and then click Slicer in the Filters group of the ribbon.

A dialog box will appear with the table headers listed. Tick the ones you want to use as filters and then click OK.

Each header will have its own Slicer and its values will appear as buttons (these are the criteria for filtering the table). Clicking a button on the Slicer will filter the table.

With this foundational knowledge, you should be able to dive deeper into filtering data in Excel. If you want to get even more advanced with filters, you can use Excel’s FILTER function.

Source link