Excel gets Python – who, when and why

Excel 365 now has access to the Python language and packages. We’ll explain what that means, who gets it and why Python in Excel is a big deal.

Python is a high-end and widely used programming language with many features in its many packages or add-ons. It’s this last bit that makes Python an important extension for Excel.

Why Python? What’s the big deal?

Why Python in Excel? Surely Excel has enough programming languages to be getting on with?  After all Excel already has Visual Basic, Javascript, Office Scripts, M language in PowerQuery and the entire range of functions/formulas already – why one more?

Python isn’t just a programming language. There are thousands of Python libraries that can do amazing things with just a few lines of code.  Instead of trying to copy all those features into Excel, Microsoft has chosen to make the Python library system available directly.

Python in Excel requirements

Before you get too excited, there are several requirements for getting Python access in Excel. Requirements that are usually hidden in the ‘fine print’ text.

Python in Excel is only available to some Microsoft 365 Insiders Beta Channel on Windows. Starting with build 16.0.16818.2000, but it’s a gradual rollout so if Python isn’t appearing in your Excel 365, be patient.

When Excel with Python goes public, it might be limited to only some high-end Microsoft 365 plans or there could even be an additional fee. All Microsoft’s saying now is “After the Preview, some functionality will be restricted without a paid license.” which is typically vague Microsoft-speak open to several interpretations (which paid licenses? separate paid license?).

Extra cost for Python access would be justified by the company because Microsoft has to supply special cloud infrastructure to make it all happen.

Do you have Python in Excel 365?

See if you have Python features in Excel 365 by trying to insert the new PY() function. In a new cell type =PY and see if the function appears on the list.

Python Charting in Excel

Excel Charting has been getting old and tired compared to other products.  Python libraries like Matplotlib or seaborn can make charts that Excel alone can’t even imagine.

Predictions, Forecasting and Machine Learning

Using scikit-learn or statsmodels libraries, add forecasting, machine learning and predictive analysis.

Data Cleaning and Normalization

Excel has limited options for tidying up or normalizing data before analysis.  PowerQuery was an improvement but there’s still a lot needed.

Enter Python with access to obvious options like removing duplicates and find missing values but also proper Regular Expressions.

PowerQuery and Python

With all the data cleaning and visualization power, a natural question is how Python integrates with PowerQuery.

There’s no direct Python link into PowerQuery, however tables made by PowerQuery into an Excel worksheet do work with Python.

How Python works in Excel

Python does NOT run in Excel on your computer, it’s a cloud service.  The new Excel PY() function connects to Microsoft’s servers, sets up a Python instance and runs the code you’ve setup.

The company makes its usual vague privacy promises, but the hard fact is still that your Excel data and code is passed to Microsoft.  The company can copy that information and share with others, they might be legally required to.  In other words, the same privacy risk that applies to all cloud services.

You can’t link Excel to an alternative Python instances, for example running on the same computer or an in-house company server.

Learn More

There are already many resources explaining Excel with Python. Excel and Python enthusiasts are very interested. Experts in both Excel and Python are so excited they need medication to calm themselves😁

Microsoft’s Getting Started page is a good place to start in understanding the crucial differences in how the PY() function works. In particular the difference between a result being added as a simple Excel value into a cell or a Python object especially the DataFrame object.

Anaconda (who is supplying the Python code and packages to Microsoft) has an opening page usefully split into help with Excel users who need to understand Python or Python users coming to Excel.  Scroll down to the Resources including a sample Excel template.