How to Use Copilot in Excel: A Guide to AI-Powered Spreadsheets

If you work with data analysis in any capacity — tracking budgets, managing inventory, analyzing sales — you already know that spreadsheets are central to the job. They’re also pretty tricky to master, but that’s where Copilot in Excel comes in. 

This guide walks you through what Copilot in Excel can do, how to set it up, and how to write prompts that actually get results. You’ll also learn about Agent Mode, current limitations, and where to go for hands-on practice.

What is Copilot in Excel?

Copilot in Excel is an AI assistant built into Microsoft 365 that lets you analyze, transform, and visualize spreadsheet data using everyday language. Instead of memorizing formula syntax or spending hours cleaning messy columns, you type what you need and Copilot does the heavy lifting. Whether you’re building a pivot table or untangling a spreadsheet you inherited from a colleague, Copilot can help you work faster.

Think of it as a skill accelerator: you bring the context and judgment, and Copilot handles the repetitive syntax and pattern-matching. 

At a high level, Copilot in Excel can:

• Generate formulas from plain-language descriptions
• Create charts and pivot tables
• Clean and standardize messy data
• Summarize trends across large datasets
• Explain existing formulas in plain language
• Apply conditional formatting rules

Copilot in Excel is part of the broader Microsoft Copilot ecosystem, which extends across Word, PowerPoint, Teams, and other Microsoft 365 apps. If you want the full picture of what Copilot can do beyond Excel, the complete guide to Microsoft Copilot on the Udemy blog covers the wider landscape.

How to get Copilot in Excel

Before you can start prompting, you need the right subscription and file setup. Here’s what to check.¹

Subscription options

Copilot is included with Microsoft 365 Personal ($9.99/month) and Family ($12.99/month) plans, which provide a monthly allocation of Copilot credits. If you use Copilot heavily, Microsoft 365 Premium ($19.99/month) adds priority access during peak times and a larger credit allowance.²

One thing to keep in mind: These plans come with a monthly AI-credit allowance, so if you use Copilot heavily in Excel you can actually run through your credits.  

For most learners getting started, the standard Microsoft 365 Personal plan is enough to explore what Copilot can do.

Technical requirements

Copilot in Excel needs a few conditions to work properly:

• File format: Your workbook must be saved as .xlsx (not the older .xls or .csv formats).
• Cloud storage: The file must be saved to OneDrive or SharePoint with AutoSave turned on. Copilot can’t access local-only files.
• Excel table format: It is recommended to format your data as a table (Ctrl + T or Cmd + T on Mac) because Copilot leans on their headers and boundaries, but they aren’t mandatory. 

How to access Copilot

Once your file meets the requirements, open it in Excel and look for the Copilot icon in the Home tab of the ribbon. Click it to open the Copilot sidebar, where you can type your prompts and see results.

Platform availability

Copilot works across Excel for desktop (Windows and Mac), the Excel web app (most core features available), and mobile (limited feature set). For the most complete experience, use the desktop version.

What Copilot can do in Excel

Here’s where Copilot gets practical. Each capability below includes an example prompt you can adapt for your own data.

Clean up messy data

Messy data is one of the biggest time drains in spreadsheet work. Copilot can standardize formats, remove duplicates, fill gaps, and fix inconsistencies across your columns.

Scenario: You have a contact list where dates are written in three different formats, names are inconsistently capitalized, and several rows have blank phone numbers.

Try this prompt: “Standardize the date format in column C to MM/DD/YYYY and capitalize all names in column A.”

Instead of writing a PROPER() formula for one column and a TEXT() formula for another, you describe the outcome and let Copilot handle the syntax.

Generate formulas from descriptions

This is where Copilot saves the most time for people who know what they want to calculate but don’t remember the exact formula syntax. Describe the logic in plain language, and Copilot writes the formula for you.

Try this prompt: “Create a formula that calculates the commission for each salesperson: 10% for sales above $50,000, 7% for sales between $25,000 and $50,000, and 5% for sales below $25,000.”

Honestly, this is the feature I reach for most. I describe the commission logic in plain English, and Copilot writes the nested IF formula in seconds, whereas doing it by hand used to take five minutes of debugging. 

It’s especially useful for complex functions like INDEX/MATCH, nested IFs, and array formulas.

Create charts and visualizations

Rather than clicking through chart menus and adjusting settings, you can describe the visualization you want.

Try this prompt: “Create a bar chart comparing quarterly revenue by region.”

Copilot selects the appropriate chart type and populates it from your data. You can then refine it with follow-up prompts, such as “Change the bar colors to blue” or “Add data labels to each bar”, without navigating the formatting menus.

Summarize data and spot trends

For large datasets, Copilot can analyze patterns and surface insights that you might miss during manual review.

Try this prompt: “What are the top three trends in this sales data over the past 12 months?”

Copilot scans the dataset and returns a plain-language summary of the most significant patterns, which is useful for preparing reports or identifying areas that need attention.

Build pivot tables

Pivot tables are powerful but intimidating for many users. Copilot lets you describe the analysis you want, and it builds the pivot table for you.

Try this prompt: “Create a pivot table showing total sales by product category and quarter.”

You get a structured view of your data without dragging fields into rows, columns, and values manually.

Explain formulas

Inherited a spreadsheet full of complex formulas? Ask Copilot to break them down.

Try this prompt: “Explain what the formula in cell D2 does in plain language.”

This is particularly valuable when you’re onboarding into a new role or collaborating on a shared workbook where the original creator isn’t available to explain their logic.

Apply conditional formatting

Conditional formatting helps you spot patterns visually, but setting up the rules can be fiddly. Copilot simplifies the process.

Try this prompt: “Highlight all cells in column E where the value is below the average.”

Each of these capabilities represents a real task you’d face in day-to-day work. The more you practice describing what you need, the faster Copilot delivers useful results. 

How to write better Copilot prompts in Excel

The single biggest factor in getting good results from Copilot is how you write your prompts. Vague requests produce vague results. Specific, context-rich prompts produce outputs you can actually use.

Here’s what the difference looks like in practice:

Request Type	Vague Prompt	Specific Prompt	Why the Specific Version Is Better
Data Analysis	“Analyze my data.”	“Compare Q1 vs. Q2 sales for each region and highlight any region where sales dropped more than 10%.”	Defines exactly what to compare (Q1 vs. Q2 sales), the level of detail (by region), and the desired insight (sales drops greater than 10%).
Data Visualization	“Make a chart.”	“Create a line chart showing monthly revenue trends for 2026, with a separate line for each product category.”	Specifies the chart type (line chart), metric (revenue), time period (2026), granularity (monthly), and grouping (product category).

The specific versions tell Copilot exactly what data to reference, what comparison to make, and what output format to use. Here are four principles that make prompts more effective:

1. Name your columns. Reference specific column headers rather than saying “the data.” Copilot maps your prompt to the table structure, so column names give it precision.
2. State the output format. Tell Copilot whether you want a formula, a chart, a new column, or a summary. This prevents it from guessing what kind of result you’re looking for.
3. Include conditions. Spell out thresholds, exceptions, and filters. “Sales above $50,000” is more useful than “high sales.”
4. Iterate. Use follow-up prompts to refine results rather than trying to get everything right in one shot. Copilot retains context within a conversation, so you can build on previous outputs.

That collaborative mindset applies directly to Copilot in Excel. The more context you provide — and the more willing you are to iterate — the better your results will be. If you want to build this skill more formally, prompt engineering courses cover the techniques in depth.

What is Editing with Copilot in Excel?

Previously marketed as Agent Mode, Editing with Copilot in Excel lets you build and edit workbooks side by side with Copilot. When updating budgets, creating financial models, or analyzing data, Copilot draws on Excel’s built-in tools — tables, charts, PivotTables, and formulas — to help get the job done. Crucially, your content stays editable and you remain in control of everything that gets modified.³

It’s particularly well-suited for complex, multi-step tasks like reshaping data, merging sheets, or building reports with multiple elements.

After you submit a prompt, Copilot analyzes your task and creates a step-by-step plan, works directly in your document, reviews the results, and evaluates whether the outcome matches your intent, all while you can watch its reasoning in the pane in real time.

Example: Instead of sending three separate prompts — clean this dataset, create a summary pivot table, and build a chart of the top five products by revenue — you give Agent Mode the full brief in one prompt. It works through each step sequentially and delivers the final result.

Current limitations of Copilot in Excel

Being honest about what Copilot can’t do saves you time and frustration. Here are the boundaries to keep in mind:

• Table format required. Copilot works best with structured Excel tables. Raw data ranges without table formatting will produce unreliable results or no results at all.
• Cloud storage only. Your file must be saved to OneDrive or SharePoint with AutoSave enabled. Local-only files aren’t accessible to Copilot.
• Accuracy isn’t guaranteed. Copilot can produce incorrect formulas or misinterpret complex requests. My rule is, “Never trust, always verify”. I name the exact columns, spell out every threshold, and check the output against one row I’ve worked out by hand. That catches mistakes before they matter. 
• Large dataset performance. Very large tables may slow Copilot’s response time or exceed its processing limits. Clear column headers and focused data ranges help.
• Feature parity across platforms. The desktop version of Excel offers the most complete Copilot experience. Web and mobile versions have a reduced feature set.
• Language support. Copilot performs best in English. Prompts in other languages may produce less accurate results.

Knowing these boundaries doesn’t diminish what Copilot can do, but helps you prompt smarter and catch issues before they become problems. If you understand where Copilot might stumble, you can verify its work more efficiently.

Copilot in Excel vs. traditional Excel skills

A question that comes up often: if Copilot can write formulas and build charts, do you still need to learn Excel the traditional way?

The short answer is yes. Copilot makes you faster, but Excel fundamentals make your prompts more effective. If you understand what a pivot table is and how it works, you can prompt for better ones. If you know the logic behind VLOOKUP or INDEX/MATCH, you can verify whether Copilot’s formula suggestions are correct.

After teaching this to hundreds of thousands of learners, I’ve found that the ones who get the most out of Copilot are always the ones who understand the fundamentals behind it. 

Research from the Nielsen Norman Group found that employees using AI tools saw productivity improvements averaging 66% across several studies of AI-assisted work, but the gains were highest among those who already understood the underlying task.⁴ The same principle applies here: the more you know about spreadsheets, the more value you get from Copilot.

Think of it as skill stacking: AI literacy combined with spreadsheet fundamentals gives you a stronger professional toolkit than either skill alone. Copilot handles the syntax and repetition. You bring judgment, context, and quality control.

That combination — understanding the tool and knowing how to direct the AI — is what makes Copilot users more productive, not the AI by itself.

How to learn Copilot in Excel with Udemy

This guide covers the fundamentals, but hands-on practice is where real skill-building happens. A structured course lets you work through exercises, get feedback, and build confidence with guided projects.

Ready to boost your productivity with AI? Enroll in Mastering Microsoft 365 Copilot and AI Agents (2026) to learn how to work smarter in Excel and across the Microsoft 365 suite. If you’re ready to build your own AI-powered solutions, check out The Complete Microsoft Copilot Studio & AI Agents Bootcamp.

Where you take your Copilot skills is up to you, but we can help you get started.