How to use Microsoft Copilot in Excel to improve productivity and efficiency

What is Microsoft Copilot in Excel?

Microsoft Copilot in Excel is an AI assistant built into Excel that lets you use natural‑language prompts to analyse data, create formulas, build charts and PivotTables, and highlight patterns in your workbook (with results grounded in your spreadsheet data).  

As organisations look to improve productivity and reduce operational inefficiencies, artificial intelligence (AI) is becoming a critical part of everyday tools, not just specialist platforms. From automating repetitive tasks to uncovering insights faster, AI is reshaping how teams work with data.

Microsoft Excel has been the go-to tool for spreadsheet management for organisations across the globe for many years. Now, Microsoft is revolutionising workplace efficiency by integrating AI throughout its product suite, and Excel is no exception.

Recognising the evolving need for smarter and quicker data management, Microsoft has introduced Copilot to Excel, harnessing the advanced AI capabilities of GPT-4. This leap forward empowers users to dramatically enhance their productivity and streamline spreadsheet tasks like never before.

For many organisations, the real value comes from combining tools like Microsoft Copilot with broader business systems and workflows to create a more connected, efficient, and insight-driven digital workplace.

This shift aligns with broader industry trends, where AI is becoming a top priority for organisations looking to improve efficiency and decision-making.

In this blog, we'll discuss the top five ways to use Copilot in Microsoft Excel, taking advantage of AI to improve your productivity and streamline workflow processes.

Benefits of using Copilot in Excel

• Save time on repetitive spreadsheet work (for example, getting to a usable first draft faster and reducing time spent searching for information), so you can spend more time on decision-making.
• Make analysis more accessible by turning natural-language questions into PivotTables, charts, summaries, and trend insights (especially useful for non-Excel specialists).
• Improve consistency and reduce errors by letting Copilot propose formulas and transformations that you can review before applying.
• Accelerate adoption of advanced Excel features (PivotTables, conditional formatting, formula columns) without needing to memorise syntax.

Microsoft’s own research on Microsoft 365 Copilot users found that the average user reported saving 14 minutes per day (nearly five hours per month), and the most efficient users saved 30 minutes per day (about 10 hours per month). In Microsoft’s Work Trend Index special report on early Copilot users, 70% said they were more productive and users were 29% faster in a series of tasks (searching, writing, summarising).

Top five Copilot uses in Microsoft Excel

1. Quickly create PivotTables

Copilot can create a PivotTable for you from a prompt, so you can move from raw data to a usable summary in seconds.

However, creating PivotTables can be time-consuming, particularly for teams working with large or complex datasets.

Copilot for Microsoft Excel streamlines this process significantly. By simply instructing Copilot with a command such as “Create a PivotTable based on [your chosen data],” you can generate one instantly.

Additionally, Copilot allows users to request specific insights. For example, asking “What is the total value of my inventory?” can automatically generate a PivotTable with the answer.

Microsoft highlights that Copilot can return insights as charts, PivotTables, summaries, trends, or outliers when you ask questions about your data - making it practical for quick operational reporting (e.g., inventory value, top categories, month-over-month changes).

This not only saves time but also enables teams to focus on analysing data rather than building reports, especially when integrated with wider business data sources.

2. Create data visualisations

Copilot can turn a table into the right chart from a natural-language prompt, so stakeholders get an instant visual instead of a wall of numbers.

Creating these visuals manually can take time and effort. Copilot simplifies this by generating visuals instantly from prompts like “Visualise this table of data.”

Tip for reporting: if your data is in a formatted table with clear headers, Copilot is more likely to pick an appropriate chart and label it sensibly. Microsoft notes that Copilot can present insights as charts based on your questions.

It can also work with more advanced datasets, including those generated through Python within Excel.

By reducing manual effort, organisations can produce consistent, high-quality reporting faster, particularly when visualisations are aligned with broader reporting frameworks and business systems.

3. Discover trends in data

Copilot can scan your worksheet for trends, outliers, and patterns and summarise the key takeaways in plain English.

Microsoft Copilot addresses this limitation by ensuring comprehensive trend detection across your data sets. It excels in uncovering new trends and patterns swiftly by conducting an in-depth analysis and highlighting the most significant insights.

All you have to do is ask Copilot to “Analyse [your chosen data] and summarise five key trends”, which will initiate a thorough examination of the data, allowing Copilot to provide a detailed analysis and summary of crucial trends in just a few seconds.

For organisations, this means faster, more informed decision-making without relying on manual analysis, helping teams respond more quickly to changing business needs.

4. Highlight, sort and filter data

Copilot can apply sorting, filtering, and conditional highlighting from a simple instruction, so you can clean up and interrogate messy spreadsheets faster.

Copilot simplifies this by executing commands instantly. For example:

• “Sort this data by cost, lowest to highest.”
• “Filter to show only rows where Region = North.”
• “Highlight all entries above 10,000 and add a note explaining why they stand out.”

Microsoft specifically calls out “highlight, sort, and filter” as Copilot-in-Excel capabilities.

This allows users to organise and access information quickly, improving efficiency and reducing time spent on manual data handling, especially across large datasets.

5. Generate formula columns

Copilot can propose and insert formula columns based on what you describe, so you don’t have to build complex calculations from scratch.

Microsoft Copilot can take care of this quickly, generating a formula column to fulfil your needs with a simple prompt. All you need to do is ask Copilot to “make a formula column based on [the specified data]” and Copilot will do so, giving you a formula column to insert into your spreadsheet.

For example, you can try:

• “Add a variance column (Actual minus Budget) and a variance % column.”
• “Create a margin % column using Revenue and Cost.”
• “Flag rows where delivery date is more than 2 days after the promised date.”

Alternatively, there is an even more seamless way of doing this. By simply clicking the ‘show suggestions for formula columns’ option, Copilot presents you with suggested formula columns that are specifically designed to enhance your spreadsheet's functionality and efficiency, further optimising your data management processes.

Frequently asked questions about Copilot in Excel

1. Is Copilot in Excel the same as ChatGPT?

Not exactly. Copilot is built into Excel and is designed to ground answers in your workbook (and, depending on your licence, your Microsoft 365 data), whereas ChatGPT is a general-purpose chatbot.

2. What do I need to use Copilot in Excel?

You typically need the right Copilot-enabled Microsoft 365 licensing in your tenant, plus the current Excel experience. Microsoft’s setup guidance also notes app/network prerequisites for Copilot in Microsoft 365. 

3. What can Copilot do in Excel today?

Microsoft highlights use cases including generating and explaining formulas, identifying insights (charts, PivotTables, trends/outliers), and helping you highlight/sort/filter data.

4. Is Copilot’s output always accurate?

No - treat Copilot as a drafting and analysis accelerator. Review formulas before applying them, sense-check summaries, and validate any business-critical results (especially when data has blanks, inconsistent categories, or mixed data types).

5. How do I get better results from Copilot in Excel?

Start by formatting your data as a table, using clear column headers, and being specific in your prompt (what you want, which columns to use, and what output format you want - chart, PivotTable, or written summary). 

How to get started?

Microsoft Copilot is a powerful tool that can transform how teams work in Excel, turning complex tasks into simple prompts and significantly improving day-to-day productivity.

However, unlocking its full value goes beyond simply enabling the tool. Organisations need the right foundations in place, including structured data, effective governance, and integration with core business systems.

As a long-standing Microsoft partner, OneAdvanced supports organisations not only in adopting tools like Copilot, but in making them work effectively within a broader digital ecosystem. Our Digital Workplace experts can help you prepare the right foundations (data quality, permissions, governance) and build repeatable Copilot use cases across your teams.

By combining Microsoft Copilot with OneAdvanced’s wider solutions and expertise, organisations can move beyond individual productivity gains and create more connected, efficient, and insight-driven ways of working.

If you’d like to go deeper, contact us today to see how we approach Copilot readiness and adoption or speak to our team about piloting a small set of measurable Excel use cases.