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