PLAIN-ENGLISH EXPLAINER
How to Analyze a Spreadsheet with Copilot in Excel
The data was always there. You just didn't have an afternoon free to go find the story in it.
If you have been handed a workbook full of raw numbers and a vague request to find something useful in it, Microsoft 365 Copilot in Excel can read through that data, suggest formulas, and point out patterns without you writing a single function by hand. This guide covers where Copilot actually lives in Excel, how to turn a request for insight into a real formula column by typing a plain-language prompt, and what you still need to check yourself once Copilot hands something back.
To analyze a spreadsheet with Microsoft 365 Copilot in Excel, select the Copilot icon in the lower-right corner of Excel, then type a plain-language request, such as asking it to add a column that calculates a value from your data or to explain a formula in a selected cell. Copilot in Excel helps you build and edit workbooks, including generating formulas and surfacing insights, all from inside the table you already have open. It is a prompt-driven conversation, not a set of buttons to click, and any suggestion is still worth checking before you act on it.
Published
Key Takeaways
- You open Copilot in Excel by selecting the Copilot icon in the lower-right corner of Excel, which opens the chat pane.
- Asking for a formula column is a plain-language request, such as 'Add a column that calculates the profit margin for each product,' not a button you click.
- Asking for a formula column gets you an actual column of working formulas, not a description of what a formula could look like.
- You can ask Copilot to explain any formula, including one it just suggested, by typing a request like 'Explain the formula in the selected cell.'
- Copilot editing only works in Excel when Calculation Options are set to Automatic, and Copilot can still make mistakes, so review what it creates before you rely on it.
Copy and Paste
Prompts You Can Use Today
Get oriented in a new workbook
Look at the data in this table and tell me what each column represents, which columns look like they could be combined into a useful calculation, and whether you notice any rows with missing or inconsistent values before I start analyzing this further.
Useful the moment you open a workbook someone else built, before you try to analyze anything in it.
Ask for a formula column instead of a formula explanation
Add a formula column to this table that calculates the profit margin for each row using the revenue and cost columns, and explain how the formula works so I can check it against a couple of rows by hand.
Naming the exact columns and the calculation you want gets a more useful result than asking Copilot to just find something interesting.
Find patterns you would otherwise have to hunt for manually
Review this table and tell me which product or category shows the largest change over the time periods in this data, what might explain that change based only on what is in this sheet, and which other columns I should look at to confirm it.
Ask it to base the explanation only on what is in the sheet, so you are not getting a guess dressed up as an insight.
Sanity-check a formula before you trust it
Explain exactly what this formula is calculating, step by step, including which cells it references and what would happen to the result if one of those referenced cells were blank or contained text instead of a number.
Paste in any formula, whether Copilot suggested it or someone else built it years ago, and ask for the same walkthrough.
Turn raw numbers into something you can present
Summarize the key trends in this data in plain language for someone who has not seen the spreadsheet, using only the numbers that are actually in this table, and flag anything that looks unusual or worth double-checking before I share it.
This is for the summary you write around the spreadsheet, not a replacement for the spreadsheet itself.
WHAT COPILOT ACTUALLY DOES HERE
What Copilot can do once it is open in your workbook
It is not a separate analytics tool bolted onto Excel. It reads the table you already have and works inside it.
Builds formulas, not just explanations
Copilot in Excel helps you build and edit workbooks, including generating formulas and surfacing insights, all from inside the table you already built.
Writes new columns from existing data
Copilot can create new columns or rows that calculate values from your existing data, such as profit margin per product, instead of just describing how you could do it yourself.
Shows its reasoning on request
A suggested formula is never a black box. You can see how it works by asking Copilot to explain the formula in the cell, which walks through the logic behind the suggestion.
Lives in a chat pane, not a menu you have to hunt for
You reach Copilot by selecting the Copilot icon in the lower-right corner of Excel, which opens a chat pane right next to your data.
WHERE COPILOT ACTUALLY LIVES
Finding the Copilot icon before you can ask it anything
Before you can ask Copilot anything about your data, you have to find it: select the Copilot icon in the lower-right corner of Excel to open the chat pane. It is easy to miss the first time, since it sits outside the ribbon entirely, in the corner of the window rather than up top with the other commands.
Once the chat pane is open, everything in this guide happens there. You type a request in plain language, and Copilot works directly against the table that is already open in front of you, not a copy or an export of it.
There is no separate menu of formula buttons to learn. Asking for a formula column and asking Copilot to explain one you already have both start the same way: open the pane, and describe what you want in a sentence, the same way you would ask a colleague sitting next to you.
If the icon is missing entirely, check that you meet the Copilot requirements for your Microsoft 365 plan before assuming anything else is wrong.
TURNING A QUESTION INTO A FORMULA
Getting an actual formula, not just a description of one
The most useful habit to build with Copilot in Excel is asking for a formula column instead of asking for an answer. If you ask a vague question about your data, you get a vague answer back in the chat pane. If you ask Copilot to add a formula column that calculates something specific from columns that already exist, you get a real column of working formulas sitting inside your table.
From the chat pane, you type a plain-language request such as 'Add a column that calculates the profit margin for each product,' and Copilot adds that formula column directly into your table. There is no separate button to click to insert it: the formula becomes a normal part of the sheet as soon as Copilot generates it, not a response you have to copy out of a chat window by hand.
This matters because a formula sitting in your actual table behaves like every other formula in that table. It updates when the underlying data changes, it can be referenced by other formulas, and anyone who opens the file later sees the calculation itself, not a static number someone typed in once and never revisited.
Before you trust anything Copilot adds, it is worth getting in the habit of checking the formula's logic rather than just the number it produces. Ask Copilot to explain the formula in the selected cell, and reading that explanation against one or two rows you can verify by hand is the fastest way to catch a misunderstanding before it becomes a formula that is quietly wrong across an entire column.
ASKING FOR INSIGHTS DIRECTLY
Skipping the part where you hunt for the pattern yourself
Formula columns solve the problem of calculating something new from your data. A separate, equally useful move is asking Copilot to look across the whole table and tell you what stands out, rather than you scrolling through hundreds of rows trying to eyeball a trend.
Copilot can create new columns or rows that calculate values from your existing data, such as profit margin per product, and that same underlying capability is what lets it answer a question like which product had the biggest swing in a given period, as long as the comparison can be built from the columns already in your table.
The quality of the answer depends heavily on how specific the question is. A prompt that names the exact columns, the exact comparison, and the exact time frame you care about gets you something you can act on. A prompt that just asks Copilot to find something interesting tends to get you a correct but shallow observation, because there was no real question behind it for Copilot to answer precisely.
Treat the chat pane like a conversation, not a single query. If the first answer is close but not quite what you needed, ask a direct follow-up naming what was missing, the same way you would clarify a request to a colleague who got most of the way there on the first try.
BEFORE YOU TRUST A NUMBER
What to check before a Copilot formula goes into a real report
A suggested formula is a strong first draft. Confirm it the same way you would confirm a number a colleague handed you.
Read the explanation, don't skip it
Ask Copilot to explain the formula and read the reasoning against at least one row you can check by hand before you trust the column it added to your table.
Test an edge case on purpose
Check what the formula does with a blank cell, a zero, or a row that looks different from the rest of the table, since those are where a plausible-looking formula tends to break.
Confirm Calculation Options are set correctly
Copilot editing is only supported when Calculation Options are set to Automatic, so if Copilot seems unresponsive, that setting is the first thing worth checking.
WHERE THIS BREAKS DOWN
What to verify yourself before you act on any of it
Copilot in Excel is genuinely useful for getting unstuck on a formula or a first pass at a pattern in your data, but it is not a substitute for understanding the numbers you are about to present or act on. Copilot uses AI to generate suggestions and can sometimes make mistakes, which is why reviewing, editing, and verifying anything it creates before you rely on it is part of using the tool correctly, not an optional extra step.
The Calculation Options setting is the other hard edge worth remembering. If Copilot stops responding to formula requests, check that Calculation Options are set to Automatic before you assume something else is broken; Copilot editing is only supported in that mode.
Beyond those two limits, treat any Copilot formula or insight the way you would treat a number a new analyst handed you for the first time: plausible, probably right, and worth a second look before it goes into something other people will act on. Checking one or two rows by hand takes a few minutes and is far cheaper than correcting a report after the fact.
The habit that actually pays off over time is not memorizing every feature. It is getting comfortable asking Copilot specific, well-scoped questions about data you already understand reasonably well, so that you are in a position to notice quickly if something it returns does not match what you expected to see.
Knowing Copilot exists is not the same as knowing what to ask it
Reading about formula columns is one thing. Building the habit of asking Copilot specific, checkable questions about a workbook that actually matters to your job is another. The first week of Nightschool AI's Copilot course is built around real spreadsheet tasks like this one.
Frequently Asked Questions
Why can't I find the Copilot button in Excel?
Look in the lower-right corner of the Excel window: select the Copilot icon there to open the chat pane. If it is missing entirely, check that your Microsoft 365 plan meets the Copilot requirements.
Can Copilot analyze a spreadsheet that someone else built?
Yes. Open it, select the Copilot icon in the lower-right corner, and start by asking it to describe what each column represents before you ask for any calculation, since that helps you catch anything that looks off early.
Does Copilot just describe a formula, or does it actually add one to my sheet?
It adds a real one. Type a request like 'Add a column that calculates the profit margin for each product' into the chat pane, and Copilot adds the formula column directly to your table as a working formula, not a block of text in a chat window.
How do I know if a Copilot formula is correct?
Ask Copilot to explain the formula to see the reasoning behind it, then check that reasoning against one or two rows you can verify by hand, including an edge case like a blank cell. Copilot uses AI to generate suggestions and can sometimes make mistakes, so this check is worth doing every time.
Why did Copilot stop responding when I tried to edit a formula?
Check your Calculation Options. Copilot editing is only supported when Calculation Options are set to Automatic, so if that setting has been changed to manual, Copilot's editing suggestions may not behave the way you expect.
Can I ask Copilot to find trends in my data instead of writing a formula?
Yes. Ask a specific question naming the exact columns and comparison you care about, such as which category changed the most across the time periods in your table, rather than a general request to find something interesting, which tends to produce a shallower answer.
Is a Copilot formula column different from a formula I write myself?
Not once it is in your table. After Copilot adds the column, it behaves like any other formula in your table: it recalculates when the underlying data changes and can be referenced elsewhere in the sheet. The difference is only in how it was created.
What should I check before sending a Copilot-built analysis to someone else?
Review, edit, and verify anything Copilot creates before you rely on it, the same guidance Microsoft gives for any Copilot suggestion in Excel. Spend a few minutes confirming the logic and testing an edge case before the numbers go into a report someone else will act on.
Stop re-deriving the same formulas from scratch
Finding the Copilot icon and the habit of checking its work take minutes to learn. Every spreadsheet after this one goes faster.