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.

**Method**

### 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**.

**FILTER Option**

#### 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.

**FILTER Option**

#### 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.

**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.

**Based on Column**

#### 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.

**=FILTER(B6:E18, EXACT(C6:C18, C15), “No results”)**

**🔨 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**.

**Based on Column**

#### 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.

**=FILTER(B6:E18,(B6:B18<>””)*(C6:C18<>””)*(D6:D18<>””)*(E6:E18<>””),”No results”)**

**🔨 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.

**Method**

### 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.

**Using Formula**

#### 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**.

**=FILTER(B6:E18, C6:C18=H3, “No results”)**

**🔨 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**.

**Using Formula**

#### 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.

**=FILTER(B6:E18, (D6:D18<H3)+(E6:E18>I3), “No results”)**

**🔨 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

**Using Formula**

#### 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.

**=FILTER(B6:E18, (D6:D18>H3)*(E6:E18<I3), “No results”)**

**🔨 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**.

**Using Formula**

#### 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.

**=FILTER(B6:E18, (E6:E18<H3) * ((C6:C18=H2) + (C6:C18=I2)), “No results”)**

**🔨 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**

**Method**

### 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**.

**=FILTER(B6:E18, (B6:B18=H3),0)**

**🔨 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**.

**Method**

### 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**.

**=FILTER(B6:F18, (B6:B18>=I2) * (B6:B18<=I3), “No results”)**

**🔨 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**.

**Method**

### 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**.

**=SUM(FILTER(D6:D18, C6:C18=G3, 0))**

**🔨 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**.

**Method**

### 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**.