A Comprehensive Guide to Python-Powered Data Insights

Excel is considered one of the most well-known and widely used spreadsheet applications developed by Microsoft. It is used to organize, analyze, and store data into tabular sheets. Analysts, sales managers, CEOs, and professionals from every field use Excel for data crunching and creating quick reports. However, when we get into more complex tasks and process larger datasets, Excel sensibly shows its limitations. 

With Python in Excel, it is possible to natively combine Python and Excel analytics within the same workbook addressing the limitations of manipulating data in Excel. A standout point of this integration is that no setup is required to use Python in Excel.  All the code and your data travel on the network to Microsoft Cloud,  executed in Microsoft Cloud, and return with the result to your Excel. By using both together and harnessing the strengths of each, we can create end-to-end solutions using Excel as a user-friendly front end, with all the heavy lifting done in Python.

Using Excel’s built-in connectors and Power Query, we can easily bring external data into Python-in-Excel workflows. Python in Excel is seamlessly compatible with the tools Excel users already know and use, such as formulas, PivotTables, and charts.

In this article, we are going to introduce the technical aspects of Python-in-Excel in more detail. Although it is still in its beta phase, we will use Python-in-Excel to get, clean, analyze, and visualize a real-world dataset. 

The user of this article is supposed to have a working knowledge of core Python along with data manipulation packages namely Pandas, Numpy, and Matplotlib. The reader must also have hands-on experience working with Excel.

 

Typical Use Cases of Python in Excel

• Manipulating large and complex datasets
• Exposing Python data analysis and manipulation as fast Excel functions
• Building compelling Excel dashboards using Python’s advanced visualization functionalities
• Seamlessly integrating Excel with Python applications to create powerful and sophisticated tools
• Using Excel as a popular and user-friendly front end to Python code
• Streaming real-time data from other systems to Python.
• Replacing VBA with Python.

 

How Python in Excel Works

To use Python in Excel, we need to type Python directly into a cell. We need an Internet connection to run Python code in Excel. All the code and data travel on the network to Microsoft Cloud,  executed in Microsoft Cloud, and the results – whether they’re data points, plots, or visualizations – are relayed back to the Excel worksheet. 

A local version of Python is not required to use Python in Excel. 

 

Why Python in Excel Runs on Cloud, Not Locally

You may ask, why Microsoft doesn’t want Excel users to have to manage a Python environment on their computer. Why not just embed Anaconda inside the Excel executable? Microsoft has listed three reasons to do so.

Security. It is very challenging to run Python securely on a local machine. All  Python code in the workbook is considered as untrusted. That’s why Microsoft decided to execute Pyhthon-in-Excel in a hypervisor-isolated container on Azure with no outbound network access. In fact, Python running on Azure is denied access to the Internet (except for the connection back to Excel). Of course, this limits a great deal of what Python can do but instead eliminates the potential security risks of running untrusted code.

Python code along with the data that it works on is sent to be executed in the container. The Microsoft-licensed Python environment in the container is provided by Anaconda and was prepared using their stringent security practices.

Collaboration. Very frequently we need to share Excel workbooks with others. We need to ensure that the Python in Excel feature always works when someone else opens it; they wouldn’t need to install Python first.

Reproducibility. Python libraries aren’t as focused on backward-compatible changes as Microsoft is. With a locally installed Python, you can’t update Excel without the risk of breaking the spreadsheet. By running Python in the cloud, a new environment can be created without removing the existing environment. In this sense, for updating an environment, the spreadsheet could simply point to the newly created cloud environment, while other spreadsheets continue to point to the old environment. This means that a four-year-old spreadsheet will continue giving repeatable results because the Python environment it was developed with is still available and working in the cloud.

 

What makes Python in Excel Stand out?

• Built for analysis. Python in Excel is natively integrated directly into the Excel grid giving us the means to perform sophisticated data analytics.
  • Data Cleaning
  • Statistical Modeling
  • Machine Learning, Predictive Analytics, Forecasting
  • Advanced Visualizations

• Leverages the best of Python analytics via Anaconda. Excel leverages Anaconda Distribution for Python which is a secure and trusted enterprise-grade distribution for data analytics. Anaconda includes the most popular Python libraries (numpy, pandas, matplotlib, scikit-learn, etc.) and is actively tested and supported by Anaconda.
• Runs on Microsoft Azure, no local setup required. Python code written in Excel runs on the Microsoft Cloud in its own hypervisor-isolated container with enterprise-level security as a compliant Microsoft 365-connected experience.
• Built for collaboration. Python in Excel workbooks can be shared with confidence as is the norm with Excel. This will done without the hassle of installing additional tools, managing intricate dependencies and libraries, or Python runtimes.

 

Activating Python in Excel

Before getting into Python-in-Excel you need to activate Python in Excel first. To do that, please consult our comprehensive guide on How to activate Python in Excel [this article is to be written].
After activating Python in Excel, the Python group will be added to the Formula tab in Excel.

 

Begin Using Python in Excel

There are three options to enable the Python formula in Excel.

• Ctrl+Alt+Shift+P Shortcut
• Excel PY function
• Insert Python

 

Ctrl+Alt+Shift+P Shortcut

This option is straightforward and self-explaining. Select a cell and press Ctrl+Alt+Shift+P. Upon pressing the shortcut keys, you should see a green PY on the left side of the formula bar inviting you to enter Python code directly into the cell.

 

Excel Python Function (=PY)

To enable Python, use the Excel function =PY in a cell. After typing =PY in the cell, choose PY from the function AutoComplete menu or add an opening parenthesis to the function: =PY(. Then, you should see a green PY on the left side of the formula bar. This invites you to enter Python code directly into the cell.

When writing Python code in Excel, use Enter to go to the next line. To execute Python code press Cntrl + Enter. It will take a while for the code to run. Meanwhile, Excel shows the #BUSY! Error. The #BUSY! error indicates that Python calculations are running in the Microsoft Cloud.

 

Insert Python

The Insert Python command can be found under the Formulas tab -> Python group.

Upon clicking the Insert Python, you will see options for Custom Python Formula and Explore Python Samples. You can choose to either insert your own Python code or explore the sample Python codes.

When writing Python code in Excel, use Enter to go to the next line. To execute Python code press Cntrl + Enter. It will take a while for the code to run. Meanwhile, Excel shows the #BUSY! Error. The #BUSY! error indicates that Python calculations are running in the Microsoft Cloud.

 

Enter Mode and Edit Mode in Python cells

When using Python in Excel, the Python cell can be in one of the following modes. 

• Edit Mode: In this mode, you can edit your Python code.
• Enter Mode: In this mode, you can select additional cells or ranges with your keyboard.

To toggle between these two modes in Excel, use the F2 key.

 

Formula Bar as Code Editor

Python code in Excel is treated as formulas. It means that we use the formula bar for code-like editing, for example using the Enter key to go to the next lines. 

To see and edit multiple lines of code at once, you need to expand the formula bar using any of the following options.

• Using the down arrow icon
• Using the keyboard shortcut Ctrl+Shift+U

 

Output Types

While executing Python code in Excel, calculations can be returned in two types; Python Object or Excel Value. To choose Python Object or Excel Value, we can either the Python Output menu or the right-click menu of the formula cell as shown in the following gif.

 

Let’s add some detail about Python Object and Excel values.

 

Python Object

Calculations are by default returned as Python Objects. In this case, the cell displays a card icon.

 

A Python object in Excel contains complementary information about the object. To view it, open the card by selecting the card icon. The card contains a preview of the object, which is helpful when working with large objects.

 

Excel Value

We can convert calculations to Excel values and output them directly into a cell. In this case, the cell displays a value icon. Returning a result as Excel values enables us to perform Excel analytics, like Excel charts, formulas, and conditional formatting, on the result.

Calculations returned as Excel values are translated to their closest Excel equivalent. In case you plan to reuse the values in a future Python calculation, it’s recommended to return them as a Python object.

 

Accessing Excel Data with Python – XL Function

We can write Python codes that refer to Excel objects using the custom Python function XL(). The XL() function accepts Excel objects like ranges, tables, queries, and names as arguments. We can type the reference name or select it while we are in a Python formula cell.

Select the Cell to Refer to it

To reference Excel objects in a Python cell, make sure that the Python cell is in Edit mode. Then select your desired cell or range. This automatically populates the Python cell with the address of the selected cell or range.

Tip: Use the keyboard shortcut F2 to toggle between Enter mode and Edit mode in Python cells. 

 

Write the Reference to a Cell

We can also directly type references into a Python cell. Here are some examples.

What We Type	What We Refer to
xl(“c10”)	Cell C10
xl(“A1:C4”)	Range A1:C4
xl(“Sales[Amount]”, headers=True)	The Amount column in the Sales table
xl(“Months”)	Named range Month

 

Order of Calculation

In a conventional Python file, statements are calculated from top to bottom. The calculation order is important when defining and referencing variables because variables must be defined before we can refer to them. Each Python statement is implicitly dependent on its immediate preceding statement.

Python in Excel is no exception to this rule. In a Python in Excel worksheet, Python cells are calculated in row-major order. Calculation starts from row 1, from column A to column XFD, then row 2, row 3… all the way around to the end of the worksheet.

Having this simple rule, if we use a variable in a Python cell that precedes a cell in which it is defined, we will simply get a NameError in Python.

 

Conclusion

In this article, we explored how to initiate Python in Excel and how to use it for basic calculations in Excel. We started with some basic concepts of Python in Excel and how it works. Then we went on activating Python in Excel, using Excel cells as Python code editors, referencing Excel objects in Python and the representing the result as Excel values or Python objects. In the final section, we got familiar with the order of calculations in Excel.