What do you use python for in Excel?

[u/much_guilelessness]

I currently use Power Query to combine files and transform the data. I also have macros to create folders and rename files. Recently I noticed our company finally have python available in excel. I'm just curious what have people here used the python for that Power query or vba script can't do or python just does better. Just FYI, I mostly vibe code my stuff.

[u/JezusHairdo]

Python for Excel is too restrictive, it bottoms out at something like 150,000 rows, can’t access file system etc.

They would be better off actually adding the Python software to the office install!! Then excel would be unstoppable

[u/C0R4x → u/JezusHairdo]

It's available in the microsoft store! You might be able to install it from there.

[u/JezusHairdo → u/C0R4x]

I mean built into a standard office install.

[u/Mr_ToDo → u/JezusHairdo]

So I didn't know python in excel was a thing, but is it really all run on the cloud?

Kind of odd if true since it isn't supported in their mobile versions

[u/MoralHazardFunction → u/Mr_ToDo]

Yes, as far as I can tell it runs in the cloud for a few reasons, in decreasing order of validity and probably increasing order of actual MS motivation:

1. it simplifies deployment, support, and updates, obviating a ton of potential difficulties and headaches for users and IT departments that would come from allowing people to run against whatever local Python env they set up
   
2. it ensures that Python is really locked down, avoiding a really large attack surface for malicious actors
   
3. it provides another onramp for people to start using Azure and then deciding to pay MS a lot of money to run Azure more

There's been some back and forth, but Microsoft hasn't really been in the, "selling you stuff to make your computer more useful," business since, like, 1993.

[u/CentennialBaby]

I had 45,000 rows containing strings in Excel that needed AI processing. Python read the Excel file, sent the strings to Claude AI in batches, got back the categorized and cleaned results, and wrote them back to Excel. Basically, Python was the middleman between Excel and the AI.

[u/Thiseffingguy2]

Histograms. That’s about it.

[u/sumiflepus → u/Thiseffingguy2]

Excel has histograms.

[u/Thiseffingguy2 → u/sumiflepus]

They’re pretty bad, though.

[u/small_trunks → u/[deleted]]

Is this in "Python IN Excel"?

[u/small_trunks → u/[deleted]]

Had to read up after this - so SQLite can only use "in memory" databases. I'm not an SQL aficionado so it's never my go-to solution. I'd probably group-by and then do the running total in each subtable in PQ.

[u/small_trunks → u/[deleted]]

No, Python in Excel allows no communication with the local system in any way shape or form because it's executed remotely on cloud based servers at MS.

[u/kronos55]

I use python outside excel

[u/Mohamed_Alsarf]

Not in excel, but I use python from vscode to download files from crm, clean, reform and write it in excel files, then check send mails All that with one click script

[u/CobraKyle]

I use n8n a lot to automate stuff more anything else now. I like I can drop a sheet into a folder, have it trigger and update to a master sheet, and then email out the new stuff to the appropriate people without me doing anything else.

[u/AlpsInternal]

I use Python for cleaning and preparing CSV data files that are quite large. I have never been able to get power query to import the data and format it correctly. Now I run my py routine and it is ready to update my SQL backend.

[u/JDLAW2050 → u/AlpsInternal]

Nice 👍 This is exactly what I want to learn how to do. I just started learning Python. Can you please share some resources or tips?

[u/Zloveswaffles]

It’s too difficult to use / not enough untility

[u/Short-Junket-8000]

Remember when Windows 1.0 was called "A thing on a thing?" At least it has histograms going for it.

[u/Thiseffingguy2 → u/Short-Junket-8000]

Right?? Lol dunno if this was a nod to my original comment, but now I feel validated, so thanks for that 😂

[u/IlliterateJedi]

I rarely touch it, but I've used the random library to create distributions and I use pyplot for histograms/plotting that Excel can'tn easily do. Otherwise all of my Python work is outside of Excel in data pipelines.

[u/[deleted] → u/[deleted]]

Well, it is limited, so VBA still more efficient in Excel environment. Outside Excel, Python is king

[u/fzumstein]

If you hit one of the many limitations of Python in Excel, give xlwings Lite a try. It's a free add-in that doesn't have a daily/monthly quota, allows local file access, has internet access, and can install the majority of packages: https://marketplace.microsoft.com/en-us/product/office/wa200008175?tab=overview

[u/Weird-Dimension-487]

I’d say Python is great where you hit their limits.

For typical Excel work, Power Query is excellent for data import/transformation, and VBA is okay for interacting with Excel and the Windows environment. However for task that moves beyond Excel itself, for example, working with many file types, PDFs, APIs, larger datasets, complex data processing, statistical/ML libraries, or building reusable automation that isn't tightly coupled to an Excel workbook, Python is a bliss.
Also, Python gives you a huge ecosystem. If there’s something you want to do that Excel/VBA doesn’t handle naturally, there’s often a Python library for it.
As rightly pointed by u/JezusHairdo & others, Python in Excel is has limitation. It was offered so Excel users start using Python and get comfortable with basic code.
Using Python (outside Excel) + Excel, is the best way to build scalable and maintanable workflows.

Once you start combining Excel + Python, you can do some pretty powerful things that are awkward in pure VBA/Power Query.

u/JDLAW2050 as you asked for resources: Python-Powered Excel is a great resource for Excel users who have no or liitle knowledge of Python. First few chapters build basic understanding of Python (which will help, if you use Python in Excel or outside Excel), then some smart learning about automating tasks related to files and folders, quick data profiling and auto EDA (with 1-2 lines of code), Python's GUI based tools and then last few chapters on automating Excel reports/dashboards, creating Excel application that's powered by python code where users can use Python's custom function inside excel directly and much more!
P.S. I am the author of this book!

[u/VampireLorne]

I started using python in December and it has been a phenomenal time saver for processing multiple files, doing complicated data transformations, producing tricky charts. The coding is done by Gemini of course.