How to use Copilot in Excel_ - Infinity Group (UK)

Updated May 2026

Key takeaways_

Microsoft Copilot in Excel helps you analyse data faster by generating formulas, creating summaries and building charts, all from simple prompts.
It can clean messy datasets, spot trends, and even suggest pivot tables, saving hours of manual work.
Use Copilot to turn raw numbers into actionable insights without advanced Excel skills, boosting productivity and decision-making.

Excel has always been powerful, but also time‑consuming. Formulas, pivots, data clean‑up and analysis still require specialist knowledge, and even experienced users spend hours wrestling spreadsheets into shape. That’s where Microsoft Copilot in Excel comes in.

Copilot brings AI directly into Excel, letting you ask for insights, build formulas, analyse data and even update your spreadsheet using plain English. Instead of searching for the right function or rebuilding reports from scratch, you can describe what you want – and Copilot helps you do it, step by step, inside the workbook.

In this guide, we’ll show you how to use Copilot in Excel in practice. We’ll cover what you need to get started, where to find Copilot, and the most useful ways teams are using it today – from analysing trends and fixing formulas to creating summaries and dashboards faster. Whether you’re an occasional Excel user or rely on spreadsheets daily, this is a practical walkthrough of how Copilot can save time without taking control away from you.

What is Copilot?

Microsoft Copilot is an AI-powered assistant, designed to help you with a wide range of tasks. It can enhance productivity and streamline workflows, providing support with content creation, data analysis, information summarisation and task automation.

Copilot offers unique benefits, differentiating it from other AI tools on the market. These include:

Customisation: Copilot adapts to individual user preferences and work styles, as well as your company data, to offer tailored suggestions
Integration: Copilot integrates with the tools you already use. Alongside Excel, this includes Word, Teams, Outlook and SharePoint
Natural language understanding: Copilot excels at understanding and responding to complex queries in natural language, making it easy to use
Continuous learning: Copilot is constantly improving through machine learning, ensuring that it stays up-to-date and effective
Security and privacy: Microsoft places a strong emphasis on data security and privacy, giving users confidence in using Copilot. Your data is ringfenced so external users cannot access it
Responsible AI: Microsoft has established a robust framework for developing and deploying AI systems responsibly. These principles are central to their AI initiatives and aim to ensure that AI benefits society while minimising potential harm

In short, it’s a safe and efficient AI tool that can drive performance across your Microsoft solutions.

What you need before using Copilot in Excel_

If you want to unleash the power of Copilot with Microsoft Excel, you’ll need a Copilot for Microsoft 365 licence. This enables you to integrate Copilot within core Microsoft applications.

Copilot in Excel is available as part of Microsoft 365 Copilot and works across Excel desktop, web and Mac. You can check pricing here. You’ll also need to have a Microsoft 365 licence to cover Excel.

To use Copilot in Excel:

Your workbook must be saved to OneDrive or SharePoint
AutoSave must be enabled
Copilot works best when your data is formatted as an Excel table

Once enabled, the Copilot icon appears within Excel, allowing you to ask questions, generate formulas, analyse data or use Agent Mode directly within your workbook.

Availability and features may vary slightly depending on your Microsoft 365 plan and deployment configuration.

How to use Copilot in Excel (step by step)_

Copilot is designed to work inside Excel, alongside your existing formulas and workflows. You don’t need to change how you use Excel. You just describe what you want to do, and Copilot helps you get there.

Step 1: Open Copilot in Excel_

Open your workbook and select the Copilot icon in the Excel ribbon. This opens the Copilot panel on the right‑hand side, where you can type prompts or choose suggested actions.

Step 2: Ask Copilot questions about your data_

Once Copilot is open, you can ask questions about the data in your workbook using plain English. For example, you might ask it to summarise trends, highlight unusual values or explain what’s happening month to month. Copilot can respond with written insights, charts, pivot tables or summaries based on your data.

Step 3: Use Copilot to create, explain or fix formulas_

Copilot is particularly useful for formulas, especially if you know what you want to calculate but not how to write it. You can ask Copilot to create a formula from scratch, explain an existing formula step by step or help troubleshoot one that isn’t working as expected. In newer versions of Excel, Copilot can also be used directly inside formulas using the =COPILOT() function for text analysis and AI‑assisted calculations.

Step 4: Let Copilot update your spreadsheet for you_

Copilot can now go beyond suggestions and make changes directly to your workbook. When editing is enabled, you can ask Copilot to create tables, format data, build charts or restructure sheets. Excel clearly shows what Copilot has changed so you can review and stay in control before accepting the updates.

Step 5: Refine results with follow‑up prompts_

Copilot works best as a conversation. If the first response isn’t quite right, you can refine it by asking follow‑up questions. For example, changing how data is grouped, adjusting assumptions or focusing on a specific time period or category. This iterative approach is often faster than manually rebuilding spreadsheets.

15 more things you can do with Copilot in Excel_

1. Spot trends and patterns_

Excel is usually used to store data across your core business areas, grouping it together into one worksheet. Copilot can help you to better analyse this data.

Using the Copilot tab with your Excel sheets, ask it to pull out trends and key takeaways from your data. You can also use it to find outliers.

This helps you to draw conclusions humans may have otherwise missed.

Image taken from Microsoft

2. Answer questions about your data_

Instead of building formulas or filters, you can ask Copilot questions in plain English and get clear answers based on your worksheet. For example:

What percentage of sales came from a specific product or service?
Which locations are performing best?
Which marketing channels drove the highest engagement this quarter?
Which teams generated the highest costs last year?

Copilot translates these questions into analysis behind the scenes, helping you reach answers faster with less effort.

3. Create pivot tables

Pivot tables are commonly used in Excel to summarise, analyse, explore and present large amounts of data. You can ask Copilot to create pivot tables by giving it details of exactly what you want to include and uncover.

It will then automatically transform your data into a pivot table you can utilise.

4. Clean and prepare data_

If one piece of data has an error or is in the wrong format, it can prevent accuracy across your entire workbook.

If something isn’t working, you can use Copilot to identify and correct errors, inconsistencies or missing values in your dataset.

This ensures you only have high-quality output that is accurate and clear.

5. Create calculated columns_

If you want to add new columns into your Excel based on calculations of existing data, Copilot can do it for you.

For example, if I have column A which tells me the cost of product items and column B which tells me sales figures for each product, I might wish to create a column C that multiples the two to tell me how much revenue each product line has generated.

By telling Copilot what I want, it can automatically add this column and data. This allows you to implement the columns you need without needing to figure out complex formulas.

6. Compare columns_

Another useful way to analyse data is to compare columns in your Excel spreadsheet, allowing you to find correlating patterns or spot difference. For example, I might want to review the leads generated by my latest marketing campaign compared with a previous campaign.

You can ask Copilot to fetch this data and provide insights. This can work across different sheets too, if your data is stored in more than one place.

7. Extract specific information_

If you’re looking to analyse a trend or share data with some, you might want to extract specific information within your workbook. However, searching for this information and pulling it out can be an annoying manual task.

Copilot in Excel can extract the data for you. Simply tell it what you want and it will serve up the information, saving you time searching.

8. Write formulas_

Some Excel formulas can be complex, especially if you’re not an experienced user. Fortunately, Copilot can do the hard work for you.

Simply describe the calculation you need in plain language, and Copilot will generate the corresponding Excel formula. This helps you to get the results you want faster, without navigating confusing formulas.

Image taken from Microsoft

9. Correct formulas_

If you’re using formulas you’ve done yourself in Excel, Copilot can also help you to perfect them. If something is showing an error, ask Copilot to examine the root of the issue. It can also provide a fixed formula to resolve the problem.

This ensures your spreadsheet works as it should, without any errors.

10. Visualise data through charts_

An Excel spreadsheet isn’t the most visually appealing method of presenting data. However, you can use it to create charts that better represent the data in a meaningful and engaging way.

Copilot can quickly generate these charts. Just tell it what you want to display or the data you wish to visualise, and it’ll automatically create charts you can share wherever you wish.

11. Combine columns_

Combining columns can help you to put data together, ready to import into systems or meet specific needs. For example, you might wish to combine separate columns with customer’s first and surnames to fit a ‘full name field’.

You can ask Copilot to combine these columns for you to quickly create a new column.

12. Set up conditional formatting_

Formatting can make an Excel spreadsheet look better and easier to understand. However, applying formatting can take time.

But not with Copilot. You can ask it to apply formatting, such as adding colour to specific values, bold text and so on. This makes it easier to get your spreadsheets into shape. It can also apply filters and sort data to get to the answers you’re seeking.

Image taken from Microsoft

You can also explore prompts within Copilot Lab for more inspiration on how you can use AI within Excel.

13. Enable Agent Mode_

One of the biggest recent changes to Copilot in Excel is the introduction of Agent Mode. This takes Copilot beyond suggestions and insights, allowing it to actively work on your spreadsheet with you, rather than just advising from the side.

With Agent Mode enabled, Copilot in Excel can:

Make changes directly to your workbook, such as adding formulas, adjusting tables, or restructuring data
Walk you through what it’s doing and explain why each change is being made
Support iterative analysis, where you refine results over multiple prompts rather than starting from scratch each time

This is a shift from “ask and review” to collaborative working. For example, instead of asking Copilot to suggest a formula and then applying it manually, you can ask it to build the calculation, test it against your data and refine it if the results don’t look right.

14. Use the COPILOT function_

Another major update to Copilot in Excel is the introduction of the COPILOT function, which allows you to use AI directly inside Excel formulas.

Instead of relying solely on the Copilot chat pane, you can now write formulas such as:

=COPILOT("Summarise the sentiment of this feedback", A2:A50)

This brings natural‑language AI into the grid itself. The key benefits include:

AI‑generated results that automatically recalculate when your data changes
The ability to classify, summarise or analyse text at scale
Seamless use alongside existing Excel functions like IF, SWITCH or LAMBDA

Because the COPILOT function is part of Excel’s calculation engine, it behaves like any other formula — meaning your analysis stays live and up to date without re‑running prompts.

This is especially useful for tasks like sentiment analysis, categorising survey responses, summarising comments, or turning unstructured text into structured data.

15. Explain and fix formulas_

Copilot in Excel isn’t just for creating formulas from scratch – it can also help you understand, troubleshoot and improve existing formulas.

If you inherit a spreadsheet with complex logic, nested formulas or unclear calculations, Copilot can:

Explain what a formula is doing in plain English
Break down complex formulas step by step
Highlight potential errors or inefficiencies
Suggest cleaner or more efficient alternatives

This is particularly useful when reviewing legacy spreadsheets, collaborating across teams, or handing work over to someone else. Instead of manually unpacking long formulas, you can ask Copilot to explain the logic directly within Excel and quickly validate whether the calculation does what you expect.

For organisations that rely heavily on spreadsheets for reporting, forecasting or finance, this feature helps reduce errors, speed up reviews and make Excel workbooks far easier to maintain.

FAQs_

Is Copilot in Excel secure?

Copilot in Excel operates within your existing Microsoft 365 security boundary. It does not train AI models on your business data, and access is governed by the same permissions already applied across SharePoint, OneDrive and Excel.

Microsoft also supports Copilot with governance and compliance tooling through Microsoft Purview, helping organisations manage data exposure, audit usage, and control how AI is adopted across the business.

This means teams can benefit from Copilot in Excel while maintaining control over sensitive or regulated data.

How does Python work with Copilot in Excel?

Python works with Copilot in Excel by giving Copilot a more powerful analysis engine to use behind the scenes. When you ask Copilot a question or request an analysis, it automatically decides whether standard Excel formulas are enough, or whether Python is better suited to the task.

If Python is needed, Copilot generates and runs it for you within Excel, then returns the results directly into your spreadsheet as tables, charts, or calculated outputs. You don’t need to install Python, write code, or manage anything separately — from the user’s point of view, it still feels like working entirely in Excel.

This combination allows Copilot in Excel to handle more advanced tasks, such as forecasting, statistical analysis, complex data transformations, and large datasets, while keeping everything accessible to non‑technical users.

Is Copilot in Excel the same as using ChatGPT with Excel?

No. Copilot in Excel is built directly into Microsoft Excel and works within your Microsoft 365 environment. It understands the context of your workbook, respects existing permissions, and can interact directly with your data, formulas and tables.

Using an external AI tool alongside Excel requires manual copying of data and doesn’t have the same level of integration or security.

Can Copilot in Excel work with existing spreadsheets?

Yes. Copilot in Excel works with existing workbooks, including large or complex spreadsheets.

For best results, data should be structured in tables, but Copilot can still analyse and explain existing formulas, highlight trends, and help clean or restructure data without needing to rebuild the spreadsheet from scratch.

Ready to take the next step?

It’s not just Excel where you can get AI power. By utilising Copilot across your daily tasks, you can win numerous rewards. This includes enhanced productivity, improved accuracy and reduced time spent on mundane work.

If you’re ready to explore AI even more. Our Copilot Adoption Hub contains everything you need to experiment with Copilot, understand the business impact and get AI insight across your Microsoft tech stack.

Access it here:

Key takeaways_

What is Copilot?

What you need before using Copilot in Excel_