Microsoft teases Python scripting in Excel

Microsoft has added support for running Python code from within Excel, bringing the world's most popular programming language, by some measures, to the world's most popular spreadsheet.

Sorry, Visual Basic.

"Today, we are excited to introduce the public preview of Python in Excel – making it possible to integrate Python and Excel analytics within the same Excel grid for uninterrupted workflow," said Stefan Kinnestrand, general manager of product marketing at Microsoft, in a blog post.

To make this happen, Microsoft has partnered with Anaconda, which is providing its data science-oriented Python distribution, including popular libraries like Pandas and Matplotlib.

“I am thrilled to announce the integration of Anaconda Distribution open data science platform for Python into Microsoft Excel – a major breakthrough that will transform the workflow of millions of Excel users around the world,” gushed Anaconda CEO and co-founder Peter Wang, in a statement.

“This integration represents a significant milestone that accelerates the adoption of Python and fulfills a long-standing request from our community."

The hope is that easy access to Python will let Excel users create more sophisticated visualizations, data manipulation, analytics, and machine learning models with their spreadsheet data.

• Updated Python support in VS Code brings browser editing and ditches open-source language server for Pylance
• SAS backs Python as alternative to its own language
• Fed up with Python setup and packaging? Try a shot of Rye
• Thinking about upgrading to Debian Bullseye? Watch out for changes in Exim and anything using Python 2.x

Anaconda's Python will live in a cozy, temperature controlled Microsoft Azure environment. The rationale for doing so is that this keeps Excel users from having to manage the potential complexity of Python configuration on a local machine.

Also, the cloud habitat for Anaconda's Python will presumably be more secure than having Excel users downloading libraries on their own. Having anticipated concerns about enabling yet another attack vector in Excel, Microsoft has preemptively declared that its snake has been tamed.

In a post on data security and Python in Excel, Redmond lists the following plus points.

• Excel's Python code runs on hypervisor isolated containers built on Azure Container Instances.
• The container has Python and a curated set of secured libraries provided by Anaconda.
• The Python code does not have access to the user's computer, devices, account, the network, any user token, or workbook properties, including formulas, charts, PivotTables, macros, and Visual Basic code.

Python developers have long been able to craft code that reads and writes data to and from Excel spreadsheets via libraries like openpyxl. But Microsoft appears to be betting that its backend management and code-ready spreadsheets will be a more appealing option.

Writing Python code in the Excel public preview – currently available through the beta channel to those in the Microsoft 365 Insiders program, and coming first to Excel for Windows, build 16818 – is simply a matter of invoking the Python function (=PY) and writing Python code in that spreadsheet cell.

"Python in Excel uses the custom Python function xl() to interface between Excel and Python," Microsoft explains in its documentation. "The xl() function accepts Excel objects like ranges, tables, queries, and names."

Importing external data can be done with Excel's built-in connectors and Power Query, Microsoft's data manipulation tool.

As this is just a public preview, Kinnestrand said users should expect further improvements related to editing, like autocomplete and syntax highlighting, error behaviors, and so on. Presently, he said, there are data size and compute limitations to prevent abuse, which is to say excessive demands on Microsoft Azure resources. ®