Apart from XLOOKUP, the other dynamic array function that gets too much publicity is FILTER Function.

29 Excel Experts ranks it as the 3rd most important function to learn.

Why all the fuss with the FILTER function? Is it the future of Lookup functions?

Here are 24 things you can do with FILTER Function.

Generally, the FILTER function has 2 required parameters;

=FILTER (array,Include,[if empty])

• array: number of column(s) and /Or Row(s) to return
• Include the logical test to determine what data to return. The result of this test must always be boolean (TRUE / FALSE)
• [if empty]: if there is no data to return, Filter function returns #CALC! error. To avoid the error, use this parameter to return a text, number, or function.

How to return an Entire table using the FILTER Function

Returning the entire table is the easiest.

The only trick is in Selecting the entire table…click the table on the topmost left corner.

How to return Adjacent Columns using the FILTER Function

The trick here is knowing how to select the adjacent columns.

NB: The criteria range column to use in the Include logical test DOES NOT have to be among the Return Columns.

How to dynamically return NON-Adjacent Columns using FILTER Function

The trick here is to use a nested FILTER Function with COUNTIF Function.

Step 1: Return all the columns first

Step 2: Use COUNTIF & Another FILTER function to return non-adjacent columns

=FILTER(FILTER(HR_Budget1314,HR_Budget1314[Budget Year]=I2),COUNTIF(H3:I3,HR_Budget1314[#Headers]))

COUNTIF(H3:I3,HR_Budget1314[#Headers]) returns an array of 1 & 0 ({1,0,0,1,0}) which FILTER function interprets 1 as TRUE and 0 as FALSE

Based on the array results of the COUNTIF function, FILTER only returns columns 1 & 3:

⭐⭐COUNTIF function dynamically returns the columns based on the criteria Headers (H4:I4)

NB: To get a different result, just change the headers in the criteria

Alternatively, If you don’t like the nested FILTER, you can use FILTER & IF.

=FILTER(IF({1,0},HR_Budget1314[Budget Year],HR_Budget1314[Expenses]),HR_Budget1314[Budget Year]=I3) 

FILTER non-adjacent columns and Rearrange them

This Video from Excel MVP Treacy Mynda gave me the idea that you can rearrange the non-adjacent columns

For example, How do you filter and return data for Hires and Department respectively for the Year 2021?

The trick is to use the IF function as shown in the above example. Select the Hires Values as [value_if_true] then Department Values as [value_if_false]

NB:

⭐You can also get the same results using the FILTER and CHOOSE Functions as shown below

=FILTER(CHOOSE({1,2},HR_Budget131420[Hires],HR_Budget131420[Department]),HR_Budget131420[Budget Year]=I3) 

⭐, Unlike the IF function which can only return 2 columns, CHOOSE Function can return more than 2.

For example, how do you Filter the Hires, Departments & Expenses

FILTER & IF function will not be able to do this but FILTER & CHOOSE will do the work

FILTER Function using AND Logic

The trick here is to know that in Excel an Asterisk (*) is used to represent an AND logic

NB:

Whenever you are nesting functions or using multiple criteria, Always remember how EXCEL evaluates a Formula (PEMDAS)

FILTER Function using OR Logic

The trick here is to know that in Excel a cross (+) is used to represent an OR logic

NB: You can use the OR Logic with any of the other columns.

FILTER All X and NOT Y

The trick here is to know that in Excel you can use ( <> ) to represent NOT logic. Credit for this goes to a student of mine Suleiman Mpole who pointed this as the alternative to using the (>) shown in the next example

An alternative to the above is using the greater than sign (>) to compare the logical test.

FILTER X or Y and NOT Both

The trick here is to know that in Excel you can use minus sign ( – ) to filter out one or the other but not Both

For example, using the below data, how can you filter all suppliers who either shipped using Railways OR supplied Furniture Product But NOT anyone who supplied Furniture Using Railways?

Below is how to do it

FILTERING DATES

Filtering dates is easy if you understand that Excel stores date as sequential Serial numbers.

Using the above example, How do you Filter all future Delivery Suppliers?

How do you tell if the date is in the future? By comparing it with the TODAY date.

Any Date value which is GREATER than TODAY is a future date

=FILTER(Supplies,Supplies[Delivery Date]>TODAY())

FILTER Function With Wild Cards

Like SUMPRODUCT, The 3 wildcard characters (?*~) used in other excel formulas do not work with FILTER Function.

However, the FILTER Function utilizes other functions (LEFT, RIGHT, SEARCH, and MID) to give you the same results.

For example, using the below data, filter all suppliers who shipped by Air.

Normally, using wildcards, Shipmode=*Air* would have worked but this does not work with the FILTER Function

How the formula works:

⭐SEARCH Function returns a number if it finds the word air in the ship mode else #VALUE!

=FILTER(Suppliers[Supplier Name],ISNUMBER({7;9;#VALUE!;11;16;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;9;#VALUE!;#VALUE!;#VALUE!;12;7;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!}))

⭐ISNUMBER function returns an array of TRUE / FALSE based on the SEARCH Results

=FILTER(Suppliers[Supplier Name],{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE})

⭐FILTER returns only the TRUE values

FILTER Weekend Days ONLY

You can combine FILTER with the WEEKDAY function to filter weekends or weekdays.

The trick is knowing which [return type] in the WEEKDAY function you will be using.

To return ONLY weekdays, just filter all days less or equal to 5. See below

=FILTER(Suppliez,WEEKDAY(Suppliez[Delivery Date],2)<=5)

FILTER ODD or EVEN numbers

MOD function can be used to check if a number is ODD /EVEN.

MOD returns a remainder of 1 for odd numbers and 0 for even numbers. This is the only trick you need.

For example, how do you return ONLY the ODD sample ID?

FILTER Items Repeated N Times

The trick here is to use the COUNTIF function to return a count on an item.

You can then compare this count with N times to return and tell FILTER what to include.

For example, how do you return a unique list of customers who have supplied 5 times

Here is how it works

NB: Below is how the COUNTIF Function works

• COUNTIF(Suppliedz[Supplier Name],Suppliedz[Supplier Name]) → {5;5;1;5;1;5;1;1;5;1;5;1;5;1;1;5;1;5;5;1;1}
• COUNTIF(Suppliedz[Supplier Name],Suppliedz[Supplier Name])=5 → {TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}

FILTER Recent Weekday Items

This is not a frequent request but when it is required you need to be ready to know how to solve it.

Given the Below data, how do you filter the Recent Wednesday data given today is 10/16/2021

The trick here is to understand how MOD Function works

Here is what you need to understand:

• Excel assigns a day of the week a number i.e. by default 1= Sunday to 7=Saturday. So Wednesday will be 4
• Excel stores dates as Sequential Serial Numbers with 1 being 1/1/1900
• MOD(startDate-Weekday_Number,7) → returns the number of days in the past given a startdate and a weekday number
• To get the exact date of this past weekday, we deduct the past days from the start days (startdate – Past days)

=FILTER(Suppliez7,(TODAY()-MOD(TODAY()-4,7))=Suppliez7[Delivery Date])

FILTER Data Based On Specific Month

The trick here is to understand the MONTH function

MONTH function returns the month number (between 1 to 12) from a given date.

For example, =MONTH(10/30/2021) returns 10

FILTER Data Based On Specific Week

Excel has a beautiful but overlooked function, WEEKNUM, that returns an integer representing the week in the year (1 to 53).

=WEEKNUM( serial_number, [return_type] )

Beautiful as the function is, it has one major weakness, WEEKNUM  doesn’t accept a range argument e.g. =WEEKNUM(K11:K110) just returns #VALUE! error.

The trick here is to know how to make WEEKNUM return a range just like the MONTH function

NB: To force WEEKNUM to work like other functions that return a range, Add a Zero to the range

=FILTER(Suppliez45,WEEKNUM(Suppliez45[Delivery Date]+0)=2)

FILTER Data For a Specific Year

The trick here is to understand the YEAR function

YEAR function returns the year part from a given date.

For example, =YEAR(10/30/2021) returns 2021

FILTER By Time in Datetime

The trick here is to learn how to extract the time part in a DateTime Value without creating a helper column

When MOD is used with a divisor of 1, the result is always the fractional part of the number, if any, because every whole number can be evenly divided by itself.

In DateTime, the time value is always the Fraction part of the serial number

Assume you have below attendance data. How can you filter staff leaving work early if the official check-out time is 4:30 PM?

Here is how it works

FILTER Last Occurrence in Excel

For any unsorted data, using MAX Function is the best option for getting the last occurrence in a number series.

Using, the example above, can you filter the staff who left the latest

FILTER the Top N or Bottom N Values

The trick here is to use the LARGE Function to filter top N and the SMALL Function to Filter bottom N

FILTER Common values between two lists

COUNTIF(tblNEW[New Supplier List],tblOld[Old Supplier List]) returns 1 (TRUE) where new suppliers are found in the old supplier’s list and 0 ( FALSE ) if not found.

Filter Fully Empty & Non-Empty Rows

From the data below, how do you filter suppliers who were fully paid, partially paid, and not paid?

Filtering the fully paid suppliers

The trick here is to use the Asterisk to represent the AND logic

=FILTER(Suppliers, (Suppliers[1st payment]<>””) * (Suppliers[2nd payment]<>””))

For fully empty cells just edit above to check for empty rows instead

=FILTER(Suppliers, (Suppliers[1st payment]=””) * (Suppliers[2nd payment]=””))

Filtering Partially Empty Rows

Who were the suppliers who were paid ONLY once?

The trick here is to use the Minus (-) sign to represent the AND logic

Filtering Partially Empty and NON-empty Rows

Who were the suppliers who were paid ATLEAST once?

Conclusion:

I hope I have shown where all the fuss on FILTER Function is coming from. If you have not been using it–Start today.

I am also sure the list above is not exhaustive, so Leave a Comment and add more ways to use FILTER FUNCTION

Recommended Link:

MOD Function

DOWNLOAD WORKSHEET

REFERENCES:

For More Details on the Logical test in the Include section. See below Videos

https://www.youtube.com/watch?v=1OCtMvbhq0gu0026ab_channel=ExcelOnFire

https://www.youtube.com/watch?v=ZCQAweoAdOwu0026ab_channel=MyOnlineTrainingHub

https://www.youtube.com/watch?v=wDR5YcAcTx0u0026ab_channel=ExcelIsFun

https://www.youtube.com/watch?v=ROc6uFEq_KEu0026ab_channel=Computergaga

After posting the article on Linkedin…I have to add the below alternative to returning non-adjacent columns

https://www.excelcampus.com/functions/filter-non-adjacent-columns/