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

Microsoft Excel is the de facto standard for a lot of corporate number crunching, but even some frequent users don’t know everything that’s possible to do in the spreadsheet program. 

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

Now, Microsoft is looking to cater to both of those audiences, making generally available both an AI copilot that can guide your use of Excel and access to a facility for writing and using Python code directly within Excel. The Copilot can create formulas and pivot tables to answer a particular question, add sophisticated conditional formatting to highlight spreadsheet cells and rows with particular properties, and—in a feature expected to roll out to some users over the next few months—enter a “Copilot in Excel with Python” mode that uses Python data science libraries to analyze and visualize data.

“We see generative AI as a way to revolutionize how people do productivity in spreadsheets,” says Catherine Pidgeon, head of product for Excel. 

Over time, while in limited availability, the Excel Copilot has grown more sophisticated. It’s gained the ability to analyze ranges of cells within spreadsheets rather than just full tables, expanded the range of pivot tables and charts it can help create, and gotten access to some of Excel’s more powerful formula functions, like XLOOKUP for linking data between tables, that even power users often consult the manual before they use.

It’s also gotten better at explaining what it’s doing, providing a description of formulas or even Python code that it’s writing and often prompting the user to confirm that’s what they’re looking to do before making changes.

“We see this as providing more visibility to the way that Copilot thinks, both so users can learn better how to interact with Copilot, but also so they can validate quickly if that’s actually what they were seeking as a response,” says Pidgeon. 

Users can also ask the Copilot to further explain what it’s doing or how a particular feature works, or even for its advice about how to analyze some data, similar to how they’d interact with other AI systems like ChatGPT. And when users utilize the “Copilot in Excel with Python” mode, the Copilot by default creates a new table for its work, leaving the raw input data unchanged. That helps safeguard against accidentally changing the input, and it also allows users to automatically rerun the analysis if the input data changes over time.

“It echoes a pretty important design principle that we have for Copilot in Excel,” says Carlos Otero, principal product manager for Excel. “All of the analysis and all of the output that we generate on the page needs to be insertable in a refreshable, and editable, and verifiable way.”

In a demonstration, he highlighted how the Python Copilot mode could be used to create scatterplots and spot clusters in a set of sales data, and even find recurring terms in associated product reviews using the Natural Language Toolkit, a powerful and widely used open source Python library. The Copilot can write the Python code necessary and even explain how it works, and users are free to tweak it as they wish. Python code written by either users or the Copilot runs on a secure cloud server, with a standard set of common libraries for tasks like data analysis and machine learning available.

Pidgeon says so far, the Copilot has proven popular both with less-experienced users who are grateful to have assistance as they learn more Excel features and advanced users who see it as a timesaver, including some within Microsoft’s own finance teams. And the Python functionality will let users do data analysis work directly within Excel that they previously had to do with other software, with the same Copilot in place to assist. It’s a potentially significant improvement for data analysts who previously would have needed to sync data back and forth between their Excel sheets and Python scripts.

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

https://www.fastcompany.com/91190721/microsoft-ai-copilot-pivot-tables-and-python-code-within-excel?partner=rss&utm_source=rss&utm_medium=feed&utm_campaign=rss+fastcompany&utm_content=rss