How to Filter Data Based on Cell Value in Excel (6 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes data in a worksheet is too complicated or crammed that we face difficulties to find out desired data. Retrieving 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 values.


Download Practice Workbook

Download the practice workbook for methods 1-4.

For methods 5 and 6, use this workbook.


6 Suitable Ways to Filter Data Based on Cell Value in Excel

This is the dataset for today’s article. There are some products and the monthly sales amount.

dataset for excel filter data based on cell value

Now, I will use this dataset to explain the methods.


1. Filter Data Based on Cell Value by 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.

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

Steps:

  • Select the cell within the range.
  • Then, go to the Data
  • After that, select the Filter option

Filter option excel filter data based on cell value

  • Excel will activate the Filter
  • Then, click the drop-down box near the Average Sales
  • After that, go to the Number Filters.
  • Then, select Between.

  • A Custom AutoFilter box will appear.
  • Then, input the range.
  • After that, click OK.

Filter option excel filter data based on cell value

  • Excel will show you the result.

Read More: Excel VBA: Filter Based on Cell Value on Another Sheet (4 Examples)


2. Apply FILTER Function to Filter Data Based on Cell Value

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

FILTER function Filter option excel filter data based on cell value

Steps:

  • First of all, go to B17 and write down the following formula
=FILTER(B5:H12, (H5:H12 >2000 )*(H5:H12<4000))

Formula Explanation

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

  • Then, press ENTER to get the output.

FILTER function Filter option excel filter data based on cell value

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

Read More: VBA Code to Filter Data in Excel (8 Examples)


3. Filter Data Based on Cell Values That Contain Text

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

Steps:

  • Enter the formula in cell B17
=FILTER(B5:E12,ISNUMBER(SEARCH("Christopher",C5:C12)))

FILTER function Filter option excel filter data based on cell value

Formula Explanation

  • There are two additional functions used in the formula. They are ISNUMBER and SEARCH
  • Here B5:E12 is the total table range. SEARCH(“Christopher”,C5:C12) this portion finds the name Christopher from C5:C12 Lastly, the ISNUMBER function is used to convert the result from SEARCH into TRUE or FALSE.
  • Then, press ENTER to get the output.

Read More: How to Filter Based on Cell Value Using Excel VBA (4 Methods)


Similar Readings


4. Filter Data Based on Date

Now we will filter data based on the dates. Our concern is to find the salespersons who joined after July.

Steps:

  • Write down the formula in B17.
=FILTER(B5:E12,MONTH(D5:D12) > 7,"No data")

Formula Explanation

Again, in the same way, B5:E12 is our table range. MONTH(D5:D12) > 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.

  • Then, press Enter and see the output.

Date data filter based on cell value

Read More: Excel VBA: Filter Table Based on Cell Value (6 Easy Methods)


5. Run a VBA Code to Filter Based on Cell Value

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.

Steps:

  • Press ALT + F11 to open the VBA window.
  • Then go to Insert >> select Module.

  • A new module will appear. Then, write down the following code.
Sub Filter_5()
Sheet2.Range("B4:H12").AutoFilter Field:=7, Criteria1:="5", Operator:=xlTop10Items
End Sub

Code Explanation

  • Range(“B4:H12”) -> 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 finding the top values.
  • Now, press F5 to run the code.
  • Finally, Excel will show the output.

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


6. Apply Keyboard Shortcut to Filter Data Based on Cell Value

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

Steps:

  • Select the cells that you want to filter.

  • Then, right-click on it.
  • After that, go to the Filter
  • Then, under that select Filter by Selected Cell’s Value.

Shortcut for excel filter data based on cell value

  • Now click on the Filter
  • After that, organize the dataset as you wish
  • Then, click OK.

  • So, Excel will filter the dataset.

Shortcut for excel filter data based on cell value

Read More: How to Filter Multiple Values in One Cell in Excel (4 Methods)


Conclusion

These are the ways to filter data based on cell values 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.


Related Articles

Md. Abdullah Al Murad

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo