Python is a world-wide used programming language known for its simplicity and extensive library ecosystem. On the contrary, Excel is the best spreadsheet for data organization, analysis, and visualization. Its importance lies in its user-friendly interface and accessibility.
So, what would happen if Python is integrated into Excel?
Yes, you heard it right. Microsoft made Python available to all Windows Beta Channel users operating Enterprise, Business, Education, Family, and Personal MS Accounts.
Still unable to find Python in Excel?
In this post, we will guide you through the exact steps of adding and using the versatile programming language (Python) in Excel. So, follow along.
Get Hands-On: Download the Practice Workbook and Work Along with the Steps
You can click below to download the practice workbook and follow along with the step-by-step instructions.
Download Exploring Python in Excel.xlsx
How To Add Python in Excel
To access Python in Excel, you need to join the Microsoft 365 Insider program in the Beta channel. Microsoft 365 Insider Program allows you to be the first to see what’s next in Microsoft 365 apps and services.
To check whether you are eligible for the Microsoft 365 Insider builds, you need to open a Microsoft 365 application (Word, Excel, or others). Go to your Account Details and look for your license under Product Information. If you are eligible, you will see an option called Microsoft 365 Insider.
Now, there are two insider levels.
- Beta Channel: For those who want to use the latest builds, ignoring the preliminary risks.
- Current Channel (Preview): For users who want early access but don’t want frequent stable updates.
You need to join the Beta channel to install insider builds. After that, you can add python into Excel.
But what if you don’t have the Microsoft 365 Insider option?
Let’s look at the regular method and the Workaround way to add python directly within Microsoft Excel.
Here are the steps to add Python in Excel:
Step 1: Join the Microsoft 365 Insider Program
Regular Method
(applicable for those who have the Microsoft 365 Insider option and the Beta Channel option available)
- Open Microsoft Excel from a Windows device.
- Go to File > Account.
- Click on the Microsoft 365 Insider button under the Office updates option.
- Click on Join Microsoft 365 Insider.
- Mark the box next to Sign me up for early access to new releases of Microsoft 365.
- Select Beta Channel from the Choose your Office Insider channel dropdown menu.
- Agree to the terms and conditions and click OK.
If you can’t reach the Beta Channel by following the Regular method, you need to use a registry command to install Microsoft 365 Insider on Windows devices. It’s official, and there’s no catch.
Workaround
(applicable for those who don’t have the Microsoft 365 Insider option or can’t access the Beta Channel because of a Microsoft Business/Enterprise account)
- Search for Command Prompt from the Windows Search bar and run Command Prompt as Administrator.
- Paste the following code: reg add HKLM\Software\Policies\Microsoft\office\16.0\common\officeupdate /v updatebranch /t REG_SZ /d BetaChannel
- Press Enter and Restart your PC.
- Open Excel and Go to File tab > Account.
- Click on the Update Options > Update Now.
- Wait for Excel to download the updates from the Beta channel.
- Restart Excel.
- Go to File tab > Account.
- Check the About Excel section. It should mention Beta Channel.
Now that you are all set, it’s time to bring python inside Excel.
Step 2: Add Python in Excel
- Open any Excel file or a blank spreadsheet.
- Go to the Formulas tab.
- Click on the Insert Python Option from the Ribbon bar. You will see the Python (preview) section there.
Note: If you can’t find the Python (preview) section inside the Formulas tab, search for python from the top search bar. Then click on Insert Python and follow the instructions.
After adding the python programming language in Excel, you should know how to use it from the formula bar. There is also a function named =PY to enable python.
So, let’s get to know the process in detail.
How To Use Python Programming in Excel
The function PY is a new function by Microsoft that adds the option to run Python code inside MS Excel. The preview is currently available for free on the Beta channel on Windows, you need to join the Insider Program. On the contrary, the stable version will be available through a subscription in the near future.
You can type Python code in the Formula Bar. However, Python code is run/executed in the Microsoft servers. So internet connection and Office 365 subscription is required.
So, here are the steps to use Python programming in Excel spreadsheet:
1. Enable Cell to Accept Python Code
Select any cell in the sheet. Go to the Formulas tab. Click on Insert Python. Select Python in Excel. Alternatively, select any cell in the sheet. Go to the Formula Bar at the top. Type =PY and you’ll see Excel suggesting a PY function. Press Tab to apply it.
PRO TIP: Keyboard shortcut key to run Python in Excel is Ctrl+Alt+Shift+P.
Either way, you’ll see the Excel cell and the Formula Bar turn green with PY written on it. It indicates that the cell is ready to accept Python code.
You will notice a few different changes applied when entering the Python mode.
2. Write and Run the Python Code
Once the cell is ready to accept Python code, you can start typing the code in the Formula Bar. For example, let’s say we want to get the value of Pi from the python library, SciPy. We have to write the following code in the Formula Bar:
# import scipy from scipy import constants #print the value of pi from scipy constants.pi
After typing the Python code, press Ctrl+Enter to run the code.
Since the code runs in the Microsoft servers, it will take some time to send the code, run it, and get the output back to you. You will see the Excel cell display #BUSY! when the code is running.
When the output is displayed, you’ll see an icon on the left of the cell. When you click on the icon, a pop-up will open, which will contain various information about the Python output value.
TIP: The output of the code can be kept as a Python value or an Excel value in the Excel sheet.
You can keep the Python value in the cell or convert it into an Excel value. To convert it into an Excel value, click on the icon left to the Formula Bar, and it will open a dropdown menu. Selecting Excel Value here will convert the Python output to an appropriate Excel value.
These are the basics of how to write and run Python code in Excel. If you want to add multiple lines to the code, you have to expand the formula bar.
3. Expand the Formula Bar To Add Multiple Lines To The Code
To expand the formula bar, take the cursor at the bottom of the Formula Bar and the cursor will change to an arrow, which will allow you to drag the bottom of the Formula Bar and expand it.
Python Libraries
In most cases, you’ll be working with Excel data and try to perform data analysis, data manipulation, data cleaning, statistical modeling, data visualization, etc. using Python’s libraries.
Good news for you, popular Python libraries for such tasks are enabled by default.
Here’s a list of the Python libraries which are imported by default:
- Numpy
- Pandas
- Matplotlib
- Statsmodels
- Seaborn
- Excel
You can also import libraries like scikit-learn, SciPy, Theano, or any other Python libraries you want.
Now, you might ask:
How to import a Python Library in Excel?
To import a library, type the import statement and it will be available.
Example:
# import scipy from scipy import constants #print the value of pi from scipy constants.pi
Variable Scope
If you have any experience with coding, you’ll know that we cannot access a variable before declaring it.
For example, if we declare the value of x as x=1 in line 2 and try to access the value of x in line 1 (before x is declared), we’ll get an error.
The same is true for Python in Excel.
Excel evaluates the cell by first taking the sheet order (left to right), then the row (left to right) and the column (top to bottom).
For instance, if you declare a variable in sheet 2 and access it from sheet 1, it will not work. You need to declare the variable at the same sheet or any of the previous sheets to access it.
Similarly, if you declare a variable in cell B2 and access it from cell B1, it will not work. You can access the variable at any of the cells right to the cell B2 (C2, D2, …) or bottom of the cell B2 (B3, B4, …) and it will work.
Here is a screenshot demonstrating it:
4 Examples of Using Python in Excel Instead of VBA
Adding Python turbocharges Excel just like they did while adding the VBA programing. In this section, we will teach you how to insert custom Python formulas in Excel and also explore Python samples.
Example 1: Add Data to a Pandas Dataframe
- Open the Excel sheet containing data.
- Select any empty cell to the right of the data (not in the same cell).
- Type =PY and press Tab to activate Python mode.
- Use the mouse cursor to select the data. The Python code will automatically update the row and column values based on the data you have selected.
- Press Ctrl+Enter and the data will be converted to a Pandas Dataframe.
We can store the Dataframe into a variable df for future reference. Basically, Dataframe is a condensed version of the data table. It’s a two-dimensional data structure in a cell.
Consider the DataFrame as an Ikea table. When you buy such tables, it’s condensed in a single package. Similarly, the Excel data table is condensed in a single cell (DataFrame). Click on the DataFrame to see the beginning and ending rows of the dataset.
Example 2: Perform Data Analysis Using Pandas
In another empty cell below the Dataframe cell, let’s use the describe() method of pandas to know more about the data.
We can get the output of the describe() method as an Excel value to print it on the sheet.
Not only that, we can group the data by department ID and then find the average salary of each department and also the number of employees in the department.
Example 3: Visualize the Data Using Matplotlib and Seaborn
We can plot the grouped data earlier using the plot() method (which uses matplotlib). Here we specify the name of the series for horizontal and vertical axis and also the type of the plot (here, kind=bar means a bar graph). We need to convert the cell into an Excel value and then select some adjacent cells and merge them to see the graph.
We can also use Seaborn to plot more sophisticated graphs. Here, we plotted the violin plot of the salary column.
Example 4: Data Extraction and Cleaning
We can extract data and do data cleaning using Pandas and regex. Here, we are taking a list of URLs where we wish to get the slug from.
First, we create a dataframe. It contains 74 rows. Some URLs are duplicated. So we remove the duplicates using the drop_duplicates() method of Dataframe.
Now we apply a function which goes through each URL and gets the slug part of the URL using regex.
FAQ
Q: Why is Python used over Excel?
A: Python is often preferred over Excel due to its scripting capabilities and advanced data analysis libraries. Libraries like scikit-learn, TensorFlow, and PyTorch make it easier to train machine learning models. This type of complex operation is not possible with Excel’s VBA.
Q: Can Python replace VBA?
A: It is possible that Python might replace VBA programming completely. The main reason would be Python’s cross-platform compatibility. Python ensures consistency across different operating systems (Windows, MacOS, and Linux). Whereas, it’s difficult to process large datasets with Excel’s VBA programming.
Q: Which is faster: VBA or Python?
A: In Excel, VBA is faster than Python (till now). While running a python code in Excel, you will see a BUSY! Icon for a few seconds. Whereas, while running a VBA script, it takes a few nanoseconds to run the code. However, whether a programming language is faster or not depends on how many Loops and variables you are using.
Q: Can Python do everything Excel can do?
A: Although Python and Excel have overlapping functionalities, Python can handle large datasets more efficiently. Python is excellent for automating repetitive tasks, interacting with external systems, and performing complex calculations. Moreover, it’s a go-to language for machine learning.
Conclusion
In this comprehensive tutorial, we covered the integration of Python with Excel, how to add and use the Python programming language in an Excel spreadsheet.
We covered two methods to add Python to Excel – through the Microsoft 365 Insider Program or via a workaround for users who do not have access to the Insider Program. Once integrated, we demonstrated how to enable a cell to accept Python code, write and run Python scripts, and explored various Python libraries available by default.
