Quick Links
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 theDatatab and clickFilterin theSort & Filtergroup to create a custom filter.
Adown arrow buttonwill 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 (columnAin our screenshot), click thedown arrow buttonin the header of the column you want to use as a filter (A1for us) and selectNumber Filterin the menu. Then, select one of the comparisons in the menu. In our example, we will use theGreater Than Or Equal Tocomparison.
A dialog box will appear, asking you to enter specific criteria to filter the data with. Once you enter it, clickOKto 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 youcreate 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 (columnB).
To do that, click thedown arrowbutton in the header of the column you want to use as a filter. Then, selectFilterand pick a comparison. We will use theEqualscomparison in our example.
Specify the criteria for the comparison in the dialog box and then click theOKbutton to filter the table.
Using Advanced Filtering
Excel has an advanced filter you may 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 canuse Excel’s logical operatorslike the ones in the table below:
Description
Comparison
Not equal to
Greater than
All conditions must be true (it has to be in the same row)
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 cellA5to follow along:
Department
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’re able to copy and paste the below criteria range in cellA1to follow along:
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:
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 canadd Slicers to a table in Excelto 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 theInserttab, and then clickSlicerin theFiltersgroup of the ribbon.
A dialog box will appear with the table headers listed. Tick the ones you want to use as filters and then clickOK.
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 may useExcel’s FILTER function.
