Analyzing a Real Spreadsheet With Copilot in Excel
Copilot in Excel is genuinely good at one thing people underuse it for: turning a vague analytical question into a concrete formula or pivot table, fast. It's also capable of confidently misreading your data and handing you a wrong number with the same tone of voice it uses for a right one. Both of those things are true at the same time, which is why this walkthrough spends as much time on verification as it does on the prompts themselves.
Note
If you haven't seen how agent mode changed what Copilot can do in Excel specifically, the overview of Copilot's agent mode covers the shift from single-formula suggestions to multi-step actions across a whole sheet, which is what makes a workflow like this one possible in a handful of prompts instead of a dozen.
The spreadsheet: a year of expense data nobody's summarized
The example for this walkthrough is a real kind of mess: an expense export with 1,400 rows, a Date column, a Vendor column, an Amount column, and a Category column where someone has been typing free text for a year, so "Travel," "travel," and "Travel - Flights" all show up as different values. Here's a representative sample of what those rows actually look like:
expense_export.xlsx, sample rows (illustrated)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Vendor | Category | Amount |
| 2 | 2026-01-08 | Meridian Office Supply | Office | $412.00 |
| 3 | 2026-01-14 | Delta Airlines | travel | $890.00 |
| 4 | 2026-01-22 | Meridian Office Supply | office | $96.50 |
| 5 | 2026-02-03 | Delta Airlines | Travel - Flights | $1,204.00 |
| 6 | 2026-02-19 | Riverside Catering | Meals: Client | $275.00 |
| 7 | 2026-03-01 | Delta Airlines | Travel | $650.00 |
Six rows are enough to see the problem the full 1,400-row sheet has: Delta Airlines alone shows up under three different Category spellings, and Meridian's two entries don't match each other either. Nothing about that is visible from a quick scroll through the sheet, it only shows up once you actually ask a question that depends on the Category column being consistent.
Get the sheet ready first
Copilot in Excel works best on data that is formatted as an Excel table. Microsoft's own guidance is to format the data as a table first, and to name the columns you care about in your prompt. The same habits that keep a normal Excel PivotTable behaving, a single header row with unique, non-blank labels, no blank rows or columns in the middle, no merged cells, and consistent data in each column, are worth applying here too. Copilot in Excel also has three modes: Allow editing (it changes the workbook), Plan (it proposes an approach for you to confirm first), and Chat only (it analyzes and answers without touching the workbook), and it opens in Allow editing by default. For a first pass on data you don't fully trust yet, Chat only or Plan is the safer place to start.
Start with a question, not a request for a formula
The instinct with Copilot in Excel is to ask for a specific function by name. That works fine once you already know exactly which function you need. Most real analysis starts one step earlier than that, with a question about the data itself.
Look at this spreadsheet. What are the top 5 vendors by total spend this year, and what percentage of total spend does each one represent? Also flag any Category values that look like duplicates of each other due to inconsistent capitalization or naming.
”This does two jobs in one prompt: it gets you the actual answer to the business question (who are we spending the most with), and it surfaces the data quality problem (the messy Category column) before you build anything on top of a column you can't yet trust.
Excel
Copilot's answer, illustrated
Top 5 vendors by total spend this year: Delta Airlines ($41,200, 18% of total), Meridian Office Supply ($22,600, 10%), Riverside Catering ($19,800, 9%), Cascade IT Services ($17,400, 8%), and Blackwood Consulting ($14,900, 7%). One note before you use these numbers: several Category values look like the same category typed differently, including "Travel", "travel", and "Travel
- Flights", and "Office" and "office". If these get merged, some of these totals and percentages will shift.
That second part is worth taking seriously before doing anything else, because any summary built on top of "Travel" and "travel" as two separate categories will understate travel spend by however much landed in the miscapitalized bucket. The vendor ranking above is a representative example of the kind of answer Copilot returns, not a claim about any real company's actual spend.
Clean the data with agent mode, then verify the row count
Standardize the Category column: merge any values that are the same category with different capitalization or minor spelling differences into one consistent value per category. Show me a list of what you merged before applying it.
”Asking it to show the merge list before applying it is the important part of this prompt. Editing mode can rewrite a whole column in one action, which is efficient right up until it merges two categories that were actually supposed to stay separate, like folding "Software" and "Software - Annual" together when your finance team actually tracks those separately for budgeting reasons. Reviewing the proposed merges costs thirty seconds and catches that kind of thing before it's baked into every downstream number.
- 1
Ask for the analysis or cleanup, not the finished formula
Describe the business question or the data problem in plain language. Let Copilot propose the formula or the pivot structure; it's usually better at picking the right function than you'd guess from typing the question yourself.
- 2
Read the formula it wrote, not just the result
Click into the cell and look at the actual formula, the same way you'd check a colleague's spreadsheet before trusting a number in it. A SUMIFS with the wrong range, or a lookup pulling from the wrong column, can still produce a number that looks perfectly reasonable.
- 3
Spot-check against a number you can verify by hand
Pick one vendor or one month and total it manually, or with a simple filter, and compare it to what Copilot's formula produced. If they match, that's real evidence the formula's logic is sound across the rest of the sheet, not just a hope.
Building the summary once the data's trustworthy
With categories standardized, ask for the actual output you need.
Build a pivot table on a new sheet summarizing total spend by Category and Month. Add a chart below it showing the top 3 categories by total spend across the year.
”This is a clean example of agent mode's real advantage over the old suggestion-only Copilot: one prompt creates the new sheet, the pivot table, and the chart, instead of you building each piece separately after asking three different questions. Here's a representative version of the summary that lands on the new sheet, with categories now standardized:
Spend Summary sheet: total by Category and Month (illustrated)
| A | B | C | |
|---|---|---|---|
| 1 | Month | Category | Total |
| 2 | Jan | Travel | $6,400 |
| 3 | Jan | Office | $1,900 |
| 4 | Jan | Meals | $2,100 |
| 5 | Feb | Travel | $7,850 |
| 6 | Feb | Office | $1,450 |
| 7 | Feb | Meals | $1,780 |
Where Copilot in Excel gets it wrong
What Copilot is actually reliable at and what still needs a second look aren't the same list, and confusing the two is where most bad numbers slip through:
| Copilot in Excel is reliable at | Still worth verifying yourself |
|---|---|
| Picking a plausible function or pivot structure for a described task | Whether the range or selection it used matches what you actually meant |
| Spotting inconsistent text values, like duplicate categories with different capitalization | Whether values that look like duplicates are actually meant to stay separate |
| Assembling a multi-step result (new sheet, pivot table, chart) in one pass | Whether a blank row, header row, or totals row silently changed which rows got counted |
Verify anything with formulas or arithmetic
Copilot is generally strong at writing the right kind of formula for a described task, and it can still get a range wrong, misread a merged cell, or apply a filter that silently excludes rows you needed included. It states the wrong answer with the exact same confidence as the right one. Never treat a number from Copilot as final without spot-checking it against something you calculated independently, especially before it goes into a report someone else will make a decision from.
The most common specific mistake is a subtle scope error: asking Copilot to analyze "this data" when the sheet has extra header rows, blank rows, merged cells, or a totals row in it. Those are exactly the layouts that trip up tabular analysis in Excel generally, so the fix is to convert the data to a table first, and then to check, once, whether the row count in its summary matches the row count you'd expect from the raw data. A pivot table that's quietly excluding the last 40 rows because of a blank row partway through the sheet looks completely normal until you compare its total against a plain SUM of the whole column.
The second common mistake is asking a vague question and accepting a vague answer. "What's interesting in this data?" gets you a generic, safe response about trends and totals. "What changed in vendor concentration between Q1 and Q3, and is any single vendor now more than 20% of total spend?" gets you an actual, checkable finding, because it tells Copilot exactly what kind of answer would count as useful.
A formula Copilot wrote is a first draft of a calculation, not a verified one. The five minutes it takes to check it against a number you know is cheaper than the meeting where someone asks why the total's wrong.
When to skip Copilot for this
For a one-off, small dataset (a few dozen rows, one clear question), it's often faster to just build the formula yourself than to write out a full prompt describing what you want. Copilot in Excel earns its time back on larger, messier, or more repetitive analysis: a big export with inconsistent data entry, a recurring monthly summary you'd otherwise rebuild by hand each time, or a question that would normally take you several nested formulas to answer correctly on the first try.
Official sources
Checked on September 21, 2026. Features, plans and names change often, so the vendor's own pages are the final word.