I've had my education in a engineering focused campus so that may skew it, but even not IT related area had programming courses to, at least, learn the basics.
Do the newer researcher still show reluctance to move from Excel or is it an old guard kind of deal?
The feature of Excel that's underestimated/overlooked by skilled programmers of Python/R is that spreadsheets immediately expose an editable visual spatial canvas datagrid GUI.
I have decades of programming C/C++/C#/Python/etc and yet I create new spreadsheets and use them every day.
Spreadsheets are much faster than wiring up a datagrid in C# or C++ Qt gui widget editor or any other "true" programming language. Spreadsheets are also faster than using Python package Pandas to import a csv into a dataframe and view it in a Jupyter notebook. And last time I checked, displaying an output grid of cells in Jupyter is read-only and not 2-way editable like Excel.
And yes, the complaint is that "MS Excel calculations are not auditable, repeatable, version controlled, etc". All true, but it still doesn't change the fact that Python doesn't have an instant datagrid GUI. UI affordances also matter in viewing science data sets as well as financial budgets.
And I was wondering if people like in the non-engineering but scientific fields, as the ones discussed in post, are exposed to the alternatives during their formation years.
If one chooses to use Excel because its the best tool great, but if its because its the perceived only option that's probably counterproductive.
Use the right tool.
Step one should be learn your tool, no matter the tool.
There's several ways to avoid the issue in Excel. If someone working with Excel isn't able to learn that (heck, a simple template would suffice), I have no hope the same demographic would have any success with R or Python.
No, not really. There are several ways to help reduce the issue, but none of them eliminate what it does.
Example that comes to mind is the data file for College Board's SAT test. The data formats for student reports for schools come in two formats: PDF (one page per student), CSV, and fixed-width. That is the comprehensive list of your options. College Board doesn't care about you as a customer. They're too big. Any request you submit will be black holed.
Some of the columns in the file indicate a range, usually in the format "X-Y". Excel will try to coerce that into a date, if it's valid.
Other columns indicate a ratio, expressed as "X/Y". Excel will coerce that into a date, if it's valid.
Other columns indicate an ID number, expressed as a large, fixed-digit number, zero-padded. Excel will coerce that into an integer, or, if it's too long, into scientific notation discarding digits.
It doesn't matter how you format the CSV. Excel will do the above.
Here's an example CSV:
ID,Range,Ratio
12345678901234567890,8-9,"7/15"
"00000000000000012345","9-10",21/35
ID,Range,Ratio
1.23457E+19,9-Aug,15-Jul
12345,10-Sep,21/35
The correct way to work with this data file is: Do not, under any circumstances, open it with Excel if you expect to use it for anything else.
The problem is, there are very few applications that work well with CSV files. I know of CsvEd and Delimit. There are several text editors with a CSV column mode that makes the file look like a table (with varying degrees of success). All of these vary between "godawful" and "a complete nightmare" in terms of performance and usability compared to Excel.
Only if you open the file, so that Exel assumes defaults for formatting. If you import the CSV (Data -> From Text in the current UI) you can specify the format with the Import Text Wizard as described on the College Board instructions for using the file. Except...
Unfortunately, the College Board has outdated instructions on their website; Excel used to offer the Import Text Wizard on opening a csv or text file, rather than making default assumptions, and the College Board instructions page, while it does tell you to use that Import Text Wizard and provide all the details of what to plug into that wizard, tells you to open the file to get it, which bypasses the wizard.
Things like this is why clerical and other low-level positions involving excel test candidates on proficiency with specific versions of excel. Higher-level workers are expected to be able to figure out these kind of changes themselves, though (or consume lower-level staff time, if they are in management.
We have now changed a 1 second automated process that anybody could do, into a 2 minute manual process that requires knowledge of data types. We've gone from a non-technical task to a highly technical task. To open a file without completely munging the data.
It also will sometimes do weird things like exclusive lock the file on disk until you close every open window of Excel because it creates data connections to the file.
You don't, because while the wizard doesn't default to headers, the first screen of the wizard has a checkbox for it, so the option is there without Power Query.
There is no checkbox in the wizard.
If not then your personal experience is irrelevant in this context.
So I get to cobble together some really ugly spreadsheets in excel when I’d MUCH rather use something more appropriate. Sometimes it’s not the person, it’s the org. In this case, government.
Furthermore, Microsoft office is installed everywhere everywhere everywhere… like it leave it love it or hate it, it doesn’t matter… it’s true (at least for huge swaths of the demographic).
So the first thing most people learn is excel and the last thing most people use is excel. It’s a database, it’s a spreadsheet, you can even use it as a word processor if you want to. And some have.
I’m not saying you could and I’m not saying you should, I’m just answering as to “why“.