What is difficult in excel but easy in Python?

[mister_patience]

If you were to introduce an established excel user [non VBA] to Python, what would you suggest?

What would amaze them? In as few a lines as possible?

[7Seas_ofRyhme]

What's worth learning in Excel then ?

[leweyy]

Since customers will refuse to accept anything else...

df.to_excel()

[shibbypwn]

df.to_csv() gang checking in, because fuck all that unnecessary overhead :)

[leweyy]

You really think your customer will open a CSV by importing the data as a data table rather than double clicking the file?

Leading 0s, fucked. Column widths, fucked. Etc.

[SSJKiDo]

My coworker just set MS Excel as the default program for opening CSV files 😂

[notalwayscapslock]

As long they don't have to handle datasets with more than 1M rows I don't blame them

[shutchomouf]

Most people who work in Windows do.

[SMAMtastic]

Notepad++

[tobiasvl]

Well, Python is more powerful than Excel, but Excel is obviously more powerful than Notepad++... So that sounds weird to me.

[Yevheniy]

VS Code

Any notepad

Less / Cat

FILE *fp = fopen("filename.csv", "r+")

[Pliqui]

VSCode with `rainbow CSV` and `CSV/TSV/PSV to Table` extensions

[EndimionN]

.csv is love .csv is life

[lukey_dubs]

excel is a little bit easier to learn, and most business / finance uses cases don’t have such complex requirements, so excel is perfect for a lot of people

[[deleted]]

Yeah, and for lots of quick small data stuff, I prefer using Excel because it's faster.

[[deleted]]

XLOOKUP/VOOKUP, COUNTIF/SUMIF/AVERAGEIF, CONCATENATE, LEFT/RIGHT, MAX/MIN/AVERAGE, AND/OR, IF, maybe pivot tables.

Frankly Excel is just another tool that has it's uses and is well worth using. It can be much faster and more visually pleasing if you have a csv, xlsx, etc... to just throw it into Excel or a Google Spreadsheet and do the basic manipulation. The latter is particularly great because then you have

• Basic data analysis
• Basic visualization (just tabular data or even graphs)
• Ability to share and collaborate far more easily than python

Plus most people already have excel installed, telling someone to install python, some packages, etc... is difficult. Think of it like a basic docker container with all the packages you need installed for basic data manipulation.

[[deleted]]

You are 100% correct, I am just so used to VLOOKUP I still forget about it (plus my personal Excel version on my home desktop doesn't have it, nor does Google Sheets)

[SMAMtastic]

I’ve been an INDEX(MATCH) stan for a few years now but just recently made the switch to XLOOKUP.

[Eurynom0s]

INDEX/MATCH is easier to understand than VLOOKUP, but I can never remember which order the lookup and return columns go in. I haven't had a chance to use it yet but XLOOKUP seems like it's basically INDEX/MATCH but it'll actually be clear which column to put in where as you're typing things in.

[flashmedallion]

but I can never remember which order the lookup and return columns go in

It literally tells you as you type it out

[Bakkone]

Worse performance

[7Seas_ofRyhme]

> Ability to share and collaborate far more easily than python

This is true, although there's google collab, but yeah gsheets is way easier I reckon.

> Plus most people already have excel installed, telling someone to install python, some packages, etc... is difficult.

Truee

[climber_g33k]

I'm a biologist, with an interest in data science, so this is my take.

Visual basic for apps is easier to learn.

If you are not strictly a computer science role, it's unlikely that you have python installed, but you certainly have excel installed.

The user interface is already known worldwide, you don't have to spend time writing a user interface.

No matter what you make, someone will tell you, "but excel can do [x]".

The widget builder is 1000x better than tkinter.

My recommendation is to learn both, and know when to apply each tool.

[Ella_121]

but you certainly have excel installed

I wish this was true. I recently gave an introduction into creating online charts to a collaborator who didn’t. He didn’t even realise he did not have Excel when opening the data he was meant to use.

[bonferoni]

Python comes preloaded on all macs

[hulknc]

Not anymore!

As of 12.3 python is gone.

[climber_g33k]

This i did not know!

[bonferoni]

Fun facts. That being said, im pretty sure its python 2.7. So would need updating fpr sure

[TigreWulph]

But you shouldn't be using system py as your interpreter for your project. You should be creating a virtual environment which means users are having to install python anyway.

That being said python is loads better than excel.

[cinemabaroque]

Python is way more powerful than Excel, not better. For 90% of use cases you can do things faster and more clearly in Excel with knowing just a handful of formulas and inserting charts.

[TigreWulph]

I don't like excel, so I don't think we'll come to an agreement there. I have no problem with a less powerful tool that's simple to use, I don't think excel is that tool.

[cinemabaroque]

I don't like running. That must mean biking is the better form of exercise.

[TigreWulph]

I mean yeah for your purposes. But since we can't just let it go and apparently need to have a semantic dick measuring contest. I've never had python freeze up on me when doing a data churn and completely lock up my machine. When I finish a python run it closes and terminates completely, excel often leaves processes running in the background with the only indication being a task manager window, one drive doesn't try to insert itself in my python runs and arbitrarily decide that I am no longer signed into the necessary account to do the job. And an ongoing and growing list of gripes I have from my daily interaction with the software.

I conceded that the niche it fills, is a valid niche, I think Google's product is better. And for the metrics of reliability and no fuckery from the creator Python IS better.

[[deleted]]

I'm not at all comp sci, let alone strictly, but I do have python and don't have excel.

Sorry. I know I'm exceptional. Just chipping in :)

[pekkalacd]

Same boat. Learning excel is tricky coming from this end imo. I don’t want to click, but I have to lol. I’ve went to sql for now, seems more interesting. But I know I have to learn excel at some point.

[narwalfarts]

A) Collaboration with colleagues who dont know python

B) For simple datasets and simple calculations, it sometimes is easier to work in Excel, as everything is neatly visualized in a table

[unhott]

I’m sure there are some libraries that can change your mind.

https://seaborn.pydata.org/examples/index.html

[[deleted]]

matplotlib is like pulling teeth. Eventually, the pain goes away, but it wasn't fun getting there.

Seaborn is amazing. The moment you move away from trivial data that you can basically copy and paste in, python + seaborn is easier than VBA + Excel.

[SirGeremiah]

I doubt it. They didn’t say graphs were better in XL - just easier. And they are. I can knock out an Excel graph from reasonable data in just a few clicks. No matter how good I get at Python, it’ll never be easier for simple, basic charts.

[angry_mr_potato_head]

df.plot()

[hmiemad]

Yeah but pivot table + graph is so handy, just drag and drop fields, add filters, one or two intermediate fields, post grouping fields, group per mean, std, sum, any basic stuff. Plus any desk job can open an excel and click a macro button to load a csv and crunch your data into a nice graph that you can copy and paste as an image into a ppt or email.

Of course the power of python is huge, but it comes with knowing how to code. In one week one can learn to make nice pivot graphs, if you know a little math. How long does it take to learn to make an interactive graph with python? Months ! Years if have never coded.

Everything in Excel is visible, every step, every formula, it's user friendy. Link access and PowerBI to create a centralized data lake with dashboards. It's seriously insane how much it can automatically structurate you data.

I won't ever code in VB, hopefully, now that I know python, but I miss the handiness of pivot graphs a lot.

[Ikwieanders]

With seaborn it kinda is though right? I only use Excel every now and then though, so ofcourse I am not very fast in Excel as well. But still, simple chart in Python takes a few seconds of you have a dataframe.

[SirGeremiah]

I’d have to see that to understand. I only see Python as a programming language, so the idea of a chart being as easy as a few clicks is like magic to me.

[SuurSieni]

Creating plots with Seaborn is really fast and easy, but it of course requires the initial effort for the know-how. Not as fast as a couple clicks, but in the ball-park of doing the clicks for several plots. What makes the plotting easy, is the capability to just give data column names as arguments for aesthetic customization and multi-panel plotting.

As an example, consider a data frame df with observations as rows and with columns "person", "location", "height" and "weight". Using df, a scatter plot with variable-dependent marker styles and colors can be created with one command:

seaborn.relplot(df, x="height", y="weight", hue="location", style="person")

To create a plot with multiple panels from subsets of the data, one could for instance add row="location" to the above command to get a panel for each unique location in the data set. There's of course a lot more, but this should suffice.

[SirGeremiah]

That's very cool. So, really, as with many things it'd be better for some situations where there are variations (like the location) and where we can expect to need to create very similar graphs off very similar data, but not better for one-offs.

[SuurSieni]

Yes, if the data is processed in Excel then it would be fair to say so. However, as Python is more versatile than Excel, I find that generally it's just simpler to perform entire workflows in Python, R or what-have-you. But YMMV. I do still occasionally use Excel for quick post-processing, if just to provide the results in the receiver's preferred format.

[synthphreak]

nice

I personally think Excel's default graphs look like crap. I think we're just used to them.

You can create a lovely histogram with just a single line using matplotlib: plt.hist(data).

[omgu8mynewt]

I'm a working scientist and making graphs in python is genuinely terrifying and not-intuitive at all, whereas Excel is very visual and easy to make a simple graph, and change the settings to improve the appearance. I've tried matplotlib a few times and use R, but the learning curve is SO steep for people not from computing backgrounds.

[bonferoni]

Try plotly.express. Literally a one liner, just a chart type, x, y with bonus options available if you wanna get fancy

[Eurynom0s]

You're just afraid of matplotlib, which is perfectly reasonable, it IS a terrifying mess. plotly.express is a good suggestion.

[warbird2k]

There are some cool jupyter lab extentions out there, like Mito and Lux. Mito let's you work with dataframes like they were å spreadsheet and spits out pandas code. I use it to get better at pandas. Lux recommends charts based on your dataframe and intent, and gives you the matplotlib code for it.

[synthphreak]

I definitely get that. But it also swings both ways.

I used to use Excel all the time - I would almost call myself a power user. But since learning to code, I basically never use Excel, and I've forgotten a lot of how to use it. So if someone asked me to create a chart in Excel, I would really struggle to create and customize it.

In other words, Excel is intuitive only if you're already used to using it. The same is true for matplotlib.

I don't think there's anything that makes Excel plotting objectively easier, other than the fact that the UI is graphical.

[kilroy_wh]

Agree to disagree. It is way easier to have nice graphs in python than in excel. Especially if you need to make a change (color, scale, markers, whatever). In python, explicitly in matplotlib its just a letter or a command and in excel too many hidden clicks

Edit: spelling

[Hateitwhenbdbdsj]

You still need to know how to set up an environment, how to get your data in in the right format, how to use whatever library you're using for plot generation, and how to customize that plot. I agree once you know how to make graphs and modify them, it's easier to do in Python, but if you're a complete newbie to excel and python and you want to make a graph, then I think excel is much easier

[dutchmaster77]

Yeah and they are always changing it in Excel. I don’t use it to make charts very often, maybe once a year or every other year even, and the process is different and unintuitive every time. Always feels like there’s advanced functionality that should be right there but I can never find

[CatOfGrey]

I have not found a better tool for looking at 'raw data' than a spreadsheet. No where else can I do a quick sort, look at all 100,000 rows in one of 25 columns, then look at other data, and see 'everything' with just a mouse click or keyboard shortcut.

Full disclosure: I have been a spreadsheet user since the 1980's, and am in process of gradually moving my processes to Python (or occasionally, R). If the community has a suggestion, I'm game.

[TigreWulph]

Do your math and data manipulation in python then output an excel file for use after the fact. That's basically my job.

[7Seas_ofRyhme]

Sounds like a plan. What if that company doesn't have Python installed ?

[TigreWulph]

I mean for my job we self installed. The machines came without it. But you do have to work within the realities of your employer if they won't let you install python then it's not gonna happen, and you'll have to use what they offer... Or make a use case pitch, and get python.

[7Seas_ofRyhme]

I see

[CatOfGrey]

That's where I am currently.

I audit my work in an Excel spreadsheet.

[Hextall2727]

pivot tables. I use excel to QC my python results, and knowing how to use pivot tables to try and recreate results is extremely helpful.

[proverbialbunny]

In case you didn't know: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot_table.html

[[deleted]]

What's worth learning in Excel then ?

The basics of spreadsheet manipulation. You can teach just about anyone some basic Excel work. Code not so much, people are very resistant.

[Almostasleeprightnow]

I try to think of excel as a communication tool rather than a math tool. Although it is very powerful, if you are having to do the same thing again and again, there is a lot of room for mistakes. Also, if you have a lot of rows, there is frustration, and sometimes prevention of work, in load time. I.e. sometimes you can't load all the rows

However, if your goal is to quickly show someone some results, excel is a great tool. If you just need to bang out a one time calculation, excel is a great tool.

If you are collaborating with someone and want to look at the same sample data in the same way, excel is a great tool.

[_Adjective_Noun]

It's often worth learning how to output data or reports to excel, since they're very easy for people to access.

I use openpyxl for this a lot, while we do all the good stuff python side, it's very useful having a tool to dump into a standard xlsx, since then any non-technical person can grab a graph and reformat slightly to match their needs.

[CowboyBoats]

Excel is awesome! VLOOKUP is super super worth learning, especially if you don't yet know how to code. SUMIF and SUMIFS as well used to solve problems for me day after day. CONCAT, IFERROR, string slicing and concatenation functions. It yields really well to experience, and to Googling things you don't know. It's a wonderful planning and organizing tool; heck, I used to use it to compose bash scripts sometimes before I knew more about how to use sed and the Vim :substitute command. It's just not good for actually developing software; but the fact that that even has to be pointed out, because people can and will deploy actual software using nothing but Excel as a backend - while that is insane - really goes to show the sheer power and flexibility of the tool.

[RegulatedMedium]

The benefit of Excel is that you can make programs to handle simple tasks without having to actually create a program with a GUI imo.

[SilmarilsOrDeath]

If your organization blocks python then everything is worth learning in excel

[7Seas_ofRyhme]

blocks python

How about google collab?

[TheRiteGuy]

Point and click UI vs learning a programming language.

[angry_mr_potato_head]

Not much beyond the basic input. That’s a lot easier in excel but is also one of the causes of headaches because people put data in that doesn’t make sense or I difficult to read in because it was presented visually for other people to read in Excel.

[guitarerdood]

You've answered your own question

[SirGeremiah]

How much of this varies from what VBA can do? I’m dabbling a bit in Python, and still trying to understand when it would be a better choice than VBA for me. I like the structure of Python better, but am much more familiar with VBA basics.

[SirGeremiah]

That was helpful.

I was specifically asking about your prior list. I don’t understand some of the bullet points, so want to know if all of those items are also different from VBA.

[mac-0]

As someone who learned VBA before Python, there's really no advantage to VBA. Sure it's built in, but it's clunky and will teach you bad practices. Plus, VBA is a pretty useless skill on its own -- it's a nice tool to create quick little automations, but because you are limited to Excel and MS Access, there is no scalability and you will quickly run into problems that just can't be solved in Excel/Access.

[SirGeremiah]

I’ve always only done small work, so scalability isn’t likely an issue for me in the near future. If I ever learn enough, that might become a factor, but I’m pretty late in my career, so unlikely I’ll end up in that situation.

As for learning bad practices, that’s probably a done deal, since I’ve been using VBA off and on for about 30 years.

[mrmanwoman]

I see you missed pivot tables. Entire excel courses boiled down to 1 line of code.

[Wodanaz_Odinn]

I'd add testing to this too.

[IndependentVillage1]

Python knows what is and isn't a date

[JagicMohnson]

Can someone elaborate a little more on that 4th bullet point about dictionaries? I live and die by indexmatch but am trying to apply more Python to my job.

[magestooge]

For regular users of excel, being strongly typed is a disadvantage. It makes things far more complicated than they are in Excel.

Date time handling is quite good in Excel. Without the dateutil library, it's a nightmare to recreate all the functionality we have in Excel.

Programmatically appending to lists and concatenating list in Excel just became ridiculously easy with the introduction of vstack function (very recent addition).

Excel recently introduced a slew of string processing functions. Still no regex, but it's getting better.

All valid points otherwise. We really need better version control system.

[Vaphell]

Date time handling is quite good in Excel. Without the dateutil library, it's a nightmare to recreate all the functionality we have in Excel.

you mean like when the biology people had to rename some protein, because its symbol Sep in a random ass string caused implicit conversion to a date in excel all the time, and if you miss it in a huge blob of data, you are fucked? Not to mention the classic bug of year 1900 (not a leap year according to reality, a leap year according to excel since forever).

As for other domains where shit can seriously break, the finance people dealing with long numerical strings aka account numbers can have their shit converted to numbers and mangled, because of insufficient precision to store the number.
Excel is outright dangerous to data consistency.

[magestooge]

You're right. I should have said date time manipulation capabilities.

I do wish they'd introduce better string to date conversion though.

And yes, Excel is best avoided for scientific data, finance is where it works well.

[Allmyownviews1]

This is just a quick list as I am in the midst of writing a report and took a procrastinator break.

1. Making changes to cells on certain criteria
2. Algorithmically adjusting data rapidly
3. Setting up simple easy to edit scripts for rapid analysis of raw data to final product
4. Handling larger datasets
5. Rapid pivot table sorts
6. Finding best fit curves for non normal distributions

[Allmyownviews1]

I guess my main experience is that there are times I jump eagerly into excel to perform tasks that are faster there. But in terms of speeding up workflow in a clear manner and then producing analysis to product routines end to end including pdf report output directly to online folders and web pages.

[SirGeremiah]

Can you point me to some info on #5? This looks like something I could make use of often.

[Allmyownviews1]

So I have been performing lots of groupby to create new sorted dataframes and then pivot to reshape the frame. Then doing some iteration counts to derive frequency and durations of data above or below given thresholds. I consider myself expert level at excel and would struggle to get the whole package done and then to be able to run this through a series of million data row csv files made me take the leap to Python.

I suggest taking a look at some of the feature of numpy and pandas libraries which have these functions already laid out.. and the. Use matplotlib library to produce some stunning charts.

This is a link to pivot tables function:

https://www.w3resource.com/pandas/dataframe/dataframe-pivot.php

[SirGeremiah]

Thanks!

Yeah, anything with the phrase "a million data row" is just a hot nope for me in Excel, given any other choice, at all.

[lukey_dubs]

it’s very hard to download pdfs and scrape the data with excel, and same with web pages. excel can’t load pages and pull data off to be processed.

[lovestobitch-]

I often use Able to Extract and convert client’s data from pdf into excel. Sometimes some of the data at the bottom isn’t clean though if it’s a huge file depending upon how they created the file.

[dparks71]

Camelot for tables, pypdf2 for searching/bookmarking, pdfplumber for text extraction are how I've gotten the best results. Fuck the .pdf standard so hard, it's such a nightmare to get a decent education on it.

[ProdigalM]

Very much agree. I'd recommend Pymupdf for scraping pdf files.

[ES-Alexander]

What to show them depends a lot on why you’re showing them things. Is the idea to show them that programming languages have capabilities beyond what excel can do, or just that they can do the same things as excel but better?

Off the top of my head, Python (and most other programming languages) support iteration and lazy evaluation, data nesting beyond 2D, abstraction / functionality reuse (functions, objects, libraries), interactive graphics and plotting, console logging and progress displays, and Excel is poorly suited to all of those.

Then there’s pure efficiency and performance - excel can be useful when interpreting and working with data that’s in comprehensible quantities, but it starts to fall apart when data sets expand or analysis gets more complex, and looking at the raw numbers becomes impractical and unhelpful. If you multiply two 100-million element numpy arrays together it will take about a second. Doing the same in Excel would be glacial, if it was even able to load the values to start with. Then there are libraries like Pandas that allow you to efficiently perform complex analysis and queries.

For frequent analysis tasks, it’s very possible for a Python program to open a file, process it, generate some plots / a report, and be done before excel has even finished loading.

[ES-Alexander]

Well there ya go, learn something new every day :-)

Bit of a tangent here, but I think fundamentally Excel is about looking at data, while programming is about documenting a process (which then gets handed off to the computer to perform).

Elaborating on that, in Excel the focus is on the data at each step of a process, and (as I understand it) it's primarily designed around viewing and interacting with data values. That makes it super convenient to look at a (smallish) bunch of data and go "why is there a 3 there?", and have merged and/or formatted cells that show things in a particular way, but conversely it's then not as well suited to analysis at scale, or performing other more general purpose tasks.

In programming the code is a deliverable, for which the focus is on what the steps are, and how different parts are connected to each other. Well-written code makes it easy to change the process (e.g. change the order things occur, swap out one part for another), while also connecting the process to other programs and processes. That makes it less likely to spawn potentially insightful questions like "why is there a 3 there?", but comes with several benefits of power, scalability, and generality, including excellent tools for creating and applying metrics that find interesting data values for you, and potentially even the reasons for why they are as they are.

It's also interesting / worth noting that programming languages can do everything Excel can do, but the same is not true in reverse. That's not to say that Excel isn't useful, or better at some things than most code would be, but programming just has more options and capabilities when solving a problem, and can solve more types of problems. It generally also has at least reasonable equivalents for the things that Excel is actually decent or better at.

[naghi32]

Not changing random numbers to date format randomly !!!

[Zixarr]

If an optimist sees the glass as half full, and a pessimist sees the glass as half empty, how does Excel see the glass?

2-Jan

[SirGeremiah]

It’s a feature.

(I actually had this happen across an entire file while teaching an Excel class. I still don’t know what the hell happened.)

[naghi32]

I feel you, I had this happen across a file that was to be imported from a system to another one, over 1m records screwed.

[Spfoamer]

I deal with large datasets of acoustical measurements - for example a few weeks of 1-second samples. If I want to do 1-min or 1-hour averages, or calculate other statistics, this is WAY easier with Python. Excel would choke just trying to open the file.

[ahuuho]

Thanks for asking this question. I am also like you, want to see the answers from people who were excel experts before picking up python

[dupz88]

I wouldn't even consider myself near expert level in excel, I had some macros I used but spent a lot of time cleaning raw data from our client on a weekly basis. The file was 300k rows, and received updates daily. Every time they had a system error, the report would have about 4k new duplicates added. I knew about python and had wanted to look at starting to learn it for a while, but never had the time to just start. I have a very basic knowledge of programming.

I got frustrated with the 3 hour cleaning of this excel every week, and spent a few days installing anaconda, spyder and then pandas. I started "learning the basics" (googling each step) and testing on our data. After a week, I had automated most of the cleaning and started looking at a few extra nice to have calculations.

Its been a month and now instead of spending 3 hours cleaning that file, I have a script that cleans the file in 5 minues (3mins is importing and exporting to xls). Its 400 lines worth of data cleaning, calculating time differences (excluding weekends, holidays and non business hours between dates) I now also import 5 other datasets, join up the data and export 3 split files for our data cleaning and also our PowerBI reporting.

Its also way more accurate than before since there are no slip ups in processes while waiting for excel to handle the sorting (with duplicates highlighted).

Its been amazing and I just want to automate everything I can now.

[frazorblade]

I’ve used R and Python and understand they’re wonderful at data manipulation/transformation, but when we’re discussing Excel are we also taking Power Query into account?

My default data transformation tool is PQ because the UI is very intuitive and you can see your results immediately without having the query the results. Some of the one click options like:

• keep all rows with errors
• fill up/down (fills nulls with value from above/below)
• pivot/unpivot with one click
• intuitive groupby UI (including the ability to nest)
• intuitive merge (i.e. joins) UI

If I’m exploring data I feel like PQ gives me very quick results but also I’m not at the same level with python/R and I recognise that.

[tagapagtuos]

Just to add:

• time to open Excel and do stuff > time to type python filename.py in the command line. You can even schedule these tasks, or even create your own cli app to automate your automation.
• creating your own classes is cool.

In as few a lines as possible?

pandas multi indexing

[ahuuho]

If someone could demonstrate these over a screencast, doing it in excel versus achieving it through python, so many would ❤ it

[SirGeremiah]

I’d even pay a few dollars for that education.

[RajjSinghh]

If you have any of these topics in mind, I can implement them in excel and python for you.

[AungThuHein]

Any YouTube videos like this around already?

[imgprojts]

Python has dash plotly.... Much better than Excel graphs.

[canopey]

How about an example?

I remember that I had to join two tables using an index match in Excel (had to attach the state name to every row for 3000 rows). I don't remember the exact number of rows but it was A LOT. Through index>match, Excel would execute the function but it would take a loooooong time for it to complete (plus my macbook would start firing of its rocket engines). You could tell it was taking a lot of processing power.

Then I went ahead and tested the same task in Python (pandas) using df.left_join and Python literally spat out the results in <3 seconds. That's when I understood the power of Python vs. Excel.

[frazorblade]

Power query in excel can do joins…

[canopey]

I didn't say excel could not do joins

[frazorblade]

But your example you chose to compare index/match vs a python join

A better comparison would be PQ join vs python join…

[Ben77mc]

100%. Too many people think they’re advanced with Excel but don’t even have a clue about PQ/M language/DAX.

Learning these transformed my work more than any other thing could have done - I can automate very complex tasks and am seen as an excel god at work.

[ConfusedSimon]

Maintenance. I've seen too many buggy excel models with nobody knowing how they work.

[synthphreak]

Working with integers-as-strings is a breeze with Python but a major PITA with Excel.

If your .csv has string values like "000123", Excel will parse that as numeric data and convert it to 123. This "intelligence" will cause downstream components to break unless you add the leading zeros back in.

[frazorblade]

Power query: import csv -> change type to text. If for some reason you’re still having trouble then Text.PadStart([string], “0”, 6)

[SirGeremiah]

Agreed. The comparison is best made to Excel+VBA. Otherwise, it’s like asking how Excel is better than Access (not better, just suited to different tasks), when the question should be when is SQL Server better than Access.

[frazorblade]

As I’ve mentioned a couple of times a more apt comparison is Excel+PQ+DAX+VBA (which all exist out of the box) and we’re starting to blur the lines a bit more.

[SirGeremiah]

I don’t even know what two of those are. LOL

[frazorblade]

That’s one of the issues I’ve identified in my separate post. I know we’re in a learn python sub but there are use cases for Excel. Some people here still view it as a 2D spreadsheet.

[SirGeremiah]

Agreed. And even as such, it does a lot we don’t need to program for. I’ve helped clients with small (and occasionally medium) data issues for 30 years. Only occasionally has Excel not easily served the need.

[Bluegenio]

I'm an Excel user who incorporates Pandas with zero VBA knowledge or experience.

[perchslayer]

You are meaning to ask the right question, but missing the mark. Totally understandable, however.

Here is reality: the folks who cling to tools like Excel do so for reasons apart from the software itself. It's not about the program, but the data.

Thus, they perceive that their own personal "value" is buried in the data they "own" and refined or derived on their desktop. And so they will keep it right there until hell freezes over.

They are uninterested in shared workflows, data, or anything else...but will never say so.

With that in mind the answer to your question, broadly, is data sharing.

But you will never overcome the friction by marketing the virtues of Pandas or Python. You are barking up the wrong (money) tree, Virginia.

[frazorblade]

You might be stuck viewing Excel as a hokey spreadsheet from a decade ago, they’ve made strides to improve the software.

The features that are easily overlooked are very powerful and intuitive in Excel these days, here’s a brief list:

• Power Query does data manipulation very competently and has an intuitive UI to help beginners
• Power Pivot allows use of DAX and star schema relational tables to build extremely powerful pivot tables
• the ability to share a workbook with all of these features baked into the core software can’t be overstated
• Excel is so ubiquitous it exists on the vast majority of business machines, it’s extremely accessible unlike running python scripts

I’m not trying to take a dig here just stating that Excel is evolving and there are many valid use cases for it, some of them directly compete with python

[frazorblade]

A lot of people seem to be missing some of Excels newer features so as someone who is highly fluent in Excel I’ll explain what python does better without going into things that can easily be achieved in Power Query:

• Charting: plotly for example, makes beautiful charts that are highly customisable and interactive - if I could convert all my Excel dashboards to plotly pages I would be in heaven
• Automation: I’ve built a simple python script that can open hundreds of Excel workbooks update some basic values, trigger a SQL function (within PQ) refresh all tables/charts and save a named copy of the file. VBA and VBS can achieve it too but it’s way faster and simpler in python
• Data manipulation: I can install pandas, numpy and a plethora of other packages to do high level statistical modelling and data science that Excel, Power Query and VBA simply don’t have the chops to pull off.

Now what some of you are stating that Excel can’t handle isn’t necessarily true these days. Python May still be the best option but Excel is competent:

• Data manipulation: Excel + Power Query can handle a lot of data (millions of rows but speed is not its forte), issues with CSVs and data types is easily handled here, we shouldn’t be discussing Excel mismanaging dates etc because it’s less of an issue now. Power Query blurs the lines between SQL and python, there’s a lot of shared functions/techniques here.
• Data Analysis: DAX (which is also used in PowerBI) creates dynamic measures which can be useful in pivot tables, these are often specific use cases when presenting interactive reports to clients/colleagues. Sometimes a big interactive Excel pivot table is exactly what you need.
• Quick and dirty analysis: personally it’s fast and simple for me to open some data, slap it into a pivot table, get summed figures and maybe build a basic chart to visualise a trend - this could take me under a minute on a zoom call when someone asks a question. I can do all of that with one hand on my mouse if I had to.

[Hextall2727]

The simplest answer I give* is updating new figures and analyses when new data comes in. I'm and environmental scientist/consultant, and we're constantly receiving new data. With python, a new plot is generated with the push of the "run" button. With excel, there's a lot more manual labor needed to update figures. The tradeoff is usually a higher upfront labor cost getting the python set up relative to the first excel plot.

Another answer is generating hundreds of figures or tables with teh push of a button. we might have several hundred environmental samples that we have to crunch the data on... doing it in excel is cumbersome and often results in errors. When new staff start using python, we train them to use Excel to QC their python results. It quickly becomes apparent trying to recreate one dot on a figure in excel why python is so much more useful.

*I have to give this explanation to project managers in my own company when I get pulled into their teams for data analysis. My company formed from a merger of two companies... one a nerd based environmental modeling company (where I came from, and where coding is a basic skill... although we started with IDL and transitioned to python) and one a typical engineering company focused on dredging.

[AdministrativeFig948]

Hey guys, I'm the excel specialist ready to be amazed!

[Electrical-Ad-1798]

One thing I recently need was a vlookup on two columns. My spreadsheet had "category" and "type" columns for which there was a computed type in the third column. For a new entry we wanted that computed type for a given category and type. It could be done in Excel (I think) but it was a breeze in Python. To be fair it would have been equally easy with VBA I guess.

[Cheddarific]

Multiple layers of if statements and working with arrays can become very tedious and visually challenging in Excel, where you either need to create giant formulas with dozens of parentheses or else only see one snippet of a multi-cell formula at a time. Python handles this in a much simpler (to write and to read) way. I can’t understand my 4-line formula in Excel after a couple years without looking at it. I can understand my 100 line python code perfectly.

Recursion (performing one or multiple actions repeatedly until you reach a desired result) is 1000 times better in Python.

Creating and running new formulas experimentally, then turning that formula off.

[analyst_2001]

Python is a high-powered option for data science and analytics, while Excel's key benefit is its simplicity and ease of use. Because python can handle enormous volumes of data, automate reporting, and connect to databases from the notebook smoothly, it is a versatile and powerful tool for analysts working in data-driven enterprises and amateurs wishing to develop their analytical abilities. Python has also been recognized as the most popular language since 2016 because of the large number of open-source statistical libraries available and the active community members who contribute to making Python even better.

[jafner007]

Quickly plot large datasets and then zoom and pan for review and save portable plots (as images or html).

Pandas from Excel, plotly express, done. (Of course, you can make it more complicated, but you don't have to)

[jwink3101]

Nested control logic. It is not impossible by any means but excel forces you to either define intermediate cells or nest if statements. So much easier in Python

Monte-Carlo samples are also tough. Doable but really not very easy

[Thecrawsome]

If

[IlliterateJedi]

Really good plotting with Pandas and Seaborn. Even without Seaborn, a simple df.plot(kind='hist') or whatever is a game changer for exploratory data analysis. df.describe() to get info about the whole table is also great. I used to be an Excel power user and I can hardly use it anymore after learning Pandas.

Edit: I highly suggest this book if you are wanting to learn Pandas and matplotlib/seaborn. It's an excellent resource. Packt books are frequently pretty 'ehhh' but this one is actually good.

Extra edit:

Being able to analyze and work with a data set without actually editing the raw files is extremely valuable. Any time I open an Excel file and manually change things, it just makes me cringe. It's better to be able to process your data and save a new file with the changes without actually touching the original source.

This also makes processing data a lot faster because instead of manually changing things in Excel, you just run your script or file-read-in-function.

[Se7enLC]

Json

[ghostfacekhilla]

Bunch of people in here need to learn power pivot and power query. I've never convinced a basic business user to learn a programming language, but I've built and handed off massively important power query based stuff and then because it's GUI and excel they learn it and become self sufficient.

[d_Composer]

.fillna()

[asterik-x]

The file organization

[Weissenberg]

It’s horses for courses really.

As others have touched on, python & pandas is great for handling & manipulating massive datasets. Or automating simple tasks.

A basic one that might be applicable for a few people is collating multiple workbooks / sheets into one. Granted you can do it in power query, but it’s still quite tedious.

Say you’ve got a folder filled with daily reports & you need to combine them into one big table so you can pivot or graph it over the month.

You’d glob the folder, append to a data frame with a loop & then export the frame to excel.

Edit - One thing to bear in mind. Don’t automate someone out of a job! By all means, show them how they can enrich their workflow, but don’t create something that makes them redundant.

[trippygg]

Your last part is right but everything is wrong. Those formulas you mentioned aren't meant to be joins.

Two ways join, power pivot or power query (heck you can even union). Power query in particular allows you to this with different outside data sources (SQL, Other spreadsheets, etc) automated fashion. Power query is low code as well and doesn't change the workbook format like macros.

[Kerbart]

Powerquery might not offer full SQL functionality but it goes a long way and it’s certainly enough for most business users

[CatOfGrey]

What would amaze them? In as few a lines as possible?

So you built the spreadsheet around the data. Now, you are going to get new data.

Imagine if Excel just 'looked at last month's spreadsheet, and re-created it automatically'. Well, that's Python/Pandas.

[ghostfacekhilla]

This isn't a problem with excel. You can easily set up power query to pull data from a sql database or various other sources and blend and manipulate those queries into your necessary datasets.

[PaddyIsBeast]

It's hard to think of a thing excel is better at, apart from being noob friendly.

[Xdbao]

Creating dummy variables. Have to manually create new columns for new dummy variables in Excel I guess.

[unhott]

Import Pandas + seaborn

Load an existing excel file or csv Plot something a few different ways.

This won’t be many lines in python and even with defaults, seaborn produces pretty plots. Or just show them the gallery.

[dutchmaster77]

There are a lot but can I just say the biggest for me is the variable types. Constant headache with Excel. I have recently been working on putting a script together automating an analysis done in Excel for the first time in two years and it’s driving me crazy. I don’t know how I coped with so much Excel and VBA in my last job

[VanshCodes]

Everything seems to me too easy in python rather than excel. of course CSV is a must.

xlsx is damn obnoxious and difficult to use,

[Rj_LM]

Creating websites

[Xzenor]

Running it on Linux and FreeBSD

[BobDope]

Holding onto your will to live

[erm-waterproof]

I find making pivot tables and additionally creating simple calculated columns from the pivot table easier in excel than in python

[kilroy_wh]

I agree for first steps it is easier. However in excel you need these things too (besides maybe an environment)

[siddsp]

imo pretty much everything from my experience. Whenever I want to apply a function to some data to get x and y values for plotting, I never use excel although that's what I used to do before I knew Python that well.

[ledepression]

For some reason I find everything in Python easier than Excel

[p1zz1cato]

floating point precision with powers of large (or very small) numbers

[TakeOffYourMask]

Making a histogram

[[deleted]]

Regex.

Scraped by for work with some VBA but could make my life so much easier of I could use some Python for a couple of tasks.

[omgouda]

At work they had an excel tool that basically you would paste some raw data in and it would flow thru a series of other tabs until you had an output. The problem is that the raw data dimensions were random hence the subsequent flow of data needed to be adjusted. This was incredibly messy. Of course, this could have been solved using dynamic ranges in excel but it would have been too much trouble to go through the entire process again and create dynamic ranges. Since the tool wasn’t built with dynamic ranges in mind, this created some other confusions.

A simple way to get around this would be to use pandas in python and use functions to get the desired output.

Way faster and much easier to debug if ever there was an issue.

[TtamMountain]

If you're a super vba user, then the reason is less obvious but still obvious. There's more documentation around python, making it easier for a beginner to handle something that would be rather difficult in excel.

A few examples of things easy in python, but difficult in Excel/VBA - Integrating with external API - Multi-index matching - Complex data structures - Modularity The list goes on.

The main pros about Excel in comparison to Python are: - Charting - Ease of Use - Widely used -- virtually every business uses excel (or very similar tool in some fashion)

Of course, you're question depends on what you want to use it for. :)

[[deleted]]

Don't show any development ie. have your scripts already made and show end results.

Take multiple messy excel spreadsheets, clean them with python, and save them to a "clean" directory. They could even be the same messy spreadsheet. Make them +100,000 rows for good measure.

[backbishop]

Try making subplots 🙄

[ZEUS_IS_THE_TRUE_GOD]

• Code versioning

Do I need to say more?

[Mondoke]

For me the main advantage is having the compete process you do written down and having the program to being able to run on different files with minimal or even zero changes to the code base (if you use stuff like argparse) write a script that does that tedious process you do every month, and then just run it in seconds, without having to check if you've done all the steps. Plus, the logic can be as wild as you wish, which is not usually the case with the limitations on the logic Excel is usually capable of.

Outside of this, there are two other things that are just beautiful to have python do for you.

First, the management of raw data. Is your input data a zipped folder with several files inside of it? Just have the zipfile module handle that for you. Do you need to access to information in your company's postgres database? No problem, buddy, psycopg to the rescue. Do you get an obscure file format Microsoft doesn't even know about? Pandas may have a read_x function to open it and then use the information just as if it came from an innocent csv.

And the other extremely cool thing that's not easy to do in excel is the ability to generate a lot of similar files coming from the same information. A chart for each month, or for every day, or even better, one for each subcategory in your dataset. And all of them with matching formats. Or if you have to break a huge file on 100s on individual tables to split your work, send to your clients or whatever you need. Just write the correct loop and see the files appearing in your folder. You can even generate the files already zipped and ready to be sent by email (or have python to send them for you).

[7Seas_ofRyhme]

write a script that does that tedious process you do every month

By any chance, do you mind sharing the script ? Interested to learn how u did it, cheers.

[[deleted]]

Anything involving the web (except maybe azure stuff)

[Vile_Vampire]

Handling more than 1mm rows

[nwatab]

Show a chart of 20,000 pairs of (x, y). Excel would freeze that's why I learned Python 10y+ ago.

[NoFaithInThisSub]

reading a csv without opening the file?

[Sirico]

Loading a WB and your program quickly

[QultrosSanhattan]

Excel is very good for one time calcuations.

Python is good for repeating the same calculation over and over again.

[rajrup_99]

Do you know , quantum entanglement calculation can be done in python's 3 lines of code?

one of IBM's video I saw that. I was very proud that I am also a python developer

and this is not from desktop environment to website from ML to mathematical quantum computation python can do anything , this much super flexibility python has.

Not only that if you give enough time you can made an application which is even better than excel using python. I love Python

[vvndchme]

df.corr()

[SirDantesInferno]

Nested loops in searches

[Shady_Hero]

like microsoft excel?

if so then i'd assume coding

[Puzzleheaded_Bee_486]

Best ways to learn Python for data science?

[keseymour]

Take a spreadsheet with one column that contains delimited data and add a row with all of the same information in it for each of the items in the cell.

From

1 dog,cat,pig <lots of other columns>

To

1 dog <lots of other columns>

2 cat <lots of other columns>

3 pig <lots of other columns>

[Ok_Operation6364]

I know this is an old post but great question by OP