How to Use Copilot in Excel: Setup, Prompts, and Limits

How to use Copilot in Excel is now a real workflow question, not a novelty question. Deckary, the agentic AI Excel add-in built for consultants and finance teams, is still the better fit for workbook-wide jobs, but Microsoft Copilot is now good enough for formulas, summaries, charts, PivotTables, and light edits inside native Excel.

For most Microsoft 365 users, we recommend Copilot as the first tool to try for one-table tasks. For multi-sheet cleanup, model builds, and high-risk workbook edits, we recommend Deckary because it follows a read workbook -> plan steps -> write formulas or sheets -> verify output -> ask before destructive edits loop.

For this guide, we reviewed the top 5 current Google results for "how to use copilot in excel" on May 9, 2026, then checked 7 current Microsoft support, FAQ, and pricing pages plus 2 external research sources. The ranking pages mostly explain where to click. The gaps are setup requirements, prompt quality, edit mode behavior, and where Copilot stops being enough for real analyst work.

How to Use Copilot in Excel: Setup Checklist#

How to use Copilot in Excel starts with file setup, not prompts. Microsoft's own support pages make that clear, and this is where most tutorial posts stay too vague.

Before you open the Copilot pane, check these five requirements:

Microsoft's Get started with Copilot in Excel page says to format your data in a table or supported range, then open the Copilot icon from the Home tab or the sparkle icon beside a selected cell. Microsoft's FAQ for Copilot in Excel adds the practical constraints: the file must live in OneDrive or SharePoint with AutoSave turned on, and the more specific your prompt is about table names, ranges, and column headers, the better the result.

If your Copilot button is missing, do not start by reinstalling Excel. Microsoft says the common causes are missing eligibility, unsupported file state, or organization settings. If you see "Unsupported file state," Microsoft's FAQ says a checked-out SharePoint file is one likely cause.

How to Use Copilot in Excel: The Prompt Pattern That Works#

How to use Copilot in Excel well is mostly a prompting problem. The best current Microsoft guidance is simple: be explicit about the data range, the column names, and the output you want.

This prompt pattern works better than vague requests:

1. Name the table, range, or columns.
2. State the action.
3. State the output format.
4. Add any rule or threshold.

Microsoft's FAQ says Copilot can use Python for deeper analysis when you ask for it, but most everyday Excel work does not need that. Start with a direct prompt tied to a clean table. If Copilot misses, tighten the request before you assume the feature is broken.

One useful way to think about it: Copilot is stronger at "do this to this table" than "figure out this whole workbook." That difference matters later when you hit multi-sheet work.

How to Use Copilot in Excel for Formulas#

Copilot in Excel is strongest on bounded formula work.

Microsoft's support docs say Copilot can create and explain formulas, generate formula columns, and help you understand why a result changed. That is enough for a lot of daily analyst work:

• Lookup formulas such as XLOOKUP, INDEX/MATCH, or SUMIFS
• Margin, growth, and variance helper columns
• Formula explanations for inherited sheets
• First-pass rewrites of broken nested IF logic

Here are prompt examples worth copying:

This is also the point where you should stay realistic. Microsoft's FAQ says Copilot output can be inaccurate and must be reviewed. Raymond Panko's 2008 paper Spreadsheet Errors: What We Know. What We Think We Can Do is still the right warning here: spreadsheet errors are common enough that no serious finance team should accept AI-written formulas without checking them.

If formula help is your main use case, also read Microsoft Copilot for Excel and Excel AI. Those posts cover where Copilot fits against broader Excel AI tools.

How to Use Copilot in Excel for Tables, Summaries, Charts, and PivotTables#

Copilot in Excel is also good at turning one table into a quick answer.

Microsoft's insights page says Copilot can return charts, PivotTables, summaries, trends, and outliers from a supported table. Microsoft's chart help page says Copilot currently supports over twelve chart types, though not every chart type in Excel.

That makes Copilot useful for:

Prompt examples:

• "Summarize the biggest month-over-month changes in Bookings and call out any outliers above 15%."
• "Create a line chart showing monthly recurring revenue over time, add data labels, and place it on a new sheet."
• "Build a PivotTable that shows total spend by vendor and quarter."
• "Highlight rows where Forecast Variance % is above 10% and sort the table descending."

This is where the current SERP intent is clear. The ranking pages are mostly beginner tutorials because most searchers want practical output, not product theory. If that is you, focus on one clean table and one clear deliverable.

How to Use Edit with Copilot for Multi-Step Work#

Microsoft now treats editing as part of the main Copilot experience in Excel, and this is the most important change many older tutorials miss.

Microsoft's Edit with Copilot in Excel page says editing is on by default when you open Copilot. After you submit a prompt, Copilot creates a step-by-step plan, works directly in the workbook, reviews the result, and evaluates whether the outcome matches your intent. You can also switch to chat-only mode if you want help without workbook changes.

Editing mode is the right choice for jobs like:

• Reshaping a table
• Creating a report sheet
• Merging sheets
• Applying formatting rules across a range
• Building a simple budget or tracker from a prompt

Use prompts like:

• "Create a monthly budget tracker with categories, conditional formatting, and a donut chart on a new sheet."
• "Merge the North and South sheets into one normalized table, then create a summary by product line."
• "Build a loan schedule with payment, principal, interest, and remaining balance columns."

The limit is scope. Microsoft's FAQ for editing says Copilot works with the currently open workbook and does not currently support work search or integration with external data sources in that editing flow. It also says changes are saved automatically and recommends working on a copy of any critical or sensitive workbook.

For analysts, that changes the risk calculation. Copilot is no longer just a side chat, but it is still not a full workbook control layer. If you want a tool built around multi-step workbook execution with overwrite checks, see AI for Excel and Deckary's AI Excel Agent page.

When Copilot in Excel Is Not Enough#

Copilot in Excel is not the right answer for every workbook.

Microsoft's own FAQ says to avoid relying on Copilot for sensitive areas such as finance, legal, or medical decisions without review. That warning matters. The 2023 paper InstructExcel: A Benchmark for Natural Language Instruction in Excel includes more than 10,000 samples across 170-plus Excel operations and 2,000 public spreadsheets, which is a useful reminder that spreadsheet reasoning is still hard even for strong models.

Here is the practical dividing line:

Microsoft and LinkedIn's 2024 Work Trend Index surveyed 31,000 people across 31 countries and found that 75% of knowledge workers already use AI at work. Adoption is real. The harder question is where you can trust it. For one-table jobs, Copilot is already useful. For finance-heavy workbook work, you still want stronger write controls and verification.

That is why the market has split. Copilot is the everyday Microsoft option. Formula bots help with one-cell tasks. Workbook agents such as Deckary handle multi-step work inside the file itself.

Summary#

How to use Copilot in Excel well comes down to four rules:

1. Save the file to OneDrive or SharePoint and turn on AutoSave.
2. Format the data as a table or a supported range with clean headers.
3. Use specific prompts that name the table, the action, and the output.
4. Review anything Copilot writes before you trust it in a real model.

If you want the broad category view, go next to Best AI for Excel. If your workflow ends in a presentation, Excel to PowerPoint is the better follow-up.

Sources#

• Microsoft Support: Get started with Copilot in Excel
• Microsoft Support: Frequently asked questions about Copilot in Excel
• Microsoft Support: Format data for Copilot in Excel
• Microsoft Support: Identify insights with Copilot in Excel
• Microsoft Support: Create charts with Copilot in Excel
• Microsoft Support: Edit with Copilot in Excel
• Microsoft Support: Frequently asked questions about editing with Copilot in Excel
• Microsoft 365 Copilot pricing
• Microsoft and LinkedIn: 2024 Work Trend Index Annual Report
• InstructExcel: A Benchmark for Natural Language Instruction in Excel
• Spreadsheet Errors: What We Know. What We Think We Can Do