Exploring Copilot Features 🚀 | Complete Guide to using Copilot in Excel

If you have ever stared at a blank spreadsheet wondering which formula to use or how to summarise 10,000 rows without losing a weekend, Copilot in Excel changes that conversation entirely. I will walk you through exactly how to enable it, generate formulas in plain English, build visual reports, and turn Excel into an AI-powered analyst that works alongside you.

Direct Answer: Copilot in Excel is Microsoft's built-in AI assistant that lets you analyse data, generate formulas, create charts, and summarise tables using natural-language prompts. To use it, your workbook must be saved to OneDrive or SharePoint with AutoSave on, your data must be formatted as an Excel Table (Ctrl+T), and you need a Microsoft 365 Copilot licence. Once active, the Copilot icon appears on the Home ribbon and accepts prompts like "highlight rows where revenue exceeds 50,000" or "add a column showing month-over-month growth."

What Copilot in Excel Actually Does

As someone who teaches automation to over 79,000 students across 74+ courses, I see the same pattern repeatedly: most Excel users only use 5% of what the tool can do. Copilot collapses that learning curve. It does four core jobs — formula generation, data analysis, chart creation, and table transformation — all triggered by typing what you want in plain English.

Coming from a Chartered Accountant background, the feature I rely on most is the analysis pane. Ask "what insights can you find in this data?" and Copilot returns trend summaries, outliers, and correlations within seconds — the kind of work that used to take 45 minutes of pivot-table wrangling.

How to Enable Copilot in Excel

Before Copilot will appear, four conditions must be met. Skip any one and the button stays greyed out:

• Microsoft 365 Copilot licence — this is a paid add-on at $30/user/month, separate from your standard 365 subscription.
• Save to OneDrive or SharePoint — local files cannot use Copilot. AutoSave must be toggled ON in the top-left corner.
• Format data as a Table — select your range and press Ctrl+T. Copilot reads structured tables, not loose ranges.
• Supported version — Excel for Microsoft 365 on Windows, Mac, or web. Excel 2021 standalone does not work.

Once these are in place, click the Copilot icon on the Home ribbon (far right) and a side pane opens with suggested prompts based on your data.

Generating Formulas with Plain English

This is where most people first see the magic. Instead of remembering whether it's XLOOKUP, INDEX/MATCH, or SUMIFS, you describe the outcome:

• "Add a column that calculates 18% VAT on the price column" — Copilot writes =[@Price]*0.18.
• "Add a column showing 'High' if sales exceed 10,000, else 'Low'" — Copilot returns =IF([@Sales]>10000,"High","Low").
• "Create a column matching customer names from the Customers table" — Copilot generates the full XLOOKUP with arguments correctly ordered.

Copilot shows the formula before inserting it, so you can verify the logic. I always recommend students click "Explain" to see a plain-English breakdown — that is how you actually learn Excel rather than just accepting AI output.

Creating Visual Reports and Charts

Type "create a chart showing revenue by region" and Copilot delivers a recommended chart type with formatting. From experience, Copilot defaults to clustered column charts; if you want a different visualisation, specify it: "create a stacked bar chart" or "create a line chart with year on the x-axis."

For dashboards, the strongest prompt is "summarise this data as a PivotTable showing total sales by category and quarter." Copilot builds the pivot, inserts it on a new sheet, and gives you a starting point you can refine manually. This single prompt replaces what used to take me 12-15 clicks across the Insert ribbon.

Data Analysis and Insights

The analysis feature is Copilot's most under-used function. Open the Copilot pane and click Analyze or type "show insights about this data." You will get:

• Automatic trend detection — "Sales in Q3 grew 23% over Q2"
• Outlier flags — "3 transactions exceed 5x the median value"
• Correlation suggestions — "Marketing spend strongly correlates with new customer acquisition (r=0.84)"
• Pre-built PivotTable suggestions with the relationships already mapped

For accountants and analysts, this is genuinely time-saving. I use it as a first-pass review on any client dataset before diving deeper.

Cleaning and Transforming Tables

Messy data is the real enemy of productivity. Copilot handles the cleanup work most people dread:

• "Split the Full Name column into First and Last Name" — done in one prompt.
• "Remove duplicate rows based on Email column" — Copilot deduplicates and reports how many rows it removed.
• "Highlight rows where status equals 'Overdue' in red" — conditional formatting applied instantly.
• "Sort by revenue descending and filter to top 20 customers" — multi-step actions chained in one request.

The Limits You Should Know About

Copilot in Excel is powerful but not perfect. It struggles with very large datasets (over 2 million cells), it cannot run macros or VBA, and it occasionally mis-reads merged cells or unstructured ranges — which is why the Ctrl+T table requirement matters. It also will not connect to external databases through Power Query on its own; you still set up the connection manually.

One more thing: Copilot does not always pick the optimal formula. Always click "Explain" and verify the logic, especially on financial calculations where a wrong nested IF can quietly distort an entire model.

Closing Thought

Copilot in Excel turns natural-language prompts into formulas, charts, pivot tables, and data insights — collapsing hours of manual work into minutes. Your specific next step: open any spreadsheet, save it to OneDrive, format your data as a table with Ctrl+T, and ask Copilot "what insights can you find in this data?" — that single prompt will show you more about your numbers than an hour of manual analysis ever did.

Keep Learning

If this was useful, these are worth reading next:

• How to Increase your Excel Skills with ChatGPT (10x Productivity 😉)
• TOP MOST IMPORTANT Excel FUNCTIONS | With ChatGPT | Free Tutorial
• Or go further with the AI Mastery Course — used by 79,000+ students across 150+ countries.

Source: Vendor public pricing pages as of May 2026 — Microsoft 365 Copilot, Google Workspace, OpenAI, Numerous.ai.