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))
Daniel
shared this idea
Wow.
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.
Thanks!
Ashvini Sharma
Lead Program Manager
Excel
463 comments
-
kevlangdo
commented
@Bill Tubs
imho it is about giving users choice. Also, not everyone is a programmer and Excel is and will continue to be a vital "business" tool for data analysis and presentation and even for data storage. Doing ad hoc data analysis in Excel is very productive. However, sometimes, you need extra tools like VBA , Pytrhon or JavaScript to go that extra mile.
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.
-
Anonymous
commented
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
@Bill Tubs,
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?
-
Roy
commented
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.
-
Anonymous
commented
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.
-
Alfred
commented
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
-
kevlangdo
commented
Hi Francis
You have a valid point about using Python, C#, VB.Net etc. and Excel from Visual Studio as an add-in. However, I think that Python and R for that manner have an edge since their are so many libraries that have been created for data analysis, data science and data engineering. Also, Python already has the APIs to be embedded into other programs like Excel. Of course, apart from Python and R, MS could extend the JavaScript or TypeScript functionality (office.js) and provide libraries in JS for the above use cases.
-
Fabricio Biazzotto commented
I am using XlWings for a while, and it works great!
-
Anonymous
commented
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.
-
shan
commented
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.
-
Anonymous
commented
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?
-
Anonymous
commented
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?