Learn how to use the new FILTER() dynamic array function in Excel 365, Excel 2021, Excel for the web, and more. This feature allows for more efficient report and dashboard building by filtering data and displaying it in a new location. In this tutorial, we’ll cover how to use the built-in filter function and then dive into using the new FILTER() function.
How to Use the Filter Function in Excel
The built-in filter function in Excel is a great tool for filtering data by multiple values. To use it, simply click the Data tab and then click Filter in the Sort & Filter group. Then, click the corresponding drop-down arrow in the header cell and select the filter options you want to apply. One limitation of this function is that it only filters data in place and cannot display filtered data in a new location.
Using the FILTER() Dynamic Array Function
The new FILTER() dynamic array function offers a more efficient way to filter data and display it in a new location. This function is available in Excel 365, Excel 2021, Excel for the web, and more. To use it, simply enter the formula =FILTER(range, include, [if_empty]) into a cell. The “range” argument refers to the data range you want to filter, “include” refers to the criteria you want to include, and “if_empty” is optional and specifies what to display if no matches are found. This function can be a great help in creating reports and dashboards.
Final Remarks
We hope this tutorial has helped you understand how to use the FILTER() dynamic array function in Excel. If you still have questions, please feel free to ask via the contact forum. And if you found this tutorial helpful, please share it with your friends and family to support us.
FAQs
1. Can I use the FILTER() function in older versions of Excel?
No, the FILTER() function is only available in Excel 365, Excel 2021, Excel for the web, Excel for iPad and iPhone, Excel for Android tablets and phones.
2. What is the advantage of using the FILTER() function over the built-in filter function?
The FILTER() function can filter data and display it in a new location, rather than just filtering data in place. This can be helpful for creating reports and dashboards.