Neil Blackwood:
Welcome to the Excel Tips Podcast. My name is Neil Blackwood and this is the first part in a 3-part series on Python in Excel. Now, Python is a very popular programming language. It's been around for a while.
It's a fully-fledged programming language and there are lots of resources on the internet for Python. Well, now, you can use Python code inside a cell in Excel. Now, as I mentioned, this is the first in a 3-part series.
Today, I just wanted to talk about the basics that will get you started. And in the second session, we'll look at some code examples and talk about using code. And then, in the last one, we'll look at some applications in Excel. Now, a trivia question, what or who is Python named after? I'll give you the answer at the end of the podcast.
So the Python icon is in the Formulas tab. You also need an internet connection. Okay. So the way Python works, when you write code inside a cell, when you hit Ctrl + Enter, because you have to use Ctrl + Enter after you enter Python code, what happens is the code is sent to a container in a Azure database. And it is run in that database and then it comes back as the result.
So you need an internet connection to use Python in Excel. It's pretty quick. Sometimes you do see the busy message pop up in the cell. One thing that that does mean is that everyone is using exactly the same version of Python right around the world because it's based on the version that the Azure database is using.
On that internet connection. There are quotas involved. So there is a premium version of Python and... That's basically a speed premium. So you get very fast response and you get a lot of that free but there is a quota. And once you meet that quota on a monthly basis, you will then drop down to a, what I'll call a standard speed and there is unfortunately a quota if you use it too much that you might even get blocked. So just a heads-up on that. There is an add-in that you can pay a subscription for, either monthly or yearly, and that gives you access to the premium on a sort of permanent basis.
Python in Excel is not meant to be a replacement for Excel's normal functionality. It's basically meant to improve Excel's data handling and data visualisation. So Python is quite strong in both of those and that's what you're gaining in having the ability to use Python within Excel. As I mentioned, there's lots of resources online for Python and most of the AI systems around the place can write very good Python code as well.
Now, to put code in a cell, there's three ways to do that. You can click on the Python icon. The way I tend to do it is typing =PY and then hitting the Tab key and that creates a Python cell, which has a green little banner on the side so that it highlights. And when you do use Ctrl + Enter to put it in, then there's also a PY in the cell as a visual clue that that cell is a Python cell.
There's also an editor. So there's an editor icon in the Formulas tab in the Python section and that opens up a task pane on the right-hand side of the screen. There's also a Python task pane as well which has lots of information on Python in Excel, so you can check that out as well. But the editor allows you to make changes. It's got a few icons that make things a bit easier. So you can either use the formula bar or you can use the editor to make changes or put code in.
Now, dependencies. When you create code in a Python cell, you can use variables which is pretty common when you are working with programming code. The thing is though, if you create a variable, let's say in cell A1, in a Python cell, you can use that variable in any other cell in that sheet. And if the sheet you were working on happened to be the first sheet in the file, then you can use that variable across the whole file. Basically, there's a top, down, left to right dependency. So if you create it in Column C, then Columns A and B can't access that variable, okay? So that's the dependencies.
What it might lead to is that you might use a initialization type sheet as your first sheet. A pretty common abbreviation is INIT for that. And you might create all of your variables and things that you want to use throughout the file and put that in the very first sheet and call it INIT, short for initialization.
There's a built-in way to refer to Excel cells. It's called the Excel function. It's actually the letter X and the letter L and then parentheses around the cell references. So you can refer to cells or ranges. If you refer to a range in Excel, you will end up with what's called a data frame. Now, a data frame is associated with what's called a Pandas. So when you load Python, there are a number of other libraries that are loaded at the same time. These are not standard with Python, but they are very common with Python.
And so, one of those libraries, and I'll talk about more of the libraries in the second episode. But one of the libraries, probably one of the most popular libraries in Python is called Pandas. It stands for panel data, and it was shortened, and it's also a panda which sort of works in with Python. And it allows you to work really well with datasets. So when you link to a, let's say, a data table or even a list, what you will see is that Python will return the word data frame.
And to actually see it, you need to click on the left-hand side of the Python cell and there's something called Excel values there. There's also something called Python object which is what the data frame is. And when you choose Excel values, then you will see all of the entries that are actually in the data frame.
Now, just a note on calculations. I mentioned earlier about the fact that there's quotas. So in the developmental stage, or if you're using Python a lot, you might want to turn Python off. So in the Formulas tab, there is a calculations options drop-down and there's a new option in there. So the middle option used to relate to data tables but the middle option now has been renamed to partial.
So basically, there is an automatic calculation which is sort of standard. The last option is manual, which means nothing is recalculated, and then the middle one is now called partial. And that will stop the Python cells calculating and also stop the data tables. So the middle one stops Python from calculating.
So Excel and Python, they go pretty well together. And in the next session, I'll be looking more at the Pandas that I mentioned and also mentioning some of the other libraries that are automatically loaded when Python is loaded. And as I mentioned, in the third in the series, we will look at some of the applications that you can use Python for in Excel.
Just to finish off, who or what is Python named after? Well, funnily enough, it's named after Monty Python, the British comedy team. So there you go. Hope you've enjoyed that and hopefully, I'll speak to you in the next episode where we'll look at more Python in Excel. Thanks for listening.
