What Is Filter In Excel?
The FILTER in Excel lets users fetch particular data from a huge database. The FILTER function presents the required data by filtering any inappropriate information from the sheet. Data filtering takes place based on the conditions specified by the user.
For example, the table below shows the name of persons and their places. We want to perform the following tasks in Excel to understand the FILTER mechanism in Excel by fetching out the person (column A) from the UK (column B).
Duplicate the table, and click the Filter option from the Sort and Filter in the Excel drop-down under the Editing group. The arrows appear on both table columns, right-click on column B, and select the Filter option. As we have selected ‘UK,’ the whole information of searched value is visible.
Key Takeaways
- The FILTER function in Excel fetches particular data based on some filters specified by the user from a huge database. Therefore, one should select the entire table to set proper filtering criteria.
- Text values, numbers, dates, and blank and non-blank cells can be filtered using the FILTER function. It does not read merged cells.
- It is better to keep headlines crisp instead of using lengthy texts. One can use the ‘Wrap Text’ function to format headlines with more than one line before using the FILTER function in excel.
Table of Contents
How To Add Filter In Excel?
- With Filter Option Under The Home Tab
We can access the Filter option under the Home tab in the following steps:
- Choose a cell range.
- Go to the Home tab in Excel.
- Click on the Sort & Filter option from the Editing
- Select the Filter option from the drop-down list.
- With Filter Option Under The Data Tab
We can access the Filter option under the Data tab in the following steps:
- Choose a cell range.
- Go to the Data tab in Excel.
- Click on the Filter option from the Sort & Filter
- With the Shortcut Key
The shortcut for FILTER in Excel is CTRL+SHIFT+L. Therefore, the user has to insert data, select the cell, and then press the shortcut keys CTRL+SHIFT+L to extract the data similarly.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How To Use Filter In Excel?
We can filter data in Excel using any of the methods mentioned above. Let us understand the method to filter cells with the following examples.
Example #1 – Filter Out Blanks / Non-Blanks
The table below shows the list of fruits, their Price/Unit, Unit, and Total Cost in columns A, B, C, and D, respectively. But, first, we need to use the following steps to filter out blanks and non-blanks in Excel.
The steps used to filter out blanks and non-blanks in Excel are as follows:
Step 1: First, we must create a duplicate table to understand the function better.
Step 2: Select the whole data (table) to filter.
Step 3: Go to the Home tab and click the Sort & Filter option in the Editing group.
Select Filter from the drop-down list, or press the shortcut keys CTRL + SHIFT + L as shown in the following image.
Please Note: The first three steps, as mentioned above, are the same for filtering the blanks and non-blanks cells.
- For blank cells:
The following steps help filter blank cells in Excel.
Step 4: The filter is added to the selected data range.
Step 5: Click the drop-down arrow highlighted in green color.
Step 6: Select the Blanks check box for filtering out the blank cells.
Step 7: Click OK.
Step 8: The filter function returns only the blank cells, i.e., the columns and rows with empty cells are visible, as shown in the following image.
- For Non-Blank cells
As mentioned above, the first three steps are the same for filtering both blanks and non-blanks in Excel.
The following steps help filter the non-blank cells in Excel.
Step 4: The filter is added to the selected data range.
Step 5: Click the drop-down arrow highlighted in yellow color.
Step 6: Deselect the Blanks check box for filtering out the non-blank cells.
Step 7: Click OK.
Step 8: The filter function returns only the non-blank cells, i.e., the columns and rows with non-empty cells (cells with data) are visible, as shown in the following image.
- Therefore, we can filter both blank (empty cells) and non-blank (cells with data) using the filter function in excel.
Example #2 – Filter Text Values
Consider the below table of a bakery’s price list of various items. The products are visible in column A as items, and prices in column B. Therefore, we need to use the following steps to filter text values in Excel.
The steps used to filter text values in Excel are as follows:
Step 1: We need to create a duplicate table to understand the function better.
Step 2: Select the whole data (table) to filter.
Step 3: Go to the Home tab and click the Sort & Filter option from the Editing group.
Select Filter from the drop-down list, or press the shortcut keys CTRL + SHIFT + L as shown in the following image.
Step 4: The filter is added to the selected data range.
Step 5: Click the drop-down arrow highlighted in green color.
Please Note: When excel identifies text in the data, it automatically displays the text filters option.
Step 6: Select the Cake check box to fetch the cake information in the cells.
Step 7: Click OK.
Step 8: The Filter function returns only the cells with data related to ‘Cake,’ i.e., the columns and rows with the text ‘Cake’ are visible, as shown in the following image.
- Therefore, we can filter text values from the table using the function filter in excel.
Example #3 – Filter Numbers
Let us consider the same table (a bakery’s price list with a list of items in column A and price in column B) we used in Example 2. We need to use the following steps to filter numbers in Excel.
The steps used to filter numbers in Excel are as follows:
Step 1: We need to create a duplicate table to understand the function better.
Step 2: Select the whole data (table) to filter.
Step 3: Go to the Home tab and click the Sort & Filter option in the Editing group.
Select Filter from the drop-down list, or use the shortcut keys CTRL + SHIFT + L as shown in the following image.
Step 4: The filter is added to the selected data range.
Step 5: Click the drop-down arrow highlighted in green color.
Please note: When excel identifies numerical data in the column, it automatically displays the Number Filters option.
Step 6: Select the Number Filters option. We can select any option available from the list.
For this example, let us choose the option Greater than to filter data greater than the specified value.
Step 7: The Custom AutoFilter window appears.
We can see that the table’s column name is already visible and greater than the selected option. Therefore, we have to enter the value with which the filter function in excel filters the data that is greater than the value.
For this example, let us filter the data that is greater than $1,500.
Step 8: Click OK.
Step 9: The Filter function returns the cells with a value greater than the $1,500 price, i.e., the columns and rows with the number value greater than ‘$1,500’ are visible, as shown in the following image.
- Therefore, we can filter numbers from the table using the function filter in excel.
Example #4 – Filter Dates
The following table shows a company’s ID, Date of joining, Location, Age, and Gender of employees in columns A, B, C, D, and E, respectively. We need to use the following steps to Filter Dates in Excel.
The steps used to filter dates in Excel are as follows:
Step 1: We need to create a duplicate table to understand the function better.
Step 2: Select the whole data (table) to filter.
Step 3: Go to the Home tab and click the Sort & Filter option in the Editing group. Select Filter from the drop-down list, or press the shortcut keys CTRL + SHIFT + L as shown in the following image.
Step 4: The filter is added to the selected data range.
Step 5: Click the drop-down arrow highlighted in yellow color.
Please Note: When excel identifies Dates in the column, it automatically displays the Date Filters option.
Step 6: Select the Date Filters option. We can select any option available from the list.
For this example, let us choose the option Equals to filter data that is equal to the mentioned value.
Step 7: The Custom AutoFilter window pops up.
We can see that the table’s column name is already visible, and the equals option is selected. Next, we have to enter the value with which the filter function in excel filters the data that is equal to the mentioned date.
For this example, let us filter the date equal to 02-04-2022.
Step 8: Click OK.
Step 9: The filter function returns the cells with the same value, i.e., values equal to 02-04-2022. The columns and rows with the same date are visible, as shown in the following image.
- Therefore, we can filter dates from the table using the function filter in excel.
Example #5 – Excel Filter by Color
The table below shows menswear and its prices. The items are visible in column A and the prices in column B. We need to use the following steps in excel to filter data by color.
The steps used to filter by color in Excel are as follows:
Step 1: We need to create a duplicate table to understand the function better.
Step 2: Select the whole data (table) to filter.
Step 3: Go to the Home tab and click the Sort & Filter option from the Editing group. Select Filter from the drop-down list, or use the shortcut keys CTRL + SHIFT + L as shown in the following image.
Step 4: The filter is added to the selected data range.
Step 5: Click the drop-down arrow highlighted in green color.
Please Note: When excel identifies colored data in the column, it automatically displays the filter by color option.
Step 6: Select the Filter by Color option. We can select any option available from the list.
Let us choose the color highlighted in the following image for this example.
Step 7: Click OK.
Step 8: The filter function returns the cells with the same color, i.e., the columns and rows with the same color are visible, as shown in the following image.
- Therefore, we can color filter in Excel using the Filter function in excel.
Example #6 – How To Filter in Excel with Search
Consider the table below with a list of vegetables, their price, total quantity, and price per quantity in columns A, B, C, and D, respectively. Then, we need to use the following steps to filter in Excel with search.
The steps used to Filter in excel with the search are as follows:
Step 1: We need to create a duplicate table to understand the function better.
Step 2: Select the whole data (table) to filter.
Step 3: Go to the Home tab and click the Sort & Filter option in the Editing group. Select Filter from the drop-down list, or use the shortcut keys CTRL + SHIFT + L as shown in the following image.
Step 4: The filter is added to the selected data range.
Step 5: Click the drop-down arrow highlighted in green color.
Step 6: Search the #DIV/0! Error in the search box for filtering cell.
Step 7: Click OK.
Step 8: The filter function returns the cells that match the search option, #DIV/0! i.e., the columns and rows with data that match the search results are visible, as shown in the following image.
- Therefore, we can filter with a search using the function filter in excel.
Example #7 – Filter By Selected Cell’s Value Or Format
The below image shows the cash flows of a bank account along with the date. The dates and items are mentioned in columns A and B, and the amount is displayed in column C. We need to use the following steps in Excel to filter by selected cell’s value or format.
The steps used to filter by selected cell’s value or format are as follows:
Step 1: We need to create a duplicate table to understand the function better.
Step 2: Select the whole data (table) to filter.
Step 3: Go to the Home tab and click the Sort & Filter option in the Editing group. Select Filter from the drop-down list, or press the shortcut keys CTRL + SHIFT + L as shown in the following image.
Step 4: The filter is added to the selected data range.
Step 5: Click the drop-down arrow highlighted in green color.
Step 6: Select the Deposit check box in the search for filtering the cells according to the selected value.
Step 7: Click OK.
Step 8: The filter function returns the cells that match the search option Deposit, i.e., the columns and rows with data that match the search results are displayed as shown in the following image.
- Therefore, we can filter by selected cell’s value or format using the function filter in excel.
How To Clear Filter?
We can clear the Filter option under the Data Tab with the following steps:
- First, choose a cell range or a table to clear filter results.
- Then, go to the Data tab in Excel.
- Select the Clear option from the Sort & Filter
- The Clear option clears the filter results.
How To Remove Filter In Excel
We can remove the filter function in Excel whenever needed. To remove it, we can use the following steps:
- First, choose a cell range or a table to clear filter results.
- Next, go to the Data tab in Excel.
- Then, click the Filter option from the Sort & Filter group.
- Finally, we can turn off the filter option by clicking the filter option again.
Another shortcut method to enable FILTER in Excel is using the shortcut keys ALT+D+F+F. The user needs to insert data, select the cell, and press the shortcut keys to extract the data similarly.
Excel Filter Not Working
The filter function may not work because of these five reasons. They are:
- The column heading should be written correctly. If the heading is lengthy and consists of more than one line, it is recommended to use the Wrap Text Excel option to format the partition in the heading cell.
- The FILTER in Excel does not work on merged cells. The function does not understand whether the columns or rows are merged; it may not pick up all the merged columns or rows.
- Whole data (the entire table) should be selected when the Filter function in excel is used. Excel cannot set the filter area if all the data is not specified.
- The Excel FILTER does not work on the data having Errors. Therefore, if there are errors, it is advised to find them using FILTER and remove them to continue using the filter option.
- Check if there are any hidden rows in the data because if the rows are not unhidden, the data will be inappropriate.
Important Things To Note
- Excel FILTER sorts both the blank and non-blank cells of the data.
- We can use wild characters like question marks (?), asterisks (*), and tilde ( ̴ ) to filter data in Excel.
- The function can filter data with multiple criteria and conditions.
- Grouping of data can be made possible by using FILTER in Excel.
- It can also be used to search any word or text string.
Frequently Asked Questions (FAQs)
Let us consider an example to understand how to use FILTER in Excel.
Consider the table with the marks obtained by students in columns A and B, respectively. Then, we need to use the following steps in Excel to understand how to FILTER in Excel.
The steps to FILTER in Excel are listed as follows:
Step 1: We need to create a duplicate table to understand the function better.
Step 2: Select the whole data (table) to filter.
Step 3: Go to the Home tab and click the Sort & Filter option in the Editing group. Select Filter from the drop-down list, or press the shortcut keys CTRL + SHIFT + L as shown in the following image.
Step 4: The filter is added to the selected data range.
Step 5: Click the drop-down arrow.
Step 6: Select the Mary check box in the search for fetching the marks obtained by Mary from the data.
Step 7: Click OK. 
Step 8: The marks obtained by Mary are displayed as shown in the following image. 
The Advanced FILTER in Excel is an updated version of the FILTER function. It is comparatively easier to use and understand. We can trim the required data from the whole data using Advance FILTER in Excel. Searching for data from a huge database is difficult, but the function helps us find the information easily, even from thousands of rows and columns.
The steps required to use the Advanced FILTER option are as follows;
a. Choose the range.
b. Go to the Data tab of Excel.
c. Click the Advanced option from the Sort & Filter group.
d. The Advanced Filter window pops up.
e. Choose the Action option from the checkbox.
f. Enter the List range and Criteria range in the drop-down boxes.
g. Click OK.
The FILTER option in Excel is available in both the Home and Data tabs.
The steps to access the FILTER option in Excel under the Home tab are listed as follows:
a. Click on the Home tab of Excel.
b. Choose the Sort & Filter option from the Editing group
c. Select the Filter option from the drop-down list.
The steps to access the FILTER option in Excel under the Data tab are listed as follows:
a. Click the Data tab of Excel.
b. Choose the Filter from the Sort & Filter group
Download Template
This article must be helpful to understand the FILTER in Excel with its examples. You can download the template here to use it instantly.
Recommended Articles
This article has been a guide to Filter in Excel. Here we learn how to add, remove, clear, and color it with advanced examples and a downloadable excel template. You can learn more from the following articles –
Leave a Reply