# How to Use Filter Formula in Excel – Filter by Formula

• Home
• / How to Use Filter Formula in Excel – Filter by Formula

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.

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

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: