Microsoft Excel is essential in business environments and is widely used for business analysis, human resource management, operations management, and performance reporting. Excel allows users to organize and manipulate data efficiently, utilizing graphing tools, pivot tables, and an array of formulas. Additionally, it features a macro programming language called Visual Basic for Applications. This enhances its functionality for more sophisticated tasks.
Despite its usefulness, Excel is somewhat limited compared to modern programming languages. Many programmers avoid Excel altogether. They prefer the extensive capabilities offered by dedicated data analysis and processing libraries, like Python's Pandas library. For many in the programming world, Excel is often an afterthought.
In the past, attempts to merge programming languages with Excel, such as PyXLL, allowed users to run Python code within Excel. However, these were unofficial implementations. Recently, Microsoft has changed this by introducing official Python integration in Excel.
Let us explore Python's implementation in Excel and demonstrate its functionality with a few simple examples. In a future article, we will compare this integration with Pandas. Our goal is to determine if Python in Excel can effectively replace Pandas for most users.
- Pandas vs Excel: Which Is the Best for Data Analysis?
- How Is Pandas Enhancing Data Analysis in Excel
How to Access Python in Excel
To use Python in Excel, you must join the Microsoft 365 Insider Program. This program grants access to early builds of Microsoft applications, including the latest features and updates. Most importantly, it allows you to access Python in Excel, which is currently only available in the beta channel.
The beta channel is specifically designed for users to test new features before they are released to the general public. To join the Microsoft 365 Insider Program and access the version of Excel that supports Python, you need to:
- have an active Microsoft 365 subscription
- enroll in the Insider Program
Once enrolled in the Insider Program, you will be able to use the beta version of Excel that supports running Python code. Finally, one last warning. For now, Python in Excel has limited availability when it comes to platforms. It is NOT supported on the following platforms:
- Excel for Mac
- Excel on the web
- Excel for iPad
- Excel for iPhone
- Excel for Android
On these platforms, you will still be able to open workbooks that contain Python. However, if you try to recalculate a cell that contains Python code the cell will display an error.
How Does Excel Run Python Code
People unfamiliar with programming might not consider this a significant topic. However, those in the programming field will immediately question how Excel executes Python calculations. In programming, setting up the proper environment with all the necessary libraries is a crucial initial step. So, how does Excel manage this?
Interestingly, Python code does not run on your computer. Instead, Excel uses a cloud-based integration provided by Microsoft in collaboration with Anaconda to run Python calculations. Rather than executing the Python code on your local PC, Excel sends it to the cloud, where it is processed in a secure environment managed by Microsoft.
Specifically, the Python code runs on Microsoft Azure, Microsoft's public cloud computing platform. This cloud-based approach eliminates the need to manage the environment on our local machine. Moreover, we can rely on the cloud setup to have access to the latest libraries and updates.
This method of running Python code reveals Microsoft's target audience: people who are proficient with Excel but not necessarily experts in Python. Python programmers usually prefer having full control over the environment where the code is executed. This allows them to manage library versions precisely. Personally, having the flexibility to choose specific library versions when using Python in Excel would be highly appreciated. However, that is a topic for another time.
Article continues below
Want to learn more? Check out some of our courses:
Why isn't Python Run Locally in Excel
The target audience of this section is programmers curious about why Python code cannot be executed locally. However, it will also benefit those familiar only with Excel. To understand why the code runs in the cloud, let us explore the three main reasons explained by John Lam, a principal architect at Microsoft, in a public statement.
Firstly, ensuring the secure execution of Python on a local machine is difficult because it requires maintaining the safety of the code and protecting the system from potential threats. Microsoft addresses this by treating all Python code in Excel as "untrusted."
Untrusted code refers to any code that cannot be assured to be safe or secure, potentially leading to harmful actions, either intentional or accidental. Examples of this include unauthorized data access, data corruption, excessive resource consumption, or system exploitation. By treating all code as untrusted, Microsoft ensures that it is executed in an isolated container on the Azure cloud platform. This container lacks outbound network access, which prevents the code from communicating externally and helps reduce security risks.
Secondly, the cloud-based approach simplifies sharing Excel workbooks with others. Running everything in the cloud ensures that a shared workbook behaves as intended whenever someone opens it. This approach eliminates the need for additional setup, which would typically be required if the code were run locally.
Finally, the cloud-based approach eases the burden on system administrators. Managing local environments can be challenging, even for experienced programmers. Keeping a local Python installation on every machine in an organization that uses the feature could be both cumbersome and time-consuming.
Despite these considerations, I still believe that once Python in Excel is released to all consumers, beyond just those in the Insider program, it would be beneficial for Microsoft to offer more advanced users the option to run the code locally. Many people work in smaller companies on less sensitive tasks where these challenges are less significant. These users would greatly benefit, if nothing else, from the flexibility of being able to work with Python in Excel without needing an Internet connection.
What Are the Benefits of Using Python in Excel
One of the biggest benefits of using Python in Excel is the ability to leverage the powerful data analysis and processing libraries available in the Python ecosystem. There is already an article on our blog comparing Excel to Pandas. However, with this new functionality, you no longer need to choose between the two. Pandas is now available as one of the libraries you can use within Excel.
Popular libraries available through Python in Excel include Pandas for data manipulation and processing, statsmodels for advanced statistical modeling, and Matplotlib and Seaborn for data visualization. For those familiar with these tools, the ability to use just these four libraries makes Python in Excel highly appealing. These libraries enable more advanced operations and ensure that data processing is more efficient than with standard Excel alone.
While running everything in an isolated online environment might seem limiting, it’s not necessarily a disadvantage. In fact, it can be seen as a great benefit. Microsoft highlights that privacy and security were their top priorities during the design of the entire system, so users receive enterprise-level security on their cloud by default. This high level of security somewhat compensates for the loss of flexibility associated with not being able to run code locally.
Using Python in Excel not only improves productivity right away but also equips users with skills beneficial for future career opportunities. As AI technology advances, Python has emerged as the primary programming language for AI applications. By learning Python through Excel, users gain valuable expertise in a widely-used language. This provides a solid foundation for career growth, whether in their current roles or transitioning to software development. Mastering Python in Excel helps individuals stay competitive and adaptable in a rapidly evolving job market.
How to Get Started with Python in Excel
Using Python in Excel is especially easy. There are two ways you can start writing Python code in Excel. The more direct way is to just click on a cell and type in =PY.
As soon as you type in the command, Excel will automatically convert that cell into a co-called Python cell. There is an alternative way of converting a cell into a Python cell, and that is to use the Formulas section in Excel. There, you should see a new section called Python(Preview). Essentially, you just click on a cell in the sheet and then click on Insert Python:
It doesn’t matter whether you choose the first approach or the second. In both cases you are going to end up creating a Python cell that looks like this:
Once a cell has been converted into a Python cell, we can write Python code inside it, and later send that code to be executed in the cloud.
How to Perform Basic Python Operations
To start, let us demonstrate how we run some Python code in Excel. To be more precise, let us print out the sentence “Hello world”. To make it work, you need to insert the code into the cell and then execute it by pressing CTRL+ENTER. This is going to commit our code i.e. send our code to Microsoft Azure. After Microsoft Azure runs the code we will see the final result in our cell:
In this context, Python in Excel operates similarly to a Jupyter Notebook. The output displayed in the cell corresponds to the last line of code executed within the Python cell. This comparison is quite helpful. However, there are some differences in functionality that we will discuss later. For example, let us create two variables, storing one value in each:
Furthermore, once we run this code, we can access these values in other Python cells we create. For instance, let us add a new variable in this Python cell called multiplier.
Afterward, we are going to create a new Python cell. Inside it, we will multiply the number stored in our number variable with the one stored inside the multiplier variable.
As can be seen, we can easily access the values that we stored in variables earlier and perform an operation with them.
What Is the Order of Execution
However, this is where the functionality diverges from how things usually work in Jupyter Notebooks. In a Jupyter Notebook, you can define variables in one cell and use them in another cell below it, as long as you execute the defining cell first. In Excel, this is not how it works. For example, if you place the cell that defines the variables lower in the sheet than the cell that uses them, running the defining cell first will still result in an error in the cell that uses the variables.
Not only will the code fail to execute properly, but you will also receive an error message similar to one you would get in Python, displayed in a specialized window called Diagnostics. This error indicates that the variable is not defined, even though you defined the variable first and created the calculation cell afterward.
Unlike in a Jupyter Notebook, where code execution is more flexible, Excel executes code more like a standard Python file, running from top to bottom. Specifically, Excel runs code from left to right and top to bottom. Therefore, you can place the cell with the variables in the same row as the cell with the calculation, as long as it is to the left, to avoid any errors.
What Is the Interaction between Python Cells and Standard Excel Cells
Of course, when writing code you are not necessarily required to define all values inside Python cells. If you need a value from any other cell, you can simply reference the name of that cell. The way that is done in a Python cell is by using the xl() function. For instance, let us say that we want to create a Python function that is going to add up two values together. It needs to sum up the value stored in the number variable with some number stored in a standard Excel cell.
As demonstrated, we can seamlessly integrate data from Python cells with that from traditional Excel cells. Additionally, those with a keen eye have probably noticed that the Excel cell holding the value utilized in the Python cell is positioned BELOW the Python cell executing the code. This highlights a key distinction: unlike Python cells, standard Excel cells are regarded as predefined values. They do not adhere to the execution order we discussed in the previous section.
- Intro to Programming: Why Beginners Should Start With Python
- Intro to Programming: How to Get Your Computer Ready to Run Python
This article explored Python’s integration into Excel and demonstrated its basic operations within Excel. Several fundamentals were discussed, such as creating Python cells, storing and utilizing variable values, executing operations, and merging data from standard Excel cells with Python cells. The execution order was also clarified. An upcoming article will delve into a comparison between Python in Excel and Pandas. The goal will be to assess their comparability and effectiveness in executing common data processing and analysis tasks.
