How to Use Copilot in Excel With FILTER Function for Dynamic Arrays

When you work with large datasets in Excel, extracting rows that match specific conditions often requires writing complex FILTER formulas. You might know the logic you need but struggle to get the syntax right, especially when dealing with multiple criteria or dynamic arrays that spill results across cells. This article explains how to use Copilot in Excel to generate and refine FILTER formulas for dynamic arrays without writing each argument manually. You will learn the exact prompts to use, how to adjust the generated formula, and what limitations to watch for when Copilot handles array outputs.

What the FILTER Function Does and How Copilot Helps

The FILTER function in Excel returns a dynamic array of rows that meet one or more logical conditions. Its syntax is FILTER(array, include, [if_empty]). The include argument must be a Boolean array of the same height as the source data. When you type the formula manually, you need to construct the include argument using operators like =, <>, or AND/OR logic wrapped in parentheses. Copilot in Excel can generate this formula from a natural language description, which reduces syntax errors and speeds up the process.

Before you start, ensure Copilot is enabled in your Microsoft 365 subscription. You need a work or school account with a Microsoft 365 Copilot license, or a Microsoft 365 Personal or Family subscription that includes Copilot Pro. The feature is available in Excel for Windows version 2402 or later, Excel for the web, and Excel for Mac version 16.78 or later. Your workbook data must be formatted as a table or a named range for Copilot to recognize the dataset correctly.

Steps to Generate a FILTER Formula With Copilot

1. Open the Copilot pane
   In Excel, go to the Home tab and click the Copilot button on the right side of the ribbon. The Copilot pane opens on the right side of the window. If you do not see the button, verify that your Microsoft 365 account has the correct license and that the feature is not blocked by your organization’s admin settings.
2. Select your data table
   Click any cell inside the table that contains the data you want to filter. Copilot uses the table name or the range reference from the active cell. If your data is not in a table, convert it first by pressing Ctrl+T and confirming the table headers.
3. Type a prompt describing the filter condition
   In the Copilot text box, write a clear sentence that describes the rows you want to see. For example, type “Show all rows where the Region column equals West and the Sales column is greater than 5000.” Copilot interprets the column names from your table headers. Press Enter or click the Send button.
4. Review the generated formula
   Copilot displays the formula in the pane, such as =FILTER(Table1, (Table1[Region]="West") (Table1[Sales]>5000), "No results"). It also shows a preview of the output. If the formula looks correct, click the Insert Formula button to place it in the active cell. The result spills into the cells below automatically because FILTER is a dynamic array function.
5. Refine the formula if needed
   If the generated formula does not match your intent, click the Refine button below the formula preview. A new text box appears where you can add or change conditions. For instance, type “Also exclude rows where Status is Cancelled” and Copilot updates the include argument to (Table1[Region]="West") (Table1[Sales]>5000) (Table1[Status]<>"Cancelled"). Click Insert Formula after each refinement.
6. Edit the formula manually in the formula bar
   After insertion, you can click the formula bar and edit the FILTER arguments directly. Copilot does not lock the formula, so you can change column references, add nested functions like SORT or UNIQUE, or modify the if_empty value. This step is useful when Copilot’s generated syntax is close but needs a small tweak.

Common Mistakes and Limitations When Using Copilot With FILTER

Copilot returns a static value instead of a dynamic array

If Copilot suggests a formula that starts with = followed by a single value, the prompt may have been ambiguous. For example, typing “Filter sales above 5000” might produce =SUMIF or =AVERAGEIF instead of FILTER. Rephrase the prompt to explicitly ask for rows: “Return all rows where Sales is above 5000.”

The formula references the wrong table or column

Copilot uses the column names from the table header row. If a header contains extra spaces or special characters, Copilot may misinterpret it. Rename the header to a single word without spaces, such as “SalesAmount” instead of “Sales Amount.” After renaming, refresh the Copilot pane by closing and reopening it, then type the prompt again.

Copilot does not support multiple conditions with OR logic in one prompt

Copilot handles AND logic well using the multiplication operator between conditions. For OR logic, generate a single condition first, then edit the formula manually to add the + operator between the OR conditions. For example, change (Table1[Region]="West") (Table1[Sales]>5000) to ((Table1[Region]="West") + (Table1[Region]="East")) (Table1[Sales]>5000).

The FILTER result does not update when source data changes

Dynamic arrays recalculate automatically when source data changes, but only if the formula references the table columns directly. If Copilot generated a formula that uses a hardcoded range like $A$2:$C$100 instead of a table reference, the array will not expand or contract with new rows. Convert the range to a table using Ctrl+T, then regenerate the formula with Copilot so it uses the structured table reference.

Copilot Pro vs Copilot for Microsoft 365: FILTER Function Support

Item	Copilot Pro	Copilot for Microsoft 365
License requirement	Microsoft 365 Personal or Family subscription	Microsoft 365 Business Basic, Standard, Premium, or E3/E5 with Copilot add-on
FILTER formula generation	Yes, in Excel for Windows, Mac, and web	Yes, in all Excel versions with the same capabilities
Dynamic array output	Fully supported with spill behavior	Fully supported with spill behavior
Refine button	Available	Available
Administrative control	No admin controls	IT admin can disable Copilot for specific users or groups

Both Copilot versions generate FILTER formulas with the same syntax and support dynamic arrays. The main difference is that Copilot for Microsoft 365 includes enterprise data protection and admin policies. For individual users, Copilot Pro provides the same formula generation without the need for IT management.

You can now use Copilot in Excel to create FILTER formulas for dynamic arrays by describing your filter conditions in natural language. Start by selecting your data table, typing a clear prompt in the Copilot pane, and inserting the generated formula. If the output does not match your intent, use the Refine button or edit the formula manually in the formula bar. For complex OR logic or non-table ranges, adjust the formula after insertion. To get the best results, keep your table headers clean and specific, and always verify the spilled output covers the expected number of rows.