Microsoft Excel to let you run Python scripts as formulas
Microsoft is adding the Python programming language to Microsoft Excel, allowing users to create powerful functions for analyzing and manipulating data.
The public preview of the feature is now available to Microsoft 365 Insiders in the Beta channel, with the goal to ultimately roll out the feature to Excel for Windows in 16.0.16818.2000.
However, even if you join the Microsoft 365 Insiders Beta channel to test the new feature, there is no guarantee that Python in Excel will be available, as Microsoft is rolling it out slowly to test the feature.
Python in Excel
The new Python in Excel feature brings a new ‘PY’ function that allows users to embed Python code directly in a cell to be executed like any macro or regular Excel function.
However, instead of running the Python scripts locally, Excel will execute the code in the cloud using a hypervisor-isolated container on Azure Container Instances. Microsoft says this container environment will include Python and a curated set of Anaconda libraries to prevent security issues.
These libraries include the data visualization and analysis tool ‘pandas’ and the visualization tool ‘Matplotlib.’
As the Python scripts will run in an isolated container, they will not have access to any local resources, including the local network, computer, files, and a Microsoft 365 authentication token.
To embed a Python script in Excel, users will use the =PY() function to open a text area where they can enter the Python code they wish to execute.
The code is then executed in the cloud container, and the results are sent back and displayed in the worksheet. Microsoft says this is all done anonymously so that your Python code is not linked back to a particular user.
“Python in Excel makes it possible to natively combine Python and Excel analytics within the same workbook - with no setup required,” Microsoft explains in an announcement.
“With Python in Excel, you can type Python directly into a cell, the Python calculations run in the Microsoft Cloud, and your results are returned to the worksheet, including plots and visualizations.”
Using the Python Panda library in Excel
Source: Microsoft
Microsoft treats Python in Excel like other embedded scripting languages, automatically blocking them if a document contains a Mark of The Web (MoTW).
Windows automatically adds MoTW flags to all documents and executables downloaded from untrusted sources, such as the internet, using a special ‘Zone. Id’ alternate data stream.
These MotW labels tell Windows, Microsoft Office, web browsers, and other apps that the file should be treated with suspicion and will cause the document to be opened in Protected View, preventing the execution of macros and embedded Python scripts.
“If you open a workbook that contains Python code from the internet, Excel Protected View won’t run Python formulas in the workbook. If a workbook is opened with Microsoft Defender Application Guard, Python formulas don’t run by default,” explains Microsoft.
To test Python in Excel, join the Microsoft 365 Insider Program and enroll in the Beta channel. However, as previously said, this feature may take some time to roll out to everyone