Have you ever tried using a filter by formula? Filtering table data often needs a filter formula to be applied. Here in this post, you will learn how to use the Excel filter formula.

Filter by Formula

If you are familiar with Excel, you must know that the Filter formula was first used in Office 365 only. This in-built formula helps you in filtering an array or a collection of cells based on the specified criteria. Moreover, it also helps in extracting matching records.

This function is a part of Dynamic Array functions that results in an array spills on the worksheet by default.

How does it work?

This function works on the given filtering conditions:

Formula breakdown:

=FILTER(array, include,[if_empty])

Argument details of the formula are as:

array – data needed to be filtered.

include – logical test supplied as criteria and some height and width are necessary as the array.

if_empty – if the filter does not show records, this argument covers the value to be filtered. It is considered an optimal argument.

Usually, the Autofilter button is the common practice to filter data. The autofilter option is placed under the Data tab, however, it comes with a problem and that is no latest updates will be done by default if any changes are made. On the other hand, the Excel Filter function is dynamic if the values get modified the results from this formula will also get modified by default.

Basic Excel Filter Formula

Beginners tend to perform simple tasks first and for this, they have to be acknowledged with basic information about Excel and its features.

The below dataset shows that you need to extract records using a specific value in the Group or column.

For this, suppose you have the expression B2:B13=”C” in the included argument. This argument can offer a Boolean array using TRUE.

=FILTER(A2:C13,B2:B13=”C”, “No results”)

Practically, you will find it simple to add the criteria in a separate cell, such as F1.

=FILTER(A2:C13, B2:B13=F1, “No results”)

In comparison with the Filter feature of Excel, this function is not helpful in making changes in the real dataset. It helps in dragging the filtered records in the spill range that starts from the cell where the formula is added:

If you cannot find similar records with specified criteria, the formula will return the value added in the if_empty argument, “No results”.

If in return you didn’t get anything then provide an empty string (“”) for the last argument:

=FILTER(A2:C13, B2:B13=F1, “”)

The Filter function works great if the data is organized horizontally. You need to ensure the ranges for the array and include arguments properly to match the width of the source array and Boolean array:

=FILTER(B2:M4, B3:M3=B7, “No results”)

Excel Filter Function – Notes

Below are some important points to consider when you need to filter in Excel with formulas smoothly.

1. The results are dependent on the organization of data while using the Filter function. That’s why it is mandatory to have empty cells down and to the right or else you will have to face the #SPILL error.
2. Also keep in mind that the results of the Excel Filter function are dynamic, which clearly means they get updated by default whenever there is a change in the original dataset. Well, the range provided for the array argument does not update when new values are added to the source data. If you want to resize the array by default, you have to switch to an Excel table.

Final Thoughts

Beginners will not understand the advanced tricks unless they know the basics of Excel. That’s why users always have to keep practicing without breaks. It helps them learn new skills and improve their productivity. Keep exploring Excel!