How to Use Python in Excel: A Step-by-Step Tutorial

 How to Use Python in Excel

A Step-by-Step Tutorial

Python is a popular programming language that can be used for data analysis, visualization, machine learning, and more. Excel is a powerful spreadsheet application that can handle large amounts of data, perform calculations, and create charts and graphs. What if you could combine the best of both worlds and use Python in Excel?

Well, now you can, thanks to a new feature called Python in Excel. This feature allows you to use Python libraries and plots in Excel, without leaving the Excel environment. You can write Python code directly in Excel cells, reference Excel objects like ranges and tables in your code, and control how Python calculations are returned.

In this tutorial, I will show you how to use Python in Excel, and give you an example of creating a plot of some data. Let’s get started!

How to Install Python in Excel

To use Python in Excel, you need to join the Microsoft 365 Insider Program and choose the Beta Channel Insider level. This will give you access to the latest builds of Excel that support Python in Excel.

To join the Insider Program, follow these steps:

1.     Open any Office app, such as Word or Excel.

2.     Select File > Account (or Office Account if you opened Outlook).

3.     Under Product Information, select Update Options > Update Now.

4.     After the update is installed, restart your Office app.

5.     Select File > Account (or Office Account if you opened Outlook).

6.     Under Product Information, select Update Options > Join Office Insider.

7.     In the Join the Office Insider dialog box, select Join Office Insider.

8.     Choose Beta Channel from the list of options and select OK.

9.     Restart your Office app.

You have now joined the Insider Program and can start using Python in Excel.

How to Use Python in Excel

Once you have joined the Insider Program, you can start using Python in Excel by following these steps:

1.     Select a cell and on the Formulas tab, select Insert Python. This tells Excel that you want to write a Python formula in the selected cell.

2.     Enter =PY in the cell, and then choose PY from the function AutoComplete menu with the Down arrow and Tab keys, or add an opening parenthesis to the function: =PY (. Now, you can enter Python code directly into the cell.

3.     To reference Excel objects like ranges, tables, queries, and names in a Python cell, use the custom Python function xl (). For example, to reference cell A1 use xl (“A1”) and for the range B1:C4 use xl (“B1:C4”).

4.     Use the Python output menu in the formula bar to control how Python calculations are returned. You can choose from Value, Plot, Table, or JSON.

5.     To refresh your Python calculations, press F9 or select Data > Refresh All.

Example: Creating a Plot of Some Data

Here is an example of using Python in Excel to create a plot of some data:

• In cell A1, enter x
• In cell B1, enter y
• In cells A2:A11, enter the numbers 1 to 10
• In cells B2:B11, enter the formula =A2^2
• Select cell C1 and on the Formulas tab, select Insert Python
• Enter =PY (xl ("A1:B11", headers=True)) in cell C1
• In the formula bar, select Plot from the Python output menu
• You should see a plot of y = x^2 in cell C1

Here is how it looks like:

![Python in Excel plot example]

Conclusion

Python in Excel is a new feature that allows you to use Python libraries and plots in Excel. You can write Python code directly in Excel cells, reference Excel objects like ranges and tables in your code, and control how Python calculations are returned.

This feature is currently available only for Microsoft 365 Insiders who choose the Beta Channel Insider level. If you want to try it out, you need to join the Insider Program and update your Office apps.