If you have a Microsoft 365 subscription, there is a good chance you already have Microsoft Copilot sitting in the ribbon of Excel — and there is an equally good chance you have clicked it once, gotten a vague answer, and never gone back. That is the most common Copilot story I hear in my Excel training webinars, and it is fixable. Copilot in Excel is not a magic button. It is a very capable assistant that responds to how clearly you ask.

Below are seven Microsoft Copilot prompts that do real, billable work in Excel — plus the setup rules that make the difference between a useful answer and a shrug.

First: Set Copilot Up to Succeed

Before a single prompt, do these three things. They account for most of the “Copilot doesn’t work for me” complaints.

1. Convert your data to a Table

Click anywhere in your data and press Ctrl + T. Copilot in Excel is dramatically more reliable when it can see a structured table with real headers. A loose block of cells with merged headings and blank rows is guesswork; a table is data.

2. Give your columns plain-English headers

“Rev_Q3_FY26_NET” means something to you. “Net Revenue” means something to Copilot. Rename your headers and watch answer quality jump.

3. Save the file to OneDrive or SharePoint

Copilot needs AutoSave and a cloud-stored workbook. A file sitting on your C: drive will leave the Copilot button grayed out or limited.

Seven Copilot Prompts That Earn Their Keep

1. Explain a formula you inherited

“Explain what the formula in cell H2 does, step by step, in plain English.”

Every business owner has inherited a workbook from someone who left the company. Instead of reverse-engineering a nested IF with four XLOOKUPs at 11 p.m., ask Copilot to narrate it. This is my single favorite use — and it is a wonderful way to learn Excel, not just outsource it.

2. Add a calculated column without writing the formula

“Add a column called Margin % that divides Gross Profit by Net Revenue, formatted as a percentage with one decimal.”

Copilot inserts the column, writes the structured-reference formula, and shows it to you before committing. Notice how specific that prompt is: exact column name, exact calculation, exact formatting. Vague in, vague out.

3. Ask for insights instead of a chart

“Show me the top three trends in this data and tell me which product line is declining fastest.”

Most people ask Copilot to “make a chart.” Ask it what the data says instead. It will surface breakdowns and outliers you were not looking for — and then you can ask it to visualize the one that matters.

4. Highlight problems with conditional formatting

“Highlight every row where Days Outstanding is greater than 60 in light red.”

Conditional formatting rules are easy to build manually but tedious to build ten times. Describing the rule is faster than clicking through the dialog, especially for multi-condition logic.

5. Sort, filter, and summarize in one shot

“Filter to the Northeast region, sort by Net Revenue descending, and show me the total for the top ten customers.”

Copilot handles compound requests well. Chaining three steps into one prompt is where the time savings actually show up.

6. Build the PivotTable you were going to build anyway

“Create a PivotTable summarizing Net Revenue by Region and by Month, with Region as rows.”

You still need to understand PivotTables to sanity-check the output — but you no longer need to drag fields around to get to a first draft.

7. Ask it to check your work

“Are there any blank cells, duplicate customer IDs, or text values in the Amount column?”

This is the prompt almost nobody uses, and it is the one that saves you from sending a report with a bad number in it. Data-quality checks are exactly the kind of tedious scanning a machine should do.

Where Copilot in Excel Still Falls Short

Being honest about the limits is part of using the tool well:

It cannot fix a badly structured workbook. Merged cells, multiple tables on one sheet, and headers buried in row 7 will defeat it. Clean structure first.
It does not know your business rules. If your company defines “active customer” as someone who ordered in the last 18 months, you have to say so in the prompt.
It can be confidently wrong. Always spot-check totals against a number you already know. Treat Copilot output as a fast first draft, not as a signed-off figure.
Feature availability varies by plan and update channel. If a capability described here is missing, check your Microsoft 365 version before assuming you did something wrong.

Copilot vs. ChatGPT and Claude for Excel Work

People ask me this in nearly every session, so here is the short version. Microsoft Copilot wins when the work happens inside the workbook — it can see your data and act on it directly. ChatGPT and Claude win when you need to write or debug a complex formula, generate VBA, or think through how a model should be structured, because you can go back and forth in depth and paste the result into Excel yourself.

In practice, most productive Excel users I train use both: an outside AI assistant to design the formula or macro, and Copilot to apply and maintain it in the live file. They are not competitors. They are different tools for different halves of the same job.

Your Next Step

Pick one report you build every month. Open it, convert it to a Table, and run prompts 1, 3, and 7 against it. Fifteen minutes is enough to know whether Copilot belongs in your workflow — and most people find at least one place where it saves an hour.

Want to go deeper? I teach live, hands-on webinars on Microsoft Excel, Power BI, ChatGPT, Claude, and Microsoft Copilot for business professionals — practical sessions built around real work, not theory. Browse the upcoming webinar schedule at PCWebinars.com and grab a seat in the next Excel or Copilot session.