Microsoft Copilot in Excel has moved from novelty to daily driver for a lot of business users — but there is still a big gap between the demo videos and what actually happens when you open a real, messy spreadsheet on a Tuesday morning. In this post I want to cut through the hype and show you exactly what Copilot in Excel does well, where it still falls flat, and how to prompt it so you get useful answers on the first try instead of the fifth.

First, Make Sure Copilot Can Even See Your Data

Before you type a single prompt, do this: convert your data range into an official Excel Table (select any cell in your data and press Ctrl + T). This one step solves more Copilot problems than anything else. Copilot works best with structured data — clean headers in one row, no blank rows or columns in the middle, no merged cells, and one type of data per column.

Also make sure your file is saved to OneDrive or SharePoint with AutoSave turned on. Copilot in Excel does not work on a file sitting on your local C: drive. If the Copilot button is greyed out, that is almost always the reason.

8 Things Copilot in Excel Does Genuinely Well

1. Writing and explaining formulas

This is the biggest time-saver for most people. Ask Copilot: “Add a column that flags any order over $5,000 that shipped more than 10 days after the order date.” It will build the formula, add the column, and — critically — explain what each part does. You can also paste an inherited monster formula and ask, “Explain this formula in plain English, step by step.” That alone is worth the license for anyone who has taken over someone else’s workbook.

2. Instantly summarizing a dataset

Prompt: “Give me a summary of this data — key totals, trends, and anything unusual.” Copilot will tell you your top customers, your revenue trend, and where the outliers sit. It is a fast first pass before you start real analysis.

3. Building PivotTables without the drag-and-drop

Try: “Create a PivotTable showing total revenue by region and by quarter, with region as rows.” Copilot builds it on a new sheet. You still need to understand PivotTables to trust and refine the result — but the setup time drops to near zero.

4. Charting on request

Prompt: “Show me a chart of monthly revenue for the last 12 months.” Copilot picks a chart type, builds it, and drops it in. It is a solid starting point, though you will usually still format the titles and labels yourself.

5. Conditional formatting and highlighting

Prompt: “Highlight the top 10% of deals in green and anything below target in red.” Copilot applies the rules directly. This is one of the most reliably correct things it does.

6. Sorting and filtering with natural language

Instead of clicking through filter menus: “Show only Q3 orders from the Northeast where margin is below 20%.” Fast, and easy to iterate on.

7. Data cleanup suggestions

Copilot is decent at spotting inconsistent date formats, trailing spaces, duplicate rows, and numbers stored as text. Ask: “What data quality issues do you see in this table?” It rarely catches everything — but it catches enough to be worth the 15 seconds.

8. Answering “what if” questions

Prompt: “If we raised prices 6% and lost 3% of volume, what happens to total revenue?” Copilot will model it out. Always sanity-check the math — but as a fast scenario sketch, it is useful.

3 Things Copilot in Excel Still Can’t Do Well

1. It won’t rescue a badly structured workbook

Merged cells, headers three rows down, blank spacer columns, five tables jammed onto one sheet — Copilot will either refuse or, worse, confidently give you a wrong answer. Structure is still your job. If your spreadsheet is a mess, fix the structure first.

2. It can’t reliably build complex, multi-step models

Ask Copilot for a full three-statement financial model or a multi-sheet commission calculator with tiered rules and it will struggle. It is excellent at single steps. It is not a replacement for someone who understands how to architect a model.

3. It doesn’t understand your business context

Copilot does not know that “Region 4” is really two territories, or that Q1 numbers are always inflated by an annual renewal cycle. It reads the data on the screen — not the context in your head. That context is exactly where your judgment still earns its keep.

The Prompt Habits That Separate Good Results From Frustration

After a lot of hours with Copilot in Excel, these four habits make the biggest difference:

Be specific about columns. Say “using the Order Date and Ship Date columns” instead of “using the dates.” Copilot guesses badly when you are vague.

Ask for one thing at a time. A prompt with four requests in it usually returns one good result and three mediocre ones. Chain your prompts instead.

Ask it to explain its work. Add “and explain how you calculated it” to any analysis request. This is how you catch the mistakes — and how you actually learn Excel instead of just outsourcing it.

Always verify the numbers. Spot-check a few rows manually. Copilot is a very fast assistant, not an auditor. The person who sends a wrong number to a client is you, not the AI.

Copilot Doesn’t Replace Excel Skills — It Rewards Them

Here is the pattern I see over and over in my training sessions: the people who get the most out of Copilot are the people who already know Excel. They know what a PivotTable should look like, so they can tell when Copilot builds a bad one. They know what XLOOKUP does, so they can read the formula Copilot wrote and spot the error. Copilot makes strong Excel users dramatically faster. It makes weak Excel users faster at being wrong.

So the play is not “learn Copilot instead of Excel.” The play is learn both — and let the AI handle the typing while you handle the thinking.

Want to Go Deeper? Join a Live Session

I run live, hands-on webinars on Microsoft Excel, Power BI, Copilot, ChatGPT, and Claude — practical, business-focused training with real examples you can use the same day. No fluff, no theory-only slides, and you can ask questions live.

Browse the upcoming live webinar schedule at PCWebinars.com and grab a seat in the next Excel or Copilot session. Bring your toughest spreadsheet question — I will answer it on the call.