How to Filter Data in Excel Using Formula

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.

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.

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

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.

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.

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.

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

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 (“”)

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.

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.

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.