What Copilot in Excel Actually Does Well
Copilot sits in the ribbon and reads your workbook through a task pane on the right. You describe what you want in the box, it proposes a formula, a formatting rule, or a PivotTable, and you accept or reject it. That is the whole interface. There is no configuration, no model selection, and no way to point it at a file that isn't open in front of you.
The five tasks where it earns its place are narrow and repeatable. Formula generation from a description. Formula explanation when you inherit someone else's nested IF mess. Outlier detection in a numeric column. Conditional formatting driven by a sentence instead of a Rules Manager dialog. And a first-draft summary or PivotTable that gives you a skeleton to adjust. Everything outside those five is either slower than doing it by hand or quietly wrong. In my experience the value isn't that Copilot is smarter than you — it's that typing one sentence beats writing a SUMIFS with four criteria from memory.
The prerequisite matters more than people admit. Copilot works on a formatted Excel Table, not a loose range. Select your data, press Ctrl+T, confirm My table has headers, and name it something real like Sales. Copilot's output quality changes noticeably the moment your data is a table with typed columns, because it can reference column names instead of guessing at cell addresses. If your data is still a raw range with merged cells in row 1, fix that first. This is the same discipline that makes Power Query reliable, and the two tools share the requirement.
Name your table and your columns before you open Copilot. A table called Sales with columns Order Date, Region, Amount, and Product beats Sheet1 with Column A through Column F every single time. I recommend also deleting any blank rows and unmerging headers first — Copilot will happily build a formula against a phantom column if a merged cell confuses its read of your header row.
Job One: Turn a Sentence Into a Working Formula
This is the best use of the tool and the one you'll come back to daily. You describe the logic in the same words you'd use explaining it to a colleague, and Copilot writes the formula and drops it into whichever cell you have selected. It works best when you name the columns exactly as they appear in your header row, because that removes the ambiguity it would otherwise have to guess through.
Click the cell where the new column should start, then type the requirement in plain English. For example: add a column that flags orders over $5,000 from the last 90 days. Do not write =IF(AND( — that just makes Copilot guess at what you meant. Describe the business rule and let it pick the functions.
Copilot returns something like =IF(AND([@Amount]>5000,[@[Order Date]]>=TODAY()-90),"Review","Standard"). Check three things: the comparison operator, the date logic, and whether it used TODAY() or a hardcoded date. Accept only if all three match what you asked for.
Sort your Sales table by Amount descending and look at the rows near $5,000. Then sort by Order Date and check the rows around the 90-day boundary. If a row at exactly $5,000.00 is excluded, the > is correct. If a row dated 91 days ago is flagged, your date logic is off. I've caught more Copilot bugs at the boundary than anywhere else.
Double-click the fill handle to push the formula down the table. Because your data is a table, Excel writes [@Amount] references and the formula holds as rows are added. If the fill writes $A$2:$A$4000 instead, you were working on a range, not a table, and the formula will silently drift when you import next month's orders.
The structured-reference detail is the one that bites people later. A formula written against A2:A4000 looks fine today and breaks the moment someone inserts a row above or pastes in 500 more orders. Copilot will produce either style depending on how your data is set up, so check the formula bar after you accept. If you see absolute cell ranges and you meant to build something durable, convert to a table first and re-run the prompt. The same trap applies when you combine Excel work with SQL output — imported query results arrive as a range unless you explicitly format them as a table.
Job Two: Explain a Formula You Inherited
Second-best use, and the one that saves the most embarrassment. Someone leaves, you inherit their workbook, and column F contains a nested formula with four IFs, an INDEX, and a reference to a sheet you didn't know existed. Highlight the cell and ask Copilot to explain it in plain English. It walks the nesting level by level and tells you what the formula actually does, which is usually not what the header claims it does.
Highlight the formula cell and type: explain what this formula does, step by step, and tell me when it returns an error. Copilot will decompose nested IFs and name the condition that produces each branch. Write the explanation in a comment on the header cell so the next person doesn't have to repeat the exercise.
Follow up with: when does this return #N/A or #VALUE!. A nested lookup on a Sales table will fail any time a Product code exists in an order but not in the Products reference table. Knowing the failure condition is more useful than knowing the happy path, because the happy path is already visible in the output.
Pick one row, work out the expected answer by hand on paper, and compare it to the formula's output. If they match, you understand the formula. If they don't, the explanation was incomplete and you should ask Copilot to re-explain that specific branch rather than trusting the summary. I treat an explanation as a hypothesis, not a fact.
A common mistake is asking Copilot to rewrite an inherited formula you don't understand. Don't. Explain it first, confirm you can predict its output for two or three known rows, and only then ask for a cleaner version. Rewriting a formula whose logic you haven't verified means you can't tell whether the new output is right or just different.
Jobs Three and Four: Outliers and Conditional Formatting
Both of these are low-risk, high-visibility tasks where a wrong answer is immediately obvious, which makes them a good place to build trust in the tool. Outlier detection lets you skip the manual sort-and-eyeball pass. Conditional formatting lets you skip the Rules Manager dialog, which is genuinely unpleasant to navigate.
Select your Sales table and prompt: highlight the Amount values that are unusually high or low compared to the rest of the column. Copilot proposes a rule, often based on standard deviations from the column mean, and applies it as conditional formatting. One column per request — asking about Amount, Quantity, and Discount at once produces a vague answer about all three.
The tool flags statistical outliers, not wrong numbers. A $48,000 order might be a legitimate annual contract, and a $3.50 order might be a test transaction. In my experience roughly half the flagged rows in a real Sales table are fine, and the exercise is worth it because the other half are genuine data-entry errors like a decimal point in the wrong place.
For visual rules, describe the condition directly: format the Amount column in red where the value is over 10000, and add a light green fill to the Region column where the region is West. Copilot applies it to the table range and shows the rule it used. Check the Applies To box in Manage Rules afterward, because it occasionally scopes the rule to the wrong rows when your table has a totals row at the bottom.
Ask Copilot for a short summary: how many orders are above $10,000 in the Sales table, what is the total Amount by Region, and which months have the most entries. Then ask it to insert a PivotTable with Region in Rows, Order Date grouped by month in Columns, and Sum of Amount in Values. You get a reviewable skeleton in one pass rather than dragging fields one at a time.
Look at the grand total in your new PivotTable and compare it to a plain =SUM(Sales[Amount]) in an empty cell. They must match. If they don't, Copilot has built the PivotTable over the wrong range or picked up a totals row as data. This single check catches most of the errors I've seen in Copilot-generated pivots.
Where Copilot Quietly Fails
The failures are consistent enough to memorize. First, cross-workbook joins. Copilot reads the open workbook, not the workbook sitting in another folder, and not the Access database your finance team maintains. Ask it to bring product names from a separate Products.xlsx and it will either decline or invent a formula referencing a sheet that doesn't exist here. If you need a real join, get both datasets into one workbook first, or do the join in Power Query.
Second, cleanup rules that need business context. Tell it to standardize the Region column and it will happily collapse West, west, and W. into one value — but it cannot know that WRegional is not a region, or that your company renamed EMEA to Europe and the Middle East in Q2 and both spellings are valid for different reporting years. Rules like that have to come from you. Third, oversized tables. There are row and column ceilings on what Copilot will process, and when you cross them the request doesn't error out cleanly — it just returns something built from a subset of your rows, which is far more dangerous than a visible failure.
Fourth, and this is the one that costs real time: dates stored as text. If Order Date imports from a system as text strings like 2026-03-14 or 14/03/2026, Copilot's date logic silently treats them as strings. TODAY()-90 arithmetic on a text column returns garbage or an error, and the model will still present the formula with confidence. Convert the column first — Data > Text to Columns with the date format set, or =DATEVALUE() in a helper column — then re-run the prompt. This is the same class of problem covered in AI-assisted Excel and SQL workflows, where imported columns routinely arrive with the wrong type.
Copilot or Power Query: Who Does the Job
The dividing line is repetition. Copilot is for the task you're doing once, right now, on data you can see. Power Query is for the task that runs every month on a file with the same name in the same folder. If you're exploring a new export to figure out what's in it, Copilot is faster because there's nothing to set up. If you're rebuilding the same cleanup for the eleventh time, Power Query wins because the steps are recorded, auditable, and refresh with one click.
Load a new export, format it as a table, and ask Copilot for a summary of what's in each column, a count of rows by Region, and the min and max of Amount. This is reconnaissance. You're learning the shape of the data before committing to a process, and there's no reason to build a query for that.
As Copilot surfaces problems — mixed date formats, duplicate Order IDs, trailing spaces in Product names — keep a running list. That list is the specification for your Power Query workflow. Skipping this step is why people rebuild their queries from scratch; the requirements lived only in their head.
When the task shows up a second or third time, move to Data > Get & Transform. Each cleanup step from your list becomes a Power Query step: change type on Order Date, trim on Product, remove duplicates on Order ID. The output is a table that refreshes, and the transformation is documented rather than implied in a formula. Our Power Query guide walks through the editor in detail.
Don't use Copilot to generate the Power Query steps themselves. It will describe the right transformations, but the value of Power Query is that the steps are visible, ordered, and editable in the Applied Steps pane. Generate the recipe with Copilot, apply it by hand in the editor, and you'll end up with a query you can actually debug six months later. The broader skill shift here, from writing formulas to directing and verifying tools, is what most data analyst roles now screen for.
Data Boundaries and the Mistakes I See Most
Before you paste anything into the Copilot box, know where it goes. Copilot in Excel runs through Microsoft 365, which means your workbook content and your prompts leave the local machine and are processed in the service. Enterprise deployments inherit your organization's data governance settings, and the admin controls in the Microsoft 365 admin center determine whether prompts are retained, logged, or used for model training. On a personal or unmanaged account the defaults are different and less protective. If your Sales table contains customer names, account numbers, or anything covered by GDPR or a client NDA, check with whoever owns your tenant before you start typing.
The practical rule I use: if the dataset would need approval to email to an outside vendor, it needs approval to send to Copilot. That doesn't mean never use it. It means strip identifiers first, work on a copy, and keep the original untouched. For a lot of analysis work the columns that matter are Amount, Region, and Order Date — none of which require customer names to be present.
The most common mistake in production reports is accepting a generated formula because it looks plausible and the column populates. Plausible and correct are different things. Build a two-cell control: =SUM(Sales[Amount]) against =SUMIFS(Sales[Amount],Sales[Region],"West") plus the other regions. If the parts don't sum to the whole, your new formula is wrong. I've seen a misclassified Region filter understate a quarterly figure by 12% and nobody caught it for three weeks.
It cannot read a workbook you don't have open, and it cannot reach a shared drive, a SharePoint list, or last month's file. If a prompt needs data from Products.xlsx, open it, bring the relevant columns in with XLOOKUP or Power Query, and then ask your question. Prompts that assume cross-file access produce answers that reference sheets which don't exist in your current file.
When the generated formula is close but not right, tell Copilot what's wrong in plain terms — exclude orders where Status equals Cancelled — rather than editing the nesting by hand. Hand-editing a formula you can't fully read is how broken logic gets into a report. If you can't explain the formula out loud, you don't own it yet.
Adding a column, renaming a header, or changing a data type can silently invalidate what Copilot produced earlier. After any structural edit, re-run your control check on the key sums. This takes two minutes and it's the difference between a report you can defend in a meeting and one you can't.
A Repeatable Workflow You Can Start Today
Here's the sequence I run on any new dataset, in order. Convert the range to a table with Ctrl+T and give it a real name like Sales. Fix column types before anything else, especially dates that arrived as text. Run Copilot for reconnaissance: a summary of each column, counts by Region, and min and max of Amount. Fix what's obviously broken by hand. Then use Copilot for the five jobs it's good at — formula generation, formula explanation, outliers, conditional formatting, and a first-draft PivotTable — verifying each one against a control figure before you move on.
When the same cleanup comes back next month, stop using Copilot for it and build the Power Query workflow. The tool is a fast way to figure out what needs doing; it is not the place to store how you do it. Keeping that boundary clear is what stops a workbook from turning into a pile of formulas nobody can audit. If you want to know which of these skills actually appear in job postings, our breakdown of how AI is changing the data analyst role covers the specific shifts recruiters are screening for.
