Copilot in Excel: From Raw Data to Clean Table and Chart in 10 Prompts
If you have Copilot in Excel and you are still mostly typing formulas by hand, you are leaving a lot of value on the table.
In this guide I will show you a realistic flow where we start with a messy data dump and use a handful of natural‑language prompts to clean it, analyze it and build a quick chart.
Before you start: prepare the data as a table
Copilot works best when your data is in a proper table with headers, not in a random block of cells.
Select your range, go to Insert > Table, confirm “My table has headers” and give the table a meaningful name like Sales.
Once that is done, make sure you can see the Copilot icon on the Home tab; if not, double‑check that you are signed in with an eligible Microsoft 365 subscription.
Prompt 1-2: understand what is in the data
Click the Copilot icon and start with something simple:
“Describe this table: what columns do I have and how many rows?”
Then follow up with:
“Show me a short summary of total sales by region” or adjust to your own columns; Copilot will propose a pivot table or a summary formula and can insert it for you.
Prompt 3-4: cleaning obvious issues
If you see inconsistent capitalization or stray spaces, ask:
“Clean up the Customer column: trim extra spaces and standardize names”.
For date or number issues you can try:
“Fix any invalid dates in the Date column and highlight rows with missing Amount values”, letting Copilot propose transformations and conditional formatting rules.
Prompt 5-7: create analysis and charts
When the data looks reasonable, ask Copilot:
“Create a summary table of total Amount by Month and Region” and let it insert either a pivot table or formulas, depending on your preference.
Next, request a chart, for example:
“Insert a clustered column chart showing total Amount by Month for each Region”.
You can refine with follow‑up prompts like “change it to a line chart” or “add data labels for the highest value”.
Copilot is very good at iterating, so do not hesitate to say things like “make the chart easier to read” or “use a different color palette”.
Prompt 8-9: spot outliers and trends
To go beyond the basics, try:
“Highlight any days where sales were more than 50% above the average” or
“Show me customers whose total purchases are in the top 10%”.
Copilot will usually respond with a written explanation plus formulas or additional conditional formatting so you can see exactly how the result was calculated.
Prompt 10: document what Copilot did
A very useful final step is:
“Summarize the main transformations and calculations you applied in this workbook”.
This gives you a mini change log that you can keep on a separate sheet, which is helpful when you or someone else opens the file a month later and wonders how the numbers were produced.
A few practical tips
- Start with specific prompts (“total sales by region in 2026”) rather than very generic ones.
- When Copilot offers formulas or pivot tables, read them once and adjust if needed; treat Copilot as a fast assistant, not an infallible oracle.
- Keep your data as a table and avoid merged cells and scattered inputs; Copilot relies heavily on clean structure.
