Microsoft Corp. is releasing a new version of Excel that will enable users to write and run Python code directly in the spreadsheet editor’s interface. 

Stefan Kinnestrand, a general manager for product marketing at Microsoft, detailed the update in a blog post today. The Python support is currently available in public preview.

Excel includes data science features that can be used for tasks such as visualizing business information and finding the average value of customer purchases. According to Microsoft, the newly added Python support will extend those features. The programming language can be used to create more advanced data science workflows than those that Excel supports out of the box.

A worker could, for example, write a Python script that scans the data in an Excel spreadsheet and finds duplicate fields. Those fields can then be deleted to prevent them from lowering the accuracy of analyses. Furthermore, Python can be used to create data visualizations and train machine learning models.

“You can manipulate and explore data in Excel using Python plots and libraries, and then use Excel’s formulas, charts and PivotTables to further refine your insights,” Kinnestrand wrote. 

Many of Python’s data science features are powered by Python modules. Those are prepackaged code components developed by the open-source community. Python modules usually have to be installed and configured manually by users, which can be a time-consuming process.

The new version of Excel skips that step. According to Microsoft, it runs on a version of Python called Anaconda Python that includes many popular data science modules in a preinstalled form. As a result, users don’t have to download any additional components or change settings to use Excel’s Python features.

One of the Python modules that Excel includes is Matplotlib. It allows users to run advanced statistical analyses on the data in a spreadsheet. Matplotlib supports several analysis methods including linear regression, which is commonly used in the enterprise to generate sales forecasts. Excel also supports seaborn, a module that can visualize the results of a Matplotlib-powered analysis in graphs.

Both Excel and Python are widely used in the enterprise. As a result, some data science teams already rely on Python scripts to analyze data in their spreadsheets. Until now, those scripts had to be written and run in a separate code editor, which required data scientists to regularly switch tabs.

Excel’s built-in Python support should simplify the workflow. According to Microsoft, users can add Python code to a spreadsheet without leaving the Excel interface. They must simply select a spreadsheet cell, type in their code with the prefix “=py” and specify the data on which the code should run.

Workers can import data into Excel for analysis using a set of built-in connections. Alternatively, they may use Microsoft’s Power Query tool for the task. The tool can not only import records into Excel from external applications but also change the data’s format to simplify processing.

Image: Microsoft