Microsoft unveils Python integration for Excel; Here’s what to expect

Microsoft Excel, which was introduced in 1985, has been a valuable asset for both businesses and individuals for nearly 40 years. Microsoft has effectively utilized this advantage by continuously introducing new features that appeal to a wider audience and encourage user loyalty. This software enables users to efficiently arrange, analyze, and present their data. In June, Microsoft unveiled significant enhancements to its spreadsheet editor, including improved visualization capabilities, the integration of Office Scripts, suggested formulas, and various other updates.

Now Microsoft has announced more features coming to Excel through integration with the high-level programming language Python.

Microsoft Excel gets Python integration

On August 22, Microsoft announced that it would bring Python to Excel through a public preview. It integrates natively with the Excel grid and can be used with the Excel ribbon without having to install it separately. Users can take advantage of its functionality by entering Python code directly into cells using the PY function, Microsoft announced in a blog post.

Users can use Power Query and connectors built into Microsoft Excel to import data from external sources into Python-enabled Excel spreadsheets. It is compatible with existing tools such as charts, formulas, pivot tables and more. Python in Excel uses the Anaconda Distribution running on Azure to provide analytics services such as predictive analytics, visualization with Matplotlib and Seaborn, cleaning data and more.

Python in Excel also leverages machine learning, forecasting techniques, and predictive analytics based on Python libraries such as scikit-learn and statsmodels to provide a variety of services such as time series modeling, regression analysis, and more.

Stefan Kinnestrand, director of product marketing at Microsoft, said: “Today we’re excited to introduce the public preview of Python in Excel, enabling the integration of Python and Excel analytics in the same Excel spreadsheet for uninterrupted workflow.”

Using Python is Excel

Step 1: Open the worksheet and select the cell you want to apply the formula to.

Step 2: Go to the template ribbon and select “Add Python”. You can also use the PY function to enable Python directly in that cell. If enabled, the cell will turn green and display the PY function.

Step 3: If you want to reference Excel elements in a Python cell, make sure it’s in edit mode, then select the individual cell or range of cells you want to apply the Python formula to.

• Python for Excel uses the Python function xl() to communicate between Excel and Python, and accepts objects such as ranges, tables, queries, and more. It is also possible to enter references directly into a Python cell with the xl() function.
• To use code-like editing functions, such as the Enter key to create new lines of code, users can use the formula bar. You can also expand it to see multiple lines of code at once using Ctrl+Shift+U.
• Users can control the return of calculations using Python in Excel using the Python print menu in the formula bar. You can either receive calculations as Excel values and print them in the cell, or receive them as Python objects that contain additional information in the cells.
• Users can also import external data into Excel using the Get & Transform feature, although the data must be imported from either Power Query or an Excel workbook.

Microsoft announced that users can share Python Excel workbooks with teammates on platforms like Outlook and Teams without additional tools. As part of Microsoft 365, it works with enterprise-grade security in the Microsoft Cloud. The Python in Excel feature is currently in public preview for users of the Microsoft 365 Insiders program. They can use it in the Microsoft Excel beta channel.