How to Use Microsoft Copilot in Excel: From Formulas to Data Analysis
Last verified: April 29, 2026 | Estimated time: 20–30 minutes | Requires: Microsoft 365 + Copilot add-on
Microsoft Copilot reached 100 million monthly active users across commercial and consumer plans (Microsoft FY2026 Q2). In Excel, type a plain-English request into the Copilot panel and it generates formulas, builds dashboards, flags anomalies, and in Agent Mode executes multi-step tasks autonomously across your spreadsheet.
Business Standard/Premium, E3/E5, F1/F3, Office 365 E3/E5, or Personal/Family
$30/user/mo enterprise · $18–21 business · $20 Pro (individual) · $39 Pro+ (consumer, advanced model access). IT admin assigns in M365 Admin Center. Allow up to 24 hrs.
Hard gate: local drive files not supported. Auto-Save must be ON.
Dismiss "Protected View" banners before opening Copilot.
Ctrl+T to convert range. Copilot works best on structured tables, not flat ranges.
Progress: 0 of 6 steps completed
1. Enable
2. Table
3. Formulas
4. Analysis
5. Charts
6. PivotTables
Step 1
Enable Copilot in Excel
Open Excel and sign in with the account that holds the Copilot license.
Save the file to OneDrive or SharePoint: File > Save As > OneDrive.
Enable Auto-Save using the toggle in the top-left of Excel.
Confirm edit mode: dismiss any "Protected View" banner by clicking Enable Editing.
Click the Copilot icon in the ribbon (Home tab, far right). The Copilot panel opens on the right.
Verify: The Copilot panel opens with a prompt field at the bottom. If you see a file-location error, move the file to OneDrive/SharePoint first.
Step 2
Format Your Data as a Table
Copilot works best on structured Excel Tables. Without a formal table, Copilot may misidentify column ranges.
Click any cell in your data range.
Press Ctrl+T (or Home > Format as Table).
Confirm the range, check My table has headers, click OK.
Verify: Filter arrows appear in the header row and a Table Design tab appears in the ribbon.
Step 3
Generate Formulas with Natural Language
Copilot translates plain-English descriptions into Excel formulas and offers to insert them directly.
Example prompts:
"Show me revenue growth rate quarter over quarter."
"Flag overdue invoices."
"Calculate the 90-day rolling average of sales."
Select the column where you want the formula inserted.
Type your request in the Copilot panel.
Copilot shows a preview with an explanation of the formula logic.
Click Insert to apply.
Verify: The formula appears in the formula bar. Spot-check the result against a known row before applying to the full dataset.
Step 4
Run Data Analysis
Copilot scans datasets to identify trends, anomalies, and outliers, then explains findings in plain English. No pivot table or formula knowledge required.
"Run a full analysis on this sales data set. I want to understand some important insights to help me make decisions about my business. Make it visual."
In Agent Mode (2026), Copilot executes this autonomously: scans data, identifies patterns, creates charts, applies conditional formatting, and delivers a summary.
Python in Excel: If your organization has Python in Excel enabled, Copilot can run statistical models, ML regressions, and predictive analytics via natural language. No Python code required.
Open the Copilot panel.
Type your analysis prompt. Be specific about what decisions you need to support.
Review the text summary returned in the panel.
In Agent Mode, Copilot also inserts charts and formatting directly. Review each element before sharing.
Verify: Charts appear on the sheet and the Copilot panel shows a written summary of key findings.
Step 5
Create Charts and Apply Conditional Formatting
Request charts and formatting rules directly in the Copilot panel. No chart wizard required.
"Create a bar chart showing monthly revenue by product line."
"Apply data bars to the Profit Margin column."
"Group clients by revenue tiers and color-code them."
Keep the table active or select the data range.
Type your chart or formatting request in the Copilot panel.
Click Insert Chart (or the formatting action button) to place it on the sheet.
Verify: Check axis labels and data series assignments. Rename ambiguous column headers if Copilot misassigns a series.
Step 6
Build PivotTables with Copilot
Copilot proposes and inserts a PivotTable from a plain-English description, including field assignments.
"Create a PivotTable showing total sales by region and quarter."
"Add a calculated field for profit margin to the existing PivotTable."
Confirm data is in Table format (Step 2).
Describe the PivotTable in the Copilot panel.
Review the field assignments Copilot proposes.
Click Insert. Copilot places the PivotTable on a new sheet.
Refine with follow-up prompts: "Break this down by month" or "Add a filter for the East region."
Verify: Confirm field assignments match your intent. Check row/column totals against source data.
Known Limitation: Multi-Sheet Models
Multi-sheet financial models with circular dependencies are where Copilot struggles most. It may generate outputs that look correct but silently break cross-sheet dependencies. Manual verification of source linkages is required.
Troubleshooting
Cause: License not assigned or not yet propagated.
Fix: Confirm with IT admin that the Copilot license is assigned in the Microsoft 365 Admin Center. Allow up to 24 hours. Confirm you are signed in with the licensed account, not a personal Microsoft account.
Cause: File is on a local drive or Auto-Save is off.
Fix: Move the file to OneDrive or SharePoint via File > Save As. Enable Auto-Save. Files accessed via UNC path must be synced through the OneDrive sync client.
Cause: Ambiguous column headers or unstructured data range.
Fix: Confirm data is an Excel Table (Ctrl+T). Rename headers to be descriptive and unique. Remove merged cells from the table range.
Cause: Agent Mode requires enterprise Copilot license and a current build.
Fix: Update Microsoft 365 apps (File > Account > Update Options > Update Now). Contact IT admin: Agent Mode requires Microsoft 365 Copilot enterprise tier, not Copilot Pro.
Frequently Asked Questions
Both Excel for Windows (desktop) and Excel for the web are supported, as long as the file is on OneDrive or SharePoint with Auto-Save enabled. Some Agent Mode features may roll out to the web version first.
No. A qualifying Microsoft 365 base plan plus a Copilot add-on are both required. There is no free tier for Copilot in Excel.
Copilot processes prompts and spreadsheet context via Microsoft's cloud. Enterprise tenants should review the Microsoft Data Processing Agreement and the Microsoft 365 Trust Center for data residency commitments. Consumer/Pro users should review the Copilot privacy statement at microsoft.com/privacy.
Claude Opus 4.5 is available in Agent Mode in Excel for the web for organizations whose IT admin enables it via the Microsoft 365 Admin Center. Anthropic is an officially sanctioned sub-processor for Microsoft Online Services as of early 2026. This requires enterprise IT configuration and is not user-selectable.
EU Data Boundary: When using Claude or other third-party models via Microsoft 365 Copilot in EU tenants, Microsoft's EU Data Boundary commitments apply. Verify your tenant's data residency configuration before deploying AI features across jurisdictions.
Video Resources
▶
Microsoft Copilot in Excel: Official Overview
Microsoft · microsoft.com/en-us/microsoft-365/copilot
▶
Copilot Agent Mode in Excel: Walkthrough
Microsoft Learn · learn.microsoft.com
Before You Use AI
Your Privacy
Copilot in Excel processes your spreadsheet data and prompts via Microsoft's cloud services. Enterprise tenants control data processing through M365 admin controls, Data Processing Agreements, and Microsoft Purview data governance. Consumer and Pro users operate under the Microsoft Services Agreement. Enterprise and consumer data handling differ significantly; review the agreement applicable to your context before enabling Copilot on sensitive data.
AI productivity tools can increase efficiency, but over-reliance on automated outputs without verification can introduce errors in financial data. Always review AI-generated formulas against known values before sharing or acting on results. If work-related stress is a concern, support is available:
988 Suicide & Crisis Lifeline: Call or text 988 (US)
Under GDPR and CCPA, you have the right to access, correct, and delete personal data processed by Microsoft services. Review your rights at the Microsoft Privacy Dashboard. TechJack Solutions maintains editorial independence from all vendors including Microsoft. This article was not sponsored or approved by Microsoft. We may earn affiliate commissions on some links; this does not influence our coverage. All facts are sourced from official documentation and public filings (April 2026).