One of the most common sources of frustration with Copilot in Excel is asking it to pull data from another tab — "go to the Lookup tab and find the rate for this region" — and getting errors, wrong results, or a response saying it can't access that data. This is a fundamental limitation of how Copilot works, not a bug.
Why Copilot can't navigate across tabs
Copilot in Excel operates on the active worksheet and data you explicitly provide as context. It doesn't browse your workbook autonomously, navigate to other sheets, or maintain awareness of tab structure the way a human looking at the workbook would. The =COPILOT() function specifically accepts a prompt and an optional cell/range reference — it reads what you point it at, not what's elsewhere in the file.
This is a deliberate design choice, not an oversight. Copilot is built to work with structured data in front of it rather than to explore a workbook the way you might do manually. Earlier versions of Copilot were somewhat more permissive about this — they could loosely reason across more of the workbook — but the current version is more constrained and more accurate as a result.
What this means in practice: if your workbook has a Rates tab, a Lookup tab, and a Data tab, and you want Copilot to combine information from all three, you need to either bring that data to one place, or write the cross-sheet formulas yourself and let Copilot work on the consolidated result.
Workaround 1: Consolidate your data onto one sheet first
The cleanest solution is to bring the relevant data from multiple tabs into a single working sheet before involving Copilot. You don't need to move the data permanently — use Excel's own cross-sheet reference formulas to pull it in:
=Sheet2!A1— simple reference to a cell on another tab=XLOOKUP(A2, Rates!A:A, Rates!B:B)— look up a value from another tab and return it to the current sheet=Rates!B2— direct cell reference from the Rates tab
Once the data you need is consolidated on the active sheet in a clean Table, Copilot can reason across it effectively.
Workaround 2: Use Power Query to merge sheets first
If you regularly combine multiple sheets into a single analysis, Power Query is a better long-term solution than manual consolidation. You can merge or append data from multiple tabs into a single query result, then run Copilot on that output. The advantage: once the Power Query is set up, refreshing it pulls in updated data from all source tabs automatically without you having to re-do the consolidation each time.
Workaround 3: Describe the other sheet's layout explicitly in your prompt
For the =COPILOT() function specifically, you can sometimes work around the cross-tab limitation by describing the data structure of the other sheet in your prompt text — effectively giving Copilot the context it can't navigate to itself:
For example: =COPILOT("The Rates table on the Rates tab has Region in column A and Rate in column B. For each row in this table, look up the Rate for the Region in column C and return it here")
This works for simple lookups but becomes impractical for complex cross-sheet logic. It's more useful for helping Copilot generate a cross-sheet formula (like an XLOOKUP referencing another tab) than for actually performing the lookup itself.
Workaround 4: Write the cross-sheet formula yourself, use Copilot to verify
Sometimes the right approach is to write the XLOOKUP or INDEX/MATCH yourself (or ask Copilot to generate it as a formula text without executing it), then paste it into your sheet manually. Once the formula is returning results, you can ask Copilot to work with those results on the active sheet.
Useful Copilot prompts for this approach:
Write me an XLOOKUP formula that looks up the value in A2 on a sheet called Rates in column A and returns the corresponding value from column B of that same sheetExplain what this formula is doing: =XLOOKUP(A2,Rates!A:A,Rates!B:B)
Workaround 5: Agent Mode for multi-sheet tasks
Agent Mode has broader access to your workbook than the standard Copilot pane and can handle some multi-sheet operations more reliably. Enable it by clicking the tools icon below the Copilot chat box and selecting Agent Mode. It can build PivotTables and dashboards that reference multiple sheets in some configurations, though its cross-tab navigation is still more limited than a human manually working the file. For complex multi-sheet analysis, consolidation first is still the more reliable approach.
What to avoid
- Don't ask Copilot to "go to" another tab — it won't, and the session can get confused trying to fulfil a request it can't complete.
- Don't expect Copilot to "know" your other tabs exist unless you explicitly tell it about them in your prompt.
- Don't try to solve a cross-sheet problem by re-describing it repeatedly — if Copilot says it can't access another sheet, more prompts won't change that; the data needs to come to it, not the other way around.
The bottom line
Cross-sheet work is the area where most people hit Copilot's real ceiling in Excel. The practical fix in almost every case is consolidation first: bring the relevant data to one place using cross-sheet formulas or Power Query, format it as a Table, then let Copilot work on that unified dataset. It's an extra step, but it's reliable — and the consolidation work itself is something Copilot can help you set up.