Guide to How to Add, Use and Remove Filter in Excel

Method 1 – Add Filter in Excel Using Home Tab

Select a random cell in the range or the whole range => navigate to the Home tab => Editing group => Sort & Filter drop-down => select Filter.

It’ll add the filter button to the headers of the columns.

Method 2 – Insert Filter from Data Tab

Select the range of cells in the Department column => navigate to the Data tab => and click the Filter icon to add a Filter to the headers.

Method 3 – Applying Keyboard Shortcut to Add Filter

Provides a way to enable the Filter with keyboard shortcut. Select a cell in the range and press CTRL+SHIFT+L. Excel will add a Filter button to every column in the range.

Note: If you want to apply multiple filters in Excel to the whole range of your data, select a random cell or the whole range, but when you need to add a Filter to a specific column, just select the cells in that specific column.

After enabling this feature, you can filter data based on cell value in Excel. We can filter Manager from the Designation column following the GIF below.

How to Filter Data in Excel

You can filter data in Excel with the Filter feature according to your needs. For this purpose, Excel provides a number of ways:
– filter by text
– filter by number
– filter by date
– filter by search or partial match
– filter by cell color or text color

1. Filter by Text

For the dataset we have used previously, we want to filter data from the IT department. We will use the Text Filter.

Click on the drop-down arrow in the Department column.

Click Text Filters => select Equals.

This command launches the Custom Autofilter dialog box.
To filter data by the text “IT”, type IT beside the equals field => click OK.

Excel makes only the relevant data visible to the IT department.

2. Filter by Number

Click the dropdown in the Annual Salary column.

From the dropdown options, select Number Filers => click Greater Than Or Equal to.

In the Custom Autofilter dialog box, type 500000 right beside the is greater than or equal field => and click OK.

This only filters the information of the employees about the given condition.

3. Filter by Date

Our dataset includes the Hire Date of the employees. We want to extract the information of the employees whose Hire Date is between 1 March 2023 and 31 March 2023.

Click the dropdown in the Hire Date column.

Click Date Filters and it will show a range of filter options for date => select Between. You can also select Custom Filter to use a custom date filter in Excel. Both of these commands will launch the Custom Autofilter dialog box.

In the is after or equal to field, type 1-Mar-23, and in the is before or equal to field, type 31-Mar-23 => click OK.

See! Excel has extracted only the data for the month of March.

4. Filter Data by Search or Partial Match

Click the drop-down in the Designation column.

In the search bar of the menu, type “Co” => click OK.

Match the relevant data whose first two letters start with “Co” which means “Coordinator” and show that data only.

5. Filter Data by Color

Color filter in Excel allows you to filter your data by color. You can filter your data
– Using cell color
– Using text color

5.1. Filter by Cell Color

In the Annual Salary column of our dataset, we can see 3 colors in that cell range: green, yellow, and red. We want to filter data based on which cell color is green.

Click on the filter button in the Annual Salary column.

Select the Filter by Color option in the menu bar => choose the Green color to filter by cell color.

Excel is only showing the filtered cells with the green color.

5.2. Filter by Text Color

The Annual Salary column includes three text colors: green, blue, and red. We will choose green to filter by text color.

Click on the Filter button in the Annual Salary column.

Select the Filter by Color option in the menu bar => choose Red color to filter by text color.

Excel has filtered the data that only contain texts with red color.

How to Apply Filter in Multiple Columns to Extract Data

Click the dropdown in the Department column.

Select IT from the filter options => click OK.

The Department column will only show the cell values with IT.
Click the drop-down in the Designation column.

Select Manager => click OK.

Excel will show only the information of the Manager from the IT department.

How to Filter Blank Cells in Excel

Click the dropdown of the Resign Date column.

From the dropdown options, keep the Blanks option selected only => click OK.

Excel is showing the rows with blank cells only and all the other rows are filtered out.

How to Use FILTER Function to Filter Data in Excel

1. Filter Data Dynamically

Select cell B17 => go to the Data tab => Data Tools group => select the Data Validation feature.

This launches the Data Validation dialog box. In the Settings tab, choose List in the Allow field.
In the Source field, select the list range or type:

=$B$6:$D$6

Click OK.

See a drop-down list.

Choose Marketing from the drop-down options.
Select cell C16 => insert the following formula:

=FILTER(B7:D13,B16= B6:D6) & ""

Press ENTER. It will return all the employees of the selected department to cell B16.

Selecting a different value from the drop-down option in cell B16 will also change the output of the FILTER function in cell C16.

2. Filter Duplicate Values

Select cell F7 => type the formula below.

=FILTER(B7:D21,COUNTIFS(C7:C21,C7:C21,D7:D21,D7:D21)>1,"Duplicates not found")

Press ENTER. You will get the duplicates that occurred in the Department and Designation column. If no duplicates were found, the formula would return “Duplicates not found”.

How to Use Advanced Filter in Excel

1. Find Unique Values in Worksheet

Let’s use the Excel Advanced Filter for unique records only. Our dataset has some duplicate values. From there, we will find only rows with unique records with the Advanced Filter.

Select a random cell in the range => go to the Data tab => click Advanced. It will launch the Advanced Filter dialog box.

Keep Copy to another location marked.
Fill the List range field by selecting your data range $B$6:$F$17.
To fill in the Copy to field, select $B$19 in the worksheet where you want to copy the result.
Put a checkmark on the Unique records only field.
Click OK.

You can see that Advanced Filter has extracted the unique records.

2. Advanced Filter with Wildcard Character to Extract Value

Contains employee names where several people have common surnames. We will use “*Alvarado” to get all the information about employees whose surname is “Alvarado”.

Select a random cell in the range => go to the Data tab => and click Advanced to launch the Advanced Filter dialog box.

Keep Copy to another location marked.
Fill the List range field by selecting your data range $B$6:$F$17.
Set the criteria range B$19:$B$20.
To fill in the Copy to field, select $B$22 in the worksheet where you want to copy the result.
Click OK.

Excel has extracted the employee name, titled Alvarado.

3. Advanced Filter with Multiple Criteria

Select a cell => go to the Data tab => and click Advanced to launch the Advanced Filter dialog box.

Keep Copy to another location marked.
Fill the List range field by selecting your data range $B$6:$F$17.
Set the criteria range B$20:$C$21.
To fill in the Copy to field, select $B$23 in the worksheet where you want to copy the result.
Click OK.

Excel will extract the data of “Manager” from “IT” department.

4. Advanced Filter to Exclude Blank Cells

We can also use the Excel Advanced Filter to exclude blank cells. Our dataset includes some blank cells, from which we want to exclude the rows with blanks.

Insert the formula below in cell G8.

=D7<>"".

Press ENTER. It’ll return TRUE, indicating that the value in cell D7 is not an empty string.

Launch the Advanced Filter dialog box from the Data tab.
Keep Copy to another location marked.
Fill the List range field by selecting your data range $B$6:$E$18.
Set the criteria range G$7:$G$8.
To fill in the Copy to field, select $B$20 in the worksheet where you want to copy the result.
Click OK.

You will get the result without the blank cells.

How to Filter by Selected Icon or Format

Select cell F7 formatted with the green arrow => right-click to bring up the context menu.
Select Filter => click Filter by Selected Cell’s Icon.

Excel will filter your data according to the format you selected in the first step.

How to Use Filter in Protected Excel Sheet

Excel allows you to use Filter in a protected Excel sheet.

For the dataset below, we have enabled Filter in the Department and Designation columns. We will use this filter on a protected worksheet.

To protect the worksheet, go to the Review tab => and click Protect Sheet to launch the Protect Sheet dialog box.

Keep the Protect worksheet and contents of locked cells marked.
Mark Use AutoFilter option only => click OK.

Your worksheet is now protected. You won’t be able to select any cells. But you have access to the Filter button.

Click the drop-down in the Department column.

Select IT => click OK.

Although you can’t select or edit any cell, Excel allows you to filter data in a protected sheet in this way.

You can also filter the Designation column similarly. But you won’t be able to filter other columns.

How to Filter Data in Excel Pivot Table

Create a Pivot Table with this data and then filter the data in that Pivot Table.

Select any cell in the range => go to the Insert tab => select PivotTable.

The PivotTable from table or range dialog box will appear. Choose New Worksheet to place the PivotTable => click OK.

Excel will take you to a new worksheet. The PivotTable Fields pane will appear on the right side of that new worksheet.

Insert the parameters in the following field:

– Filters: States
– Rows: Product
– Values: Quantity and Sales

Excel will create the Pivot Table.

Select a cell in the PivotTable => go to the PivotTable Analyze tab => and click Insert Slicer.

In the Insert Slicers dialog box, select Product and States => click OK.

This will insert two slicers titled Product and States.

Select any item in the Product Slicer, and you will see that the States slicer automatically shows the state where the product is available. The Pivot Table will show the price of that product.

How to Reapply Filter After Editing Data in Excel

Filter the department column by “IT” as we have done previously.

Change a filtered cell from “IT” to “Sales”.

You will see the Filter is not updating the list.

To update the list, go to the Data tab => Sort & Filter group => click Reapply.

You will see the updated filtered data.

How to Clear Filter in Excel

Click on the Filter button to bring up the filter options.

Select Clear Filter from “Department” option.

It will make the Filter inactive and show all of the data again.

How to Remove Filter in Excel

Navigate to the Data tab => Sort & Filter group => click the Filter icon to deactivate it.

With the deactivation, Excel will remove the Filter button from your data.

Note: The shortcut key for both inserting and removing the filter is CTRL+SHIFT+L.

How to Fix If Filter Is Not Working in Excel

Sometimes you may face some problems, like the Excel filter not working after a certain row or not filtering the entire column. This may occur for several reasons.

If your data range includes blank rows, then you may face a problem because the Excel filter stops at blank row. In this case, select your data range manually before applying Filter. You can also Sort your data in ascending order and then apply the Filter command.
When Excel doesn’t update after any change in filtered data, just use the Reapply command.
Merged cells can interfere with the functioning of filters. If merging is necessary, then learn to filter in Excel with merged cells.
If there are variations in cell formats (such as text mixed with numbers), Excel might have difficulty applying filters. Ensure that all data in the filtered range is formatted consistently.

What Is the Difference Between Sort and Filter in Excel

Select a cell in the column you want to sort => Data tab => click Sort A to Z.

It will expand the selection and sort your data in alphabetically ascending order.

You can also access the Sort feature from the Filter drop-down options.

Download Practice Workbook

Filter in Excel.xlsx

Filter in Excel: Knowledge Hub

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!