How to Use Copilot in Excel With Spilled Array Formulas Across Sheets

Spilled array formulas in Excel automatically fill adjacent cells based on the formula logic. When you work with these dynamic arrays across multiple sheets, Copilot can help you create, edit, and debug them. The challenge is that Copilot does not natively support cross-sheet array references in its natural language prompts. This article explains how to structure your data, write formulas that Copilot can interpret, and apply them correctly across sheets.

You will learn the exact syntax for referencing spilled ranges in other sheets. You will also see how to combine Copilot suggestions with manual formula adjustments. By the end, you can use Copilot to build spilled array formulas that span multiple sheets without breaking the dynamic behavior.

How Copilot Handles Spilled Array Formulas

Spilled array formulas use the dynamic array engine introduced in Excel 365. When you write a formula like =SORT(A2:A100), Excel spills the result into adjacent cells automatically. Copilot can generate these formulas through natural language prompts such as Sort the values in column A.

However, Copilot currently does not understand cross-sheet references in its generated formulas. If you ask Copilot to Reference the spilled range from Sheet1 in Sheet2, it may produce a static reference or an error. You must manually adjust the formula after Copilot creates it.

Prerequisites for Cross-Sheet Spilled Arrays

Before you begin, confirm the following:

• You have Excel 365 or Excel for the web. Spilled arrays are not available in older Excel versions.
• Your data is in a single column or row per source sheet. Spilled arrays cannot merge multiple non-adjacent ranges.
• Copilot is enabled in your Microsoft 365 subscription. You need Copilot Pro or Copilot for Microsoft 365.
• The target sheet has enough empty cells for the spill range. If cells are blocked, Excel returns a #SPILL! error.

Steps to Create a Spilled Array Formula Across Sheets Using Copilot

Follow these steps to make Copilot generate a base formula, then adapt it for cross-sheet use.

1. Open the source sheet and prepare your data
   Place the data you want to spill into a single column on Sheet1. For example, put product names in column A from A2 to A100. Ensure there are no blank rows inside the range.
2. Open the Copilot pane
   Go to the Home tab and click Copilot. The pane opens on the right side of the Excel window.
3. Ask Copilot to create a base spilled formula
   Type a prompt like Sort the values in column A or Filter column A for values greater than 100. Copilot generates a formula such as =SORT(Sheet1!A2:A100). Insert it into a cell on Sheet2.
4. Convert the formula to reference the spilled range
   Edit the formula to use the spill range operator. Change =SORT(Sheet1!A2:A100) to =SORT(Sheet1!A2#). The # operator tells Excel to use the entire spilled array from Sheet1. If Sheet1 does not yet have a spilled formula, first create one there. For example, in Sheet1 cell B2 enter =SORT(A2:A100). Then in Sheet2 reference =Sheet1!B2#.
5. Test the formula for dynamic updates
   Add a new value to the source data in Sheet1 column A. The spilled array on Sheet1 expands automatically. The formula on Sheet2 referencing Sheet1!B2# also updates without manual changes.
6. Handle errors if they appear
   If you see #SPILL!, check that the target range on Sheet2 is empty. If you see #REF!, the source sheet name or cell reference is incorrect. Verify the sheet name spelling and that the source formula is spilling correctly.

If Copilot Still Has Issues With Cross-Sheet References

Even with the steps above, you may encounter specific problems. Below are common scenarios and how to fix them.

Copilot Generates a Static Reference Instead of a Spilled Reference

When you ask Copilot to reference another sheet, it often creates =Sheet1!$A$2:$A$100 rather than =Sheet1!A2#. This static range does not expand when new data is added. Replace the absolute range with the spill operator manually. First, ensure the source sheet already has a spilled formula. Then edit the Copilot-generated formula to use #.

Spilled Array on One Sheet Does Not Update When Source Data Changes

This usually happens when the source formula is not a dynamic array formula. Confirm that the formula on the source sheet uses a function that spills, such as SORT, FILTER, UNIQUE, SEQUENCE, or RANDARRAY. If the source formula is a regular formula copied down, it will not spill. Convert it to a single dynamic array formula.

Copilot Cannot Find the Sheet Name When Writing the Formula

Copilot may produce a formula with a wrong sheet name, especially if the sheet name contains spaces. Always use single quotes around sheet names with spaces. For example, ='Sales Data'!A2#. After Copilot generates the formula, check the sheet name and add quotes if needed.

Copilot Spilled Arrays vs Manual Spilled Arrays: Key Differences

Item	Copilot-Generated Formula	Manually Written Formula
Cross-sheet reference	Not supported natively; requires manual edit	Fully supported with SheetName!Cell# syntax
Spill range detection	Detects spills only in the active sheet	Detects spills in any sheet
Error handling	May produce #SPILL! if target range is not empty	Same behavior
Dynamic expansion	Works after manual # operator added	Works automatically
Best use case	Quick one-sheet formulas for sorting or filtering	Complex cross-sheet data pipelines

Use Copilot for initial formula generation when working within a single sheet. For cross-sheet spilled arrays, rely on manual formula writing or edit the Copilot output as described above.

You can now create spilled array formulas in Excel that reference data across sheets using Copilot. Start by generating a base formula with Copilot, then replace the range with the # operator. Always verify that the source sheet contains a dynamic array formula before referencing it. For advanced scenarios, combine Copilot with manual edits to build robust cross-sheet data models.