How to Analyze Data in Excel with Microsoft Copilot

João Barros 05 de July de 2026 4 min read

Microsoft Copilot in Excel changes how we explore data: instead of mastering complex formulas or building PivotTables by hand, you describe what you need in plain English and the assistant returns insights, creates formula columns and suggests charts. It is a real help for anyone who works with spreadsheets every day but doesn't consider themselves an Excel expert. The result is more time to interpret the numbers and less time fighting with syntax. Let's see how to analyze data in Excel with Copilot, from scratch.

Prerequisites

An active Microsoft 365 Copilot license — Copilot in Excel is a paid add-on and is not part of base Excel.
Excel for the web or desktop Excel (Microsoft 365), signed in with your work or school account.
The file saved to OneDrive or SharePoint, with AutoSave turned on.
Your data organized as a table, with clear column headers (for example: Date, Region, Product, Sales, Cost).

Step 1: Organize your data as a table

Copilot works much better when your data is formatted as an Excel table. Click a cell inside the data and press Ctrl+T (or go to Home > Format as Table). Make sure "My table has headers" is checked and that every column has a descriptive name. Clear headers help Copilot understand what each column means.

Step 2: Save to OneDrive and turn on AutoSave

Copilot in Excel only analyzes files stored in the cloud. Go to File > Save a Copy (or Save As) and choose a folder in OneDrive or SharePoint. Then turn on AutoSave in the top-left corner of the window. Without this step, the Copilot button appears greyed out.

Step 3: Open the Copilot pane

Click any cell inside your table and, on the Home tab, select the Copilot button (alternatively, use the Copilot icon in the lower-right corner). A pane opens on the right, ready for natural-language requests. The first time, it may take a few seconds to load.

Step 4: Ask for insights in natural language

Type a question about your data. The more specific your request, the better the answer. Copilot can return results as summaries, trends, outliers, charts or PivotTables — and insert them into a new sheet with one click. Try prompts like:

What trends are there in sales by region over time?
Show the top 5 products by total sales in a PivotTable.
Which region has the lowest average margin?

Tip: clean data with no blank rows and consistent types gives noticeably better answers.

Step 5: Create a formula column

Copilot also writes formulas for you and explains what each one does. For example, ask for a column that calculates the margin of each row:

Add a "Margin" column with (Sales - Cost) divided by Sales, as a percentage.

Copilot proposes the new column and shows the formula it will use, with the table's structured references:

= ([@Sales] - [@Cost]) / [@Sales]

Review the suggestion and click Insert column to apply it. If something is off, adjust your request and try again.

Step 6: Refine your prompt

For more useful answers, name the columns and the format you want. Compare "analyze sales" with a precise request:

Create a bar chart of Sales by Month, sorted from highest to lowest.

Naming columns and asking for a specific format (chart, PivotTable, list) cuts down the back-and-forth and gives you exactly what you're after.

Check the result

Confirm the new column appears with correct values and pick one row to validate the formula by hand. For PivotTables and charts, check that the totals match the source data. If Copilot replies that it can't analyze, one of three things is usually missing: the data isn't formatted as a table, headers are absent, or AutoSave is off.

Conclusion

In just a few steps, you went from a raw sheet to insights, calculated columns and charts — without writing a single formula by hand. From here, explore text insights, automatic conditional formatting, or combine your work with Copilot in Power BI for richer reports. Tip: keep the prompts that work well in a notes file; reusing them saves a lot of time. What's the first report you'll let Copilot build for you?