How to Dynamically Filter in a Range in Excel

Filtering is a useful feature in Excel that allows you to display only the data that meets certain criteria. For example, you can filter a list of products by category, price, or rating. However, sometimes you may want to filter your data based on a dynamic value, such as the current date, a cell reference, or a formula. This is called dynamic filtering, and it can help you create more flexible and interactive reports and dashboards.

In this article, we will show you how to dynamically filter in a range in Excel using different methods, such as the FILTER function, the Advanced Filter, and the Slicer. We will also explain the basic theory behind each method, and provide a detailed example with real numbers and an Excel table.

The FILTER Function

The FILTER function is a new function in Excel that was introduced in 2019 as part of the dynamic array functions. It returns a filtered array of values from a source range, based on one or more criteria. The syntax of the FILTER function is:

=FILTER(array, include, [if_empty])

where:

• array is the range of cells that you want to filter.
• include is a Boolean array that specifies which rows or columns to include in the result. The array must have the same size as the array argument, or be a single row or column. You can use logical operators and expressions to create the include array based on your criteria.
• if_empty is an optional argument that specifies what to return if the include array is empty or all FALSE. By default, the FILTER function returns #CALC! error in this case.

The FILTER function is a dynamic array function, which means that it can return multiple values in a spill range. A spill range is a range of cells that automatically adjusts to fit the size of the output array. You can refer to the whole spill range by using the spill reference operator (#), which is appended to the cell where you enter the formula.

For example, if you enter the formula =FILTER(A2:B10,A2:A10>5) in cell C2, it will return a spill range of values from columns A and B where the values in column A are greater than 5. You can refer to this spill range as C2#.

The Advanced Filter

The Advanced Filter is a tool in Excel that allows you to filter a range of data based on complex criteria. Unlike the regular Filter tool, which can only filter data based on one or more criteria in the same row, the Advanced Filter can filter data based on criteria in different rows or columns, using AND and OR logic.

To use the Advanced Filter, you need to have three components:

• A data range that you want to filter.
• A criteria range that defines the criteria for filtering the data. The criteria range must have at least one header that matches a header in the data range, and one or more rows that specify the criteria under each header. You can use logical operators, wildcards, and references in the criteria range.
• An extract range that specifies where to display the filtered data. The extract range must have the same headers as the data range. If you leave the extract range blank, the filtered data will be displayed in place of the original data.

To apply the Advanced Filter, follow these steps:

1. Select the data range that you want to filter.
2. Go to the Data tab and click on Advanced in the Sort & Filter group.
3. In the Advanced Filter dialog box, choose whether to filter the list in place or copy to another location.
4. If you choose to copy to another location, specify the extract range in the Copy to box.
5. Specify the criteria range in the Criteria range box.
6. Click OK to apply the filter.

The Slicer

The Slicer is a tool in Excel that allows you to filter a range of data or a PivotTable by using a visual interface. You can use a Slicer to quickly and easily apply one or more filters to your data, and see the filtered results in a dynamic way.

To use a Slicer, you need to have a range of data or a PivotTable that you want to filter. Then, follow these steps:

1. Select the data range or the PivotTable that you want to filter.
2. Go to the Insert tab and click on Slicer in the Filters group.
3. In the Insert Slicers dialog box, check the boxes for the columns that you want to use as filters. You can select one or more columns.
4. Click OK to insert the Slicers on the worksheet.
5. To apply a filter, click on one or more items in a Slicer. The selected items will be highlighted, and the unselected items will be grayed out. The data range or the PivotTable will be filtered accordingly.
6. To clear a filter, click on the Clear Filter icon in the top right corner of a Slicer. To clear all filters, click on the Clear All icon in the Slicer Tools tab.

Example

To illustrate how to dynamically filter in a range in Excel, let us use the following example. We have a table of sales data for a company, as shown below:

We want to filter this table based on the following criteria:

• The date should be in the current month (February 2024).
• The product should start with the letter A or B.
• The category should be X or Y.
• The region should be North or South.
• The sales should be greater than or equal to 500.

We will use the three methods described above to achieve this.

Using the FILTER Function

To use the FILTER function, we need to create a Boolean array that specifies which rows to include in the result, based on our criteria. We can use the following formula to create the Boolean array:

=((MONTH(A2:A11)=MONTH(TODAY()))*(LEFT(B2:B11,1)="A"+LEFT(B2:B11,1)="B")*(C2:C11="X"+C2:C11="Y")*(D2:D11="North"+D2:D11="South")*(E2:E11>=500))

This formula works as follows:

• The MONTH(A2:A11)=MONTH(TODAY()) part returns an array of TRUE or FALSE values, depending on whether the date in column A is in the current month or not. For example, if the current date is 02/22/2024, this part will return {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}.
• The LEFT(B2:B11,1)="A"+LEFT(B2:B11,1)="B" part returns an array of 1 or 0 values, depending on whether the product in column B starts with the letter A or B or not. For example, this part will return {1;1;0;0;0;0;0;0;0;0}.
• The C2:C11="X"+C2:C11="Y" part returns an array of 1 or 0 values, depending on whether the category in column C is X or Y or not. For example, this part will return {1;1;0;1;1;0;1;1;0;1}.
• The D2:D11="North"+D2:D11="South" part returns an array of 1 or 0 values, depending on whether the region in column D is North or South or not. For example, this part will return {1;1;0;0;1;1;0;0;1;1}.
• The E2:E11>=500 part returns an array of TRUE or FALSE values, depending on whether the sales in column E are greater than or equal to 500 or not. For example, this part will return {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}.
• The * and + operators perform element-wise multiplication and addition on the arrays, respectively. The * operator acts as an AND logic, and the + operator acts as an OR logic. For example, {TRUE;FALSE}*{FALSE;TRUE} returns {FALSE;FALSE}, and {TRUE;FALSE}+{FALSE;TRUE} returns {TRUE;TRUE}.

• The final result of the formula is an array of TRUE or FALSE values, depending on whether the rows in the data range meet all the criteria or not. For example, the final result will be {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}.
• We can use this array as the include argument in the FILTER function, and specify the data range as the array argument. For example, we can enter the following formula in cell G2:

=FILTER(A2:E11,((MONTH(A2:A11)=MONTH(TODAY()))*(LEFT(B2:B11,1)="A"+LEFT(B2:B11,1)="B")*(C2:C11="X"+C2:C11="Y")*(D2:D11="North"+D2:D11="South")*(E2:E11>=500)))

• This formula will return a spill range of values from columns A to E where the rows meet all the criteria. In this case, the only row that meets the criteria is the 9th row, so the formula will return:

• We can refer to this spill range as G2#.

This is how we can use the FILTER function to dynamically filter in a range in Excel. Note that the FILTER function is a volatile function, which means that it will recalculate every time the worksheet changes. This can be useful if you want to update the filtered data automatically, but it can also slow down the performance of your workbook if you use it excessively.