Get greater control over your Excel workbooks by converting dynamic COPILOT() responses into static, editable values that won’t change unexpectedly. This guide walks you through simple yet powerful methods to ensure your AI-generated results stay stable, compatible with features like Stock data, and remain auditably accurate. Whether you’re preparing a one-off report or embedding COPILOT() data into larger workflows, you’ll find the right static solution here.
The new Copilot() function has many uses but there are a few problems to keep in mind:
- Copilot, like any AI, make mistakes. You need a way to correct those errors.
- Stop updates. The Copilot() function will refresh itself, like many other Excel functions. That means the results might change, even if the prompt hasn’t altered, the AI can reinterpret the prompt. Each update will count against the monthly AI credit quota.
- Some Excel features, like Stock data, won’t work with a dynamic array source. An interim step is needed to make Copilot() play nice with other parts of Excel.
Excel has several ways to do that, depending if it’s a one-time action or you need something that will update as the Copilot() response changes.
We’ll start with this Copilot() result
=COPILOT("list of UN countries, include headings","Only countries that start with the letters A or B, show short name and continent")
The list is strictly accurate but there are some issues. A full list has some more problems see Facts are a slippery thing with the Copilot() function in Excel
Countries the Bahamas are listed as “North America” but you might prefer to show “Caribbean”. Same for Belize, “Central America” or Azerbaijan “Central Asia”.
Simple Paste Values
Just copy the Copilot) spilled array to another set of columns.
- Click any cell in the Copilot() result list
- Ctrl + A to select whole spilled array.
- A nice little change by some smart developers. Ctrl + A should, strictly, select the whole sheet but selecting only the array is more practical
- Ctrl + C to copy selection
- Select a new cell location to start pasting the results.
- Paste Values to insert just the results without the original formula in the first cell.
- Choose “Paste Values” from Home | Paste menu
- Or right-click Paste Special | Values
Here’s a result with the pasted cells in blue and changed to the results we prefer. We’ve added a simple PivotTable
Once that’s done you could delete the Copilot() columns completely.
This is good for one-time Copilot requests that aren’t going to change.
Paste over Copilot()
If you’re confident doing Paste Values, just paste directly over the Copilot list.
- Click any cell in the Copilot() result list
- Ctrl + A to select whole spilled array
- Ctrl + C to copy selection
- Click on the top-left cell of the Copilot() list.
- In other words, the cell with the Copilot() function.
- Paste Values to insert just the results.
- Choose “Paste Values” from Home | Paste menu
- Or right-click Paste Special | Values
The list looks the same but all the cells are normal, not made by Copilot. Notice cell A1 is plain text, not a formula.
Automatically updating option
Copy then Paste Values is easy and enough for ‘one-time’ copying. But there are situations where you’d want to process changing Copilot() results.
PowerQuery
My preferred method of copying from a dynamic array because you get more choices for rearranging the list. PowerQuery can filter, sort and even change results refreshing each time the source is updated or as requested.
Most important, the result are ‘plain’ cells which can be handled in many different ways. Especially linked data types such as Stock data.
Excel 365’s PowerQuery can accept a dynamic array as input, something relatively new to Excel.
Select the first (top-left) cell of the dynamic array.
Data | Get & Transform Data | From Table/Range.
That opens the whole dynamic array in PowerQuery.
Add the steps to apply. We’ve already applied Transform | Use first rows as Headers (aka Promoted Headers), see above.
Now add whatever other steps you like. We’ve removed the Company Name column, because we only want the Stock Codes. Also replaced Microsoft with Intel (INTC) just to demonstrate how PowerQuery can be setup to add/delete or change specific data.
Now load the changed data into Excel, what you get is a table (not a dynamic array).
That means you can do things not possible from a dynamic array, for example, turning the Stock Codes into linked Stock Data.
Hopefully, PowerQuery will be able to directly call the Copilot() function. Something for the Excel team To Do list?
Copy with Index()
The Index() function lets you copy a single column (or row) from a spilled array. A nice trick which is often given as a solution online. However, the result is another dynamic array which doesn’t get around many incompatibility issues. We mention it, in case it’s useful in your situation, for example extracting some columns from a dynamic array.
=Index(B3#,0,2) or =Index(B3#,,2)
Copies the second column from the Copilot() formula in cell B3.
The copied column is in Col D (blue shading).
