Microsoft Excel Tutorial - Using Python in Excel.
Today, August 22, 2023, Microsoft will release a preview of Python in Excel. It is a big day for me... I've been trying unsuccessfully to learn Python for ten years. Once Microsoft added it to Excel, I finally have some cool things working.
To download this workbook: Python In Excel - Getting Started - 2614 Sample Files - MrExcel Publishing
In this video: a getting started with Python in Excel tutorial.
How to open the Python editor in Excel
Ctrl+Enter versus Enter in the Python Editor in Excel
Returning a Value or a Python Object
Referring to an Excel range in Python
Using Variables in Python in Excel
Plotting Data Using Python in Excel
Which Python Libraries are loaded by default?
K-Means Clustering for customer segmentation
Table of Contents:
(0:00) Python in Excel is in preview
(0:29) Python Excel 101
(0:53) =PY( to Open Python Editor
(1:05) Enter is New Line. Ctrl+Enter commits the code.
(1:29) Return as Value or Object with Ctrl+Alt+Shift+M
(2:08) Loading Excel range to Python
(2:33) Show Card of Data Frame
(2:47) Referring to Data Frame
(3:19) Changing Data Frame to Excel Value
(3:37) Using Variables in Excel Python
(4:05) Cell order for variable use
(4:59) Plotting data in Excel with Python
(5:30) Python libraries always loaded
(5:51) K-Means Clustering in Excel Python
(8:15) Recap of Important Excel Python
(9:48) Cost for Python in Excel
(10:42) What are your thoughts?
Today, August 22, 2023, Microsoft will release a preview of Python in Excel. It is a big day for me... I've been trying unsuccessfully to learn Python for ten years. Once Microsoft added it to Excel, I finally have some cool things working.
To download this workbook: Python In Excel - Getting Started - 2614 Sample Files - MrExcel Publishing
In this video: a getting started with Python in Excel tutorial.
How to open the Python editor in Excel
Ctrl+Enter versus Enter in the Python Editor in Excel
Returning a Value or a Python Object
Referring to an Excel range in Python
Using Variables in Python in Excel
Plotting Data Using Python in Excel
Which Python Libraries are loaded by default?
K-Means Clustering for customer segmentation
Table of Contents:
(0:00) Python in Excel is in preview
(0:29) Python Excel 101
(0:53) =PY( to Open Python Editor
(1:05) Enter is New Line. Ctrl+Enter commits the code.
(1:29) Return as Value or Object with Ctrl+Alt+Shift+M
(2:08) Loading Excel range to Python
(2:33) Show Card of Data Frame
(2:47) Referring to Data Frame
(3:19) Changing Data Frame to Excel Value
(3:37) Using Variables in Excel Python
(4:05) Cell order for variable use
(4:59) Plotting data in Excel with Python
(5:30) Python libraries always loaded
(5:51) K-Means Clustering in Excel Python
(8:15) Recap of Important Excel Python
(9:48) Cost for Python in Excel
(10:42) What are your thoughts?
Transcript of the video:
This is a big day. Python in Excel.
It's in a preview right now. This is so funny.
I've been trying unsuccessfully to learn Python for the longest time.
I went back to Amazon, I said, "All right, what was that book I bought?". And look at this.
I bought it on August 25, 2013, equal today is August 22nd, 2023, three days shy of 10 years ago.
I just couldn't get it.
I couldn't figure out all of the prerequisites to get Python to run, but now the great news is, look at this, in Insiders Beta, we can now insert Python in Excel.
All right, so this is the Getting Started guide from a guy who knew nothing about Python before an hour ago, so this is the Python 101.
Okay. So we start with Python.
=PY, it's a new function, like that, open paren, and look at that.
The green in the formula bar tells us that we are in a Python code editor.
Now a couple of things here, Enter does not accept this, it goes to a new line.
So I'll just do simple little Python code there, 2+2, to actually commit that I have to press Control + Enter and that will commit it. Enter goes to a new line in the code.
And that's only in the Python editor.
The regular formulas continue to work the way that they work.
Now, a really important thing here is we can either return an answer as a value to the cell or as a data frame.
And there's a dropdown right here next to the formula bar that you can choose Excel value or Python object.
Right now, it's always going back as a Python object, although you can control that with Ctrl+Alt+Shift+M.
Every time I press Ctrl+Alt+Shift+M, I can go back and forth.
All right, so I'm going to do 2+2, my very first Python code from about an hour ago and I will do Ctrl+Enter, and success, 4, there we are.
Okay, now, the useful thing here is to be able to refer it to an Excel range.
All right, so I'm just going to come out here, =PY(, and it's very nice. It builds the reference for you.
You just use your mouse and select the range.
So it's the Excel function, gives us the range and headers=True.
In this case, I wanted to come back as a data frame, so Ctrl+Enter at this point. And this is like one of those data types.
I can click show card and it shows me the first five records.
It has a little index number out here, 0 based and the last five records.
And then I can reuse that data frame, so here, let's give this a try.
We're going to refer to that data frame that we already have.
So =PY, to get into the Python editor. Beautiful.
And then I'm going to refer to the data frame. It'll wrap it in the Excel function for me and [ .
Let's see, let's get sales, so this is in apostrophes dot sum and press Control + Enter to accept that. A little bit wider here.
Okay, first off, I didn't mean to return it as a data frame.
I really have to get used to this whole concept of returning it as an Excel value, right?
So there's the answer that I was expecting and it matches the sum down here.
All right, so, now that's interesting here.
First we define a data frame and then we refer to the data frame later.
But, the great thing is variables will make this all a lot easier.
So, let's create a data frame =PY to get into the Python and we'll create a variable called DF for data frame is equal to this range, Control + Enter. All right, great.
Now we've created a data frame.
Here, we're going to get rid of these, just so there's no question.
And now that I have a variable called data frame, I can just refer to that in any later code and later code is defined as left to right.
So right here, we know what DF is.
Right here, we know what DF is, but we don't know what DF is here. Does that make sense?
You have to define the variable before you can use the variable, so now that we've defined the DF variable, I can start here with =PY(. Great, and I can reuse that variable later.
Like here, DF, left square bracket in apostrophe sales, closing apostrophe, closing square bracket dot sum.
And I want to return that as not a data frame, but as a value and Control + Enter. Look at that.
Our very first hello world Python here to referring to an Excel data frame and assigning it to a variable and then doing some math on that data frame.
Python includes the ability to plot data.
So, any code you copy from the internet, they're going to tell you to import pandas as pd. Matt plot live data, frame is equal to that range.
And then I'm going to do a scatter plot of the X and Y and plot show, and I get this.
Now, that's returned to Excel as a value, not as a data frame.
The other cool thing here is if you go to formulas and initialization, they show you that these import statements are preloaded. So these are all Python libraries that are there.
So that means that even though the internet tells you that you have to use these, you don't.
So if I do Control + Enter here, that plot should continue to work thanks to the initialization pane.
All right, now, I got to tell you, the thing that intrigued me the most 10 years ago when I started reading about Python, was this cool thing called K means clustering, right?
This was late in the book like chapter 13.
It was the first place in the book where I said, "Wow, this is something that I really can't easily do in Excel and I would love to go to use Python for this, so here's our plot of all these data points.
And the idea is you have clusters of customers, clusters of customers.
And so in this case, we're going to import something from the science kit, which is one of the supported libraries, but it's not in the initialization, so it's not one of the things that's automatically imported, so we don't need to import pandas as pd.
We don't need to import mat plot lib, but we do have to do this item.
All right, so points of my data using the XL and then choose which columns I want to compare.
So in this case, it's only two columns, X and Y.
You still have to do this though, because remember when we import this, we get that index number down the left-hand side, and then right here, this is the K means.
And we specify the number of clusters.
Try and break this into three groups of customers, let's say. And so it does the fit.
And then here we're plotting the X and Y, we're coloring it with the labels and that must be like a chart theme or something like that.
And we're going to add centers and those will be red, size equals 300 and plot dot show. All right, so here we go.
Control + Enter. We're breaking into three clusters.
Takes some time. It's a lot of data.
So right here, the kind of teal cluster is centered right there.
The purple cluster is centered right there, and the yellow cluster is centered right there.
You can try different items here. Let's try to break it into 8, control + enter.
All right, so it identifies these clusters. Let's try 5, Control + Enter.
All right, so this is great for customer segmentation.
You can try and figure out which customers you should market to.
Like in theory, all of these blue customers would get the same email message or something like that.
Literally, I read about this almost 10 years ago.
Couldn't figure out what I needed to figure out in order to make it work. All right, so, this is exciting.
Remember, it is only in Insider's beta, so file account.
You have to be on Microsoft 365 Insider and sign up for the beta channel.
It's coming out today.
At some point, will they roll it out to a segment of the population or everybody? Not sure.
Look on the formulas tab to know that you have it or just right here at =PY(.
If it recognizes that and you get the nice little green symbol there next to the formula bar, you know that you have it.
Things to remember that will take me some time to get used to. Enter goes to a new line in the code.
Control + Enter will commit it.
Remember that as you're entering, you're going to either return the answer as a Python object or as a value, Control+Alt+Shift+M.
We'll talk about back and forth between the two or the little dropdown.
The XL function refers to a range. You can create variables to reuse it later.
Just remember to create the variable above or to the left of where you want to refer to that variable.
Or here you can just put everything in one single cell like that at or even more.
Also, any code you copy from the internet, they're going to have a lot of initialization and you're not going to have to do those at least for these five standard libraries that Excel has supported already.
Today, right now, while this is in preview, it is free.
Microsoft is saying that eventually when it goes live, you can consume Python created by others, but for you to write Python, there might be an additional fee.
Not quite sure that I agree with that.
I'm sure there was a lot of engineering hours that were put into here and they feel like they have to recoup that, but is that fair given that Python is free and open source?
Although, clearly from the book, I couldn't figure it out until they put it in Excel, so maybe I'm their ideal customer, the people that have wanted to learn about Python, but it was just too overwhelming to learn, right?
So that's it today, August 22nd, 2023, Python debuts and Excel. Microsoft is really excited about this.
It'd be interesting to hear from the people that use Python all the time.
Is this useful to you or not, to the people who are Excel people, those people watching this channel, is this enough to get you up over that hurdle to actually try a little bit of Python?
I'm pretty excited with my successful K means clustering just nine years, 11 months, and 28 days after I first read about it.
Hey, thanks to the Excel team for giving us Python and Excel. Thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please, down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
It's in a preview right now. This is so funny.
I've been trying unsuccessfully to learn Python for the longest time.
I went back to Amazon, I said, "All right, what was that book I bought?". And look at this.
I bought it on August 25, 2013, equal today is August 22nd, 2023, three days shy of 10 years ago.
I just couldn't get it.
I couldn't figure out all of the prerequisites to get Python to run, but now the great news is, look at this, in Insiders Beta, we can now insert Python in Excel.
All right, so this is the Getting Started guide from a guy who knew nothing about Python before an hour ago, so this is the Python 101.
Okay. So we start with Python.
=PY, it's a new function, like that, open paren, and look at that.
The green in the formula bar tells us that we are in a Python code editor.
Now a couple of things here, Enter does not accept this, it goes to a new line.
So I'll just do simple little Python code there, 2+2, to actually commit that I have to press Control + Enter and that will commit it. Enter goes to a new line in the code.
And that's only in the Python editor.
The regular formulas continue to work the way that they work.
Now, a really important thing here is we can either return an answer as a value to the cell or as a data frame.
And there's a dropdown right here next to the formula bar that you can choose Excel value or Python object.
Right now, it's always going back as a Python object, although you can control that with Ctrl+Alt+Shift+M.
Every time I press Ctrl+Alt+Shift+M, I can go back and forth.
All right, so I'm going to do 2+2, my very first Python code from about an hour ago and I will do Ctrl+Enter, and success, 4, there we are.
Okay, now, the useful thing here is to be able to refer it to an Excel range.
All right, so I'm just going to come out here, =PY(, and it's very nice. It builds the reference for you.
You just use your mouse and select the range.
So it's the Excel function, gives us the range and headers=True.
In this case, I wanted to come back as a data frame, so Ctrl+Enter at this point. And this is like one of those data types.
I can click show card and it shows me the first five records.
It has a little index number out here, 0 based and the last five records.
And then I can reuse that data frame, so here, let's give this a try.
We're going to refer to that data frame that we already have.
So =PY, to get into the Python editor. Beautiful.
And then I'm going to refer to the data frame. It'll wrap it in the Excel function for me and [ .
Let's see, let's get sales, so this is in apostrophes dot sum and press Control + Enter to accept that. A little bit wider here.
Okay, first off, I didn't mean to return it as a data frame.
I really have to get used to this whole concept of returning it as an Excel value, right?
So there's the answer that I was expecting and it matches the sum down here.
All right, so, now that's interesting here.
First we define a data frame and then we refer to the data frame later.
But, the great thing is variables will make this all a lot easier.
So, let's create a data frame =PY to get into the Python and we'll create a variable called DF for data frame is equal to this range, Control + Enter. All right, great.
Now we've created a data frame.
Here, we're going to get rid of these, just so there's no question.
And now that I have a variable called data frame, I can just refer to that in any later code and later code is defined as left to right.
So right here, we know what DF is.
Right here, we know what DF is, but we don't know what DF is here. Does that make sense?
You have to define the variable before you can use the variable, so now that we've defined the DF variable, I can start here with =PY(. Great, and I can reuse that variable later.
Like here, DF, left square bracket in apostrophe sales, closing apostrophe, closing square bracket dot sum.
And I want to return that as not a data frame, but as a value and Control + Enter. Look at that.
Our very first hello world Python here to referring to an Excel data frame and assigning it to a variable and then doing some math on that data frame.
Python includes the ability to plot data.
So, any code you copy from the internet, they're going to tell you to import pandas as pd. Matt plot live data, frame is equal to that range.
And then I'm going to do a scatter plot of the X and Y and plot show, and I get this.
Now, that's returned to Excel as a value, not as a data frame.
The other cool thing here is if you go to formulas and initialization, they show you that these import statements are preloaded. So these are all Python libraries that are there.
So that means that even though the internet tells you that you have to use these, you don't.
So if I do Control + Enter here, that plot should continue to work thanks to the initialization pane.
All right, now, I got to tell you, the thing that intrigued me the most 10 years ago when I started reading about Python, was this cool thing called K means clustering, right?
This was late in the book like chapter 13.
It was the first place in the book where I said, "Wow, this is something that I really can't easily do in Excel and I would love to go to use Python for this, so here's our plot of all these data points.
And the idea is you have clusters of customers, clusters of customers.
And so in this case, we're going to import something from the science kit, which is one of the supported libraries, but it's not in the initialization, so it's not one of the things that's automatically imported, so we don't need to import pandas as pd.
We don't need to import mat plot lib, but we do have to do this item.
All right, so points of my data using the XL and then choose which columns I want to compare.
So in this case, it's only two columns, X and Y.
You still have to do this though, because remember when we import this, we get that index number down the left-hand side, and then right here, this is the K means.
And we specify the number of clusters.
Try and break this into three groups of customers, let's say. And so it does the fit.
And then here we're plotting the X and Y, we're coloring it with the labels and that must be like a chart theme or something like that.
And we're going to add centers and those will be red, size equals 300 and plot dot show. All right, so here we go.
Control + Enter. We're breaking into three clusters.
Takes some time. It's a lot of data.
So right here, the kind of teal cluster is centered right there.
The purple cluster is centered right there, and the yellow cluster is centered right there.
You can try different items here. Let's try to break it into 8, control + enter.
All right, so it identifies these clusters. Let's try 5, Control + Enter.
All right, so this is great for customer segmentation.
You can try and figure out which customers you should market to.
Like in theory, all of these blue customers would get the same email message or something like that.
Literally, I read about this almost 10 years ago.
Couldn't figure out what I needed to figure out in order to make it work. All right, so, this is exciting.
Remember, it is only in Insider's beta, so file account.
You have to be on Microsoft 365 Insider and sign up for the beta channel.
It's coming out today.
At some point, will they roll it out to a segment of the population or everybody? Not sure.
Look on the formulas tab to know that you have it or just right here at =PY(.
If it recognizes that and you get the nice little green symbol there next to the formula bar, you know that you have it.
Things to remember that will take me some time to get used to. Enter goes to a new line in the code.
Control + Enter will commit it.
Remember that as you're entering, you're going to either return the answer as a Python object or as a value, Control+Alt+Shift+M.
We'll talk about back and forth between the two or the little dropdown.
The XL function refers to a range. You can create variables to reuse it later.
Just remember to create the variable above or to the left of where you want to refer to that variable.
Or here you can just put everything in one single cell like that at or even more.
Also, any code you copy from the internet, they're going to have a lot of initialization and you're not going to have to do those at least for these five standard libraries that Excel has supported already.
Today, right now, while this is in preview, it is free.
Microsoft is saying that eventually when it goes live, you can consume Python created by others, but for you to write Python, there might be an additional fee.
Not quite sure that I agree with that.
I'm sure there was a lot of engineering hours that were put into here and they feel like they have to recoup that, but is that fair given that Python is free and open source?
Although, clearly from the book, I couldn't figure it out until they put it in Excel, so maybe I'm their ideal customer, the people that have wanted to learn about Python, but it was just too overwhelming to learn, right?
So that's it today, August 22nd, 2023, Python debuts and Excel. Microsoft is really excited about this.
It'd be interesting to hear from the people that use Python all the time.
Is this useful to you or not, to the people who are Excel people, those people watching this channel, is this enough to get you up over that hurdle to actually try a little bit of Python?
I'm pretty excited with my successful K means clustering just nine years, 11 months, and 28 days after I first read about it.
Hey, thanks to the Excel team for giving us Python and Excel. Thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please, down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
