Microsoft Excel Tutorial: Microsoft Labs in Cambridge releases a great Python editor for Excel.
Just 27 days after Python preview appears in Excel, Microsoft has added a dramatically better Python editor.
To download the workbook from today: Excel Adds Python Editor 27 Days After Python Debuts - Episode 2625 Sample Files - MrExcel Publishing
This new editor in the task pane offers AutoComplete, Intellisense, and automatic code coloring. Take a walkthrough the editor in today's video.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
Table of Contents
(0:00) Python Editor Added to Excel
(0:10) Free Silo Photos
(0:20) Theories on backstory
(0:59) Get Add-Ins moving from Insert to Home tab
(1:22) Update Excel Labs add-in twice
(1:49) Open Excel Labs & Python Editor
(2:00) Contrast Formula Bar editor to Python Editor
(2:17) Expand Editor
(2:33) Pane shows preview of data frame
(2:45) Color coded
(3:00) Images won't render in editor
(3:10) Adding a new Python cell
(3:24) AutoComplete
(3:45) Choose Python object or Excel values
(3:56) Easier to use XL function in formula bar than python editor
(4:50) Sorting in Python
(5:40) Scratchpad without committing the code
(6:03) In Manual Calculation mode, only one cell calculates when committing code
(6:34) Wrap-up
Just 27 days after Python preview appears in Excel, Microsoft has added a dramatically better Python editor.
To download the workbook from today: Excel Adds Python Editor 27 Days After Python Debuts - Episode 2625 Sample Files - MrExcel Publishing
This new editor in the task pane offers AutoComplete, Intellisense, and automatic code coloring. Take a walkthrough the editor in today's video.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
Table of Contents
(0:00) Python Editor Added to Excel
(0:10) Free Silo Photos
(0:20) Theories on backstory
(0:59) Get Add-Ins moving from Insert to Home tab
(1:22) Update Excel Labs add-in twice
(1:49) Open Excel Labs & Python Editor
(2:00) Contrast Formula Bar editor to Python Editor
(2:17) Expand Editor
(2:33) Pane shows preview of data frame
(2:45) Color coded
(3:00) Images won't render in editor
(3:10) Adding a new Python cell
(3:24) AutoComplete
(3:45) Choose Python object or Excel values
(3:56) Easier to use XL function in formula bar than python editor
(4:50) Sorting in Python
(5:40) Scratchpad without committing the code
(6:03) In Manual Calculation mode, only one cell calculates when committing code
(6:34) Wrap-up
Transcript of the video:
Python Editor added to Excel.
Just a quick shout out to Waldemar on Unsplash.com.
If you're ever looking for silo photos as a metaphor for corporate environments.
This guy has just some beautiful, beautiful photos out there.
There are times where you would really want to know the backstory between something.
August 22nd, 2023, Python debuts in Excel, and 27 days later the editor arrives.
Now, there's a couple of theories.
One theory could be that the editor just took longer than Python.
Another theory is that the people in Redmond debuted Python. Then the people at Excel Labs in Cambridge took a look at that and said, "We can make this better, right?" And if that theory is the one.
If they spun this up in 27 days and tested it and rolled it out to us, that's amazing.
Kudos if that's the way it went down.
All right, first off.
To get this, you're going to go to Get Add-Ins.
Which forever has been on the Insert tab.
But it sounds like they're moving it to the Home tab.
Here on my Excel, it's on the Home tab.
That could just be an experiment though.
So it's either on the Insert tab, under get add-ins or on the Home tab.
You'll choose add-ins and then go look for Excel Labs.
Now if you've already downloaded the Advanced Formula Environment.
You probably already have the icon out here.
When you click it, it says that we need to Update Now.
So go ahead and click update now.
Okay, well, I paused the video there.
Excel Labs disappeared.
So I went to Add-ins, my Add-ins, See All, Excel Labs.
Clicked on Excel Labs.
Clicked on Update Now again.
And that seemed to have worked.
I don't know why it took two tries.
So the advanced formula environment, we've had this.
Putting ChatGPT in Excel thing.
And then down here the Python editor, that's the amazing part.
All right, so here's a workbook from a month ago where we were building Python in the formula bar.
Come here to Excel Labs, scroll down and open the Python editor.
We can minimize the formula bar now.
All right, first thing, we should be able to expand the editor here.
For some reason that's not working for me today, but it's easy enough just to expand it, right?
I'll check this out.
We have a choice of all Python cells or just the selected Python cell.
So if you want to work in one cell, you can do that.
This particular cell just happens to be returning a data frame.
It is almost like the console showing you the output preview of what the data frame's going to look like.
We'll go back to All Python Cells.
Code is color-coded, so comments in green.
It's just so much better to work in this instead of in the formula bar.
A couple things, they said that if the preview is an image here that that is not working right now.
So they can't render images in the pane yet.
If you're currently on a Python cell, the choice down here is to add a Python cell at the bottom.
But if you choose a cell that's not a Python cell, then it'll say add Python cell in the active cell.
You can write some code here.
Look at that, auto-complete shows all the choices.
It's just crazy that for three or four weeks we've been writing Python without auto-complete and working in the formula bar.
It just feels like this is an entire order of magnitude better than what we've had.
Once you get your code, you're going to choose whether you're going to do a Python object or convert to Excel values and then save it to the cell.
It's not until you save it to the cell that it actually gets written there.
Here's a brand new workbook and I just want to create a data frame to point to that range.
With Ctrl+Alt+Shift+P, it's easy.
We just let's say, df= and then select the range and it'll build that syntax for us.
Want to escape there.
Say, add Python sell in F3.
It doesn't work.
We can't select the cell and have it fill in the XL for us.
So you're going to have to type the XL and everything.
Headers equals true.
Let's see, we're going to return this as convert to Excel values.
We'll just show df and save.
So we get our Python output.
And then I'll come down here to another cell.
df=df.sort on values and we want to sort by sales.
Ascending is false.
I will return that as Excel values and save, right?
And we've sorted high to low.
I love that this is a scratch pad out here in the task pane.
So let's say I'm in the middle of writing some code and I need to go look something up, all right?
I don't have to commit this code.
I can just leave it over here in the task pane and go select a sale, go select another sheet, go out to YouTube and watch a video or something like that, and then come back and finish it and click save.
That's really nice.
They also said if we go to manual calculation mode that then you can in essence change just one Python cell and have only that cell calculate when you commit.
You don't have to run everything.
So there's certainly advantages there.
This is a great improvement from the Excel Labs people in Cambridge, and it's hard to believe that we've been writing Python without this now that we have it.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast 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.
Just a quick shout out to Waldemar on Unsplash.com.
If you're ever looking for silo photos as a metaphor for corporate environments.
This guy has just some beautiful, beautiful photos out there.
There are times where you would really want to know the backstory between something.
August 22nd, 2023, Python debuts in Excel, and 27 days later the editor arrives.
Now, there's a couple of theories.
One theory could be that the editor just took longer than Python.
Another theory is that the people in Redmond debuted Python. Then the people at Excel Labs in Cambridge took a look at that and said, "We can make this better, right?" And if that theory is the one.
If they spun this up in 27 days and tested it and rolled it out to us, that's amazing.
Kudos if that's the way it went down.
All right, first off.
To get this, you're going to go to Get Add-Ins.
Which forever has been on the Insert tab.
But it sounds like they're moving it to the Home tab.
Here on my Excel, it's on the Home tab.
That could just be an experiment though.
So it's either on the Insert tab, under get add-ins or on the Home tab.
You'll choose add-ins and then go look for Excel Labs.
Now if you've already downloaded the Advanced Formula Environment.
You probably already have the icon out here.
When you click it, it says that we need to Update Now.
So go ahead and click update now.
Okay, well, I paused the video there.
Excel Labs disappeared.
So I went to Add-ins, my Add-ins, See All, Excel Labs.
Clicked on Excel Labs.
Clicked on Update Now again.
And that seemed to have worked.
I don't know why it took two tries.
So the advanced formula environment, we've had this.
Putting ChatGPT in Excel thing.
And then down here the Python editor, that's the amazing part.
All right, so here's a workbook from a month ago where we were building Python in the formula bar.
Come here to Excel Labs, scroll down and open the Python editor.
We can minimize the formula bar now.
All right, first thing, we should be able to expand the editor here.
For some reason that's not working for me today, but it's easy enough just to expand it, right?
I'll check this out.
We have a choice of all Python cells or just the selected Python cell.
So if you want to work in one cell, you can do that.
This particular cell just happens to be returning a data frame.
It is almost like the console showing you the output preview of what the data frame's going to look like.
We'll go back to All Python Cells.
Code is color-coded, so comments in green.
It's just so much better to work in this instead of in the formula bar.
A couple things, they said that if the preview is an image here that that is not working right now.
So they can't render images in the pane yet.
If you're currently on a Python cell, the choice down here is to add a Python cell at the bottom.
But if you choose a cell that's not a Python cell, then it'll say add Python cell in the active cell.
You can write some code here.
Look at that, auto-complete shows all the choices.
It's just crazy that for three or four weeks we've been writing Python without auto-complete and working in the formula bar.
It just feels like this is an entire order of magnitude better than what we've had.
Once you get your code, you're going to choose whether you're going to do a Python object or convert to Excel values and then save it to the cell.
It's not until you save it to the cell that it actually gets written there.
Here's a brand new workbook and I just want to create a data frame to point to that range.
With Ctrl+Alt+Shift+P, it's easy.
We just let's say, df= and then select the range and it'll build that syntax for us.
Want to escape there.
Say, add Python sell in F3.
It doesn't work.
We can't select the cell and have it fill in the XL for us.
So you're going to have to type the XL and everything.
Headers equals true.
Let's see, we're going to return this as convert to Excel values.
We'll just show df and save.
So we get our Python output.
And then I'll come down here to another cell.
df=df.sort on values and we want to sort by sales.
Ascending is false.
I will return that as Excel values and save, right?
And we've sorted high to low.
I love that this is a scratch pad out here in the task pane.
So let's say I'm in the middle of writing some code and I need to go look something up, all right?
I don't have to commit this code.
I can just leave it over here in the task pane and go select a sale, go select another sheet, go out to YouTube and watch a video or something like that, and then come back and finish it and click save.
That's really nice.
They also said if we go to manual calculation mode that then you can in essence change just one Python cell and have only that cell calculate when you commit.
You don't have to run everything.
So there's certainly advantages there.
This is a great improvement from the Excel Labs people in Cambridge, and it's hard to believe that we've been writing Python without this now that we have it.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast 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.
