Analyze data with context and precision

  • 5 minutes

When you open a workbook full of raw data, the first challenge is knowing where to start. Copilot helps you move from raw data to insight more quickly by highlighting what matters, answering questions about your data, and bringing in more context when you need it.

Prepare your data for Copilot actions

Before Copilot can take actions like sorting, filtering, highlighting, or generating formulas, your data needs to meet two basic requirements:

  • Format your data as a table or a clean range (single header row, no merged cells, no blank rows or columns, no subtotals)
  • Save your file to OneDrive or SharePoint

If the Copilot button isn't available, save your file to the cloud first.

Note

Formatting your data as a table unlocks the full set of Copilot actions. You can still ask questions about any selected range without full table formatting. See Get started with Copilot in Excel for supported range details.

Surface key patterns with sorting, filtering, and highlighting

Once your data is ready, you can describe what you want to see. Copilot translates your request into actions like filters, sorting, or conditional formatting.

Representative prompts:

  • Identify and highlight any expenses that exceed the budget in this financial statement.
  • Bold the top 10 values in the Profit column.
  • Sort the project tasks by due date, from earliest to latest.
  • Filter to show only deals that closed in Q2 with a value above $50,000.

Multi-turn prompting works well here. For example:

  • Highlight the highest value in this column
  • Now sort those rows by value, largest to smallest

This process lets you refine your view step by step as your analysis evolves.

Ask Copilot about any part of your sheet

You don't need perfectly structured data to start exploring. When you select any area of your worksheet, you can ask Copilot to analyze it directly. This analysis is useful when working with data that hasn't been cleaned or formatted yet, or when you need to extract insights from a notes section or summary block that lives outside your main table. Use prompts like:

  • What are the key takeaways from this section?
  • Are there any inconsistencies in this data?
  • Summarize the items in the selected range as three bullet points.

Some features like formula generation and PivotTables still require structured tables. But you can begin exploring your data immediately, without reformatting first.

Copilot can also use the context of your prompt to identify the relevant data, even if it's located on another sheet in the same workbook.

Enrich your analysis with internal and external data

Copilot can bring more context into your workbook so you don't have to switch between tools.

Depending on your organization's setup and licensing, Copilot may use different data sources behind the scenes, or give you options to choose where results come from.

Use organizational data for business context

Copilot can use your organization's Microsoft 365 data, including emails, meetings, chats, and files, to enrich your analysis when your workbooks depend on business context:

  • List yesterday's emails about the Henderson account in a table with sender, subject, and action items.
  • Find the budget figures discussed in last week's finance meeting and add them to this table.
  • Get the latest project status updates from the team SharePoint site and summarize them by owner.

Use web data for external insights

Copilot can also bring in information from the web to supplement your analysis with up-to-date or external data. Use this option when you want to add benchmarks or reference information:

  • Find the current exchange rates for EUR, GBP, and JPY and add them to this table.
  • List the top five publicly traded competitors in enterprise SaaS and their estimated market cap.

Note

The ability to use organizational data or web data depends on your Microsoft 365 Copilot setup and your organization's configuration. In some experiences, Copilot may automatically determine the best data source for your request.

By combining internal data, business context, and external benchmarks, you can build richer, more complete analyses without leaving Excel.