Python in Excel documents

Python has finally become part of Excel, and from now on you can add pieces of Python code to a sheet to define datasets, process and visualize them. If you have a Microsoft 365 subscription and have downloaded update 2405 (Build 17628.20164), you will be able to use Python normally (no longer reserved for testers only). In this short intro you will learn what Python is and how it can enrich your reports.

Python is a general-purpose programming language that was created in the late 1980s. It has many libraries of which you can use only a basic set in Excel, and in data analysis it is superior to Excel because it offers statistical processing, finding patterns and outliers, as well as some unique ways to visualize data.

Parts of Python code are added to the worksheet with the help of the PY function, whose syntax is:

PY (<python code>,<return type>)

The first argument represents the code, and the second indicates whether the value is returned as Excel (0) or as Python (1).

You can add Python code to a worksheet simply by creating a formula and entering the PY function, and this can also be done from the Formulas ribbon by running the Insert Python option.

After this action, on the right side of the Excel window, a panel will open where you can see a short introduction and some basic examples. In this text, I will try to demonstrate some of them myself.

First, it is necessary to define the range of data that we will use, a dataframe. I created a simple table with employee names, their ages and salaries and named it Employees. I went to cell C8, clicked Insert Python, and then selected the range containing the data table. By clicking on CTRL+ENTER, we will complete the process of defining the data range.

We will correct the formula by giving the range a name, and the line of code should look like this:

df = xl(“Employees[#All]”, headers=True)

Let’s do some simple statistical analysis! In cell E3 I added the code:

df.describe()

it is a Pandas function that performs a simple statistical analysis. If we save the formula as an Excel object, a dynamic table will appear showing the number, mean, standard deviation, etc.

If we save the formula as Python code, pressing CTRL+SHIFT+5 will bring up a tab showing the given statistics.

Let’s create a simple chart where we will show the salary amount in relation to the age of employees. We’ll add the Python code to cell E5:

import seaborn as sns
sns.barplot(df,y=’SALARY’,x=’AGE’)

and then, when the processing is finished, press CTRL+SHIFT+F5.

You can find some other useful options in the Formulas ribbon. Initialization shows which Python libraries are available after starting Excel, Diagnostics opens a panel of the same name on the right if errors occur in the code, and the Reset menu has options for resetting the environment and variables…