Hi everyone, Kevin here. Today, we’re going to look at how you can use Python directly within Microsoft Excel. If you’ve never heard of Python before, it’s a powerful and also popular programming language that supports things like data manipulation and visualization, statistical modeling, machine learning, just to name a few. By bringing Python into Excel, you can take advantage of all of these different capabilities. It basically turbocharges Excel. We’re going to start with the absolute basics of how you even just use Python in Excel, and then we’ll look at some more advanced scenarios. Let’s check this out. Here I am in Microsoft Excel, and if you’d like to follow along today, you can access the workbook down below in the description of this video. In Excel, there are a few different ways that you can access Python. The first way, up on the top tabs up above, let’s click on the option titled formulas. Right here in the center of the ribbon, you’ll see that there’s now a new category titled Python, and right here, we can insert Python code. I’ll click on this icon, and you’ll notice that a few different things have changed on the screen. Within the formula bar, we now see this PY icon. This indicates that we can enter Python code here. Also, if we look down at cell A1, you’ll also see the PY. You can also enter the code directly into cell A1. With Excel, there are many different ways that you could insert Python code. Here, I’ll click on this X icon. You can also simply enter in a function. I’ll type in = PY, and then open parenthesis, and that too also turns on the Python mode. Here you see that icon again in the formula bar and also within the cell. Let’s click on the X one more time. You can also use a shortcut key. You could press control, alt, shift, and P, and that too also turns on Python mode. Now that we have Python enabled, we can start entering in some code. You can enter Python code directly into the cell, or you could also enter code directly into the formula bar up above. Now, this is a multi-line editor, but currently we only see one line here. To give you a little bit more space, over on the right-hand side, you can click on this dropdown arrow, and that exposes a few more lines. Here, I could also collapse it. You could also click on this dividing line, and here I could expand it, or here I could also collapse it. Now, a quick shortcut key, you could press control, shift, U, and that’ll expose more lines, and you press it again, and it’ll toggle back to one line. I’ll leave the multi-line view turned on. Let’s now start entering in some code, and we’re going to start with my favorite example. Let’s just say, Hello World in Python. I’ll insert a quote, and let’s type in Hello World, and here I’ll close out the quotes. And then let’s press enter. But you notice when I press enter, it doesn’t really do anything. It just inserts a new line within the multi-line editor. To commit the Python code, here you’ll see this tool tip that says you need to press control, enter. So, let’s try that. I’ll press control, enter, and here now in the cell, we see that it says Hello World. Congratulations. You just wrote your first Python code in Excel, making some nice progress. Now, one thing you might’ve noticed is when I press control, enter here, in cell A1, it said busy for a very brief moment before it showed the results hello world. Now you might wonder why. Hello world is really simple Python code. Well, the reason why is the Python code runs in the Microsoft cloud. Basically, that means on Microsoft servers. It doesn’t actually run on your local computer. So, it causes a slight delay. Now you might think that’s a disadvantage, but by having Python run on Microsoft servers, you don’t actually have to install Python on your local computer and keep track of all these different Python versions. So, it actually works out being somewhat of an advantage. Let’s now click into cell C4 and we’re going to enter in some more Python code. So again, to do that, let’s go up to formulas and then click on insert Python. And there again, we see that PY. I’m going to enter in a variable name. Let’s call this chocolate chip. That’s my favorite type of cookie. A variable is a container and I can assign a value to that container. So, let’s set chocolate chip equal to three. Let’s say I have three chocolate chip cookies, and then to commit this, I’ll press control, enter, and here we see three as the result. With Python, we always get the last result back in the cell. So, what do I mean by that? Well, let’s, let’s add one more variable. Here, let’s type in oatmeal raisin. A lot of people like this type of cookie, but it’s not my favorite. And let’s set this equal to, let’s say I have two of these and I’ll press control, enter. And here in cell C4, you see that I get a two back. So, what happened to chocolate chip cookie? Well, again, Excel will only ever show the last result you get back from Python. And in this case, it happens to be oatmeal raisin, and we have two of those. One of the neat things is I can now reference these variables throughout my entire workbook. All of my Python code acts as part of the same environment. To make that real, let’s use an example. Right here in cell, let’s say B6, I’ll type in, let’s say total cookies. And over here, let me adjust the column width and then let’s click into cell C6. And again, let’s insert some more Python code. For this, I’ll create a new variable called total cookies and let us set it equal to chocolate chip, And I want to add, let’s say oatmeal raisin. This will tell me how many total cookies I have. And here I’ll press control, enter, and that commits the code. And here you see that I get five back. So again, this is now taking the value of chocolate chip and oatmeal raisin from this Python code in a different cell. That’s pretty powerful. One thing to be aware of is that the position of your Python code makes a difference. I’ll click into cell C6 and then press control X to cut this and let’s move it up to cell C3 and I’ll paste it. But when I do that over on the right-hand side, we see that this pane opened up with an error message and here too, you also see an error message. This pane over on the right-hand side, this is similar to a console in an IDE. Let’s close out diagnostics, and I want to show you another way to get there. Up on formulas, here too in that Python group, there’s also the option to open up this pane and I’ll click on that. And here again, we see that error message. It says that the name chocolate chip is not defined, but why is that? Here, if I click into this cell, I define chocolate chip. Well, the reason why is Excel first runs this Python code before I’ve defined any of these values, which is why I get this error message back. So let me cut this and let’s move it to the left to see if that works. And there too, I also get that error message. Let’s now cut it again and maybe move it to the right and here it works and let’s cut it again and I can move it underneath and there it continues to work. So why is that? Well, the way that Excel runs Python code, it first evaluates the code from the leftmost position all the way over to the rightmost position. Then it works its way down the sheet and it also works from the leftmost worksheet to the rightmost worksheet. So, one thing to keep in mind is let’s say that you’re importing data or you’re defining values. It makes sense to do that in the leftmost topmost position on your leftmost worksheet, and then you can use that throughout all of your other worksheets within the workbook. So far, we have been assigning all of our variables directly in the Python code, but you can also use values directly from your Excel sheet. Over here, let’s close out the diagnostics pane and in cell B5, let’s type in, let’s say sugar cookies, another tasty type of cookie. And let’s say I just have one of them. Now, total cookies is only showing me chocolate chip plus oatmeal raisin, but I would also like to include my sugar cookies. Here at the very end, I’ll enter another plus sign and let me click directly into this cell. Here you’ll notice that it added an XL and then it has cell C5. So here it’s taking in data directly from the Excel sheet using this function. I could press control enter and here I get a result of six back. To make my code a little cleaner, let me remove this reference. I’ll delete that portion and let’s press control enter and I’ll click back into this cell. This is currently just referred to as C5, but in the top left-hand corner, I can click into the name box and let’s call this sugar cookie and I’ll hit enter. Let’s go back into cell C6 where I calculate what the total cookies are. At the very end, I’ll add a plus sign and now let’s reference this cell again. You’ll see it uses the XL function, but now instead of referring to C5, it shows me sugar cookie and makes it a little bit more readable. I’ll press control enter and here too, I also get six back as the result. I can now work with this result just like I can with any other Excel cell. Let’s say that I really just want to know how many chocolate chip and oatmeal raisin cookies I have here. I’ll enter in an equal sign and here I’ll select total cookies and then I’ll subtract the sugar cookies and here let’s press enter and I get five back. So again, I can work with the results of this Python code anywhere in my Excel workbook. For now, I’ll click into this cell and let’s delete that value. As I go through and I update different values on my sheet, so let’s say here I press control enter. It’ll recalculate all the different Python code on this sheet. Especially if you have a lot of code, that could slow things down. Up on the top tabs under formulas over on the right-hand side, we have something called calculation options. I’ll click on this. Currently it’s set to automatic. This means whenever you hit enter or control enter, it’ll go through and run all of the different calculations. You can also set it to partial where it’ll continue to evaluate all of your different Excel formulas, but not your Python code, or you could set it to manual where you have to go through and calculate. Let’s test this out. I’ll select manual and then I’ll click into this cell where I define how many chocolate chip and oatmeal raisin cookies I have here. Instead of three, let’s change this to a five and then I’ll press control enter. Now you’ll see that my total cookies hasn’t changed at all. That’s because I now have to calculate that. Up on formulas over on the right-hand side, here I have the option to calculate now. I could also press the F9 key. I’ll click on that. And here we see total cookies updates to eight. So especially if you have a large sheet with a lot of Python code, this can come in very handy. Let’s go back up to calculation options and I’ll switch it back to automatic. As we’ve been walking through all of these different examples, one thing that might have stood out to you is next to our Python code result, we get this icon here. If I click on this icon, this shows me more information about the result. Here it tells me that it’s an integer. That’s the type. And here I also see the value. This is referred to as a Python object. I don’t click out of that and up above you can shift the output type. When I click on this dropdown, currently I see that it’s outputting as a Python object. That’s what that icon represents, but I can also have an output as a standard Excel value. I’ll select that. And here now we just see a standard Excel value in this cell. You can also toggle between those types with a keyboard shortcut. I can press control, alt, shift, and M, and that’ll shift back to a Python object, control, alt, shift, and M, and that shifts it back to an Excel result. Now, at first glance, it doesn’t seem to make that much of a difference. Okay. So, there’s no icon here now. What difference does that make? Well, let’s jump onto the next worksheet titled descriptive statistics to see how it can make a difference. On the descriptive statistics worksheet, here I have a table with some cookies that we carry here at the Kevin Cookie Company and also the associated quantity. Let’s click into cell A6 and let’s insert some Python code. I’ll click on this icon and there again, I get that PY. I’m going to enter in the variable cookie table and I’ll set it equal to this table right up here. I’ll select all of this. There again, we see the function XL. It takes this range and here automatically identified that I have headers. So, it says headers equal true. Then I’ll press control enter. And here we now see that it says data frame. This is a Python object. I can right click on this object and there’s the option to show the data type card. And when I click on that, I can see all the different contents. Now you might be wondering what is a data frame? Well, it’s very similar to a table that contains data. That’s all it is. So here I’ll click out. Now this just shows me the data frame and all that data contained within this one cell. But let’s say I want to expand it just like a standard Excel table. Right up above, just like we did on the last sheet, I’ll click on this dropdown and instead of outputting as a Python object, I can output as an Excel value. And when I do that, there’s my table and there’s all the data contained within. It takes up a little bit more space. Let’s say you don’t want to see all of the data. You just want to store it in that data frame. Well, again, let’s click on this and here we can output it as a Python object, and here it’s now all contained within this one individual cell. In this example, I built a data frame using a very simple table of data directly on this worksheet, but you can pull in data from just about anywhere. It could be SQL Server, from Azure, from a CSV file. Up on top, let’s click on the data tab and over on the left-hand side, you can get data from just about anywhere using something called Power Query. If you’re interested in learning about Power Query and how it might help you pull in and import and automate getting data, be sure to check out the video in the top right-hand corner. Once you import that data, you can assign it to a data frame and then you can use it with Python. So far, we’ve been looking at some basic uses of Python, but a lot of the power of Python comes from being able to use different Python libraries. Up on the top tabs, let’s click into formulas. And again, here in the center, we have the Python group. Let’s click on the option that says initialization. This opens up a pane over on the right-hand side, and as part of Python, we get all of these different libraries. Here we import NumPy, we get Pandas, Matplot, Statmodels, Seaborn. Some of these help us with analysis and visualization. You can also import other libraries as well. For example, let’s say you want to work with PyTorch or JSON or LTK or regex. You can import these as well. Within your Python code, you simply type in import and then you can type in PyTorch and that will load that library, and you can now use that as part of your analysis and also visualization. I’ll close out of this pane. You might be wondering, well, how do I take advantage of these different libraries? Well, let’s take a very basic example of the pandas library and a method called describe. Now you might be wondering, well, what is a method? A method is very similar to a function in Excel, say the sum function, but instead it operates on an object. So, let’s test this out. I’ll click into cell A6. This is where I have my data frame. Remember this data frame is just this very simple table. And within here at the very end, I’ll enter in a dot and here I’ll type in describe. This is the name of the method. And then I’ll open and close my parentheses and then hit control enter. That method is now acting on that object, but you still see that it says data frame. So, but I want to see the results. So up on top, let’s click here, and instead of looking at the Python object, we can now switch to the Excel value. I’ll click on this. It’s busy. And here we see the output of that method or the describe method. So here I get a count of all the items. I have three, I get the mean, the standard deviation. And here I also get the percentiles. Now imagine trying to calculate all of this directly in Excel. You’d have to use different formulas and functions to get all of these different results, but with Python and with pandas and this method, we can get this entire output very easily. To see all of the different methods available within pandas, you can click on the link down below and all of the other libraries also have methods available that can do all sorts of fancy different analysis and visualizations. Let’s now jump into the very last worksheet titled Gantt chart to see the true potential power of using Python together with all of these different libraries. On the Gantt chart worksheet, I would like to generate a Gantt chart that shows me how we’re performing against all of these different tasks. I have the start date, the end date, but unfortunately when I click on insert and we look at all the different charts available, there is no Gantt chart option. Luckily, the matplot library as part of Python includes Gantt charts. So, we could build this out. First let’s insert all this data into a data frame. I’ll click into cell A10 and then let’s go back up to formulas, click on insert Python and I’ll insert a DF for data frame equals and let’s set it equal to all this data and I’ll press control enter. So here now I have a data frame that contains all the different data in this table. Now I want to plot this out as a Gantt chart, but I’m not sure exactly what code to use to make this happen. So, let’s jump over to Bing Chat to see if maybe AI can help us with this. Here I am in Bing Chat and you can click on a link to this down below in the description. Bing Chat is a fantastic way to access GPT4 entirely for free. I posed the question. I have a data table with three columns, including tasks, start date, and end date. Can you write Python code that uses the matplot library to turn this into a Gantt chart? And here it says, sure. It could help me with this. And here it gave me all this code to help me with this. Right up here, I’ll click on copy and then bring this back to Excel. Here I am now back in Excel and I’m currently in A11. Let’s go up and insert some Python code and I’ll paste in all of that code from Bing Chat. I’ll expand this a little bit so we could see this code better. Now, one thing to call out is here it makes reference to DF. Remember when we set up this data frame, we called this DF and then we imported all this data from the table. So just make sure that the name that it uses here aligns with the name that you use in your data frame. All of this looks good, so I’ll press control together with enter, and now it’s currently calculating. Let me reduce the amount of space that this takes, and here it says that I got an image back. Let’s go up to the top and let me switch this to an Excel value. And when I do that, here I see the Gantt chart. Let’s click on this icon to expand the image and look at that. Just like that, I have a beautiful looking Gantt chart. Now, of course, I probably need to make a few tweaks. If we look down at the bottom, it doesn’t show the date. It just shows the actual value of the date, but overall, this was so easy. And remember, this is not something that I could do very easily directly within Excel. All right, well, that was a quick look at how you can use Python in Excel. It really makes Excel so much more powerful. To watch more videos like this one, please consider subscribing and I’ll see you in the next video.