At a glance
This is the first in a three-part series on Python in Excel:
- Part 1: the basics
- Part 2: starting to code
- Part 3: application in Excel
Python in Excel offers opportunities to access external libraries that have pre-built solutions for many data related tasks.
When to use Python
Python is not replacing Excel formulas and functions. Python code has been included to extend Excel’s functionality. Some things are hard to do — or impossible — with Excel’s formulas and functions. Python can make them possible — or at least, easier.
Python should be used when Excel’s formulas and functions can’t provide a solution.
Python requirements
- Insert Python icon in the Formulas tab (see Figure 1)
- Internet connection
How it works
After you enter Python code in an Excel cell, the code is run in the cloud in an Azure container. Azure is Microsoft’s online data base. The result is then passed back to the Excel cell.
This means everyone in the world is using the same version of Python. You may see the message “@BUSY” display in the cell whilst the code is being run.
Usage quota
Python in Excel has a quota of free, high-speed usage per month. After the quota is exceeded, the speed slows down and the “@BUSY” message will display more often. A monthly subscription can maintain faster speeds throughout the month.
Resources
There are lots of Python code resources on the internet. Various artificial intelligence systems can write Python code and help you build a solution when stuck.
Python icon
The Insert Python icon is in the Formulas tab — see Figure 1.
Entering Python code
There are three ways to enter Python code in a cell. Select a cell and do one of the following:
1. click the Insert Python icon in the Formulas tab
2. type =PY and press the Tab key
3. have the Python Editor task pane open (Editor icon in Figure 1) and click the Add Python button.
When you click the Insert Python icon the Python in Excel task pane displays — see Figure 2.
You can click the buttons in the task pane and scroll down to see more information on Python in Excel.
You can write the code in the Formula Bar or use the Python Editor task pane. The Python Editor task pane has more built-in help than the Formula Bar.
Ctrl + Enter
When you enter code in the Formula Bar you must press Ctrl + Enter when you finish. Pressing Enter inserts a new line in the Formula Bar. Python code usually requires at least a few lines of code.
Formula Bar expansion
The Formula Bar will need to be expanded to display the Python code. The keyboard shortcut to expand/reduce the Formula Bar is Ctrl + Shift + U. It acts as a toggle and expands or reduces the Formula Bar depending on the current display.
Simple example
In Figure 3, cell A1 uses two variables: gst and sales, to capture two values. I pressed Enter between each line. The variables are used to perform a calculation. Press Ctrl + Enter to complete the code. The result of the calculation is displayed.
Note: [PY] displays in the left of the cell to denote a Python cell.
The interesting thing with Python is that every other Python cell in this sheet can use these two variables in their Python calculations since this was entered in cell A1.
Dependencies
Python in Excel uses a top down, left-to-right dependency structure. This means any variables created in column A can be used in column B, and any variables created in row 1 can be used in rows 2 and below.
The left-to-right dependency also applies to the sheet position. If the example in Figure 3 was in the first sheet in the file, then every other sheet in the file can access the gst and sales variables in Python code.
This means you can set up global Python variables in the first sheet, to use in the rest of the file.
Text example
The code in cell B1 in Figure 4 shows that the plus sign can also be used to combine text.
There are built-in string (text) functions you can access by using the full stop. This is done by enclosing the text in parentheses, then type the full stop to access the functions. See two examples in Figure 5.
Calculation
When one Python cell calculates, all Python cells calculate. During the development phase, this may cause you to hit your free monthly quota limit. You can turn off Python cell calculation in the Formulas tab.
To stop the Python cell from calculating, click the Calculation Options drop down and select Partial — see Figure 6.
Remember to return the option to Automatic, which is the standard calculation option.
Referring to cells/ranges
The xl() Python function references cells and ranges. Selecting a range whilst editing in Python code will automatically include the xl() syntax. The following examples demonstrate the syntax of referring to a cell and a range.
In Figure 7, the code adds two cells together and displays the result.
In Figure 8, there is a calculation with a range and a cell. The result is not displayed because the result is a range or, in Python terminology, a DataFrame.
To display the result, you need to click the icon on the left of the PY green section in the Formula Bar and choose Excel Value — see Figure 9.
The resulting list is shown in Figure 10. This list spills down like a dynamic array formula.
Note: [PY] does not display in the Python cell when it spills.
DataFrames are an important part of Python and will be covered in more detail in part 2 .
The companion video and Excel file will go into more detail to demonstrate these techniques.
