How to Use Copilot in Microsoft Excel to Generate Formulas, Charts, and Data Analysis

Microsoft Copilot in Excel brings generative AI directly into your spreadsheets, allowing you to analyse data, generate formulas, create charts, identify trends, and ask natural-language questions about your workbook — all without leaving the Excel interface. Powered by large language models integrated through the Microsoft 365 Copilot platform, this feature transforms Excel from a manual calculation tool into an intelligent analytical assistant.

This guide covers how to enable and use Copilot in Excel, the types of tasks it can perform, how to write effective prompts for spreadsheet analysis, its current limitations, and best practices for getting the most accurate and useful results.

Requirements for Using Copilot in Excel

Before you can use Copilot in Excel, you need:

• Microsoft 365 subscription — Copilot in Excel requires a Microsoft 365 Personal, Family, or Business subscription with the Copilot add-on, or a Microsoft 365 Copilot enterprise licence.
• Excel for Microsoft 365 — Copilot is available in the desktop app (Windows and macOS), Excel for the web, and the mobile apps. You must be on the Current Channel or Monthly Enterprise Channel to access the latest features.
• Data in an Excel Table — Copilot works best (and some features require) your data to be formatted as an Excel Table (Insert > Table). Copilot may prompt you to convert your data range to a table if it is not already formatted as one.
• AutoSave enabled — your workbook must be saved to OneDrive or SharePoint with AutoSave turned on for Copilot to function.

How to Open Copilot in Excel

1. Open a workbook in Excel for Microsoft 365.
2. Ensure your data is formatted as an Excel Table and the file is saved to OneDrive or SharePoint with AutoSave on.
3. Click the Copilot button in the Home tab of the ribbon, or press the keyboard shortcut Alt + I on Windows.
4. The Copilot side panel opens on the right side of the screen, ready to accept your prompts.

What Copilot Can Do in Excel

Analyse and Summarise Data

Copilot can examine your dataset and provide instant insights:

• “What are the key trends in this data?”
• “Summarise the sales performance by region.”
• “Which product category has the highest average profit margin?”
• “Are there any outliers in the revenue column?”

Copilot analyses the data in your table and returns a natural-language summary, often accompanied by charts or highlighted cells to illustrate its findings.

Generate Formulas

One of Copilot’s most practical capabilities is generating Excel formulas from plain-English descriptions:

• “Add a column that calculates the profit margin as a percentage of revenue.”
• “Create a formula that counts the number of orders above £500.”
• “Add a column that categorises each sale as ‘High’, ‘Medium’, or ‘Low’ based on the total amount.”
• “Calculate the year-over-year growth rate for each month.”

Copilot generates the formula, explains what it does, and offers to insert it into your spreadsheet. You can review the formula before accepting it, and Copilot applies it to the entire table column automatically.

Create Charts and Visualisations

Copilot can suggest and create charts based on your data:

• “Create a bar chart showing sales by category.”
• “Show me a line chart of monthly revenue over the past year.”
• “Generate a pie chart showing the distribution of expenses by department.”

The generated chart is inserted into a new sheet or alongside your data, and you can customise it further using Excel’s standard chart editing tools.

Sort and Filter Data

• “Sort this table by total sales in descending order.”
• “Filter to show only orders from the UK placed in Q3 2025.”
• “Highlight all rows where the delivery was late.”

Add Conditional Formatting

• “Highlight cells in the profit column that are negative.”
• “Apply a colour scale to the revenue column from red (lowest) to green (highest).”
• “Bold all rows where the status is ‘Overdue’.”

Ask Questions About Your Data

Copilot excels at answering ad-hoc questions without requiring you to build formulas or pivot tables manually:

• “What was the total revenue in March?”
• “How many customers placed more than three orders?”
• “What is the average order value for each sales representative?”
• “Which month had the highest return rate?”

Writing Effective Prompts for Copilot in Excel

Be Specific About Columns and Data

Reference your actual column names to avoid ambiguity:

• Weak: “Show me the totals.”
• Strong: “Calculate the sum of the ‘Revenue’ column grouped by ‘Region’.”

Specify the Desired Output

• “Add a new column called ‘Tax’ that calculates 20% of the ‘Subtotal’ column.”
• “Create a pivot-style summary showing total ‘Units Sold’ by ‘Product Category’ and ‘Quarter’.”

Use Follow-Up Prompts for Refinement

Copilot maintains context within a session, so you can iterate:

1. “Show me monthly revenue trends.” (Copilot creates a chart)
2. “Now add a trend line to that chart.”
3. “Change the chart type to a stacked bar chart.”

Request Explanations

If Copilot generates a complex formula, ask it to explain:

• “Explain what this formula does step by step.”
• “Why did you use XLOOKUP instead of VLOOKUP here?”

Working with Copilot’s Formula Suggestions

When Copilot suggests a formula, it presents it in a preview pane with:

• The formula text itself.
• A plain-English explanation of what the formula calculates.
• A preview of the results it will produce.
• An Insert Column button to apply the formula to your table.

Always review the formula and its preview before inserting. Copilot’s suggestions are generally accurate, but edge cases — particularly those involving dates, currency formatting, or nested logic — should be verified manually.

Limitations and Considerations

• Table format required — Copilot requires your data to be in an Excel Table. Data in plain ranges may not be fully accessible to Copilot’s analysis engine.
• AutoSave requirement — files must be saved to OneDrive or SharePoint. Local-only workbooks cannot use Copilot.
• Data size limits — Copilot may struggle with very large datasets (hundreds of thousands of rows). For best performance, keep your tables focused and consider summarising data before asking Copilot to analyse it.
• No VBA or macro generation — Copilot currently generates formulas and chart configurations, not VBA macros or scripts.
• Accuracy — while Copilot’s formula generation is generally reliable, always verify results against known data points before using them in reports or decision-making.
• Language support — Copilot works best with English prompts, though it supports other languages with varying levels of accuracy.
• Sensitive data — be aware that your prompts and data context are processed by Microsoft’s AI services. Review your organisation’s data governance policies before using Copilot with confidential data.

Advanced Use Cases

Scenario Analysis

“What would total profit look like if we increased prices by 10% while losing 5% of unit sales?”

Copilot can create what-if scenarios by generating calculated columns that model the impact of hypothetical changes on your existing data.

Data Cleaning

• “Identify duplicate rows based on the ‘Email’ column.”
• “Flag cells in the ‘Phone’ column that do not match a valid UK phone number format.”
• “Add a column that extracts the domain name from each email address.”

Time Series Preparation

• “Add columns for Year, Month, and Quarter extracted from the ‘Order Date’ column.”
• “Calculate the rolling 3-month average of ‘Revenue’.”

Copilot in Excel represents a fundamental shift in how spreadsheet analysis is performed. By combining the precision of Excel’s formula engine with the natural-language understanding of modern AI, it enables both experienced analysts and occasional spreadsheet users to extract deeper insights from their data with significantly less effort.