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

3 years ago 375

Microsoft Excel's caller FILTER() relation is simply a large instrumentality for reporting and dashboards. We'll amusement you however to usage it to get much done.

Filtering is simply a immense portion of galore Microsoft Excel sheets, and fortunately, determination are galore ways to bash it. Microsoft 365's caller FILTER() relation is simply a almighty upgrade. You won't needfully get antithetic results utilizing this caller dynamic array function, but you'll get those results utilizing a azygous function! In this article, we'll reappraisal the built-in Filter feature, past we'll larn however to usage the caller FILTER() function.

I'm utilizing Microsoft 365 (desktop) connected a Windows 10 64-bit system. The built-in Filter diagnostic is disposable successful ribbon versions only. FILTER() is disposable to Microsoft 365, Excel 2021, Excel for the web, Excel for iPad and iPhone, Excel for Android tablets and phones. For your convenience, you tin download the objection .xlsx file. This nonfiction assumes that you person basal Excel skills, but adjacent a beginner should beryllium capable to travel the instructions to success.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

How to usage the Filter diagnostic successful Microsoft Excel

Excel's Filter diagnostic lets you filter by aggregate values, truthful you mightiness not request the FILTER() relation astatine all. For that reason, we'll enactment done a speedy example, truthful you tin marque an informed choice. Figure A shows a elemental information acceptable with filters. To use filters, click the Data tab and past click Filter successful the Sort & Filter group.

Figure A

To usage the filters, simply click the due dropdown arrow successful the header cell. Try that present by clicking the Region's dropdown. The resulting pane lets you filter successful galore antithetic ways. When nary filter is set, each of the values are checked. To benignant by 1 oregon much values, but not each of the values, uncheck the (Select All) item. Then, cheque the items you privation to see. Figure B shows Northwest and Southwest selected. Subsequently, the filtered acceptable shown successful Figure C displays lone records for those 2 values; determination are nary records for Central shown.

Figure B

Figure C

This diagnostic is flexible capable for astir situations, but it has astatine slightest 1 limitation: You can't show the filtered acceptable successful different location. This diagnostic lone filters the records in-place. Thanks to the caller FILTER() function, doing truthful is astir arsenic casual arsenic utilizing the Filter feature.

About the FILTER() relation successful Excel

Displaying a filtered acceptable successful different determination makes for casual reporting and works particularly good successful a dashboard setup. Until FILTER() was introduced, getting a filtered acceptable successful different determination required a spot of effort and skill.

FILTER() is 1 of Microsoft 365's caller dynamic array functions. It supports what's known arsenic a spill range, which is the effect of a dynamic array look that returns aggregate values—its output spills beyond the input cell. In short, a spill scope is simply a scope of calculated results from 1 formula. When you prime immoderate compartment successful a spill range, Excel highlights the full scope with a bluish border. You volition ever find the look successful the topmost compartment of that range.

This relation uses the syntax

FILTER(array, include, [if_empty])

where array identifies the root data, see identifies the value(s) you privation to spot successful the filtered information set, and the optional if_empty specifies the worth to show erstwhile the effect is an bare set.

You tin usage FILTER() to instrumentality a azygous file oregon several. In addition, you tin filter by a azygous file oregon several. Now, let's enactment the FILTER() relation to work.

How to usage the FILTER() relation successful Excel

Using FILTER() has 1 requirement: You indispensable usage the aforesaid headers successful the filtered acceptable range. The archetypal information acceptable successful B2:F14 is the source data. Simply transcript the headers to different location.

Although not a requirement, the relation is much flexible if you usage input cells for the see argument. Figure D shows the indispensable setup. We'll usage file I to make much flexible, dynamic, functions. The filtered information acceptable volition show successful columns K done 0.

Figure D

Although you volition seldom privation to show the filtered results of lone 1 column, it is possible, truthful let's enactment done a speedy example. Specifically, show the unit for the Southwest portion by entering the relation

=FILTER(D3:D13,E3:E13=I5,"No results")

into M3, arsenic shown successful Figure E. Then, participate Southwest into I5, the Region input cell. (Because the relation is entered into a azygous compartment and spills the results into the adjacent range, implicit referencing isn't required.)

Figure E

The effect is casual to decipher by reviewing the function's arguments:

The array argument, D3:D13, displays lone matching values from the Personnel file (column D).
The see argument, E3:E13=I5, filters the database by the worth successful compartment I5, which is Southwest.

The relation returns lone the unit values for the Southwest region.

Let's complicate things a spot by adding much show and filtering values. Enter the relation

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

in K3, arsenic shown successful Figure F. (I've applied due formatting due to the fact that the FILTER() relation can't.)

Figure F

The see argument, (D3:D13=I4)*(E3:E13=I5), mightiness beryllium unfamiliar to you. The * quality serves arsenic an AND operator. In plain English, this statement filters by the unit worth successful I4 and the portion worth successful I5. The resulting filtered acceptable contains lone 1 grounds for James successful the Southwest region. To use an OR relation successful the see argument, usage the + quality alternatively of the * character.

There's 1 much mode to amended results though. When the filtered acceptable has aggregate records, you mightiness privation to benignant the results utilizing SORT()

How to adhd the SORT() relation successful Excel

The SORT() relation is different caller dynamic array function. It returns a sorted array and uses the pursuing syntax:

SORT(array,[sort_index],[sort_order],[by_col])

where array is the lone required statement and identifies the scope to sort. The optional arguments follow:

sort_index: A numeric offset worth that identifies the enactment oregon file to benignant by

sort_order: The fig 1 for ascending benignant oregon -1 for descending, with 1 being the default

by_col: The logical values TRUE for a enactment benignant and FALSE for a file sort, with TRUE being the default

The existent FILTER() returns lone 1 record, truthful let's region James from I4 and instrumentality a descending sorted filtered acceptable for the Southwest region, sorted by the Value column. To bash so, participate the function

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

in K3, arsenic shown successful Figure G.

Figure G

SORT()'s array statement is the full FILTER() function. The sort_index worth of 2 sorts by the Value column, the 2nd file successful array. The sort_index worth is -1, which applies a descending sort. The effect is simply a sorted filtered acceptable that shows the highest worth archetypal for the Southwest region.

Worth noting

There are a fewer things you mightiness privation to see erstwhile applying FILTER() to your ain work. First, the illustration expanse includes an input compartment for each file successful the root information set. However, I urge that you see input cells lone for the columns referenced by the FILTER() function's see argument. Offering an input compartment that doesn't update the filtered acceptable is confusing to users.

The built-in Filter diagnostic converts the information acceptable into a Table object. The FILTER() functions proceed to notation the aforesaid root Table. When entering the functions, if you usage the enactment method to insert references, you volition spot Excel's Table operation referencing astatine enactment alternatively of the existent scope (array) references that I use. Don't interest astir it—everything volition inactive work.

SEE: Windows 10: Lists of vocal commands for code designation and dictation (free PDF) (TechRepublic)

Because the root information is simply a Table object, everything is dynamic. If you add, delete oregon modify a grounds successful the root data, the filtered information acceptable volition update accordingly.

In a existent reporting oregon dashboard situation, see utilizing information validation to make dropdown lists for the input cells. Doing truthful volition forestall errors erstwhile the input worth is invalid.

You tin wrapper a FILTER() relation successful an aggregate relation specified arsenic SUM(), AVERAGE(), and truthful on. Doing truthful volition instrumentality lone 1 value, the effect of the aggregate connected the filtered results of FILTER().

Stay tuned

Using FILTER() to lucifer aggregate values crossed aggregate cells tin beryllium complicated. In fact, the built-in Filter diagnostic is overmuch easier to usage successful this respect. In a aboriginal article, I'll tackle that request utilizing FILTER(). We'll proceed the survey with an nonfiction connected filtering connected worth columns, specified arsenic Value and Commission.