Excel Filter Data Based on Cell Value (6 Efficient Ways)

Filtered data

Sometimes data in a worksheet is too complicated or crammed that we face difficulties to find out desired data. Retrieve data from a dataset sometimes turns into a challenge. For this kind of purpose, we need to filter our data. Excel provides various ways to Filter Data Based on Cell Value. In this article, I will show how to filter data based on cell value.

Download the Practice WorkBook

Here method 1 to 4 is in the first file and 5 to 6 in the second one.

6 Ways to Filter Data Based on Cell Value in Excel 

1. Filter Data Based on Cell Value Using Filter Option

There is a built-in option which is called Filter in Excel. This option helps to filter any type of data. Let’s assume we have a dataset of some products with their monthly sales, total sales, and average sales. Now we will filter the data based on their average sales.

Filter Data Based on Cell Value Using Filter Option

Our target is to find the rows where average sales are between $2000 and $4000.

Step 1: Select the cell within the range.

Select the cell with in the range

Step 2: Now follow the steps.

  1. Go to the Data tab.
  2. Select Filter option.
    Select filter option

Step 3: Select the arrow of the Average Sales column.

Select the arrow

Step 4: As we want to filter numbers that’s why first select the Number Filter option then Between

Select number

Step 5: Then enter the filter criteria (as our criteria are numbers between 2000 and 4000, that’ why enter the ranges) then press the Ok button

Enter the filter criteria

Step 6: Now observe the filter data

Filtered data

2. Filter Data Based on Cell Value Using Filter Function

Let’s see how we can use Excel’s Filter Function for the previous dataset. Our scenario will be such as:

Filter Data Based on Cell Value Using Filter Function

Let’s see the fundamentals of the Filter function.

FILTER (array, include, [if_empty])

This is the syntax of the Filter function. It takes the range with given criteria and returns the array of filtered values. The breakdown of the function is as follows.

Array -> This is the range or array to filter.

include -> This takes the boolean array, provided as criteria.

[if_empty] -> Lastly, it is the value to return when no results are returned. This argument is optional.

Step 1: Enter the formula in cell A16

=FILTER(A4:G11, (G4:G11 >2000 )*(G4:G11<4000))

Formula Explanation

The first portion A4:G11 is our whole table range. Then as our condition is average sales greater than $2000 and less than $4000, that’s why (G4:G11 >2000 )*(G4:G11<4000)

Enter the data using Filter Function

Step 2: Press Enter and all the filtered data will be shown

All the filtered data

[ Note: Filter function is only available on Office 365. It will not be available in Excel 2019 or earlier versions. ]

3. Filter Data Based on Cell Values that Contain Text

Let’s assume that we have a dataset of products with their sales persons’ names, joining dates, and total sales. Now we will filter data based on the salesperson’s name.

filter data based on text

Step 1: Enter the formula in cell A15

=FILTER(A4:D11,ISNUMBER(SEARCH("Christopher",B4:B11)))

Formula Explanation

There are two additional functions used in the formula. They are  ISNUMBER

and SEARCH

Click on the function’s name to learn more about them.

Here A4:D11 is the total table range. SEARCH(“Christopher”,B4:B11) this portion finds the name Christopher from B4:B11 range. Lastly, the ISNUMBER function is used to convert the result from SEARCH into TRUE or FALSE.

 Enter the formula using filter and isnumber and search function

Step 2: Press Enter and see the output

 Press Enter and see the output

4. Filter Data Based on Date

Now we will filter data based on Date for the same dataset used on method 5. Our concern is to find the salespersons who joined after July.

Step 1: Enter the formula in cell A15

=FILTER(A4:D11,MONTH(C4:C11) > 7,"No data")

Formula Explanation

Again, the same way, A4:D11 is our table range. MONTH(C4:C11) > 7 this portion finds the month after July from joining dates using the Month function. Lastly, a simple message is “No data” given if there is no matched data.

enter data usign Filter and month function

Step 2: Press Enter and see the output

 Enter and see the output

5. Filter Data Based on Cell Value Using VBA Code

Now we will see how we can filter any data using VBA code. Here we will do the same thing which was done in method 1. But here we will use only VBA code to do filtering. Our concern is to find the top 5 average sales from the dataset.

Filter Data Based on Cell Value Using VBA Code

Step 1: Follow the steps. (Alt+F11 shortcut for opening Visual Basic window)

  1. Go to the Developer tab.
  2. Click on the Visual Basic option.
    how to open vb

Step 2: Then go to the Insert option and select Module

Insert module

Step 3: Now enter the code in the module and press the run button (Shortcut F5)

Code: 

Sub Filter_5()
 Sheet2.Range("A3").AutoFilter Field:=7, Criteria1:="5", Operator:=xlTop10Items
End Sub

Code Explanation:

Sheet2.Range(“A4:G11”) -> This is a range of our table in the worksheet

AutoFilter Field:=7 -> As our average sales column is in the 7th column that’s why we are using 7.

Criteria1:=”5″ -> As we need to find the top 5 average sales that’s why the criteria are 5.

Operator:=xlTop10Items -> This is for find the top values.

Step 4: Observe the final output

VBA code output

6. Filter Data Based on Cell Value Using Shortcut

Now we will see the shortcut for filtering values in Excel. For this again we will consider the same dataset above.

Step 1: Select the cells that you want to filter

Read More: How to Filter Cells with Formulas in Excel (2 Ways)

Select the cells that you want to filter

Step 2: Right Click on it. Then go to the Filter option. Under that select Filter by Selected Cell’s Value.

Keyboard Shortcut – > Menu Key > E > V

Filter option

Step 3: Now click on the Filter icon

Click on filter icon

Step 4: Now customize the filtering option according to your need

Now customize the filtering option according to your need

[ To remove the filter option from the column header, use this shortcut

Menu Key > E > E]

Conclusion

These are the ways to filter data based on cell value in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.


Further Readings

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo