Ravindra BagaleCourses & study guides मराठी Track your progress

28. Copilot and AI in Excel

28.2 Get Your Data Ready: Copilot Works Best on a Clean Excel Table

By Last updated:

Example first. Amir points Copilot at an export with a blank row in the middle, a typed "Total" row, a header called Amt and two merged title cells. He asks for "sales by city" and gets a total that includes the Total row twice. Nothing is wrong with Copilot here: the data was ambiguous. Ten minutes of cleaning turns the export into an Excel Table called tblOrders with clear headers, and the same prompt now gives the right answer first time.

Preparing data for Copilot — in 30 seconds

What: Copilot understands data best when it is an Excel Table with one header row, clear column names (with units), one fact per column and no blank rows, merged cells or typed totals.

Why: Copilot reads headers and table boundaries to decide what you mean. A Table gives it named columns such as tblOrders[Amount], and the formulas it writes are then easy for you to read and check.

Tiny example: Rename Amt to Amount (₹), delete the blank row and the typed Total row, press Ctrl+T, and name the Table tblOrders. Then the prompt can say "In tblOrders, sum Amount by City".

First look at the data (before → after):

Preparing data for Copilot: before and after tables, fictional Blinkit Maharashtra data

Expected result — Clean Table tblOrders

Order ID City Amount (₹)
BLK-1001 Pune 64
BLK-1002 Nashik 270
Before table and formula as text

Before — Messy export

A B C
Order City Amt
BLK-1001 Pune 64
— — —
BLK-1002 Nashik 270
Total — 334

Formula / click path

Delete blank row and typed Total row
Ctrl+T › My table has headers › OK
Table Design › Table Name: tblOrders

Do it step by step:

  1. Delete fully blank rows and any typed Total or Subtotal rows inside the data. Totals belong in the Table's Total Row, not in the data.
  2. Unmerge cells (Home › Merge & Center › Unmerge Cells) and keep only one header row.
  3. Give every column a clear name with units, for example Amount (₹) and Mins.
  4. Click any cell in the data and press Ctrl+T. Keep My table has headers ticked and select OK.
  5. On Table Design, type the name tblOrders in Table Name and press Enter.
  6. Save the workbook in OneDrive or SharePoint and make sure Formulas › Calculation Options is set to Automatic.

Why a typed Total row is the most dangerous mistake

In the messy export above, the data rows add up to ₹334 (64 + 270), and there is also a typed row "Total 334". Any tool that sums column C, a person with AutoSum or Copilot, can get ₹668 because the Total row is counted as one more order. In a Table there is no such row: you switch on Table Design › Total Row and Excel adds a SUBTOTAL that sits outside the data.

A 10-point "Copilot-ready" checklist

# Check Why Copilot cares
1 Data is an Excel Table (Ctrl+T) with a short name like tblOrders Clear boundaries and named columns
2 Exactly one header row Two header rows make the second one look like data
3 Headers say what and which unit: Amount (₹), Mins "Amt2" tells Copilot nothing
4 No blank rows or columns inside the data A blank row can look like the end of the data
5 No typed totals or subtotals inside the data They get counted again
6 No merged cells Merged cells break sorting, filtering and reading
7 Dates are real dates, numbers are real numbers Text "450" is not summed; text dates cannot be grouped by month
8 One kind of fact per column (no "Pune - 411001" in one cell) Split it first (Module 5)
9 Calculation Options set to Automatic Microsoft: editing with Copilot is only supported in Automatic mode
10 File saved in a modern format (.xlsx) in OneDrive or SharePoint Microsoft lists Strict Open XML as unsupported; home plans need the file in OneDrive

Is a Table required? Microsoft's current tips page does not insist on a Table, and Copilot can read normal ranges. Older Microsoft guidance did require a Table or a supported range. In practice a Table still gives the most reliable answers, because the prompt and the formulas can use names like tblOrders[Amount] instead of guessing where the data ends.

AutoSave: Microsoft's pages disagree. The FAQ for home subscriptions says Copilot in Excel needs AutoSave on, which means the file must be in OneDrive. The newer Copilot in Excel tips page says Copilot runs with AutoSave on or off, and even suggests turning AutoSave off when you want to try Copilot's changes without saving them. Safe habit: keep the file in OneDrive, and when you experiment on an important file, work on a copy.

Add a Definitions sheet. If your company has its own meaning for a word, write it down in the workbook. For example a sheet called Definitions with "Net sales = Amount of Delivered orders only; Late = Mins more than 15". Then start prompts with "Use the Definitions sheet". Microsoft's tips page also mentions workbook rules and custom skills for the same purpose: telling Copilot your house rules once instead of in every prompt.

Microsoft sources (checked 7 October 2026): Copilot in Excel tips; Frequently asked questions about Copilot in Excel; FAQ about Copilot in Microsoft 365 subscriptions.

Practice task (try it before you open the answer):

  1. Take the messy export above (headers Order, City , Amt; a blank row; a typed Total row).
  2. Make it Copilot-ready using the checklist.
  3. Say what the correct total is and what a careless sum of column C would give.
Answer
  1. Delete the blank row and the typed Total row, rename the headers to Order ID, City (no trailing space) and Amount (₹).
  2. Press Ctrl+T, keep My table has headers, name the Table tblOrders, and switch on Table Design › Total Row if you want a total.
  3. Correct total ₹334 (64 + 270). A careless sum of column C including the typed Total row gives ₹668.

Ravindra Bagale's Tip

Spend the first ten minutes on the data, not on the prompt. A clean Table with honest headers beats the cleverest prompt on a messy sheet.