How to Use Copilot to Analyze Excel Data

Microsoft’s Copilot is changing how we work with Excel, turning complex data analysis into a simple conversation. Instead of memorizing hundreds of formulas, you can now ask your spreadsheet questions in plain English. This article will guide you through exactly how to use Copilot to analyze your Excel data, generate formulas, highlight trends, and create visualizations without being a formula expert.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Copilot for Excel?

Think of Copilot as an AI-powered assistant built directly into Excel. It sits alongside your data and can understand context, recognize patterns, and perform tasks based on your requests. The traditional way of analyzing data involved a steep learning curve of functions like VLOOKUP, SUMIFS, and the infamous nested IF statements. Copilot flattens that curve, allowing you to ask for what you need, and it handles writing the formulas and creating the charts for you.

For marketing teams tracking campaign performance, sales managers monitoring pipelines, or small business owners reviewing financials, this means getting answers from your data in seconds, not hours.

Getting Started with Copilot in Excel

Before you can start analyzing data, you need to make sure your setup is ready. There are a few key requirements to get Copilot up and running in Excel.

Key Requirements:

• A Copilot Subscription: You'll need either a Copilot Pro plan for personal use or a Copilot for Microsoft 365 plan for business users.
• Your file must be in the cloud: Copilot only works on Excel files saved in either OneDrive or a Microsoft SharePoint site. It won’t work on files saved locally on your computer.
• Your data must be in an Excel Table: This is the most important step. Copilot can't analyze a plain range of cells. You must format your data as an official Excel Table first.

How to Format Your Data as an Excel Table

If your data isn't already in a table, it's a quick fix. Simply click any cell within your data range and press Ctrl + T (or Cmd + T on a Mac). A small window will pop up to confirm the data range. Make sure the "My table has headers" box is checked if your data has column titles, then click OK. Your data range will now have alternating colored rows, indicating it’s a formal Excel Table.

Once you've met these criteria, the Copilot button will become active on the "Home" tab of the ribbon. Click it, and a chat pane will open on the right side of your screen, ready for your questions.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Key Copilot Features for Data Analysis

Copilot offers several powerful ways to interact with your data. Let's break down the most common use cases for a typical business analysis task.

1. Ask Questions and Get Quick Insights

The most straightforward feature is simply asking Copilot questions about your data. This is where you can get immediate, high-level summaries without building a PivotTable or writing a single formula.

Imagine you have a sales table with columns for Date, Product, Region, Sales Rep, and Revenue. You could ask:

• "What was the total revenue for the West region?"
• "Which Sales Rep had the most sales in January?"
• "How many units of the 'Pro Widget' were sold?"
• "Show me the top 5 highest revenue transactions."

Copilot will analyze the table and provide a direct answer in the chat pane, often including a button to insert the result directly into a new worksheet.

2. Generate New Formula Columns

This feature alone is a massive time-saver. Instead of manually writing and debugging formulas, you can describe the calculation you want, and Copilot will suggest the correct formula column. It places the proposed column directly in your table with a clear explanation of the formula it used, which you can then confirm.

Using the same sales data, here are some example prompts:

• Prompt: "Create a column for commission that is 10% of the Revenue." Result: Copilot will create a new column named "Commission" and insert the formula =[@Revenue]*0.1 into every row.
• Prompt: "Add a column to show the year from the Date column." Result: It will add a "Year" column with the formula =YEAR([@Date]).
• Prompt: "Make a new column that shows 'Large' if revenue is over $1000 and 'Small' otherwise." Result: It will add a column using a formula like =IF([@Revenue]>1000, "Large", "Small").

3. Highlight, Sort, and Filter Data

Sometimes you don't need a new calculation — you just need to see the important parts of the data you already have. Copilot can apply formatting and filters to make key trends stand out instantly.

Here are some things you could ask:

• "Highlight the top 10% of values in the Revenue column."
• "Bold all rows where the region is 'North'."
• "Sort the table by Revenue from largest to smallest."
• "Show me only transactions that happened in February."

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

4. Create Charts and Visualizations

Turning a wall of numbers into an understandable chart is where data truly becomes valuable. Copilot streamlines this process, letting you request a visualization without touching the chart builder.

Ask Copilot to create common charts like:

• "Create a bar chart showing total revenue by sales rep."
• "Generate a pie chart that breaks down sales by product."
• "Make a line chart showing revenue trends over time using the date column."

Copilot will generate the chart and you can either add it to a new sheet or ask for refinements. If the first chart isn't quite right, you can follow up with prompts like, "Now change it to a column chart" or "Add data labels to the bars."

Practical Walkthrough: Analyzing a Simple Sales Dataset

Let's walk through a real-world scenario. Imagine you have a table named SalesData with the following columns: Date, ProductCategory, Region, UnitsSold, and Revenue.

Let's use Copilot to analyze Q1 performance.

Step 1: Get a High-Level Summary

Click the Copilot button in the ribbon to open the chat pane. First, let's get a feel for the data.

Your Prompt: "What was the total revenue for Q1?" Copilot examines the Date and Revenue columns and returns a simple answer, like "$450,230."

Step 2: Find the Top Performing Category

Now, let's learn which products are selling best.

Your Prompt: "Which product category had the highest revenue?" Copilot will analyze the ProductCategory and Revenue columns, likely giving you the name of the top category and perhaps the total revenue it generated.

Step 3: Drill Down by Region

Let's visualize the regional performance for our top category. Suppose our top category was 'Electronics'.

Your Prompt: "Create a bar chart of revenue by region, but only for the Electronics category." Copilot will first filter the data for 'Electronics' and then generate a bar chart visualizing the revenue breakdown across the different regions. You can click to add this chart to your workbook.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 4: Add a New Insights Column

Let's add a calculated column to see the average revenue per unit for each transaction.

Your Prompt: "Add a new column called 'AvgPrice' calculated by dividing Revenue by UnitsSold." Copilot will suggest the new column with the formula =[@Revenue]/[@UnitsSold] applied to the entire table. You can review the formula in the prompt and click "Insert column" to add it to your table.

Tips for Writing Effective Prompts

The quality of your output depends on the quality of your input. Here are a few tips to get the best results from Copilot:

• Be Specific and Clear: Instead of "show sales," try "Show the sum of 'Revenue' grouped by 'Region'." The more specific you are, the better Copilot can understand your goal.
• Refer to Column Headers: Use the exact names of your column headers in your prompts (e.g., "Sort by 'Sales Rep'"). This helps Copilot locate the right data immediately.
• Work in Steps (Iterate): You don't have to write one perfect, complex prompt. Start with a broad question, then refine your request with follow-up prompts. For example, first ask for "total sales by country," and then follow up with "Now highlight the top 3 countries."
• Double-Check the Results: Copilot is incredibly powerful, but it’s still an AI. After it generates a formula or insight, take a second to review it for accuracy. It's a great assistant, but you are still the analyst in charge.

Final Thoughts

Copilot fundamentally changes the experience of using Excel for data analysis, replacing the need for deep formula knowledge with the ability to ask clear questions. It empowers anyone on a team - from marketers to founders - to pull valuable insights from their data without getting bogged down in technical complexities.

While Copilot is excellent for analyzing data inside a single spreadsheet, most businesses deal with information scattered across various apps. We built Graphed for precisely that challenge. We use a similar conversational AI approach, allowing you to connect all your data sources — like Google Analytics, Shopify, Facebook ads, and Salesforce — in one place. You can ask questions in natural language, just like you would with Copilot, to instantly create live, cross-platform dashboards in seconds and have a real data analyst for brainstorming. This provides a single source of truth for your business performance without the manual reporting headaches.