Python in Excel

This month sees “a significant evolution” in the analytical capabilities available within Excel by releasing a Public Preview of Python in Excel.  Python in Excel makes it possible to natively combine the programming language Python and Excel analytics within the same workbook - with no setup required.  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 visualisations.

Python in Excel is rolling out to Public Preview for those in the Microsoft 365 Insiders program, using the Beta Channel in Excel for Windows.

Given there are several languages out there that analysts use (e.g. R, SQL to name but two), it is not unreasonable to consider why is Python the first cab off the rank?  There are several reasons mentioned by the Microsoft team, which we summarise below.  These are detailed below.

Built for analysts

Every day, millions of users around the world rely on familiar Excel tools such as formulas, charts and PivotTables to analyse and understand their data.  As of this update, Python in Excel will also be natively integrated directly into the Excel grid.  To get started simply use the new PY function which allows you to input Python code directly into Excel cells.

For example, to create DataFrames with just a few simple clicks, it’s no more difficult than this:

Excel users now have access to powerful analytics via Python for visualisations, cleaning data, machine learning, predictive analytics, and more.  Users may now create end to end solutions that seamlessly combine Excel and Python all within Excel.  Using Excel’s built-in connectors and Power Query, users can easily bring external data into Python in Excel workflows.  Python in Excel is compatible with the tools users know well, such as formulae, PivotTables and Excel charts. 

An example of an advanced visualisation, for instance, might be the following.  You can tap into the potential of well-known Python charting libraries like Matplotlib and seaborn to create a wide variety of charts, spanning from conventional bar graphs and line plots to specialised visualisations such as heatmaps, violin plots and swarm plots.

There is more though.  You can consider the impact of machine learning, predictive analytics and forecasting.  For examples, you may leverage the capabilities of Python libraries like scikit-learn and statsmodels to apply popular machine learning, predictive analytics, and forecasting techniques such as regression analysis, time series modelling, etc.  You might even throw in a LAMBDA along the way!

You can consider data cleansing too.  You may make efficient use of advanced data cleaning techniques such as locating missing values, standardising formats, removing duplicates and employing techniques like regular expressions for pattern-based transformations.

Python analytics via Anaconda

Anaconda is a leading enterprise Python repository used by tens of millions of data practitioners worldwide.  Python in Excel leverages Anaconda Distribution for Python running in Azure, which includes the most popular Python libraries (e.g. pandas, Matplotlib, scikit-learn), and is securely built, tested and supported by Anaconda. Python provided by Anaconda supports a wide array of analytics with Python in Excel.

Runs securely on the Microsoft Cloud, with no setup required

Python code used by Excel runs on the Microsoft Cloud with enterprise-level security as a compliant Microsoft 365 connected experience.  The Python code runs in its own hypervisor isolated container using Azure Container Instances and secure, source-built packages from Anaconda through a secure software supply chain.  Python in Excel keeps your data private by preventing the Python code from knowing who you are, and opening workbooks from the internet in further isolation within their own separate containers.  Data from your workbooks can only be sent via the built-in xl() Python function, and the output of the Python code can only be returned as the result of the =PY() Excel function.

Built for teams

Users can share Python in Excel workbooks with confidence.  Teammates can seamlessly interact with and refresh Python in Excel based analytics without needing to worry about installing additional tools, Python runtimes or managing libraries and dependencies.  Users may share workbooks using collaboration tools such as Microsoft Teams and Microsoft Outlook and work together seamlessly via comments, @mentions, and co-authoring with colleagues as they normally would in Excel.  Sensitivity labels applied to your workbooks containing Python will keep them compliant with your organisation's information protection policies too.

Python in Excel is currently available to users running Beta Channel on Windows.  This feature will roll out to Excel for Windows first, starting with build 16.0.16818.20000, and then to the other platforms at a later date.  To use Python in Excel, join the Microsoft 365 Insider Program.  Then, choose the Beta Channel Insider level to get the latest builds of the Excel application.  

We shall raise our own thoughts and findings in due course…