How to use the FILTER() dynamic array function in Excel

Microsoft Excel’s new FILTER() perform is a good device for reporting and dashboards. We’ll present you how one can use it to get extra performed.

A blank Excel spreadsheet

Picture: PixieMe/Shutterstock

Filtering is a big a part of many Microsoft Excel sheets, and fortuitously, there are lots of methods to do it. Microsoft 365’s new FILTER() perform is a robust improve. You will not essentially get completely different outcomes utilizing this new dynamic array perform, however you may get these outcomes utilizing a single perform! On this article, we’ll assessment the built-in Filter function, then we’ll discover ways to use the brand new FILTER() perform.

I am utilizing Microsoft 365 (desktop) on a Home windows 10 64-bit system. The built-in Filter function is on the market in ribbon variations solely. FILTER() is on the market to Microsoft 365, Excel 2021, Excel for the net, Excel for iPad and iPhone, Excel for Android tablets and telephones. In your comfort, you possibly can obtain the demonstration .xlsx file. This text assumes that you’ve primary Excel abilities, however even a newbie ought to have the ability to observe the directions to success.

SEE: 83 Excel suggestions each consumer ought to grasp (TechRepublic)

Tips on how to use the Filter function in Microsoft Excel

Excel’s Filter function enables you to filter by a number of values, so that you may not want the FILTER() perform in any respect. For that purpose, we’ll work by way of a fast instance, so you can also make an knowledgeable alternative. Determine A exhibits a easy knowledge set with filters. To use filters, click on the Knowledge tab after which click on Filter within the Kind & Filter group.

Determine A


  Use the header dropdowns to filter knowledge.

To make use of the filters, merely click on the suitable dropdown arrow within the header cell. Strive that now by clicking the Area’s dropdown. The ensuing pane enables you to filter in many alternative methods. When no filter is about, the entire values are checked. To kind by a number of values, however not the entire values, uncheck the (Choose All) merchandise. Then, examine the objects you need to see. Determine B exhibits Northwest and Southwest chosen. Subsequently, the filtered set proven in Determine C shows solely data for these two values; there aren’t any data for Central proven.

Determine B


  Choose the values you need to see within the filtered set.

Determine C


  Filtering by Northwest and Southwest excludes the Central data.

This function is versatile sufficient for many conditions, however it has not less than one limitation: You possibly can’t show the filtered set in one other location. This function solely filters the data in-place. Due to the brand new FILTER() perform, doing so is nearly as straightforward as utilizing the Filter function.

Concerning the FILTER() perform in Excel

Displaying a filtered set in one other location makes for straightforward reporting and works particularly effectively in a dashboard setup. Till FILTER() was launched, getting a filtered set in one other location required a little bit of effort and talent.

FILTER() is one among Microsoft 365’s new dynamic array capabilities. It helps what’s often called a spill vary, which is the results of a dynamic array formulation that returns a number of values—its output spills past the enter cell. In brief, a spill vary is a spread of calculated outcomes from one formulation. When you choose any cell in a spill vary, Excel highlights all the vary with a blue border. You’ll all the time discover the formulation within the topmost cell of that vary.

This perform makes use of the syntax

FILTER(array, embody, [if_empty])

the place array identifies the supply knowledge, embody identifies the worth(s) you need to see within the filtered knowledge set, and the optionally available if_empty specifies the worth to show when the result’s an empty set.

You should utilize FILTER() to return a single column or a number of. As well as, you possibly can filter by a single column or a number of. Now, let’s put the FILTER() perform to work. 

Tips on how to use the FILTER() perform in Excel

Utilizing FILTER() has one requirement: You could use the identical headers within the filtered set vary. The unique knowledge set in B2:F14 is the supply knowledge. Merely copy the headers to a different location.

 Though not a requirement, the perform is extra versatile in the event you use enter cells for the embody argument. Determine D exhibits the required setup. We’ll use column I to create extra versatile, dynamic, capabilities. The filtered knowledge set will show in columns Okay by way of 0.

Determine D


  To implement the FILTER() perform you must duplicate the supply headers.

Though you’ll seldom need to show the filtered outcomes of just one column, it’s doable, so let’s work by way of a fast instance. Particularly, show the personnel for the Southwest area by coming into the perform

=FILTER(D3:D13,E3:E13=I5,”No outcomes”)

into M3, as proven in Determine E. Then, enter Southwest into I5, the Area enter cell. (As a result of the perform is entered right into a single cell and spills the outcomes into the adjoining vary, absolute referencing is not required.)

Determine E


  As is, the outcomes have minimal use as a result of there’s just one column of values.

The result’s straightforward to decipher by reviewing the perform’s arguments:

  • The array argument, D3:D13, shows solely matching values from the Personnel column (column D).
  • The embody argument, E3:E13=I5, filters the listing by the worth in cell I5, which is Southwest.

The perform returns solely the personnel values for the Southwest area.

Let’s complicate issues a bit by including extra show and filtering values. Enter the perform

=FILTER(B3:F13,(D3:D13=I4)*(E3:E13=I5),”No Outcomes”)

in K3, as proven in Determine F. (I’ve utilized acceptable formatting as a result of the FILTER() perform cannot.)

Determine F


  Though a bit extra advanced, this perform filters for 2 values.

The embody argument, (D3:D13=I4)*(E3:E13=I5), is perhaps unfamiliar to you. The * character serves as an AND operator. In plain English, this argument filters by the personnel worth in I4 and the area worth in I5. The ensuing filtered set incorporates just one report for James within the Southwest area. To use an OR operator within the embody argument, use the + character as a substitute of the * character.

There’s yet one more manner to enhance outcomes although. When the filtered set has a number of data, you may need to kind the outcomes utilizing SORT()

Tips on how to add the SORT() perform in Excel

The SORT() perform is one other new dynamic array perform. It returns a sorted array and makes use of the next syntax:


the place array is the one required argument and identifies the vary to kind. The optionally available arguments observe:

sort_index: A numeric offset worth that identifies the row or column to kind by

sort_order: The number one for ascending kind or -1 for descending, with 1 being the default

by_col: The logical values TRUE for a row kind and FALSE for a column kind, with TRUE being the default

The present FILTER() returns just one report, so let’s take away James from I4 and return a descending sorted filtered set for the Southwest area, sorted by the Worth column. To take action, enter the perform

=SORT(FILTER(B3:F13,E3:E13=I5,”No Outcomes”),2,-1)

in K3, as proven in Determine G.

Determine G


  The SORT() perform types the filtered report set.

SORT()’s array argument is all the FILTER() perform. The sort_index worth of two types by the Worth column, the second column in array. The sort_index worth is -1, which applies a descending kind. The result’s a sorted filtered set that exhibits the best worth first for the Southwest area. 

Price noting

There are some things you may need to embody when making use of FILTER() to your personal work. First, the instance sheet contains an enter cell for every column within the supply knowledge set. Nevertheless, I like to recommend that you simply embody enter cells just for the columns referenced by the FILTER() perform’s embody argument. Providing an enter cell that does not replace the filtered set is complicated to customers.

The built-in Filter function converts the information set right into a Desk object. The FILTER() capabilities proceed to reference the identical supply Desk. When coming into the capabilities, in the event you use the choice technique to insert references, you will note Excel’s Desk construction referencing at work as a substitute of the particular vary (array) references that I exploit. Don’t fret about it—all the things will nonetheless work.

SEE: Home windows 10: Lists of vocal instructions for speech recognition and dictation (free PDF) (TechRepublic)

As a result of the supply knowledge is a Desk object, all the things is dynamic. For those who add, delete or modify a report within the supply knowledge, the filtered knowledge set will replace accordingly.

In a real reporting or dashboard state of affairs, think about using knowledge validation to create dropdown lists for the enter cells. Doing so will forestall errors when the enter worth is invalid.

You possibly can wrap a FILTER() perform in an mixture perform similar to SUM(), AVERAGE(), and so forth. Doing so will return just one worth, the results of the combination on the filtered outcomes of FILTER().

Keep tuned

Utilizing FILTER() to match a number of values throughout a number of cells could be sophisticated. In actual fact, the built-in Filter function is far simpler to make use of on this respect. In a future article, I will sort out that requirement utilizing FILTER(). We’ll proceed the research with an article on filtering on worth columns, similar to Worth and Fee. 

Additionally see

Recent Articles


Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox