How to Set Up Copilot in Excel for Data Analysis

What Copilot in Excel Does

Copilot in Excel is an AI assistant embedded in Microsoft 365 that responds to plain-English prompts typed into a side panel. It can generate charts, create pivot tables, write formulas, highlight trends, and filter data. In its newer Agent mode, rolled out to Microsoft 365 Copilot subscribers in January 2026, it takes multi-step actions on your workbook autonomously and narrates each change as it works.

The practical difference between standard Copilot and Agent mode is meaningful. Standard mode suggests changes that you manually apply. Agent mode makes changes directly to the workbook, displays a running log of what it did, such as "Removed 12 blank rows" or "Reformatted 340 dates in column A," and allows you to pause, review, or redirect at any point. Microsoft describes this as the tool "working alongside users" with transparency and control preserved throughout.

This guide covers how to set up Copilot in Excel correctly and how to use both modes for practical data analysis.

Prerequisites Before You Start

Four conditions must be met before Copilot in Excel will function.

Microsoft 365 Copilot license. Copilot in Excel requires the Microsoft 365 Copilot add-on, which costs $30 per user per month on top of a qualifying Microsoft 365 Business or Enterprise plan. A standard Microsoft 365 subscription without the Copilot add-on gives you a grayed-out Copilot button in the ribbon but no actual functionality.

Supported file format. The workbook must be saved in .xlsx or .xlsm format. Files in the older .xls format will not work. To convert a legacy file, use File > Save As and select .xlsx before proceeding.

OneDrive or SharePoint storage. Until February 2026, Copilot in Excel requires AutoSave to be active, which means the file must be stored in OneDrive or SharePoint rather than locally on your hard drive. Microsoft announced in January 2026 that support for locally stored modern workbooks is rolling out in February, removing this requirement for users who prefer local storage.

Third-party cookies in browser. If you use Excel Online in a web browser instead of the desktop application, third-party cookies must be enabled for Copilot to load and run.

Step 1: Format Your Data as an Excel Table

Copilot works most accurately with data organized in Excel's native Table format rather than a plain range. A Table gives Copilot structured column names, data types, and boundaries to work with.

To convert a flat range to a Table, click any cell inside your data, then press Ctrl+T on Windows or Command+T on Mac. In the dialog that appears, confirm the data range and check "My table has headers" if your first row contains column labels. Click OK. The range becomes a Table with alternating row shading and dropdown filter arrows.

Practices that improve Copilot's output quality:

• Use clear, descriptive column headers with no merged cells
• Keep each column to a single data type: dates in one column, numbers in another, text in another
• Remove blank rows or columns embedded within the data range
• Move subtotal or summary rows outside the Table boundary

Copilot in Excel processes up to 2 million cells per workbook. For most business spreadsheets, this limit is not a constraint. For very large datasets, consider filtering or aggregating the data in Power Query before running Copilot on the result.

Step 2: Open the Copilot Panel

On the Home tab of the Excel ribbon, find the Copilot button, which appears as a small sparkle or star icon. Click it to open the Copilot panel on the right side of the screen.

If the button is grayed out after clicking, check the following:

• Toggle AutoSave on using the switch in the upper-left of the application window
• Confirm you are signed in to the Microsoft account that holds the Copilot license
• Verify the file is saved in .xlsx or .xlsm format and stored in OneDrive or SharePoint

Once the panel opens, you will see a text input field and a set of pre-written suggested prompts. You can select a suggestion or type your own request.

Step 3: Use Standard Copilot Mode for One-Shot Analysis

Standard mode handles analysis tasks where the result can be reviewed before applying. Copilot generates a suggestion in the panel and you decide whether to add it to the workbook.

Practical prompts that work reliably for business analysis tasks:

Trend analysis: "Show me monthly revenue as a line chart and highlight months where revenue fell below the prior month." Copilot will generate the chart and apply conditional formatting to flag the relevant months.

Formula generation: "Add a column that calculates gross margin as revenue minus cost of goods sold, divided by revenue." Copilot will write the formula, show the syntax, and explain each component.

Duplicate detection: "Highlight any duplicate values in the Order ID column." Copilot applies a conditional formatting rule without deleting any data.

Pivot table creation: "Create a pivot table showing total sales by region and product category for Q1." Copilot builds the pivot table on a new sheet and configures the row, column, and value fields.

One practical note on accuracy: Microsoft's own documentation states that Copilot in Excel "generates insights and formulas that can be inaccurate or inappropriate" and "cannot understand meaning or evaluate accuracy." Every formula or insight Copilot produces should be reviewed before it is used in a report or shared with stakeholders.

Step 4: Use Agent Mode for Multi-Step Tasks

Agent mode, available to Microsoft 365 Copilot subscribers from January 2026, handles tasks that require several sequential actions. Rather than handling one request at a time, Agent mode accepts a compound prompt and executes each step in order.

To enable Agent mode, look for a mode toggle in the Copilot panel, which may appear as a dropdown menu or a labeled switch next to the prompt field, depending on your version.

An example prompt suited to Agent mode: "Clean the sales data by removing blank rows, converting all dates in column A to the format YYYY-MM-DD, and then create a pivot table showing total units sold by month."

As Agent mode works through the task, it shows a step-by-step log of actions. You can stop the process at any step and redirect it. After completion, scroll through the affected columns and inspect the pivot table before treating the output as final. Agent mode can misinterpret ambiguous instructions, so specific prompts with clear column references produce more reliable results.

Step 5: Combine Copilot with Excel's Native Tools

Copilot generates starting points. Excel's native tools refine them. A productive workflow combines both:

1. Prompt Copilot to generate an initial chart or pivot table
2. Switch to Excel's native chart editor or PivotTable field list to adjust layout, colors, or groupings
3. Ask Copilot to explain a formula it wrote if you want to understand the logic before applying it
4. Export or share the workbook through the standard File > Share or File > Export process

For analysts who work with data from multiple sources, Copilot in Excel handles only the current open workbook. To combine data from different files or systems, use Power Query first to consolidate the data into a single table, then run Copilot on that result.

For users who want to skip spreadsheet setup entirely, VSLZ AI accepts a file upload and returns charts and statistical summaries from a plain-English prompt, without requiring table formatting or cloud storage configuration.

What Copilot in Excel Does Not Handle

Copilot in Excel is scoped to the active workbook. It does not connect to external databases, pull live data from APIs, or access files that are not currently open. Multi-source analysis requires Power Query to consolidate sources first.

Language support is limited to eight languages as of early 2026. Users working in languages outside this set may encounter inconsistent or empty responses.

Copilot does not retain conversation history between sessions. If you close and reopen a workbook, previous Copilot interactions are not available.

The 2 million cell limit applies per workbook, not per sheet. Very large datasets that approach this limit may produce slower or incomplete responses.

Summary

Setting up Copilot in Excel requires a Microsoft 365 Copilot license, an .xlsx or .xlsm file stored in OneDrive or SharePoint (or locally from February 2026), and data structured in an Excel Table. Standard Copilot mode handles one-shot analysis and formula suggestions, while Agent mode, introduced in January 2026, runs multi-step edits with step-by-step narration. Review every output before using it in reports, as Copilot can generate incorrect formulas or misleading summaries.