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.
