Copilot Prompts for Excel That Actually Work
Bottom line: the prompt matters less than the table under it. Name the exact columns, give Copilot one clean header row, and say where the result should go. Below are Copilot prompts for the 6 jobs most people open Excel for: cleaning data, formulas, analysis, PivotTables, charts and conditional formatting. Each comes with the table shape it needs and the failure to watch for.
Type them in the Copilot pane (the button in the lower-right corner, or F6), then swap in your own column names.
Before any prompt: 4 things to set up
1Name the columns
This is the rule that matters most. Microsoft's own tip: naming the columns you want analysed produces more accurate results. Analyse this makes Copilot guess. Compare Revenue by Region doesn't.
2One header row, nothing merged above it
Microsoft no longer lists Excel Table formatting as a requirement. But a single header row, no blank rows and no merged titles on top still make Copilot's job much easier. Ctrl+T (Format as Table) gets you there in a second.
3Pick the mode
Edit mode is the default and changes the workbook directly. Chat mode analyses and answers without touching the file. Plan mode writes a plan before it edits. Questions go in chat, big multi-step jobs start in plan.
4Keep a way back
Copilot runs with AutoSave on or off, so switch AutoSave off while you experiment. Undo and version history still work. And set Calculation Options to Automatic, because Copilot won't edit otherwise.
One more for European teams: Copilot supports many languages, but Microsoft says it was trained mostly on English sources and may not perform as well in others. If your headers are in German or Dutch, keep the prompt short and name the columns exactly as they're written.
Prompts to clean data
The table needs: one row per record, and a unique ID column if you have one.
Find duplicate rows based on Customer ID and Email and highlight them. Don't delete anything yet.
Split Full Name into 2 new columns, First Name and Last Name, right next to it.
Trim extra spaces in the Company column and make the capitalisation consistent.
Prompts for formulas and lookups
The table needs: a shared key column (Customer ID, SKU, employee number) in both the main sheet and the lookup sheet.
Add a column that pulls the Region for each Customer ID from the Customers sheet.
Explain the formula in the selected cell step by step, in plain English.
For the first one, Microsoft's own example has Copilot recommend a formula such as XLOOKUP and add the new column itself. I like that it writes real formulas on the grid instead of pasting numbers. You can click the cell and see exactly what it did.
Moving off the old =COPILOT() formula? That's a different job, and I wrote a separate guide on what replaces the COPILOT function.
Prompts for data analysis
The table needs: dates stored as real dates, numbers as numbers, and one metric per column.
Which 3 products drove the drop in Revenue between Q1 and Q2? Use the Product, Quarter and Revenue columns.
Summarize the Comments column, find the main themes and sentiment, and add them in a new column.
For text analysis like the second prompt, hover over the small superscript numbers in Copilot's answer. They show the rows it actually used. That's your fastest check.
Prompts for PivotTables
The table needs: flat data with one column per field (Month, Category, Sales) and no subtotal rows inside it.
Create a PivotTable with the sum of sales, with month in rows and category in columns.
Now filter it to the last 6 months and sort the categories by total sales, highest first.
Prompts for charts
The table needs: the axis column on the left (dates or categories), the series in columns to its right, and headers you'd be happy to see in a legend.
Create a line chart showing change over time. Show data labels and gridlines.
Change it to a clustered column chart, move the legend to the bottom and title it Monthly Sales by Region.
The charts stay linked to the source data, and you can keep editing them by asking follow-up questions. Microsoft doesn't publish a list of chart types Copilot can or can't build, so just ask for the one you want.
Prompts for conditional formatting and validation
The table needs: the column the rule reads must hold numbers or dates, not text that looks like them.
Highlight in red every row where Days Overdue is above 30.
Add a drop-down to the Status column with Open, In progress and Closed.
Don't see the Copilot button at all? That's a settings problem, and here's the fix list for Copilot not showing in Excel. For everything else, my full guide to Copilot in Excel covers the rest. I have no commercial interest in Microsoft, by the way (I'd tell you if I did).
Questions about Copilot prompts for Excel
What are the best Copilot prompts for Excel?
The ones that name the exact columns, say where the result should go, and ask for one job at a time. For example: Add a column that pulls the Region for each Customer ID from the Customers sheet.
Does my data need to be formatted as an Excel table?
No. Microsoft no longer lists it as a requirement. A single header row with no merged cells still makes Copilot's job easier, and Format as Table is a quick way to get there.
Can Copilot create PivotTables and charts in Excel?
Yes. Describe it in plain words, for example: Create a PivotTable with the sum of sales, with month in rows and category in columns. Charts and PivotTables stay linked to the source data and editable.
How do I stop Copilot from changing my file?
Use chat mode. It analyses the workbook and answers in the pane without changing anything. Edit mode, the default, works directly in the workbook.
How do I check whether Copilot got the analysis right?
Read the formulas it wrote, hover over the superscript numbers in text analysis to see the source rows, and spot-check a few totals yourself. Microsoft says to review everything Copilot creates before you rely on it.
Do Copilot prompts for Excel work in languages other than English?
Yes, many languages are supported. Microsoft notes that Copilot was trained mostly on English sources and may not perform as well in other languages.
Want your team writing prompts like these?
I run live online Copilot workshops for groups of up to 20 people, from 1.5 to 4 hours. Teams that live in Excel have their own session.