Microsoft’s AI Copilot will now help you create pivot tables and write Python code inside Excel

Microsoft Excel is the de facto standard for number crunching for many companies, but even some casual users don’t know everything that’s possible in a spreadsheet program.

And many data scientists and machine learning experts still prefer to do some of their work with other tools, including the Python programming language and its popular data science libraries.

Now, Microsoft is looking to cater to both of those audiences, making available both an AI driver that can guide your use of Excel and access to a place to write and run Python code directly within Excel. Copilot can create formulas and pivot tables to answer a specific question, add complex conditional formatting to highlight spreadsheet cells and rows with certain characteristics, and—in a feature expected to roll out to some users in the next few months—insert “Advanced. Analysis mode” that uses Python data libraries to analyze and visualize data.

“We see productivity AI as a way to revolutionize the way people are productive in spreadsheets,” said Catherine Pidgeon, head of product for Excel.

Over time, while available in limited quantities, Excel Copilot has grown in sophistication. It gained the ability to analyze a range of cells within spreadsheets instead of just full tables, expanded the range of pivot tables and charts that can help create, and gained access to some of the most powerful Excel formula functions, such as XLOOKUP for linking data between tables, that even power users often consult the manual before using .

It’s also better at explaining what it does, providing an explanation of the formulas or Python code it’s writing and generally prompting the user to make sure it’s what they want to do before making changes.

“We see this as providing more visibility into how Copilot thinks, both so users can better learn how to interact with Copilot, but also so they can quickly confirm if that’s really what they were looking for in response,” said Pidgeon.

Users can also ask Copilot to further explain what it does or how a certain feature works, or even its advice on how to analyze certain data, such as how to interact with other AI systems such as ChatGPT. And when users use the Advanced Analysis mode, Copilot automatically creates a new table for its task, leaving the raw input data unchanged. That helps protect against accidentally changing the input, and allows users to automatically restart the analysis if the input data changes over time.

“It’s like the most important design principle we have in Copilot in Excel,” said Carlos Otero, Excel’s principal product manager. “All the analysis and all the output we generate on the page needs to be put in a way that is renewable, editable, and verifiable.”

In the presentation, he highlighted how the Advanced Analysis Mode can be used to create scatterplots and clusters of spots on a set of sales data, and even find recurring words in related product reviews using the Natural Language Toolkit, a powerful and widely used open source Python. the library. Copilot can write the necessary Python code and explain how it works, and users are free to modify it as they wish. Python code written by users or Copilot runs on a secure cloud server, with a common set of common libraries for tasks such as data analysis and machine learning available.

Pidgeon says that so far, Copilot has proven popular with novice users who appreciate the help as they learn Excel’s features and advanced users who see it as a time saver, including some on Microsoft’s finance teams. And the Python functionality will allow users to perform data analysis work directly within Excel that they previously had to do with other software, with the same Copilot to help. It’s a huge potential improvement for data analysts who would previously need to sync data back and forth between their Excel sheets and Python scripts.

“You can see and edit code right in the application you use every day to analyze your data,” says Pigeon. “You don’t have to go anywhere else.”