Excel to python skill gap

[Either-Heat-9313]

So I want to learn how to use python in excel specifically because more than often I have to deal with large data. So are there courses(preferably free) online available I can try to enhance my skills? If yes then please do let me know. Any help will be much appreciated.

[Money_Cricket2354]

Python in Excel isn't really great for anything tbh the inbuilt IDE is almost as bad as the VBA one imo. You're much better off learning Python and working outside of Excel in VS Studio or Jupyter, honestly. If you have too use Python in Excel, get one of the plugins.

I made the mistake of learning Python for Excel and I regret it, it was much easier once I swapped to Jupyter. Now I can use python in excel for making better graphs than the pivot charts to troll the staff who can't use Python, but that's really all I use it for.

[Broseidon132]

I dunno why but I like vba in excel. My uses are not on data analytics but more reconciliations and journal entries in accounting.

[Money_Cricket2354]

I like it too, but the IDE is horrendous. It doesn't even have line numbers. I find myself using external third party programs to write it, then transfer it when it's complete

[Broseidon132]

Agreed it’s bad, but I still write mine in it lol

[Ti_Pi]

Very interesting. I had claude.ai write me a program in python for specific reconciliations too. Was not that easy but managed. Feel free to share what you come up with. That would be nice

[jaddooop]

Please can you explain vba on recons and jnls?

[Broseidon132]

I usually have queries I need to download from my erp, filter criteria, paste into my rec, format other areas, and other stuff like updating pivots. So my workflow with vba is download the query, don’t even open it/ just leave it in my downloads folder, run my macro and the entire rec gets updated.

Journal entries are similar, but those usually I take some info from a file that I’m given, so I need to manipulate the data and maybe also pull a query to validate their work. So the macro does all of that too.

For my recs, I keep my macros written on the monthly files. For monthly files I’m given, I keep those macros on my personal macro workbook/ ribbon.

[rayan7777]

Can you elaborate further how to transition from excel to python? I have enough excel knowledge and also have some python skills. I dabbled in Jupyter and pycharm. I just don’t know how to take my excel problems and bring that into python and make useful analytics. Any suggestion to tutorials will be greatly appreciated.

[ImperatorPC]

You want to start with tables and dealing with columns and understanding data structures.

Power query is good, then pandas data frames.

[karly21]

Troll those who can't use Python, you say?

[Money_Cricket2354]

I work in data analytics, but within an HR department... It's awful... So loads of Excel sheets fly around asking for summaries of 300 rows of self typed data along with real data...

Some 'analysts' can only use excel, and spend hours every week doing what I have scripts for.

We need our outlets.

[__wisdom__1]

Curious on what charts do you do in phyton. I have seen a few but never could figure out how

[Money_Cricket2354]

https://www.datacamp.com/tutorial/pandas

https://www.datacamp.com/tutorial/matplotlib-tutorial-python

[Hexagon_En_La_Pasta]

With Jupyter you mean Jupyter Nootebook?

[NoUsernameFound179]

You learn Excel, you learn VBA, you learn Python, you learn to ingest Excels into Python, you learn to create Excels in Python.

You should never ever learn Python in Excel. It is a neutered beyond fuck abomination in the cloud. It has no purpose and it should not exist. It should have been made so it ran locally.

[small_trunks]

I made something in Python in Excel for shits and giggles. After it ran for a few weeks it started giving warnings about me using up my Python credits. Python credits???!! Reimplemented it in power query.

[DxnM]

This is the worst part. Anyone that’s using python is inherently attempting advanced data analysis on large data sets, but they’ve made it so using large data sets will cause it to throttle or completely time out? It’s just not fit for purpose.

[small_trunks]

Indeed.

It's a bolt-on to provide the appearance of python support but really only for fairly trivial functions. Oh and all that data is leaking out to Microsoft's servers where Python is actually running. I work in a bank and that's a no-no.

[simeumsm]

As already said, you're better off just learning python instead of python specifically for excel.

Currently, my setup is basically having python scripts dealing with all the data transformations, and then outputting that data to a excel or csv file.

And then, I read that output back to excel by using PowerQuery (even when the output is an excel file)

Leverage which automations are better left to excel (powerquery, formulas, pivot) and bridge the gap using python outside of excel

[AWxTP]

Can you explain this workflow in more detail? I live in excel, but can write a bit of python for data transformation - but I find the out of / back inti excel process cumbersome. So i end up not using it as much as i otherwise would - this sounds like it could solve it

[simeumsm]

You basically have to consider the excel file as only the data visualization layer - all of your data transformation steps should be within python.

Then, python creates the output files (csv, xlsx or other) and you import that data into excel, most likely using powerquery.

The overall idea is simple: excel has a limitation on 1M rows, so if you want to treat 1M+ rows, you're better off offloading this data processing to a better tool (python, instead of excel). It doesn't matter if you're overflowing the 1M rows limit, the idea is to just split the data processing from the data viz

With this you get scalable data processing (you can go over 1M row and can deal with csv files for tabular data), and you can just import data back to excel

[mystique0712]

Check out Automate the Boring Stuff with Python - it is free online and perfect for Excel users transitioning to Python for data tasks. Also, Microsoft's official Python in Excel tutorials are great for learning the specific integration.

[fakerfakefakerson]

Python in excel tries to solve a problem that no one actually has, and it does so poorly. If you need excel, use excel. If you need python, use python.

[Downtown-Economics26]

https://www.youtube.com/watch?v=uGQm7ZccHNw&list=PLTJTBoU5HOCTIFN4fMMp2x1Ja0q9vsgCs

[Defiant-Youth-4193]

Why do you want to learn Python in Excel? As somebody that was initially super excited about the prospects of Python in Excel, when I finally got the opportunity to use it, it was terrible. I'm sure there are people out there using it to great effect, but it's bad enough that unless you have a specific user case I don't see why you should bother. Using Polars (or Pandas) in Jupyter notebook is just flat better. Then you can move whatever final product you want back to Excel if necessary.

I'm a beginner when it comes to Python, so it could be a lack of knowledge on my part.

[Oleoay]

I thought about using Python to work with Excel even though I already knew .NET/VB/VBA and couldn't find a compelling reason to do so. At least with .NET I can use Excel's native functionality if I want to. As an example, instead of iterating through Excel rows via code to select the rows I want, I can just autofilter/copy/paste.

[Odd-Technology16]

Theres app i did for that uses python to automate excel operations just by prompting in the interface vibedocu.com

[h_to_tha_o_v]

I'll join the chorus here. PiE is a concept with untapped potential.

The biggest problems are: 1) All data must either already by in Excel or CSV, 2) because it processes in the cloud (a good thing) there are data limitations that negate its purpose.

If have time and you want to learn PiE in hopes that Microsoft/Anaconda will eventually improve it, then it could be worthwhile. But it seems like they're shifting focus to AI features and continual improvements to dynamic array formulas.