Getting to know Python in Excel

Getting started with Python in Excel

Once you know that you have a license that provides access to Python in Excel, it is important to ensure that you are in a location associated with that license. If, for example, you try to use a personal OneDrive account when your license is associated with your company account, you’ll need to switch to a local drive or folder associated with the company account. If you find that you do not have access to Python in Excel even though you have a license, check the file location. If you try to use Python in Excel in a location not connected to a valid license, you will encounter a #BLOCKED! error. You’ll learn about the errors that can occur when using Python in Excel later in the chapter, in the section “Learning about Python in Excel error codes.”

Once you have made sure that you are in an appropriate location to access Python in Excel, there are several ways to begin creating Python code:

• Click the Insert Python button on the Formulas tab.

• Enter Python directly into a cell in a sheet.

• Use the Python Editor, which you open from the Formulas tab.

The first method of using Python in Excel—clicking the Insert Python button—allows you to access the Python in Excel Help pane, which is useful when you’re getting started. The other two methods of accessing Python in Excel do not offer the Python in Excel Help pane. You may want to use them once you are more familiar with using Python in Excel. We’ll look at all three methods next.

Clicking the Insert Python button

To start using Python in Excel by clicking the Insert Python button, go to the Formulas tab in Excel, find the section labeled Python, and click the Insert Python option (see Figure 2-2). (You will use other options in the Python section of the Formulas tab later.)

FIGURE 2.2 The Insert Python option is in the Python section of the Formulas tab.

When you click the Insert Python option, some changes occur:

• A green Python prompt appears in the formula bar, as shown in Figure 2-3, and a new symbol with square brackets appears next to the green checkmark, replacing the fx (insert function) symbol. (You will learn more about this new symbol later in this chapter.)

FIGURE 2.3 When you select the Insert Python button, the formula bar changes to show a green Python prompt.

• A hint is displayed, telling you to use Ctrl+Enter to commit the code. Whereas you can commit an Excel formula by pressing Enter, with a Python command, you need to press Ctrl+Enter to commit the code. In Python in Excel, clicking Enter takes you to the next line of Python code, which allows you to create multiple lines of Python code for a single cell. To signify that the Python code is complete, you must press Ctrl+Enter or click the green checkmark to the left of the Python prompt.

• A Python in Excel Help pane appears on the right side of the screen, offering help with Python in Excel. Because Python in Excel is a new development, Microsoft offers resources to help demystify the functionality (see Figure 2-4).

FIGURE 2.4 The Python in Excel Help pane offers resources to learn about this feature.

The Tour Samples dropdown filter allows you to choose which sections are displayed in the Python in Excel pane (see Figure 2-5). The default is to show all tour samples, but you can select the filter that best suits your needs.

FIGURE 2.5 Select an option from the dropdown menu to choose which section to show.

From the Tour Samples dropdown filter, you can choose to view information, open a template to explore a topic, or open a feature. If you choose Tour Samples or All Tips, you will see everything you can explore. Figure 2-6 shows some of the samples you can explore.

FIGURE 2.6 You can choose a sample to see what Python in Excel can do.

If you choose Get Started from the dropdown menu, you will see only the Get Started features.

Figure 2-7 shows one of the Get Started features, called Try Out the Python Editor. As its name indicates, it allows you to access the Python Editor.

FIGURE 2.7 The Try Out the Python Editor section

In some versions of Python in Excel, there is a Get More Premium Compute link at the bottom of the Python in Excel Help pane. You can click this link to sign up for the Python in Excel add-on (see Figure 2-8). If you do not see this link in your version, look for a diamond icon.

FIGURE 2.8 The option to sign up for the Python in Excel add-on is available from the Python in Excel pane.

Entering Python directly into a cell

The second method of accessing Python in Excel is to enter Python code directly into an Excel cell. To do this, you must enter the new Excel function PY(), which indicates that the cell will be used for Python code.

This is the syntax of the PY() function:

=PY(python_code, return_type)

where python_code is the Python code to be run, and return_type indicates whether the result is viewed as a Python object (return_type = 1) or as an Excel value (return_type = 0).

The PY() function is not a typical Excel function. It cannot be used with other Excel functions, and python_code and return_type must be static values. While it is possible to enter Python code using the previous syntax, you will enter =PY to change the prompt, enter Python code, and then choose the return type setting via the user interface. Using =PY rather than entering the full syntax of the Excel PY() function allows you to take full advantage of the Python environment for entering Python code.

Excel IntelliSense recognizes the PY() function as the function for creating Python formulas, as shown in Figure 2-9.

FIGURE 2.9 IntelliSense recognizes the function PY().

If you select the PY() function, the green Python prompt and the square bracket symbol shown in Figure 2-3 appear, but the Python in Excel pane does not.

Using the Python Editor

The two methods of accessing Python in Excel we’ve just discussed—clicking the Insert Python button and selecting the PY() function—enable you to type Python code in the formula bar. The third method of accessing Python in Excel, which we’ll look at now, is to use the Python Editor. The Python Editor is a more recent development that gives you access to a Python notebook.

The Python Editor was developed while Python in Excel was available for preview. If you are looking online for information about Python in Excel, you may see mention of the Diagnostics pane. The Diagnostics pane no longer exists, and the functionality it provided is now part of the Python Editor. As the tooltip in Figure 2-10 indicates, the Python Editor provides you with an integrated development environment (IDE). It provides syntax help and error debugging and is particularly useful for more complex Python code. (You will use the Python Editor for comprehensive examples later in the book.) You access the Python Editor by clicking Editor in the Python section of the Formulas tab.

FIGURE 2.10 The Python Editor is accessed from the Formulas tab.

When you click the Editor option, the Python Editor appears on the right side of the screen (see Figure 2-11).

FIGURE 2.11 When you click Editor on the Formulas tab, the Python Editor appears on the right side of the screen.

Because you have not yet entered any Python code, you will not yet see any Python code in the Python Editor. There is an option at the bottom of the pane to start entering Python code in the currently selected cell. (You will become familiar with the features of the Python Editor in Chapter 4, “Using the Python Editor.”)