Posey's Tips & Tricks
What You Need To Know About Using Python in Excel
Microsoft's latest update to Excel can help increase your productivity with just a little bit of Python knowledge.
One of the most useful new features that Microsoft has incorporated into Excel in recent years is the ability to incorporate Python code directly into a spreadsheet. While it has long been possible to send Excel data to a PowerShell script for processing and to have that script update your spreadsheet based on the results, doing so involves quite a bit of work. Python support, on the other hand, is natively included in Excel. Before you start using it though, there are a few things that you need to know.
The most important thing to know about Python in Excel is that you access it at the cell level. You can actually create multiple Python scripts inside of a worksheet, simply by linking those scripts to individual cells. To add Python code to a cell, you need only to select the cell and then type =PY. Upon doing so, you will see a message like the one shown in Figure 1, indicating that you can enter a Python formula. Now, just press the Tab key and you can enter as much Python code as you like. When you are done, press Ctrl+Enter to save your work.
[Click on image for larger view.] Figure 1. Type =PY and press the Tab key to begin entering Python code.
The other, and arguably simpler, way to add Python code to a cell is to select the cell and then go to the Formulas tab and click the Insert Python icon. You can see this icon in Figure 2. Once again, you will need to press Ctrl+Enter when you are done in order to save your changes. One of the nice things about using the Insert Python icon as opposed to invoking Python by typing =PY is that doing so causes Excel to display an option to take a tour of Python in Excel, so that you can learn the basics. You can see what this looks like in Figure 3.
[Click on image for larger view.] Figure 2. You can add Python code by clicking the Insert Python icon, found on the Formulas tab.
[Click on image for larger view.] Figure 3. When you use the icon to launch Python in Excel, you are given the option of starting a tour, which can help you to learn the basics.
Another really important thing that you need to know about Python in Excel is that Microsoft has created a special Python function that is used for interacting with Excel data. You can use this function to interact with cells, ranges and other types of Excel data such as names, tables, or queries. The function is appropriately named xl.
So suppose for a moment that you wanted to define a variable in Python called A and you wanted to set the variable’s contents equal to the value that is stored in cell A1. The way that you would do that is to invoke Python in your cell of choice and then type:
A = xl(“A1”)
In this case, A is a Python variable, xl is the name of the designated Excel function, and A1 is the name of the cell whose value is being assigned to the Python variable. You can see what this looks like in Figure 4.
[Click on image for larger view.] Figure 4. I have assigned a cell’s value as a Python variable.
As previously mentioned, after you enter Python code, you will need to press Ctrl+Enter to commit the code. If you have performed a simple variable assignment, as I have, then the cell will reflect the value of the variable. A small Python icon will also appear inside of the cell to indicate that the value has come from Python, as shown in Figure 5.
[Click on image for larger view.] Figure 5. The [PY] icon indicates that the value is being produced by Python.
There is one more really important thing that I want to show you about how Python works in Excel. As you saw in the previous figure, I added the Python variable assignment to cell A4. With that in mind, I am going to enter A * 2 into two different cells. As you may recall, A was the name of the Python variable that I assigned. I am going to enter this calculation into cells B3 and B5. You can see the results in Figure 6.
[Click on image for larger view.] Figure 6. I have entered A * 2 into two different cells.
As you can see in the figure above, cell B5 produced the expected result of 10. However, cell B3 is displaying a Python error, even though I entered exactly the same calculation into both cells.
The reason why this happens has to do with the way that Excel processes Python code. Excel begins processing Python code with cell A1. It then moves on to B1, then C1, and so on, all the way to XFD1. Once Excel has calculated all of the Python code on Row 1, it moves on to Row 2. It performs all of the calculations on Row 2 before moving on to Row 3. This continues, one row at a time, until Excel reaches the end of the spreadsheet.
The reason why this matters is because you can’t reference a Python variable that has not been declared (or assigned). I performed a variable assignment in cell A4. The Python formula that I entered in cell B3 produced an error, because the variable referenced by that formula was not assigned until the next row. Conversely, the formula entered in cell B5 produced the expected result because by the time that formula was processed, the variable had already been assigned.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.