Python as an Excel scripting language
Let us do scripting with Python! Yay! Not only as an alternative to VBA, but also as an alternative to field functions (=SUM(A1:A2))
Thank you to all those participated in our survey. What an amazing number of responses, many of them with very deep content. We’re processing the over ten thousand responses(!), and already appreciate the time so many of you took to answer with passion and experience.
Please know this survey is used to help influence various topics – both on Python as well as other related topics that the comments started to bleed into. Given the passion, I want to be clear this remains an area of exploration for us, without any specific timeline.
We’ll provide updates as we progress on this feature request.
Lead Program Manager
I earn my living doing data engineering, with Python. But I also use Excel for to share data output or to load into a pivot table. Even using Databricks or Anaconda is not as efficient as using Excel. That said, sometimes I need to do some extra processing or would like to add machine learning and VBA doesn't cut it, so I need to combine Excel and Python.
To @Roy and @Rodrigo, Thank you. The large organization argument makes sense.
Rodrigo Merlo commented
Some other thoughts:
Sometimes you want to handle data with python and use excels quick and dirty plotting.
Sometimes you want to handle data using excel tables and use python’s plotting.
Sometimes you want to do everything in python and use excel as a universal input output.
Honestly the combinations never end.
Having a cell contain a python list, object or function.
Turning a whole excel spreadsheet into a python function. So you can design the function as you would in excel and then using that as part of a larger python calculator.
Rodrigo Merlo commented
Edit: I agree with your answer to that question
Having python running in the background in excel can let advanced users make richer spreadsheets that everyone in an organization can use due to it being an excel file that everyone understands.
I used to work doing engineering calculations for various disciplines like heat transfer and fluid dynamics and would have loved to be able to send my python calculations as an excel spreadsheet were anyone could run different scenarios just playing around with the enabled cells.
I actually did that sort of thing by having python import and export excel files but that is just waaaay to much extra work.
I know you can use vba for that but then you miss pythons libraries and plotting.
Bill Tubbs commented
In response to @Anonymous below who wrote:
"If you are using python, why would you need to use excel?? I genuinely want to know an answer to this. If you are performing data analysis python should be all you need. I could see using Excel as a front-end just for viewing the data that was crunched by python in the background, but I would say that is a fairly inefficient software combination."
Have you ever worked in a large organization and tried to convince 500 or so other people to stop using Excel and learn computer programming?
Indeed... and as MS ignores the idea, more will be created on the Python end.
MS: that thing you're sucking? It's called "hind ***" and y'all best watch you don't get pushed off that too.
If you are using python, why would you need to use excel?? I genuinely want to know an answer to this. If you are performing data analysis python should be all you need. I could see using Excel as a front-end just for viewing the data that was crunched by python in the background, but I would say that is a fairly inefficient software combination.
Abner Wilding commented
Excel and Python: Perfect combination for data analysis and more.
Something about Python being the goto language.
Bill Tubbs commented
"Because python is easy to learn, anybody can pick it up within a few weeks of learning, there are multiple easy to follow books and how to's, it writes like a sentence, it's the fastest growing language to date, it already fully interfaces with Excel, it doesn't require a license to use...do I need to go on?" (Brian Janiszewski)
You missed the main one: Because Python is becoming the go-to language for data analysis.
Brian Janiszewski commented
"Perhaps then the request is for a built-in editor and for the code to be saved inside Excel. Fine, but why pick Python? Why not make it more general, and allow people to pick their own language? Visual Studio Code would make a good basis for this"
Because python is easy to learn, anybody can pick it up within a few weeks of learning, there are multiple easy to follow books and how to's, it writes like a sentence, it's the fastest growing language to date, it already fully interfaces with Excel, it doesn't require a license to use...do I need to go on?
Kay Sauter commented
++ for comment by @kevlangdo
Fabricio Biazzotto commented
I am using XlWings for a while, and it works great!
We are using OpenPyxl for creating, manipulating Excel/Office365 files. Works great for our purporses.
Francis King commented
The idea of replacing VBA with Python won't work for a simple reason - we're already there.
Right now VBA isn't truly embedded in Excel. It's a stand-alone product which happens to be bundled with Excel.
VBA attaches to Excel over a COM interface. You can already do this with Python. Or C#...
Perhaps then the request is for a built-in editor and for the code to be saved inside Excel. Fine, but why pick Python? Why not make it more general, and allow people to pick their own language? Visual Studio Code would make a good basis for this.
I would be content with it just replacing VBA- which honestly doesn't ever work as a result of all of MS O365 **** of SharePoint Online! It is honestly mind boggling that MS manages to get worse with every "improvement" that MS claims to implement.
I love Python too. So much, i learned much better environments than Excel, including export to Excel for Excel fans/users. There are things like Open Refine, SQL, have you tried to Google? Much better way how to spend your life and do your work than waiting if Microsoft will hear you :)
Jeremy Petzold commented
@ Anonymous, because no everyone needs to use an external data set and programming environment for handling data? Because Python is a much nicer language than VBA?
Why are you still trying to change MS plans instead of using much better and faster Python with Jupyter, Pandas aso., which can easilly handle data Excel is dreaming of?