Image by Editor

Microsoft Excel is a well-known data analysis tool, but with Microsoft Copilot built in, it becomes even more accessible. Copilot can help you analyze data, generate formulas, create visualizations, and uncover insights using simple natural language. Copilot in Excel is an AI-powered assistant built directly into your spreadsheet workflow.

In this tutorial, we will show you how to start using Copilot in Microsoft Excel. Instead of writing complex formulas manually, you can simply type something like “Calculate total sales by region” or “Highlight the top 10 customers,” and Copilot will do it for you instantly.

Prerequisites and Requirements

To use Copilot in Excel, you need:

• A Microsoft 365 subscription that includes Copilot (such as select personal plans like Microsoft 365 Personal or Family, or business and enterprise plans with a Copilot add-on)
• Excel for Microsoft 365 (desktop, Mac, web, or iPad versions)
• Your workbook saved to OneDrive or SharePoint (with AutoSave enabled) for full functionality in many features

Tip: If you’re not subscribed yet, you can start a Microsoft 365 trial to test Copilot.

Step 1: Preparing Your Data

Start by opening Excel and loading the workbook you want to analyze. Then make sure the data is organized in a way Copilot can read.

The easiest option is to convert your data into a table:

• Select any cell inside your dataset
• Go to Insert >> click Table
• Check My table has headers if your first row contains field names
• Click OK

You can also keep the data as a normal range, but it must follow strict rules:

• Only one header row
• Headers must be in columns, not rows
• No duplicate or blank headers
• No empty rows or columns
• No merged cells
• No subtotals
• Consistent formatting throughout the dataset

Step 2: Opening Excel and Accessing Copilot

• Go to the Home tab >> click the Copilot button (it usually appears as an icon or as a “Copilot” label)
  • This opens the Copilot pane on the right side
  • You’ll see conversation starters (suggested prompts) to help you get started quickly

Alternative Quick Access:

• Select a specific cell or range
• A small Copilot sparkle icon may appear next to it
• Or right-click >> select Copilot >> choose the available relevant options

Step 3: Exploring What Copilot Can Do

Copilot in Excel works especially well for natural language tasks. Here are the main capabilities:

Creating and Understanding Formulas:

• Ask Copilot to add new columns with calculations
• Example prompt:

"Create a formula column for Profit"

• Copilot generates multiple formulas along with an explanation
• You can select whichever method you prefer

• Another formula example

• One more formula example

• Select the formula you want to use
• Select a cell in the Excel sheet, then click Add to cell
• Copilot automatically adds the formula to the new column

• Copilot analyzes the entire dataset
• You can also choose prompts from the suggestions it provides

• Select a prompt from the suggestions, such as “Calculate Profit Margin%.”

Analyzing Data for Insights:

• Ask questions about trends, outliers, or summaries
• Example prompts:

"What are the top 5 products by sales?"

• It shows the process behind the calculations

• Click Add to new sheet

"Show me trends in monthly revenue."

• Copilot can respond with charts, PivotTables, summaries, or highlighted data

"Create a bar chart showing sales by region."

"Summarize this table by Report Date."

"Summarize by Product Category"

Highlighting, Sorting, and Filtering Data:

• It generates the steps for applying Conditional Formatting
• You can follow those exact steps to highlight sales values greater than 1000

Explore:

• “Filter for numbers greater than five.”
• “Sort this table by date descending.”

Tip: Copilot can’t always apply formatting directly to the existing sheet. In some cases, it generates a new downloadable file with the highlights or formatting applied. Download that file to use the updated formatting.

Importing Data: Copilot can help you import data from the web, from saved files in OneDrive or SharePoint, or from communications within your organization.

Pro Tip: Be specific in your prompts. Mention column names, conditions, or your desired output format (for example, “as a chart” or “as a PivotTable”).

Transitioning to Newer Copilot Features

Since some features are being phased out, newer Copilot experiences may be more useful:

• Agent Mode in Excel: Enables side-by-side editing and workbook building with Copilot. Start a new workbook in Excel for the web and enable editing in the Copilot pane.
• Copilot Chat: Useful for basic questions directly in Excel and available across many Microsoft 365 apps.
• Analyst: Helpful for deeper reasoning and more advanced data analysis.

These options can provide a more powerful and up-to-date experience.

Tips for Best Results

• Data: Always format data as a table for the best results. Keep your data clean and structured.
• Be Specific in Your Prompts: Instead of “analyze this data,” try “find the month with the highest average order value.”
• Iterate: If Copilot’s first answer isn’t quite right, follow up with a clarifying prompt in the same chat.
• Review Before Accepting: Copilot will often suggest changes rather than apply them automatically, so always review what it proposes before confirming.
• Use Natural Language: You don’t need to know Excel syntax; just describe what you want in plain English. Copilot understands conversational requests.
• Save your file to OneDrive or SharePoint to use AutoSave and unlock the full set of AI features.

Common Limitations

Copilot is powerful, but it is not perfect. It also has some limitations.

• It depends heavily on clean data and works best with well-structured, tabular datasets
• Some advanced or older app skills are being removed
• Performance and exact capabilities can vary by subscription tier and region
• Always verify important calculations manually, since results should be reviewed for accuracy
• Complex business logic may still require manual formulas

Conclusion

Getting started with Copilot for Microsoft Excel is surprisingly simple. Copilot in Excel becomes much easier to use when you treat it like a smart assistant sitting beside your worksheet. Start with a clean table, open the Copilot pane, and ask for one concrete task at a time. Once you get comfortable with summaries, sorting, formulas, charts, and PivotTables, you can move into deeper analysis workflows. With Microsoft Copilot, Excel becomes less about memorizing formulas and more about understanding your data.