Copilot in Excel Cannot Find Your Data? Fix the Table First

What you will fix

Copilot in Excel may give a vague answer, say it cannot find the data, or work from the wrong area when the worksheet is not structured clearly. This guide uses a small fictional order sheet to create a clean table, select it, and ask for a limited data check.

The evidence label is documentation-based. Microsoft says Copilot in Excel needs a supported table or range, and the workbook must be saved in a supported location with AutoSave on. The menus and requirements below come from Microsoft’s current support documentation. The input and expected checks are illustrative. No Copilot run is claimed.

Before you start

You need:

Excel for Microsoft 365, Excel for the web, or another supported Excel surface with Copilot available on your account.
An .xlsx workbook saved to OneDrive or Microsoft 365 SharePoint with AutoSave enabled.
A blank worksheet. Do not use customer, employee, financial, or other sensitive data for this exercise.

If the Copilot button is missing, check your license and organisation settings first. A better table will not create access that your account does not have.

Fictional input

Create a sheet named Orders and paste this data into cell A1:

order_id,region,units,status
O-201,West,12,Open
O-202,North,7,Backorder
O-203,West,0,Open
O-204,South,15,Cancelled

To reproduce the failure pattern, first leave the data as a loose range. Add a blank row below it or a note in the same worksheet, then click a cell outside the data before opening Copilot. The exact response can vary by account and version. The point is to remove the ambiguity before asking for analysis.

Three steps

1. Check the workbook and the selected data

Confirm that the workbook is an .xlsx, .xlsb, or .xlsm file saved to OneDrive or SharePoint. Confirm that AutoSave is on.

Click inside the fictional data. If the range has two header rows, blank headers, duplicate headers, empty rows or columns, merged cells, subtotals, or inconsistent values, fix those first. Microsoft lists these as problems for a supported range. The simplest route is to convert the data to a table.

2. Convert the range into a table

Select any cell in the four-column data block. Choose Home > Format as Table. Confirm that the selected range is correct and that My table has headers is checked. Select OK.

Click inside the new table. If Excel gives it a default name, rename it to OrdersTable from the table options. Keep the source rows unchanged. The table is the input for the next check, not a place to store an unreviewed answer.

Open Copilot from the Excel ribbon or the Copilot icon. If the current Excel surface offers modes, choose Chat or Plan for this first check rather than asking it to edit the workbook.

3. Ask for a bounded inspection

Paste this prompt into Copilot in Excel:

Use only the table named OrdersTable.

Report:
1. the number of data rows;
2. the distinct values in the status column;
3. the order_id values where units are zero;
4. any blank cells in the table.

Show the column names you used before giving the answer. Do not change cells, add formulas, sort rows, or create a chart. If you cannot access OrdersTable, stop and tell me exactly what data source you can see.

Read the answer against the table yourself. Do not treat a confident response as a completed review.

Illustrative output

A correct check for this fictional input would identify four data rows, the statuses Open, Backorder, and Cancelled, O-203 as the zero-unit order, and no blank cells. This is an editor-written expectation, not output captured from Copilot.

If Copilot reports a different row count or status list, stop. Check the selected cell, table name, header row, filters, and source values before changing the prompt.

Acceptance checks

The troubleshooting pass is complete when:

the workbook is in a supported location and AutoSave is on;
the table has one header row with unique, nonblank column names;
the table contains exactly the four fictional records;
the answer uses OrdersTable and names the columns it inspected;
O-203 is identified as the zero-unit row;
the source table is unchanged after the check;
no chart, formula, sort, or other workbook edit was requested or accepted.

If it still cannot find the data

The Copilot button is missing: check the Microsoft 365 subscription, app version, privacy settings, and organisation configuration. Feature access varies by account.
It says the table is unavailable: click inside OrdersTable, confirm the table name, save the workbook, and reopen the Copilot pane. If the file is still a loose range, format it as a table.
It reads the wrong rows: remove blank rows and nearby notes from the source area. Ask Copilot to report the table name and column names before asking for calculations.
It starts editing the workbook: stop the task or undo the change. Reopen the request in Chat or Plan mode and state that the source must remain unchanged.
The answer has the wrong count: compare the table’s visible rows with the source values. Check filters and hidden rows, then repeat the bounded inspection.

Limits and privacy

This guide fixes a data-shape problem. It does not verify that Copilot’s calculations or business interpretation are correct. Use approved files only, and keep sensitive data out of a practice workbook. For a different spreadsheet review task, see Use ChatGPT to Turn a Spreadsheet into a Data Dictionary.

Evidence label

Documentation-based troubleshooting guide with fictional worksheet data and an illustrative expected output. No Copilot execution, screenshot, measured result, or personal test is claimed.

Sources

Next lesson

For a source-linked outlier review, read Use Copilot in Excel to Find Outliers Before You Write the Summary.

One useful idea for your workweek: Get The Weekly Verdict.