You want to ask Copilot a question that needs data from two or more open Excel workbooks. By default, Copilot in Excel works on the active sheet or table in the current workbook. It cannot natively query across separate files. This article explains the current limitations and shows you the practical workarounds to combine data from multiple workbooks so Copilot can analyze it as one source. You will learn how to use Power Query to merge workbooks and how to structure consolidated tables for Copilot to process.
Key Takeaways: Using Copilot with Multiple Workbooks
- Copilot single-source limitation: Copilot in Excel can only analyze one Excel table or named range at a time, not data spread across multiple open workbooks.
- Power Query > Data > Get Data > From File > From Workbook: Merges data from several workbooks into a single table that Copilot can then reference.
- Consolidated table structure: After merging, you must format the combined data as an Excel table with headers to make it accessible to Copilot.
Why Copilot Cannot Query Multiple Workbooks Directly
Copilot in Excel is designed to work with one structured data source at a time. That source must be an Excel table formatted with headers. When you open Copilot, it reads the active table or the current selection. It cannot automatically detect or merge data from other open workbooks. This is a deliberate design choice to avoid ambiguity and to ensure Copilot returns accurate results based on a single, well-defined dataset.
The technical root cause is that Copilot binds to the Excel object model at the worksheet level. Each workbook is a separate process or document, and Copilot does not have cross-workbook awareness. To work around this, you must consolidate your data sources into one table before invoking Copilot.
What Copilot Can Do with a Single Workbook
Inside one workbook, Copilot can analyze multiple sheets as long as the data is structured as tables. It can suggest formulas, create charts, and answer questions about the data in the active table. It cannot, however, join data from a table on Sheet1 with data from a table on Sheet2 unless you first combine them into a single table.
Steps to Merge Data from Multiple Workbooks into One Table for Copilot
The only reliable method to use Copilot across multiple workbooks is to consolidate all data into one Excel table in a single workbook. The following steps use Power Query, which is built into Excel for Microsoft 365, Excel 2021, and Excel 2019.
- Open a new blank workbook
This will be the destination file where you combine all data. Close the source workbooks or keep them open; Power Query can read from both. - Go to Data > Get Data > From File > From Workbook
In the new workbook, click the Data tab. In the Get & Transform Data group, click Get Data. Select From File and then From Workbook. Navigate to the first source workbook and click Import. - Select the table or sheet to import
The Navigator pane shows all tables and sheets in that workbook. Click the table or sheet that contains the data you need. If you select a sheet, Power Query will import all data on that sheet. Click Load to load the data into the new workbook. - Repeat for each additional workbook
Go to Data > Get Data > From File > From Workbook again for each source file. Load each one into the same workbook. Each import creates a new query and a new table in the workbook. - Append all queries into one table
Go to Data > Get Data > Combine Queries > Append. In the Append dialog, select Three or more tables. Add all the imported tables to the list. Click OK. This creates a new query that stacks all rows from every source table. - Load the appended query as a table
In the Power Query Editor, verify the column names match. If any column names differ, rename them so they are identical. Click Close & Load. The combined data appears as a new table in your workbook. - Format the combined data as an Excel table
Click anywhere in the combined data. Press Ctrl+T to open the Create Table dialog. Ensure the range is correct and check My table has headers. Click OK. This step is mandatory because Copilot only recognizes Excel tables. - Open Copilot and start asking questions
Click the Copilot button in the Excel ribbon or press Alt+Windows logo key, then C. In the Copilot pane, type questions about the consolidated table. For example, “Show total sales by region” or “Find the average order value per customer.”
Common Issues When Using Copilot with Consolidated Data
Copilot Says It Cannot Find a Table
This happens when the combined data is not formatted as an Excel table. Even if the data looks like a table, Copilot only recognizes data that has been explicitly formatted with Ctrl+T. Check that the table name appears in the Table Design tab under the Table Name field. If not, press Ctrl+T to create the table.
Column Headers Do Not Match Across Source Workbooks
Power Query appends rows based on column names. If one workbook has a column named “Sales Amount” and another has “Revenue”, the data will appear in separate columns. Before appending, edit each query in Power Query Editor to rename columns so they are identical. Use the Transform tab > Rename option.
Performance Slows Down with Large Datasets
Merging multiple workbooks can produce a table with hundreds of thousands of rows. Copilot may respond slowly or time out. To improve performance, filter out unnecessary rows in Power Query before loading. Use the Remove Rows or Filter Rows options in the Power Query Editor.
Copilot Single-Table vs Consolidated Multi-Workbook: Key Differences
| Item | Single Table in One Workbook | Consolidated Table from Multiple Workbooks |
|---|---|---|
| Setup effort | None, the table already exists | Requires Power Query to merge and format |
| Copilot compatibility | Full, works immediately | Full, once the data is formatted as a table |
| Data refresh | Manual or automatic within the same file | Manual refresh via Data > Refresh All |
| Risk of column mismatch | None, columns are already aligned | High, requires careful renaming in Power Query |
| Performance with large data | Depends on table size | Can be slower due to merge overhead |
If Copilot Still Cannot Access the Consolidated Data
If you have followed all steps and Copilot still does not recognize the table, check that the workbook is saved to a OneDrive or SharePoint location. Copilot in Excel requires the file to be stored in the cloud to enable AI features. Also verify that your Microsoft 365 license includes Copilot. You must have Copilot for Microsoft 365 or Copilot Pro to use this feature.
Another common issue is that the table contains merged cells. Copilot cannot process merged cells. Before creating the consolidated table, ensure all cells in the source data are unmerged. Use the Merge & Center dropdown in the Home tab and select Unmerge Cells.
Finally, if you still see an error, try closing and reopening the workbook. Copilot sometimes needs a fresh session to detect newly created tables. After reopening, click anywhere inside the table and then open the Copilot pane.
You can now consolidate data from multiple workbooks into one Excel table and use Copilot to analyze the combined dataset. Start by using Power Query to append the files. Then format the result as a table with Ctrl+T. For future work, consider storing all related data in a single workbook to avoid the merge step entirely. To further streamline your workflow, name each table clearly using the Table Design tab so Copilot can reference them by name in natural language queries.