How to Set Up Copilot in Excel for Data Analysis

What You Need Before You Start

Copilot in Excel does not work with every Microsoft 365 subscription. You need the Microsoft 365 Copilot add-on, which costs $30 per user per month on top of a standard M365 Business or Enterprise plan. Personal and Family subscriptions picked up limited Copilot access in 2026, but the full data analysis features described here require the business add-on.

Two other requirements are non-negotiable: the workbook must be saved to OneDrive for Business or SharePoint (not your local hard drive), and your data must be formatted as an Excel Table. Neither of these is obvious from Microsoft's marketing materials, and both are responsible for most of the "Copilot isn't working" reports on community forums.

Step 1: Verify Your License

Open Excel and go to File > Account. Under Product Information, you should see "Microsoft 365 Apps for Business" or an Enterprise equivalent. Look for a Copilot indicator in the upper-right ribbon. If the Copilot button is grayed out or missing, your organization has not assigned the Copilot license to your account. Contact your IT admin or check the Microsoft 365 Admin Center under Users > Active Users > Licenses.

If you are self-managed as a solo founder or small team, you can add the Copilot license from your Microsoft 365 admin portal. The current price is $30 per user per month billed monthly, or $360 annually. Organizations with fewer than five users can still purchase the Copilot add-on as long as they have a qualifying M365 base plan. The annual commitment saves roughly $120 per user compared to monthly billing.

Step 2: Save Your File to OneDrive

Copilot requires cloud-backed version history to perform multi-step edits safely. A file saved only to your desktop will display a banner asking you to move it before Copilot engages.

To move an existing file: go to File > Save As > OneDrive, then select your OneDrive for Business account. The file path in the title bar should change from a local path to a SharePoint or OneDrive URL. If you work in an organization, SharePoint team sites also work.

For new workbooks, simply start in OneDrive by creating the file from office.com or by saving directly to OneDrive from the Save dialog. Local autosave and OneDrive sync are not the same as saving to OneDrive. Confirm the save location explicitly before opening the Copilot pane.

Step 3: Format Your Data as an Excel Table

This step is the single most common reason Copilot refuses to act on data. An unformatted range of cells looks like a table to human eyes, but Copilot requires the formal Excel Table structure to understand column names and relationships.

To format your data as a table: select any cell inside your data range, press Ctrl+T (or Cmd+T on Mac), and confirm that the "My table has headers" checkbox is checked. Excel will apply a default table style and add filter dropdowns to each column header.

Practical requirements for a Copilot-ready table:

• No merged cells anywhere in the data range
• Every column must have a unique header in row 1
• No blank rows or columns inside the data area
• No formula-heavy helper columns that reference external sheets in the same table range

If your data comes from a CSV, import it via Data > From Text/CSV rather than copy-pasting. The import wizard preserves column types, which helps Copilot identify numeric fields for aggregation correctly.

Step 4: Open the Copilot Pane

With the file saved to OneDrive and data in a Table, click any cell inside the Table. The Copilot button in the Home tab ribbon will become active. Click it to open the Copilot pane on the right side of the screen.

If you see "Copilot works better with Excel tables," that confirms your data is not yet formatted as a Table. Go back to Step 3.

If the button is active but clicking it shows "This feature is not available in your current view," you may be in a protected sheet or a shared workbook with restricted permissions. Check Review > Protect Sheet and remove the password if needed. Shared workbooks in legacy compatibility mode also block Copilot. Save a copy as .xlsx to resolve that.

Step 5: Running Your First Analysis

Copilot in Excel accepts natural language prompts. Start with a specific question about your data rather than a vague instruction. Compare these two prompts:

• Weak: "Analyze my data"
• Strong: "Show me total sales by region for Q1 2026, sorted from highest to lowest, as a bar chart"

The stronger prompt specifies what to calculate (total sales), how to group it (by region), which period to filter (Q1 2026), the sort order, and the output format. Copilot generates the chart and the underlying formula, which you can inspect before accepting.

For summary statistics, try: "What are the top 5 products by revenue this quarter?" or "Highlight rows where the refund rate is above 10%." Both produce actionable output in seconds. The "Highlight rows" prompt generates a conditional formatting rule, not just a visual suggestion, so it persists if you add new data.

What Copilot Cannot Do

Microsoft's own documentation warns against using Copilot for tasks requiring accuracy or reproducibility. In testing by Office Watch in early 2026, the COPILOT() function returned factually incorrect results on financial calculations. Treat Copilot output as a first draft that requires verification, not a finished analysis.

Copilot also does not work across multiple sheets in one prompt. If your data is split across a sales sheet and a cost sheet, you need to consolidate it into a single Table before Copilot can run a cross-calculation. Power Query is the right tool for that merge step.

For very large datasets with millions of rows, performance degrades noticeably. DuckDB or a dedicated analytics layer handles those volumes more reliably than Excel Copilot.

A Complete Workflow: From CSV to Chart

Here is a practical end-to-end example. You have a 12-month sales CSV exported from your CRM with columns: date, product, region, quantity, unit_price.

1. In Excel, go to Data > From Text/CSV, select your file, confirm column types (date as Date, numeric as Decimal), and click Load.
2. The data lands in a new sheet as a formatted Table automatically.
3. Save the workbook to OneDrive via File > Save As.
4. Click the Copilot button in the Home tab.
5. Type: "Calculate total revenue (quantity times unit_price) by product and region for the full year, and show the result as a pivot table with a bar chart."
6. Copilot creates a new sheet with the pivot and chart. Review the formula logic in the Copilot pane before accepting.

The entire process takes under five minutes once the prerequisites are in place. The bottleneck is almost always Step 3: getting the data into a clean, properly formatted Table.

For analysts who want to skip the CSV import and formatting step entirely, VSLZ AI accepts the raw file directly and returns charts and statistical summaries from a plain English prompt, with no Table conversion or OneDrive requirement.

Practical Summary

Copilot in Excel is useful for quick aggregations, formula generation, and basic visualization on clean, structured data. The three requirements that must all be true simultaneously are: the M365 Copilot license ($30/user/month), the file saved on OneDrive or SharePoint, and the data formatted as a formal Excel Table. Accuracy on complex or high-stakes calculations is not guaranteed, and multi-sheet analysis requires a manual consolidation step first. For straightforward datasets under a few hundred thousand rows, it handles the most common analyst tasks without any formula knowledge required.