Copilot in Excel: Transforming Data Analysis

Microsoft

Nov 19, 2024

Data analysis is a critical yet challenging task for business professionals. From creating and preparing data to gaining insights and conducting advanced analyses, the process can be time-consuming and complex. Enter Copilot in Excel: a powerful tool designed to simplify and transform data analysis. We recently announced the general availability of Copilot in Excel. Now at Ignite, we’re excited to share the following announcements:

Copilot in Excel with Python is generally available in US (EN-US) for Windows
You can create a table specific to your needs with Copilot
Copilot helps you pull in data from your organization and search the web
Copilot's enhanced text analysis capabilities provide new ways to reason over your data and derive insights

Read on for more details!

Copilot in Excel with Python is generally available

We’re excited to announce that Copilot in Excel with Python is generally available. For deeper, more complex analyses, Copilot offers advanced capabilities that were previously out of reach for many users. With the ability to describe the desired analysis in natural language, Copilot can automatically generate, explain, and insert Python code into Excel spreadsheets. This lowers the barriers to entry, making advanced analytics accessible to a broader audience, and unlocks powerful analytics via Python for visualizations, cleaning data, machine learning, predictive analytics, and more - without needing to be Python proficient yourself. Copilot in Excel with Python also renders stunning visuals that were previously not possible or were difficult to create. Unique visuals like heatmaps, pairplots, multiplots, and violin plots help you understand and communicate your analysis.

Copilot in Excel with Python conducts advanced analysis to run a Monte Carlo simulation.

Create a table specific to your needs

Whether creating a project budget, inventory tracker or sales report, starting from scratch to create a spreadsheet that meets your needs can be daunting and time consuming. Copilot in Excel’s new start experience enables people of all skill levels to create a personalized table for their tasks. Simply tell Copilot what you want to create, and Copilot will suggest and refine a template with headers, formulas and visuals to get you off to a great start!

For instance, you can quickly create a table with information on your organization's sales team. Copilot can add columns, apply conditional formatting, and even change table colors on command, streamlining the creation and adjustment process. Once you’re happy with your table you can insert it on the page and continue using Copilot.

A person uses Excel and clicks on the Copilot icon which opens a window. The person types a prompt asking for a table and Copilot creates the table. The person asks for additional adjustments which Copilot makes before inserting the table into the spreadsheet.

These updates will begin to roll out to Windows and web by the end of the year for those with a Microsoft 365 Copilot license.

Pull in data from the graph and search the web

Once you have the structure of your table, it's time to gather information you can add to it - Copilot can help here too. We’re excited to announce that Copilot in Excel can reference Word, Excel, PowerPoint, and PDF files from your organizational data. For instance, you can ask Copilot in Excel to list the announcements from a newsletter drafted in Word. Copilot will respond with a list which you can insert into a new spreadsheet or copy and paste into your existing table.

You can stay in the flow of your work as you gather organizational information too. For example, you can ask Copilot for all the employees who report to a specific manager and insert this list into a spreadsheet.

You can also seamlessly search the web directly within Copilot in Excel to find public information like dates, statistics, and more without disrupting your workflow. For instance, you can look up a table of countries and their exchange rates. Easily copy and paste info into your table.

Copilot in Excel gathers public data from the web.

One of the top customer requests is to import data from another Excel file. This is possible with Power Query but doing so takes time and knowledge not to mention that searching for the right file can be frustrating. Now you can ask Copilot for help finding and importing a table from an Excel file using everyday language. For example, say you want to add budget details to your sales team table. You can describe the data that you need and Copilot imports the relevant data from another Excel file to your sales team table. Because this import is powered by Power Query, it brings precise data directly from your organization's data sources, as a live refreshable connection. As your data is updated in the budget file, it also updates in your sales team table.

Copilot in Excel syncs data in a table from another Excel file.

These updates will begin to roll out by the end of the year to Windows and web for those with a Microsoft 365 Copilot license.

Text analysis updates help you understand your data better

Once data is prepared, the next crucial step is analysis. Copilot in Excel empowers users to gain insights without becoming spreadsheet specialists. And a lot of data is text-heavy such as customer feedback and survey results. Copilot in Excel’s text updates can help you understand your data even better.

You can simply ask Copilot to summarize your text data and now Copilot includes citations helping you understand the source – this saves time and helps ensure accuracy.

Copilot in Excel provides a summary with citations.

Sometimes you may want to dig deeper to sort and rank the text data by topics. You can now just ask Copilot to do so. This helps you analyze unstructured text data much faster. For example, say you have thousands of customer reviews. Copilot can break them down into several topics like quality, service, and price, helping you quickly identify which topic you want to look into further. You can also insert the topics into a new column in the table to preserve the analysis results and enable the creation of PivotTables and charts to visualize topic quantities and trends, so you can decide what to do next without having to read through everything.

Copilot in Excel identifies topics in a sheet of survey data.

Copilot can help you understand the sentiment of text too, which is especially helpful with survey data. For example, you can ask “please summarize sentiment for column 'feedback'” and Copilot will analyze and provide insight on positive, negative, and neutral sentiment. Copilot gives you an option to insert sentiment data into the table as a new column.

Copilot in Excel identifies the sentiment in a sheet of survey data.

Text analysis is particularly valuable for organizations that rely on qualitative data to inform decision-making. By transforming raw text into actionable insights, Copilot in Excel helps professionals make data-driven decisions confidently and efficiently. Text analysis updates will begin to roll out by the end of the year to Windows, web, Mac, and iOS for those with a Microsoft 365 Copilot license.

Learn more

Copilot in Excel is a game-changer for data-driven professionals across industries and roles. By simplifying data preparation, enhancing analytical capabilities, and enabling advanced analysis, it transforms the way data is managed and utilized. Check out the Ignite sessions below for more information. Please provide feedback in the app!