To filter data in Excel is quite usual, often you need to filter data to find the sets of data you require to operate a particular operation. Today we are going to show you how to filter data in Excel using formula.

Before diving into the big picture, let’s get to know about today’s practice workbook.

We have a basic table of three columns; *Team, Group, Win. *We will filter the data with different criteria using formulas.

It’s a basic table with a simple scenario, in practical cases, you may encounter a much larger and complex data set and scenario.

## 1. Combining Excel INDEX-SMALL Formula to Filter Data

We can filter data using the formula, consists of a combination of **INDEX** and **SMALL** functions.

The **INDEX** function returns the value at a given location in a range or array.

`INDEX (array, row_num, [col_num], [area_num])`

**array: **A range of cells, or an array constant.

**row_num: **The row position in the reference or array.

**col_num: **The column position in the reference or array. This is an optional field.

**area_num:** The range in reference that should be used. This is an optional field.

The **SMALL** function returns numeric values based on their position in a list ranked by value.

`SMALL (array, n)`

**array:** A range of cells from which to extract the smallest values.

**n:** An integer that specifies the position from the smallest value, i.e. the nth position.

Along with these two functions we are going to use the **IFERROR** and the **ROW** function.

Let’s write the generic formula first

`IFERROR(INDEX(return_array,SMALL(IF(criteria_check, ROW(criteria_array),""),ROW()-ROW(starting_row))),"")`

**IFERROR** envelops the nested formula to avoid errors

You may have understood we intended to produce the *row_number *within the **INDEX **function using the **SMALL **function.

Inside the **SMALL **function, we have set the array within the **IF **function.

Write the formula in Excel.

Here our *return_array *is the *Team *column and we have checked whether *Group A *matches inside the *Group *column or not.

This logical test returns an array of **TRUE **or **FALSE. TRUE **for matching with the criteria and **FALSE **otherwise.

Can you imagine what the **ROW(C: C) **returns as the true value?

Yes, it returns the row numbers of that column. We find an array of row numbers from this.

Hope you have understood that this **ROW** is the *if_true_value *of the **IF **function. And we have passed empty strings for **FALSE **values.

So ** (IF(criteria_check, ROW(criteria_array),"") **returns an array of “” and

*row number*

**.**

The row number, where the matches are found and empty otherwise. Now, this is the array for the **SMALL **function.

** ROW()-ROW(starting_row) **returns the incremental number of rows, that is the

*n*for

**SMALL.**

**SMALL **here returns a row index value which will be used by the **INDEX** function to return a value from a non-empty cell. The **INDEX** function selects the reference to return values.

To execute the formula you need to use **CTRL + SHIFT + ENTER. **

Here, we have filtered for *Group A. *Our list has three teams from Group A and the formula returns that.

Change the criteria and see whether it’s working fine or not.

Now, we have selected *Group I*, and the teams from *Group I *have come in front of us.

Let’s see another approach.

Our generic formula will be

`IFERROR(INDEX(return_array,SMALL(IF(criteria_check, ROW(return_array)-ROW(starting_row)+1,""),ROWS(starting_row:present_row))),"") `

Can you see the differences?

Yes, some changes to the **ROW **function. Earlier we have used a single **ROW **function. But now we have used two** ROW **functions.

These two return the row number from the starting. And the **ROWS **function returns the incremental number of rows which is the n of the **SMALL **function.

Write the formula in Excel.

Apart from the changes the mechanism is similar to the earlier formula. Don’t forget to press **CTRL + SHIFT + ENTER** to execute the formula.

Change the criteria value to observe different results.

You can change the criteria from *Group *to *Win *number. The only change you need to make is the *criteria_array.*

Here we have changed the *criteria_array* for our needs. We need to find the *Win *number inside the *Win *column. And we have found the answer.

## 2. Using Excel FILTER Function to Filter Data

If you are using Excel 365, then you can use a built-in function called **FILTER. **

The** FILTER **function filters a range of data based on supplied criteria and extracts matching records.

`FILTER (array, include, [if_empty])`

**array: **Range or array to filter.

**include: **Boolean array, supplied as criteria.

**if_empty: **Value to return when no results are returned. This is an optional field.

You can use the **FILTER **function in different circumstances. Let’s explore some of them.

### I. Filtering with Single Criteria

All you need to do is insert the array and introduce the criteria in the *include *field of the **FILTER **function.

We are using Excel 365 to show you examples of **FILTER. **

Here we have inserted the entire table as our *array. ***FILTER **allows that. So, our result consists of three columns.

And we have checked whether the *Group *column has criteria *Group I* or not.

We have found the result. We have listed three teams from group *I *and the formula returned that.

It’s better to practice using the *if_empty *field. So, we have changed the earlier formula a bit and used “Not Available” in that field.

### II. Filtering with Multiple Criteria

You can use multiple criteria within the **FILTER **function.

The use of multiple criteria can be occurred by **AND **or **OR **logic operation.

**AND **logic is when both criteria have to be fulfilled. We use the asterisk symbol (`*`

) to denote the **AND **operation.

`FILTER(array, (range1=criteria1) * (range2=criteria2), if_empty)`

Here we have two criteria, *Group *and *Win. *We have checked the criteria value with their respective columns and multiplied them. It returned the teams from group I with 2 wins.

For** OR **logic operation, any of the conditions need to be satisfied. We can use plus sign symbol (`+`

) to denote the **OR **logic.

`FILTER(array, (range1=criteria1) + (range2=criteria2), if_empty)`

Here we have checked the criteria value with their respective columns and added them. It returned the teams from group I as well as all the teams having 2 wins regardless of the group.

We can combine the **AND-OR **logic together considering our demand.

Here we have two groups; I and A. We have checked the condition and added them together before multiplying with the *Win *criteria. We have found the teams from groups I and A with 2 wins.

### III. Filtering within Dates

You may need to filter data within any particular date. Don’t you? Yes, obviously. Some circumstances may arise to filter data by dates. **FILTER **can be used there also.

We will use the mechanism of **AND **logic operation here. We will compare the starting date and the end date, then **AND **them.

To show you an example, we have added a column *Last Win* to our table and made a scenario where we need to find the values within two specific dates.

We have set the conditions (equal or greater than the starting date and less or equal to the end date) and multiplied them.

Here we have found the teams that have their *Last Win *from 7 December 2020 to 14 February 2021.

### IV. Filtering Duplicates

When working with a large worksheet, there’s often a possibility that some duplicates would sneak in.

To filter duplicates, we need to use the **FILTER** function together with **COUNTIFS**.

The formula will be something like below

`FILTER(array, COUNTIFS(column1, column1, column2, column2,...)>1, if_empty)`

Write the formula in Excel.

We have inserted the columns within the **COUNTIFS **function and the rest is similar to every other operation we have seen previously.

To show the examples, we have made some changes to our dataset (inserted duplicates).

### V. Filtering Blanks

You may have understood that most of our operation is based on **AND **logic operation. For filtering out the blanks we will also take the help of **AND **logic.

To identify non-blank cells, you use the “not equal to” operator (`<>`

) together with an empty string (“”)

`FILTER(array, (column1<>"") * (column2=<>"")*..., if_empty)`

Write the formula in Excel.

We have made some adjustments to our dataset and brought some empty cells to show the example.

We have several empty cells in each of the columns. The formula provided the result without any blank or empty cells.

### VI. Filtering with Non-adjacent columns

So far we have found all the columns for filtered data. You may choose the adjacent columns to find them after filtering. But what if you need to find adjacent columns?

No worries! You can do that easily. All you need to do is use a nested **FILTER. **

Let’s write the formula first.

`FILTER(FILTER(array,include),{1,0,1}) `

Write the formula in Excel.

{1,0,1} is the *include *field for the outer **FILTER. **1 marks the columns to be kept and 0 marks the columns to be excluded.

In place of 1 and 0, you can use **TRUE **or **FALSE. **

### VII. Filtering with the Desired Number of Rows

Sometimes it happens that you may need several filtered data which means a specific number of rows to be shown.

To do that you need to use the **INDEX **function with **FILTER.**

`INDEX(FILTER(array,include),{1;2},{1,2,3}) `

To keep things simple we are writing {1;2} or {1,2,3}. You need to insert your desired values here.

You may have understood that these are the *row_number *and the *column_number *respectively of the **INDEX **function. So they return the rows and columns you need to show.

Write the formula in Excel.

We have wrapped the formula with **IFERROR **to eradicate any errors.

**Practice Workbook**

You are welcome to download the practice workbook from the link below.

*Caution: If you open the workbook in an Excel version other than 365, you may not see the results for the FILTER function.*

## Conclusion

That’s all for the session. We have tried showing you how to filter data using formula in Excel. Hope you will find this helpful. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.

**<< Go Back to Data | Filter in Excel | Learn Excel**