MD Tanvir Rahman
Data filtration based on cell value is the ultimate goal to filter from congestion. Worldwide a lot of people use Excel to filter data. One thing common among all is finding the necessary data. This article, “Excel Filter Data Based on Cell Value” will be very helpful for professionals along with learners.
📁 Download Excel File
To practice and get the overview, please download the excel file from here:
Learn to Filter Data Based on Cell Value in Excel with These 7 Ways
There are 7 different approaches to filtering data based on cell value in excel. Here we consider a dataset of the Sales report of two months containing Sales Person, Region, Sales Unit(June), and Sales unit (July). In the dataset, there are 5 columns and 18 rows. Let’s jump to the approaches. To filter data based on cell value in excel, we are going to use FILTER, EXACT, AND, OR logic. We will also show you the use of VBA to filter data.
1. Basic Filter Data Based on Cell Value in Excel
You can filter manually using the FILTER option from the Data of Top Ribbon selecting a cell as well as selecting a particular column range.
1.1 Filtering Data Based on Cell Value Using Filter Option
Using the Filter Option from the Top Ribbon you can filter the data. Follow the steps below,
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, Select a cell from the table i.e. B6.
- Secondly, Click Data from the Top Ribbon.
- Thirdly, Click on Filter.
- After that, There will be a drop-down box in each column heading.
- Next, Click on the drop-down box.
- Then, Select the Number Filters to filter sales units.
- After that, Select Between.
- Therefore, you will get a Custom Autofilter box.
- Get filtered data by selecting the data range in Custom Autofilter.
We intend to find sales units between 20000 to 40000 in column D which is the data for June. We get 6 data containing Jenny, Mike, and Ammy.
1.2 Filtering Data Based on Cell Value Using Context Menu
Also selecting an entire row, right-clicking the mouse, and using the keyboard you can filter data manually.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the entire column of data.
- Then right-click on the mouse and get a context menu.
- Next, click on Filter and select Filter by Selected Cell’s Value.
- Therefore you will get a drop-down box in the column heading.
- Then click on Number Filters and select Between.
Similarly, you will get the desired output like the previous method.
2. Filtering Column Based on Another Column in Excel
If you are asked to match exactly consider the higher or lower case or blank cells. You can do it in this process.
2.1 Filtering Data Based on Case-Sensitive Cell Value
Considering higher-case or lower-case we can use the MATCH function to match exactly. Check out the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially choose a cell to get the output i.e. G6.
- Insert the following formula at G6 containing FILTER and MATCH functions.
🔨 Formula Breakdown
👉 Logical expression of the formula, FILTER(array, EXACT(range, reference_cell), “No results”)
👉 EXACT(C6:C18, C15) dictates to match C15=a within a range of C6:C18 exactly. However, the EXACT function does not consider ‘a’ as ‘A’.
👉 FILTER function runs filtration in the array B6:E18 to get overall data. If C15 is unable to match data then it signals to type No results.
- Then, Press Enter and get the output containing two rows.
2.2 Filtering Out Blank Cell
Sometimes some cells don’t have any values. To eliminate the entire row for not having proper information, please follow the below process.
⬇️⬇️ STEPS ⬇️⬇️
- Select a blank cell i.e. G6.
- Insert the formula to eliminate the data for not having data in a cell.
🔨 Formula Breakdown
👉 (B6:B18<>””)*(C6:C18<>””)*(D6:D18<>””)*(E6:E18<>””) find the blank cell.
👉 FILTER function runs an array in B6:E18 and filters out the full row for not having data in any cell of a row.
👉 If no proper cell is found then it signals to type No results.
● After pressing Enter, you will get the rows that have no blank cells.
3. Filtering Data in Excel Using Formula
Using a variety of formulas, one can easily filter based on criteria. Whatever the criteria are, you can use mathematical logic as well as functions for the best results.
3.1 Using Basic Formula
Using the FILTER function you can easily filter the dataset based on some criteria.
⬇️⬇️ STEPS ⬇️⬇️
- Initially select a cell i.e. G6.
- Suppose, we intend to find out the sales persons in region-A; So, we imputed H3=A.
- Impute the following formula to get sales person in region-A.
🔨 Formula Breakdown
👉 C6:C18=H3 tells to search H3 values in C6:C18 range.
👉 FILTER runs an array from C6 to C18 to find out region A. If it fails to find out the region then the output is No result.
● Finally, we get the sales persons that are responsible for region A.
On the other hand, When we imputed E in H3 to find out the sales persons of region E; There is data and therefore it results in No results.
3.2 Using OR Logic
Suppose your boss tells you to find some data that is sales unit in June is less than 10000 or in July it is more than 40000 units. Here comes the OR logic.
⬇️⬇️ STEPS ⬇️⬇️
- Get a cell to input the formula i.e. G6.
- Then insert the formula with OR logic.
🔨 Formula Breakdown
👉 Logical expression is, =FILTER(array, (range<ref_cell)+(range>ref_cell), “No results”)
👉 (D6:D18<H3) means input data of H3 the data below 10000 in D6:D18 or I3 finds the data greater than 40000 in E6:E18.
👉 FILTER function runs the array in the entire dataset B6:E18.
👉 If it is unable to match based on logic, it will show No results.
● Now, Press Enter and get the results.
Ammy from region C is the only name that is twice in this sheet that is less than 10000 in June or in July it is more than 40000 units
3.3 Using AND Logic
Suppose you need to find the names of sales persons that are sales units in June is more than 25000 and in July it is less than 40000 units. Here comes the AND logic.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, Get a cell i.e. G6.
- Then insert the formula with AND logic.
🔨 Formula Breakdown
👉 Primarily, Logical expression is, =FILTER(array, (range>ref_cell)AND(range<ref_cell), “No results”)
👉 Next, (D6:D18<H3) means input data of H3 the data greater than 25000 in D6:D18 and I3 finds the data less than 40000 in E6:E18.
👉 After that, the FILTER function runs the array in the entire dataset B6:E18.
👉 Lastly, If it is unable to match based on logic, it will show No results.
● Lastly, Press Enter and get the results.
There are six names from different regions who get higher than 25000 sales units in June and less than 40000 units in July.
3.4 Using Multiple AND and OR Logic
Suppose you need to find out the names that got more than 25000 units of sales in June from a specific region for example A or D. With a combination of AND and OR logic, you can find data.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select a blank cell i.e. G6.
- Secondly, Insert region in H2, I2 respectively A, and D.
- Then, insert the formula with a combination of OR and AND logic.
🔨 Formula Breakdown
👉 Logical expression is, =FILTER(array,(range<ref_cell) *((range=ref_cell2)+(range=ref_cell3)),”No results”)
👉 (C6:C18=H2) + (C6:C18=I2) means find the region A=H2 or D=I2 in range C6:C18.
👉 FILTER function runs the array in the entire dataset B6:E18. When it is unable to match based on logic, it shows No results.
● In the end, Press Enter and get the results.
Therefore, we get the names of salespersons that are from the A or D region and got sales units of more than 25000.
📕 Read More: 9 Easy Examples to Filter Data Using Formula in Excel
4. Filter Cells Containing Specific Text in Excel
Suppose, you need to find a specific text from the dataset. You can filter specific texts using the FILTER function and defining the range. Follow the steps below,
⬇️⬇️ STEPS ⬇️⬇️
- Initially Navigate a cell i.e. G6.
- Then Insert the formula defining its range and array.
🔨 Formula Breakdown
👉 Logical expression of this formula =FILTER(array, (range=cell_ref),0)
👉 B6:B18=H3 indicates to find H3= Mike in range B6:B18.
👉 Finally FILTER function runs an array to the entire dataset B6:E18 to collect data that match Mike.
● Therefore, by Pressing Enter we get the output containing Mike.
5. Filter Based on Cell Date
To find a specific date or activity of some date, you can use the FILTER function. Please check out the below steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a blank cell i.e. H6.
- Then write down the formula in H6.
🔨 Formula Breakdown
👉 General Logical expression of the formula is =FILTER(array, (range>=cell_ref1)AND(range<=cell_ref1), “No results”)
👉 Here from (B6:B18>=I2) and (B6:B18<=I3), 17-Aug=I2 and 25-Aug=I3. That means we need to find data after 17-Aug and before 25-Aug from B6 to B18.
👉 Finally, the FILTER function runs an array; If it finds no match then it writes No results.
● In the end, Press ENTER, and here is the output containing data between 17-Aug to 25-Aug.
6. Filter and Calculate (Sum, Average, Min, Max) Based on Cell Values Without VBA
Using the FILTER function you can find the summation of a specific group of data. You can average them, and find the maximum or minimum values of that specific group.
⬇️⬇️ STEPS ⬇️⬇️
- Initially select a cell i.e. H6.
- Then input the formula in H6.
🔨 Formula Breakdown
👉 Primarily, Logical expression of the formula is =SUM(FILTER(array,range=cell_ref, 0))
👉 Then C6:C18=G3 tells to search Region C=G3 in range C6:C18.
👉 Next FILTER function runs an array to collect data from the entire dataset.
👉 Finally SUM function adds the filtered data.
- Similarly in G6, G7, and G8 insert the formula containing AVERAGE, MIN, and MAX functions respectively.
=AVERAGE(FILTER(D6:D18, C6:C18=G3, 0))
=MIN(FILTER(D6:D18, C6:C18=G3, 0))
=MAX(FILTER(D6:D18, C6:C18=G3, 0))
- Finally, Press Enter and you will get the output of the sum, average, minimum, and maximum of region C.
7. Filter Data Based on Cell Value of Another Sheet with VBA
We can use VBA code to filter the data based on any criteria. With the help of the Developer option, you can generate a program of your own. To execute, please follow the necessary steps.
⬇️⬇️ STEPS ⬇️⬇️
- First of all, Check first if your Developer option is enabled or not.
- To enable the Developer option, right-click on the mouse at the top Ribbon and select Customize the Ribbon.
- After that, Click on Developer and click on OK.
- Now Developer mode is on.
- Then, Click on the Visual Basic option.
- Next, To insert the VBA code, click on Module from the Insert.
- Later, Insert the code and Run.
Code
Sub Filter_3()
Sheet10.Range("B5:E18").AutoFilter Field:=3, Criteria1:="3", Operator:=xlTop10Items
End Sub🔨 Code Breakdown
👉 Sheet10.Range(“B5:E18”) indicates the entire dataset of sheet no 10 of that workbook.
👉 AutoFilter Field:=3 tells to sort from the 3rd column that is based on June.
👉 Criterial:=”3” is the no of results that we want to obtain.
- Finally inserting the code, you will get the top three results.
📄 Important Notes
🖊️ Use of EXACT function is used to detect case sensitivity. It doesn’t match upper-case to lower-case.
🖊️ Be careful while you use multiple mathematical logic.
🖊️ Check the sheet number while you use the VBA code.
📝 Takeaway from This Article
📌 Learn the use of FILTER and EXACT functions.
📌 Application of AND and OR mathematical logic discussed.
📌 Using VBA code Filtering the data from a table is also demonstrated.
Conclusion
We tried to demonstrate every possible way to filter data based on cell value in Excel. I hope you enjoyed your learning. Any suggestion is appreciable. Please leave your thoughts in the comment section. For better understanding and new learning don’t forget to visit www.ExcelDen.com.
