Copilot in Excel: Analyze Data and Build Formulas

Excel is where data comes alive, and Copilot transforms how you interact with spreadsheets. Instead of memorizing complex formulas or spending hours building pivot tables, you can describe what you need in plain English and let Copilot handle the technical work.

What You'll Learn

By the end of this lesson, you will know how to use Copilot to analyze data, generate formulas, create charts, ask questions about your spreadsheets, and automate common data tasks.

Getting Started with Copilot in Excel

Requirements

Copilot in Excel works best when your data is:

• Formatted as a table. Select your data and press Ctrl + T to convert it to an Excel table. Copilot requires table-formatted data to function properly.
• Clean and structured. Each column should have a clear header, and data should be consistent (no mixed formats in the same column).
• Saved to OneDrive or SharePoint. Copilot in Excel requires your file to be saved to the cloud, not just stored locally.

Opening the Copilot Panel

Click the Copilot icon in the ribbon toolbar. The Copilot panel opens on the right side of your screen with suggested prompts based on your data.

Analyzing Data with Natural Language

The most powerful feature of Copilot in Excel is the ability to ask questions about your data in plain English.

Asking Questions

With your data formatted as a table, try these types of questions:

• "What is the total revenue for Q3?"
• "Which product had the highest sales last month?"
• "What is the average order value by region?"
• "Show me the top 10 customers by total purchases"
• "What percentage of orders were returned?"

Copilot will analyze your data and provide answers, often inserting the results directly into your spreadsheet as new columns or summary tables.

Identifying Trends and Insights

Go beyond simple calculations:

• "What trends do you see in the monthly sales data?"
• "Are there any outliers in the expense data?"
• "Which departments consistently exceed their budget?"
• "How does this quarter compare to the same quarter last year?"

Copilot can identify patterns and provide insights that might take you hours to discover manually.

Generating Formulas

Never struggle with formula syntax again. Describe what you need, and Copilot writes the formula.

Basic Formulas

• "Add a column that calculates the profit margin for each product" (generates a formula like =(Revenue-Cost)/Revenue)
• "Create a column that shows the year-over-year growth percentage"
• "Add a column that categorizes orders as Small (under $100), Medium ($100-$500), or Large (over $500)"

Complex Formulas

Copilot handles advanced formulas that would normally require significant Excel expertise:

• "Calculate a 3-month rolling average of sales for each product"
• "Add a column that ranks employees by performance score within their department"
• "Create a VLOOKUP that matches customer IDs from Sheet2 and pulls in their contact information"
• "Write a formula that calculates business days between the order date and ship date, excluding weekends and holidays listed in the Holidays table"

How It Works

When you request a formula, Copilot:

1. Analyzes your table structure and column names
2. Generates the appropriate formula
3. Shows you the formula with an explanation
4. Lets you preview the results before applying

You can then insert the formula column or modify it. Always verify the results on a few rows to make sure the formula works as expected.

Creating Charts and Visualizations

Copilot can generate charts from your data with simple prompts.

Basic Charts

• "Create a bar chart showing total sales by region"
• "Make a line chart of monthly revenue over the past 12 months"
• "Show a pie chart of market share by product category"

Advanced Visualizations

• "Create a chart comparing this year's monthly sales to last year's"
• "Build a scatter plot showing the relationship between advertising spend and sales"
• "Make a stacked bar chart showing revenue breakdown by product line for each quarter"

Copilot inserts the chart directly into your worksheet. You can then customize colors, labels, and formatting using Excel's standard chart tools.

Data Manipulation Tasks

Copilot can handle common data cleaning and transformation tasks.

Sorting and Filtering

• "Sort the data by revenue in descending order"
• "Filter to show only transactions over $10,000"
• "Show me only the rows where the status is Pending or In Progress"

Data Cleaning

• "Highlight any duplicate entries in the Email column"
• "Find rows where the date format is inconsistent"
• "Identify cells that contain errors or are blank in the Revenue column"

Formatting

• "Apply conditional formatting to highlight cells where profit margin is below 10%"
• "Color-code the Status column: green for Complete, yellow for In Progress, red for Overdue"
• "Bold the top 5 values in the Sales column"

Pivot Table Assistance

Copilot simplifies the creation of pivot tables:

• "Create a pivot table showing total sales by region and product category"
• "Build a pivot table that summarizes average order value by month and customer segment"
• "Make a pivot table showing the count of support tickets by priority and department"

Real-World Excel Workflows

Monthly Sales Report

1. "Summarize total sales, average order value, and number of transactions by product category"
2. "Compare these numbers to last month and calculate the percentage change"
3. "Create a bar chart of the top 10 products by revenue"
4. "Highlight any products where sales decreased more than 15%"

Budget Tracking

1. "Add a column calculating the variance between budgeted and actual spending"
2. "Flag any departments that are more than 10% over budget"
3. "Create a chart showing budget vs. actual by department"
4. "What is the total projected overspend for the year based on current trends?"

HR Headcount Analysis

1. "Calculate the average tenure by department"
2. "Show the distribution of employees by salary band"
3. "Which departments have the highest turnover rate?"
4. "Create a chart showing hiring trends over the past 12 months"

Tips for Better Results in Excel

Name your columns clearly. Copilot uses column headers to understand your data. "Revenue" works much better than "Col_F" or "Data1."

Convert data to tables first. Always use Ctrl + T to format your data as an Excel table before using Copilot.

Verify formula results. Spot-check a few rows manually to confirm Copilot's formulas are calculating correctly.

Be specific about calculations. Instead of "analyze this data," say "calculate the month-over-month growth rate for the Revenue column and add it as a new column."

Key Takeaways

• Copilot in Excel requires your data to be formatted as a table (Ctrl + T) and saved to OneDrive or SharePoint.
• Ask questions about your data in plain English to get instant analysis, from simple totals to trend identification.
• Describe the formula you need in words, and Copilot generates the correct Excel formula with an explanation.
• Copilot can create charts, pivot tables, and conditional formatting from natural language descriptions.
• Always verify formula results and spot-check calculations before relying on them for business decisions.