Python is one of the most popular programming languages for data analysis, thanks to its rich set of libraries and tools that make it easy to manipulate, visualize, and model data. Excel is one of the most widely used spreadsheet applications for data entry, calculation, and reporting. What if you could combine the best of both worlds and use Python in Excel?

Well, now you can. Microsoft has recently announced the public preview of Python in Excel, a feature that allows you to access Python directly from the Excel ribbon and use its capabilities in your spreadsheets. This means you can use Python formulas, plots, and libraries in Excel, and leverage Excel’s features like PivotTables, charts, and Power Query to further refine your insights.

In this article, we will show you how to use Python in Excel and what benefits it can bring to your data analysis workflow.

How to Enable Python in Excel

To use Python in Excel, you need to join the Microsoft 365 Insider Program and opt for the beta channel to get the latest Excel builds. You also need a Microsoft 365 subscription that includes Excel. Once you have these prerequisites, you can enable Python in Excel by following these steps:

• Open Excel and go to File > Options > Trust Center > Trust Center Settings > Trusted Add-in Catalogs.
• In the Catalog URL box, enter https://excel.officeapps.live.com/insider/pythonaddin and click Add Catalog.
• Check the Show in Menu checkbox and click OK.
• Restart Excel and go to Insert > Get Add-ins.
• In the Office Add-ins dialog box, click Shared Catalog.
• Select Python in Excel and click Add.

You should now see a new Python tab in the Excel ribbon.

How to Use Python Formulas in Excel

One of the main features of Python in Excel is the ability to use Python formulas in your cells. You can write any valid Python expression that returns a single value and use it as a formula. To do this, you need to prefix your expression with =PY( and end it with ). For example, if you want to calculate the square root of a number in cell A1, you can write =PY(math.sqrt(A1)) in another cell.

You can also use cell references, named ranges, and array constants as arguments for your Python formulas. For example, if you have a range of numbers named Data, you can write =PY(sum(Data)) to get the sum of the numbers.

You can also use Python libraries in your formulas, as long as they are supported by Microsoft. Microsoft has partnered with Anaconda, an enterprise Python repository, to bring the most popular Python libraries to Excel, including pandas, statsmodels, and Matplotlib. You can import these libraries using the standard import statement in your formulas. For example, if you want to create a pandas DataFrame from a range of cells, you can write =PY(pandas.DataFrame(A1:C10)) in another cell.

How to Use Python Plots in Excel

Another feature of Python in Excel is the ability to create plots using Python libraries like Matplotlib and Seaborn. You can use these libraries to create various types of visualizations like heatmaps, violin plots, and swarm plots. To do this, you need to select a range of cells that contains your data and click on Insert > Python Plot in the ribbon. This will open a dialog box where you can write your Python code to generate the plot.

For example, if you have a range of cells that contains the height and weight of some people, you can create a scatter plot using Matplotlib by writing this code:

import matplotlib.pyplot as plt
plt.scatter(data[:,0], data[:,1])
plt.xlabel('Height')
plt.ylabel('Weight')
plt.show()

The variable data represents the selected range of cells as a NumPy array. You can use any valid Matplotlib code to customize your plot. Once you click OK, the plot will be inserted into your worksheet as an image.

You can also use Seaborn to create more advanced plots like heatmaps or violin plots. For example, if you have a range of cells that contains the scores of some students on different subjects, you can create a heatmap using Seaborn by writing this code:

import seaborn as sns
sns.heatmap(data)
plt.show()

The variable data represents the selected range of cells as a pandas DataFrame. You can use any valid Seaborn code to customize your plot.

How to Use Power Query with Python

Power Query is a feature in Excel that allows you to connect to various data sources, transform and clean your data, and load it into your workbook. You can use Power Query with Python to access external data sources that support Python connectors or run custom Python scripts on your data.

To use Power Query with Python, you need to go to Data > Get Data > From Other Sources > From Python Script in the ribbon. This will open a dialog box where you can write your Python code to get the data. For example, if you want to get some data from a web API that supports Python requests, you can write this code:

import requests
response = requests.get('https://api.example.com/data')
data = response.json()

The variable data represents the data that you want to load into your workbook. It can be any Python object that can be converted into a table, such as a list, a dictionary, or a pandas DataFrame. Once you click OK, the data will be loaded into the Power Query Editor, where you can further transform and clean it before loading it into your worksheet.

Benefits of Using Python in Excel

Using Python in Excel can bring many benefits to your data analysis workflow, such as:

• You can leverage the power and flexibility of Python to perform complex calculations, data manipulation, and modeling that are not possible or easy with Excel formulas.
• You can use Python libraries to create stunning and interactive plots that can enhance your data visualization and storytelling.
• You can access external data sources that support Python connectors or run custom Python scripts on your data using Power Query.
• You can combine Python and Excel features to create hybrid solutions that suit your needs and preferences.

Wrap-Up

Python in Excel is a new feature that allows you to use Python in your spreadsheets. You can use Python formulas, plots, and libraries in Excel, and leverage Excel’s features like PivotTables, charts, and Power Query to further refine your insights. This feature is currently in public preview for Microsoft 365 Insiders in the Beta Channel. If you want to try it out, you can join the Microsoft 365 Insider Program and enable Python in Excel following the steps we showed you in this blog post.

We hope you found this guide helpful and learned how to use Python in Excel. If you have any questions or feedback, please let us know in the comments below. Happy coding!

-------------------------------------------

Credit: Cover Photo by Lukas Blazek on Unsplash