Getting to know Python in Excel

Entering Python code in Excel

We will start with some basic examples where you will enter Python code from the formula bar. This section covers some of the Python syntax that allows you to access and manipulate Excel data by using Python in Excel. Later in this chapter, you will have the opportunity to explore more Python syntax to prepare for more involved examples later in the book.

Trying an example: “Hello World”

Let’s start with a classic for learning a new programming language: “Hello World.” If you are familiar with other Python interfaces, you may expect to use the print command for this. However, in Excel, the Python print command is not needed to get a value to appear in a cell. Let’s look at what happens when the print command is entered.

For this example, indicate that cell F8 will contain Python code by entering the Excel function PY()in the formula bar or by selecting Insert Python on the Formulas tab. Once you see the green Python prompt, type the Python command print("Hello World"), as shown in Figure 2-12.

Commit the code you typed by clicking the green checkmark or pressing Ctrl+Enter. The Python Editor appears, showing the results (see Figure 2-13).

While the phrase Hello World appears in the Python Editor, it does not appear in cell F8 on the Excel worksheet (see Figure 2-14).

In cell F8 of the Excel worksheet, you see the letters PY in square brackets ([PY]) and the word None. [PY] indicates that the Python object is shown. We noted earlier that there are two ways to show the results of Python code in a cell: either as a Python object or as an Excel value. Remember the new symbol that appears next to the formula bar? It is the Python Output option, and you can click it to toggle between the Python Object view and the Excel Value view (see Figure 2-15).

Instead of clicking the symbol, you can use the keyboard shortcut Ctrl+Alt+Shift+M to toggle between these output options. If you toggle the Python Output option to Excel Value, you will still see None in the cell, as shown in Figure 2-16. With this output option chosen, the Python Editor shows the Python object first and then the Excel value 0 (None) (see Figure 2-17). What you now see in cell F8 is the equivalent of a null in Excel.

To have the words Hello World appear in F8, you must omit the Python print command. Just enter "Hello World" in cell F8 and commit the Python code by clicking the green checkmark or pressing Ctrl+Enter (see Figure 2-18).

Cell F8 now shows the correct value (see Figure 2-19), and your first Python example is complete.

Using comments

When creating Python code, it is important to consider other workbook users. Excel users unfamiliar with Python may find it difficult to understand the purpose of the Python cells. When creating code, it is helpful to enter comments to indicate what the code is doing. Python may be easier to read than many other programming languages, but it still helps the reader if you break up lines of code with an explanation of the purpose of each section. A comment line in Python begins with #.

To see how comments work, select cell F8 and prepare to enter a new line of Python code in the formula bar by going to the end of the “Hello World” code you already entered and pressing Enter to start a new line of code. Expand the formula bar and enter the following Python code (see Figure 2-20):

#This Python code displays 'Hello World'

A comment line is used to improve the readability of the code and doesn’t actually change the code or the output. So, when you commit the Python code you just typed, the output in cell F8 does not change.

Taking a first look at Python in Excel errors

You saw earlier that the Python print command is not needed to output text in Python in Excel. However, the Python interface does recognize print as a command, as shown in Figure 2-21.

Some Python commands are not recognized in the Python in Excel frontend. An example is the input command. This command is used in other Python platforms to accept a user response. The following Python code on a platform that allows input prompts the user to enter a name and then outputs a response:

print("Enter your name:")
x=input()
print("Hello, " + x)

You already know you need to omit the print command for Python in Excel. You will need to remove it from the previous code so that it looks like this:

"Enter your name:"
x=input()
"Hello, " + x

Now you can indicate that cell F9 will be a Python cell and enter the modified Python code, as shown in Figure 2-22.

Commit the Python code in cell F9 by clicking the green checkmark or pressing Ctrl+Enter. Figure 2-23 shows how the results appear in the worksheet.

You have already met the new Excel function PY(), and now you have caused the new Excel error #PYTHON!. Because it is an Excel error, you can view the issue by hovering over the warning symbol (see Figure 2-24).

The Python Editor also appears when an error occurs and shows the same error message (see Figure 2-25):

StdinNotImplementedError: raw_input was called, but this frontend does not support input requests.

While this is not a true Python error because input is recognized, it is a Python in Excel error. If you were to enter unrecognized Python syntax, you would trigger the same #PYTHON! error, but you would get a different message, as shown in Figure 2-26:

NameError: name 'InvalidSyntax' is not defined.

This is clearly a different message type. If you encounter this error message when entering Python code, you should check to make sure you have entered the commands correctly. (You will learn about the other error codes encountered when using Python in Excel and how to deal with them later in this chapter.)

Getting to know variables

It’s time to move on to Python code that interacts with data. Python can handle a variety of data structures. For example, a variable is a name that you attach to an object so that you can give an indication of what it is and refer to it again. To keep your code easy to read, you can use variables to name your data structures.

Let’s return to the “Hello World” example in cell F8 of the worksheet we’ve been working with in this chapter. Modify the Python code so it looks like this (see Figure 2-27):

message="Hello World"

This does not impact the output in cell F8, as you can see in Figure 2-28.

If you move to cell G8 and indicate that it will be a Python cell, you can refer to the variable message (see Figure 2-29).

IntelliSense recognizes the variable message. When you commit the Python code, cell G8 also contains the output Hello World (see Figure 2-30).

You must follow these rules when choosing variable names:

• A variable name must begin with a letter or an underscore (_).

• A variable name can contain only letters, numbers, and underscores. It cannot include spaces.

• A variable cannot have the same name as a keyword. For example, assigning a value to True will result in an error.

• While you can use Python function names as variable names, doing so is not recommended since it can be confusing.

A variable takes the data type of the object it is attached to, and you can reassign a variable. If you reuse a variable for an object with a different data type, the variable’s data type will change too.

Understanding calculation order

It is easy to see in a piece of Python code which definition of a variable comes first. But what is the order of the Python code when you use Python in Excel across multiple cells and sheets?

In the example you just completed, cell G8 is to the right of cell F8, and the variable defined in cell F8 is recognized in cell G8. Now, prepare cell E8 for Python code and enter the variable message in that cell. As you can see in Figure 2-31, the variable message is not recognized in cell E8.

For Python in Excel, cell E8 comes before cell F8. So, in this case, trying to use the variable from F8 in E8 causes a Python error, as indicated by the following message (see Figure 2-32):

NameError: name 'message' is not defined.

The calculation order is from left to right and from top to bottom. This left-to-right order also applies to the sheets in the workbook. This means you must take care when moving sheets containing Python code.

Entering lists

Now that you have learned about the order of Python calculations and the importance of variable names, you can start entering data using Python in Excel. Let’s begin with a familiar concept from Excel and Power Query: lists.

A list is simple and extremely useful. It can be thought of as a single column or row of data. It may contain data objects with different data structures. The Python syntax for creating a list uses square brackets ([]).

To try your hand at creating a list of numbers, enter the following Python code in cell E12 (see Figure 2-33):

numbers_list=[1,2,3,4,5]

Using the Excel Value view, you see the list spilled from cell E12, as shown in Figure 2-34. (Spilling means that the formula is in cell E12, but the result spills into surrounding cells—in this case, E13:E16.)

You may be accustomed to creating lists in M code in Power Query. One difference in Python is the way that ranges generate lists. To use a range, you must use the Python nested function list(range). For example, Figure 2-35 shows the use of the function numbers_list=list(range(1,5)).

If you use the method numbers_list=list(range(1,5)), the range that is generated extends to the number before the second parameter of the range() function (see Figure 2-36). That is, the list does not include the second parameter, which in this case is 5.

You have viewed the list as an Excel value. Now change the view to the Python Object view, as shown in Figure 2-37. (Note that in Figure 2-36, rows 8 and 9 are hidden so we can concentrate on cell E12.)

An Insert Data icon appears next to the list (see Figure 2-38). You can click it to see more information.

Click the Insert Data icon to open the menu shown in Figure 2-39.

You use the options under Field in this menu to enter data into the next available Excel cell. The following properties are used in Figure 2-40:

• arrayPreview, similar to the Excel Value view, is used in cell F12.

• Python_str, the list in Python syntax, is used in cell G12.

• Python_type, the Python object type, is used in cell H12.

• Python_typeName, the name of the Python object, is used in cell I12.

This information is displayed using new Excel functions for Python cells:

• =E12.arrayPreview in cell F12

• =E12.Python_str in cell G12

• =E12.Python_type in cell H12

• =E12.Python_typeName in cell I12

Ensure that you still have cell E12 selected, go back to the Insert Data menu, and choose Show Data Type Card. Figure 2-41 shows the data type card that appears. The data type card allows you to see the data in the Python object—in this case, the list object. In this example, data is in a 4x1 list, and you can see all the data without needing to extract it to a cell.

Another way to access the data type card for cell E12 is to right-click this cell and select Show Data Type Card (see Figure 2-42).

Perhaps the easiest way to access the data type card is to click the Python object icon or, as you can see in Figure 2-43, use the keyboard shortcut Ctrl+Shift+F5.

You can also view the Excel data in the Python Editor, as shown in Figure 2-44. (We will look at the Python Editor in more detail in Chapter 4.) This is the only place other than the formula bar where the variable name is shown.

You can add data to the list by referencing the variable name. Enter the following Python code in cell J12:

numbers_list.append(5)
numbers_list

Because you are adding to an existing variable, you do not need to enter a new variable, but you do need to add a line of Python to output the existing variable. Figure 2-45 shows the results in the Excel Value view: The value 5 is added to the end of numbers_list.

It is possible to create a nested list. Our next example shows the difference between a nested list and an array. Enter the following Python code into cell K12:

number_nested_list = [[1,2],[3,4],[5,6]]

Figure 2-46 shows the results in the Excel Value view.

The numbers 1 to 6 are now displayed in pairs on three rows. Although the nested list looks like an array, it has the properties of a list. To see what we mean, in cell M12, use the following Python code to add number_nested_list to itself:

number_nested_list+number_nested_list

Figure 2-47 shows the results. More values are added to the list, and there are now six rows; the values in the list are not added together. Arrays can handle arithmetic operations, whereas lists (even nested lists) cannot. Mathematically, arrays are related to matrices.

The Python library NumPy allows you to define arrays and use them in complex mathematical and statistical operations. We’ll look at it and other preloaded Python libraries next.