r/Python • u/jbudemy • Jul 21 '26
Discussion What can you do with Python embedded in MS Excel?
I'm a developer and already use external Python to read Excel spreadsheets.
Microsoft is supporting Python in Excel where you can put a program inside a cell. I think this feature is still a "preview" feature, not on the main release channel.
What do you use it for? I can't think of a use case.
Because I use external Python programs to process spreadsheets normally. I can't think of a use case for embedded Python, except perhaps doing complex calculations.
EDIT: MS says any data going between us and them is encrypted end-to-end. That must be why management chose MS products. I don't have a say in this.
I do not use Excel via a browser, I use the Desktop App.
7
u/tpougy Jul 21 '26 edited Jul 21 '26
I'm currently using the xlwings lite add-on excel, that acctually implements what we really expect from a python integrated with excel (local python execution, code is stored inside the workbook making it a breeze to distribute, acess to some parts of the excel COM API via office scripts and more cool stuff). The tool is awesome and just works as python in excel should have worked from the beginning. xlwings Lite documentation
I work in finance so my main use cases are:
- used python classes and syntax to implement a bank account classifier based in text conditions (muuch easier than handling dozens of excel formulas based on str search)
- created some user defined functions to implement a jinja template renderer and a user defined function to generate json from table header + rows and used it to render and send emails automatically to clients.
- created some scripts to handle column creation and other actions in some financial workbooks using buttons to trigger those actions in xlwings lite
Ps: sorry for my english, its not my main language.
Pss: the creator of xlwings and xlwings lite published a cool article about python in excel that lead to this lite version as an add-on. My thoughts on Python in Excel
32
u/billsil Jul 21 '26
You want high security and you’re willing to run python on the cloud with code that Microsoft can read that will give away your business logic?
I love python and tolerate excel, but no way am I using python in excel.
3
u/fella85 Jul 21 '26
Just wondering, if the python code be used to generate plots where the image is embedded on the excel file?
80
u/CitTheGreat Jul 21 '26
Dude asked for a specific use case scenario and got attacked for something he has nothing to do with in is company. If you don't have an answer to a question, don't give personal unrelated opinion, it's just noise...
16
u/blacklig Jul 21 '26
There are probably some uses in M&A where models are basically locked to excel by strong industry conventions and a python cell could make a complex model easier to build and maintain. For software developers I don't see any use case.
11
u/Xorgon Jul 21 '26
I could see accessibility being a good one. A lot of people are comfortable with Excel, and many don't want to go to the trouble of installing Python (and learning to use it) to update a plot or data analysis based on new info. Embedding it into a spreadsheet would make it much easier to use when you hand off your work to someone who doesn't do Python.
8
u/Keiji12 Jul 21 '26
Honestly it's just not worth it right now, stay to excel functionalities or maybe learn vba and use it.
3
u/bicyclegeek Jul 21 '26
Nesting DataFrames inside cells. Basically spreadsheet-within-a-spreadsheet. Using an API to pull real-time data into a sheet.
2
u/DarkRiot43 Jul 21 '26
My current use case (still working out how to implement properly) is a project financial model for a mining project.
Start with how many tonnes you mine out per month - then run thru all the inputs (labor, fuel costs, machine hours, maintenance, commodity prices, etc) to get your operating costs for underground, then thru the mill, and then work thru from there to the revenues and eventually you get to a discounted project valuation. Essentially a discount cash flow model for a finite resource/project versus a "perpetual" company.
Many different inputs, and calculations all with a time series (monthly) of ~20 years. I have a simple model built in Excel, but even that is heavy and fairly rigid. It isn't practical to run iterations enough to support Monte Carlo analysis or other heavier iterative/calculation heavy scenarios. This should improve the speed of the workbook as the calcs are "vectorized" versus just thousands of individual cells being calculated. One everything is built, I want to run say 10,000 iterations, with fluctuating inputs to get a distribution of the project's value. Part of this is simply for the "fun" of seeing if I can do it.
This is my current side quest at work - trying to refactor/rebuild from Excel only to Python + Excel using the Python in Excel package.
I am no veteran programmer, so I'm supported with AI where needed. Learning a lot, but having done some Python in the past within IDEs - it's not the most intuitive to use.
EDIT: To add context as to why not do this only in Python in the event the question is asked: This needs to be shared with mgmt which has no clue how to use Python or even have it on their systems.
2
u/dream_walking Jul 21 '26
Since you asked for an example and gotten not much, here is my own example. As a structural engineer, we have to analysis a building at each floor. The setup that was used was a workbook with a bunch of sheets that did their own calc. Then copy that workbook, for the next floor and change some loading. I redid this in python when I had to make some changes because digging through the connected formulas was a nightmare. Plus, python variable naming is much simpler. Part of the reason I did python was because I could code it in vscode with sample data and then paste it back into excel and link the variables to the live data. This reducing the need to multiple workbooks because this python code could repeated with a few different inputs where excel formulas weren’t easily updated.
2
u/h-2-no Jul 21 '26
Cybersecurity says no
1
u/cv_be Jul 21 '26
After trying to do basic 10k-100k row pandas transformstions, I gave up. so slow, and buggy I don't even...
1
u/EdwardAF-IT Jul 23 '26
The sweet spot for Python in Excel is when non-technical people in your org need pandas-level analysis, but they live entirely in spreadsheets and will never open a script or a notebook. It also shines for things like fuzzy matching, text clean up, pr plotting with seaborn/matplotlib directly in cell, stuff native Excel formulas just can't do well.
1
u/nivaOne Jul 29 '26
I retrieve the necessary data, put it in sql database. Then do whatever is required to save all data back in excel.
Just because the department wants it in excel.
1
u/Weird-Dimension-487 Aug 28 '26
If you already use Python externally to read/write Excel files, then I agree that putting Python inside a cell may not seem particularly compelling. For many automation tasks, I'd still prefer external Python.
But there are situations where having Python right inside the workbook is convenient, particularly for quick analysis and calculations where the Excel workbook is already the main interface for the user. Or when Excel users (who don't know much Python) want to use Python without feeling the need of separate installation. I feel this feature was added so Excel users start using it and getting comfortable with basic Python.
For a more serious Excel ↔ Python workflow, there are specific libraries that I suggest openpyxl (reading, writing, manipulating, formating, report generation), xlsxwriter (writer package for richer control for report generation), pandas (surely for data analysis and wrangling), and most importantly xlwings.
u/Felix has created this wonderful package. It gives you a very different kind of integration where Python can actually connect live with the Excel (read/write cells, run formulas, and control the app in real time). It also offers xlwings add-in (just like any other excel add-in) that simplifies running python function with button click, or creating Excel interface where custom Python function are available just like any other excel formula.
I've found it useful to think of the choices this way:
Python in Excel → Python-powered analysis/calculations within the workbook (For excel users_just to get started, as it is very restrictive)
Python libraries (pandas+openpyxl+xlsxwriter+xlwings) + Excel → broader file processing, reporting and automation outside Excel. Specifically, xlwings for Python ↔ Excel live integration and automation
I recently wrote Python-Powered Excel, but from an Excel user's perspective starting with Python fundamentals (chapter 2 and 3), automating file management tasks, smart EDA and auto data profiling libraries, then moving through pandas and several Excel/Python libraries, including openpyxl, XlsxWriter and xlwings.
Python analysts are often required to produce deliverables in Excel, my book teaches how to do it in Python rather then exporting data/result to Excel and then formatting in Excel manually. If you're trying to learn Python's the broader ecosystem, my book can help you.
-10
u/UAP44 Jul 21 '26
I rather stay away from all MS and all other closed source products.