Data analysis is Copilot's strongest use case in Excel — more so than formula generation for most people. Instead of building formulas to surface insights, you ask questions directly and Copilot answers with summaries, charts, PivotTables, or plain-English explanations. Here's how to actually use it well.
Before you start: format your data as a table
Copilot works significantly better on properly formatted Excel Tables than on raw cell ranges. Select your data, go to Insert > Table, confirm headers are correct, and make sure each column has a clear, descriptive name. Copilot uses column headers as context — "Revenue" and "Region" give it much more to work with than "Column A" and "Column B."
Opening the Copilot pane
Click anywhere inside your table, then click the Copilot button in the Home tab ribbon. The pane opens on the right. You can type questions or requests directly — no special syntax required, just plain English.
Asking for summaries and overviews
The fastest way to get value from Copilot on an unfamiliar dataset is to ask for a high-level summary first, then drill into specifics. Good starting prompts:
Summarize this data for meWhat are the key trends in this spreadsheet?Give me a summary of sales by regionWhat does this data tell me about Q1 performance?Show me the top 5 products by revenue
Copilot typically responds with a written summary plus the option to insert a chart or PivotTable. You can accept, modify, or ask follow-up questions to go deeper.
Finding trends and patterns
Is there a trend in the Revenue column over time?Which month had the highest sales and what drove it?How did performance change between Q3 and Q4?Which product categories are growing and which are declining?Show me month-over-month growth as a chart
Tip: treat Copilot's first answer as a starting point rather than a final answer. Follow up with more specific questions — "why did revenue drop in March?" or "break that down by region" — to get progressively more useful insights from the same conversation.
Finding outliers and anomalies
Are there any outliers in the Order Value column?Which rows have unusually high or low values?Flag any entries where the discount is greater than 30%What is the highest value in the Revenue column and which row does it belong to?Are there any customers with only one order?
Comparisons and breakdowns
Compare sales performance across regionsWhich salesperson has the highest average deal size?Break down expenses by category and show me which is largestHow does the East region compare to the West region year to date?
Getting charts from questions
Copilot can generate charts directly from plain-language requests without you touching the chart builder:
Create a bar chart showing revenue by product categoryShow me a line chart of monthly sales over the past yearVisualize the top 10 customers by total spend
The chart appears as a suggestion in the pane — click Add to sheet to insert it. You can continue refining it with follow-up prompts ("make it a pie chart instead," "add a title") before inserting.
Asking Copilot to explain your data
One of the most underused features: asking Copilot to explain what's actually in a spreadsheet you didn't build yourself.
Explain what this spreadsheet is trackingWhat do the columns in this table represent?Explain what the formula in column F is doing in plain EnglishWhat would I need to know to work with this data?
Prompt habits that improve results
- Reference column names directly: "the Revenue column" is clearer than "the numbers"
- Be specific about what you want back: "as a chart," "as a written summary," "as a PivotTable"
- Ask one question at a time rather than combining multiple requests — Copilot handles focused prompts more reliably than compound ones
- Use Ctrl+Z to undo anything Copilot inserts that you don't want — it works on everything Copilot adds to the sheet
The bottom line
Data analysis is where Copilot in Excel earns its keep for most users — not formula writing, but the ability to ask questions about a dataset and get immediate, actionable answers without building reports manually. Start with a summary prompt, then drill into specifics based on what surfaces. The more descriptive your column headers, the better the answers.