Microsoft Excel continues to evolve, and one of its most exciting recent updates is native Python integration. Excel now supports Python functions (in beta access), allowing users to write and execute Python code directly within their spreadsheets.
But what exactly is Python? While it’s a powerful programming language, itโs not just for developers. Python is widely used for data analysis, automation, and machine learning, making its integration with Excel a game-changer for users looking to enhance their workflow. In this article, weโll explore how to use Python in Excel and, more importantly, exactly why it’s so powerful.
Table of Contents
How To Use Python in Excel
To use Python in Excel, follow these steps:
- Ensure you have Python integration enabled in Excel (available for Microsoft 365 Insiders – Beta Channel).
- Open an Excel worksheet and click on the cell where you want to enter Python code.
- Use the Python formula:
=PY(
- Write the Python code you want to execute inside the function.
- Separate parameters with a comma if needed.
- Close the brackets and press “Enter” to run the Python script.
You can also use Python to interact with Excel. To do that, you’ll need to get Python for your computer.
To use it with Google Sheets, check out our video.
What is Python in Excel: Basics
Python is a high-level programming language known for its clear syntax and extensive libraries. Itโs widely used for:
- Data analysis and automation
Machine learning and AI
Statistical modeling and forecasting
With Excel’s Python integration, advanced users can now write and execute Python code alongside traditional Excel formulas in the same spreadsheet.
Benefits of Using Python in Excel
The introduction of Python in Excel brings several powerful benefits:
1. Seamless Python Access
With the new PY function, you can directly enter Python code into Excel cells and combine it with Excel formulas for enhanced data analysis.
2. Advanced Automation
Using Python libraries like openpyxl and xlwings, you can automate repetitive Excel tasks such as:
import openpyxl
# Load an existing Excel file
wb = openpyxl.load_workbook(“report.xlsx”)
sheet = wb.active
# Modify a cell value
sheet[“A1”] = “Updated with Python”
# Save the file
wb.save(“report_updated.xlsx”)
3. Data Visualization Beyond Excel Charts
Python expands Excelโs visualization capabilities, enabling advanced charts like heatmaps, violin plots, and swarm plots.
import matplotlib.pyplot as plt
import pandas as pd
# Create a sample dataset
data = pd.DataFrame({“Category”: [“A”, “B”, “C”], “Values”: [30, 45, 25]})
# Create a bar chart
plt.bar(data[“Category”], data[“Values”])
plt.title(“Advanced Chart in Excel using Python”)
plt.show()
4. Secure Cloud Execution
Python calculations run on the Microsoft cloud, ensuring data security and enterprise-level protection for businesses.
5. AI and Predictive Analytics
By leveraging machine learning libraries like scikit-learn
, Excel users can predict trends and automate data analysis.
from sklearn.linear_model import LinearRegression
import numpy as np
# Sample data
X = np.array([1, 2, 3, 4, 5]).reshape(-1, 1)
y = np.array([10, 20, 30, 40, 50])
# Create and train a model
model = LinearRegression()
model.fit(X, y)
# Predict the next value
prediction = model.predict([[6]])
print(“Next predicted value:”, prediction[0])
6. Integration with Popular Python Libraries
Excel’s Python integration provides access to powerful libraries, including:
pandas
โ for data manipulationmatplotlib
โ for visualizationseaborn
โ for statistical plotsscikit-learn
โ for machine learning
To import these libraries into Excel, simply use the Python formula within a cell or script them using xlwings
.
Can You Use Python in Excel?
Yes, according to Microsoft, if you’re a business or Enterprise customer you can now use Python in Excel.
When Will Microsoft Add Python to Excel for Users
Currently, Python is only available in Excel as a โPreviewโ for Microsoft Insider Beta users, which requires users to set up and join the program. As of this writing, Pythonโs creator has yet to release the program to other users, nor have they announced an official date of when it will be available. Then it’s rolling out to Excel for Windows first.
If you aren’t in that category, be patient. The full availability is imminent.
How To Get the Excel Python Integration Early
If you want access to the PY function early, you must join the Microsoft Insider Program to get the latest builds. Depending on how you joined Microsoft, you may need to pay the subscription if you don’t have a paid license.
Here is how to join the insider program for using Python in Excel:
- Open a Microsoft 365 desktop app on your PC, like Excel or Word
- Click on the โFileโย option towards the top-left part of the screen
- Click on the โMoreโย option and choose the โAccountโย option
- Locate your license type under the โProduct Information.โ Determine if you can join the Insides Program by comparing your license to the following:
- Family and Personal licenses can join insider builds
- Business and Education licenses require the administrator to enable insider builds
- Perpetual licenses do not receive feature updates, so you won’t be able to join the insider program
- Click on the โMicrosoft 365 Insiderโย button. Here, you will be prompted to choose the insider level:
- Preview: This build is best for people who want the best of two worlds: To adopt new features early and get stable but less frequent updates
- Beta: This build is best for users who want to get the latest build early. The features in this build are still under development and can be unstable. We recommend using this channel to get the Python in Excel function.
- Once you have selected the insider channel you want to join, click the โcheckboxโ to agree to the insider program terms and conditions
- Click on the โOKโ button, and the download for the new build should start
- After the installation is finished, restart your computer and Excel
Python Excel Function Syntax
There are multiple ways to use a function in Excel. One of the most popular methods to use a formula is typing the Function formula in the โfxโ textbox.
Here is the Python for Excel formula:
=PY(code, type)
The formula requires two parameters to work. These are:
- code: Thisย parameter is the Python code you want to run in your Excel spreadsheet. Ensure the code is static text, as dynamic code can sometimes cause unexpected results or errors.
- type: This parameter specifies the return type desired by the user. This has to be a static number. For example, 0ย for an Excel value or 1ย for a Python plots object. Inputting any other number will result in an error.
The PY formula canโt be used with other Excel functions, so ensure both parameters use static parameters. Furthermore, formulas canโt be input if you use them alongside other functions.
For example, the FORMULATEXT function can detect the formula for Python in Excel, and Excel add-ins can edit the function.
Frequently Asked Questions
Can Python Replace VBA?
Python is a general-purpose programming language that can be used for data analysis, data visualization, and documentation, whereas VBA is designed for automating tasks in Office.
VBA is built into Office, meaning that it has better compatibility. The PY function can not replace the functionality of VBA, as the Python code runs in the Microsoft Cloud, whereas VBA code runs natively.
Can I Use Python With Excel?
Microsoft Excel users can now use Python in Excel formulas to run code in an Excel spreadsheet. However, they must join the Beta program. This allows users to use the native Excel tools, including Excel’s formulas, in the same workbook.
Wrapping Up
Python in Excel is a new way to analyze data and perform Python calculations. Thanks to the Anaconda distribution, using the Python function in the Excel application will unlock endless potential in Data Science. We’re looking forward to seeing more Python functionality in Excel.
Related: