PSA: if you use Excel as intended, you would just go File/New/Blank Worksheet, switch to the Data tab, then use the 'From Text/CSV' wizard to specify the data types for each column in your data source, neatly bypassing this entire issue.
But yes, just double-clicking the CSV from Explorer, using the legacy 'open this as a sheet' functionality, experiencing data loss and then complaining about it (and the state of Excel, MSFT and The World in general) on social media is much, much more fun...
In Japan everyone passes around CSVs in shift-jis still for exactly this reason - the default opening action of Excel with CSVs.
Its easy to say its people being dumb, but at this point I really wish excel just wasn't so confident in itself and actually asked you during the default open operation what you want to do.
Actually UTF-8 WITH BOM csv file also can be read/written like that since Excel 2016, but of course average users aren't knowledgeable as like programmers.
>then use the 'From Text/CSV' wizard to specify the data types for each column in your data source, neatly bypassing this entire issue.
Something people do not understand is that type information in CSV files is conveyed out of band. The application that is reading the CSV file must know the datatype which effectively turns each CSV file into an application specific format.
Yes, but without inventing a time machine to go back to 1985 or so (at which time the safe option did not exist, so better invent temporal code backporting as well), that's not a viable option.
Any change to Excel's 'open a worksheet' logic would break so many workflows it's just not funny anymore. I'm not kidding if I say I suspect it would significantly impact several countries' GDP for a while.
Even the (sometimes comically inadequate) heuristics that Excel uses to auto-determine field types can't be updated, for very similar reasons. Backwards-compatibility is... interesting...
> Any change to Excel's 'open a worksheet' logic would break so many workflows it's just not funny anymore.
My preferred work flow would be to open the csv, change some formatting, then reinterpret the original data.
It would require keeping two sets of data for each cell, but that seems to already be the case since F2-enter on each cell would accomplish exactly that. Last I tried.
I wonder what is the ratio of people dealing with dates versus genes is... Probably very substantial on favour to those who deal with dates. So things just to work for them is likely better option.
It should be tough to have such a name.
The most important systems like tax/insurance/airline booking are often the most unkind systems.
If you're not familiar with computers, it's almost impossible to imagine the potential cause of problems is your name.
This has got a "Bobby Tables"[1] vibe to it. While I personally do not like the concept of adjusting data to fit a tool, as others have pointed out, Excel, for better or worse, is ubiquitous. To the obvious detriment of genomics. And likely others.
Any tool that seeks to replace it would need to be as easy to use, as flexible. And preferably fewer bugs.
The problem is that the paradigm for spreadsheets works well for many projects, even if the implementations are crap.
>Even then, a scientist might fix their data but export it as a CSV file without saving the formatting.
Is this supposed to be a joke? Since when does excel support CSV files? Yes you can import and export CSV files but that is just there to check a box. That feature doesn't actually work. Just import and export .xlsx files in your applications directly.
CSV is such a bad format because it's not even a standard, there is RFC4180 but most people have never heard of it. CSV is complicated enough that anyone who thinks they can implement it will get it wrong on their first attempt but simple enough that people believe they can implement it on their first attempt.
Opening CSVs in Excel almost never worked for me. Good thing there's LibreOffice Calc with spiffy import dialog with all the csv options/variants where you can preview the result.
It was there, the problem is that by default if you open files the way most people do (double-clicking or dragging) then Excel decides you must not want to be bothered by the options so it fills them in for you. "Helpfully" not even using the most basic inference to assign types in a way that doesn't destroy the data.
I meant to describe what I do and why, sorry if I offended you by unsolicited wrong advice. And as others say, nothing changed during that time - the import function is/was always there but it's still hidden somewhere.
>Since when does excel support CSV files? Yes you can import and export CSV files but that is just there to check a box. That feature doesn't actually work. Just import and export .xlsx files in your applications directly.
You're being hyperbolic and your .xlsx advice doesn't apply to other systems we don't control that only offer .csv files.
Examples... my credit card website and Ebay only offer csv downloads of data. I use MS Excel to import those csv files all the time and it works well enough.
All those caveats with csv are irrelevant when the system that has the data people want only offers csv. Using Excel's feature to import csv -- and being aware of the format dangers -- is more practical than manually retyping all the data from scratch.
> Microsoft Excel may be fleeting, but human genes will be around for as long as we are. It’s best to give them names that work.
This is the crux of the issue. Yes, Excel could do better with support for CSV. (OpenOffice and LibreOffice habdle this better, for example.) But no, genetics is not some niche use-case for this better support. It's just one example of many. I do analytics of another sort for a bank, and we run into this problem all the time with those pesky 16-digit account numbers.
But, while Microsoft might do well to make changes to address their poor implementation, someone somewhere will implement the next best thing and screw it up, too.
I'd like it to have support for things like "this is a <format> cell", where format is something like text, numeric, date, currency. I'm not sure how you'd specify that in HTML.
I've been thinking about a project exactly to replace this sort of Excel misuse. The frontend should be about as Excel-like as possible, where you can just easily type (or import) as many rows and columns you like, but without the formulas and other real spreadsheet calculations. Instead, make it easy to add data types, validation for those types, foreign keys, etc, and store the whole thing in a database.
Yeah, that's pretty similar to what I want. The other feature I'd love is the ability to add "Formatting rows" which are rich text and are marked as not being part of the data. They're merely decorative. I find such formatting is often considered essential for client-facing spreadsheets (somewhat understandably so they know what's going on), but it makes it quite messy to extract data from the sheet.
I'd quite like these files to be openable in simple viewer/editor software that might not support formulas. I think once you support formulas you open pandora's box with regard to which formulas are supported.
Parquet is not plain text (but that is an oxymoron anyways), but otherwise afaik it is pretty solid data interchange format supported by a number of tools already. It doesn't have native formatting system, but it does allow arbitrary key-value metadata on columns so surely something could be cooked up with that.
The real problem is of course using Excel to maintain data. That's not what it's for. That's what databases are for. Excel is for complex calculations over rows and columns. And of course it should be better at data validation; you should be able to tell it whether a certain column is a date or not, and Excel shouldn't be jumping to stupid conclusions about it, but it does, and that means you need to be careful and not use Excel for something like that.
Excel misuse is sadly rampant and one of my main frustrations at my previous project. Excel is popular for this sort of misuse because when you open it, it presents you with an empty table and you can immediately start typing. This invites tabular data. But Excel inevitably fucks up because it lacks proper data types, data validation, or foreign keys, and loves making assumptions about what you mean. This makes it a terrible and harmful choice for any sort of serious data. I even proposed a new project for an Excel-like frontend backed by a database exactly for these sort of situations. Because I do get what attracts people to Excel for this sort of thing. And most people don't realise what a terrible choice it is.
There’s not really a better tool that I’ve found. Excel is ubiquitous and pretty easy to use.
And there aren’t good “data browsers” that have as low a learning curve.
I’ve been especially looking for a JSON browser/editor since excel doesn’t do that well and I’m unsuccessful trying to explain how to use basic text editors to people who can’t do basic functions like open files that aren’t associated with a program.
"And there aren’t good “data browsers” that have as low a learning curve."
If someone is trained to the point of working on genetic data at this level, should they not also have been trained to a reasonable level in domain appropriate software and tools?
I'm continually shocked at how technically incompetent academics can be. Otherwise brilliant people with the most batshit technical workflows I've ever seen.
Like everybody else, they take the path of least resistance. Of course, when that path is Excel they're in for a lot of resistance later (like needing to rename genes), but at first it feels smooth.
I consider myself pretty trained and still use Excel for “eyeballing” datasets of up to a million records.
There’s many times where I don’t want to do anything more than open, sort, filter, and never see the file again. And I’d like something better than Excel, but haven’t found it.
Maybe 70% the time, vi or BBEdit or shell commands work but otherwise excel.
For anything of significance, I use Python notebooks or dedicated data environment.
If there was a better tool, I think people would train. But there’s not, that I’m aware of, so Excel sticks around.
>The real problem is of course using Excel to maintain data. That's not what it's for. That's what databases are for. Excel is for complex calculations over rows and columns.
I use MS Excel extensively and create new .xlsx files every week even though I know databases like SQLite, MySQL, and did consulting for Oracle RDBMS. The problem is that databases do not include a GUI for data viewing.
And even though I also know programming tools like C++ Qt and C# WinFroms to slap in front of databases, starting with a blank Excel grid is faster and easier than wiring up a datagrid UI control to a database and compiling an app.
If I then want to share a dataset with a colleague and email it to them, the easiest friction-free way is to attach an .xlsx file. Sending them an email with attachments of SQlite .db file + executable app for Windows/macOS is much more cumbersome. The alternative of sending them a link to a cloud-based "database-as-worksheet" SaaS platform just creates another set of problems. An all-in-one db+gui tool like MS Access also isn't really an option since it doesn't have the same powerful GUI data manipulation as Excel.
The scenario of "I just sent you an xlsx where the rows highlighted in red are problems and if you can just add your notes to column K, that would be great. Thanks!" -- is not easy in other tools that are not spreadsheets.
People (including programmers skilled in databases) constantly "misuse" Excel because it's the most practical way to get work done compared to the friction of alternative tools.
What'd be wrong with using something like DBeaver, SqliteStudio, or PG-Admin? Or buying a tool like DataGrip? Microsoft Access even allows you to create your own UI forms for a database.
You don't need to write your own GUI for databases. Loads exists.
That solves one small part of the problem - viewing and editing the data in a UI. But can those tools address the problem raised by jasode?
> "I just sent you an xlsx where the rows highlighted in red are problems and if you can just add your notes to column K, that would be great. Thanks!"
This is a pretty common use-case, and neatly demonstrates a few of the reasons Excel is so popular. Sharing a self-contained DB with a colleague that they can view + edit with software they likely already have, modifying the schema easily on the fly, highlighting some rows. And that's not to mention the programmability - from having a simple "=SUM(...)" cell, to hacking some VBA or the newly introduced Lambda (https://www.microsoft.com/en-us/research/blog/lambda-the-ult...)
I wouldn't personally build anything important with Excel as a sort of DB, but I understand why some people would want to
>What'd be wrong with using something like DBeaver, SqliteStudio, or PG-Admin? Or buying a tool like DataGrip? Microsoft Access
1) Those utilities are not typically included in the workstation image of laptops/desktops unlike MS Excel which is already part of Office 365. Millions are already familiar with the GUI of Excel.
2) The datagrid viewers in those tools are not powerful and feature-rich like Excel. They are often missing features that are taken for granted by Excel users:
- formatting any arbitrary row or column with bold/italics and change the font color or background cell color.
- pivot the data via drag & drop UI (instead of manually writing a SQL cross-tab query)
- hide rows or collapse rows into outlines
- cut rows 37 to 52 and paste them above row 5. That type of behavior is not easy in generic database viewers because most tables -- by typical design of RDBMS table row ids -- do not consider the visible spatial ordering of rows the way the end user wants to see them on the screen unless one adds an extra column to the table such as "gui_view_order_id". Excel has user specified row ordering as default out-of-the-box behavior.
- ... tons of other GUI features like formulas, spell check, etc
Add an extra table mapping id to gui_order, then just write an easy query/report (or edit the one you already have) to give you a view that's ordered by gui_order first. Not that hard, you just need to make the semantics of what you're doing explicit. This is the inherent tradeoff between raw editing on a spreadsheet (quick and dirty, very error prone) and working on something that has real structure to it, reflecting some underlying semantics.
Please, keep adding to the "tons of other GUI features" list. I'm just started on a Python/Qt/Sqlite project for spreadsheet-like functionality backed by a strongly-typed database. The things that you mention are perfect features. I'd love to know more.
But the real nice thing is that SQLite will allow spreadsheet-like behaviour by allowing one to enter an invalid data type. I could check that in Python or I can store it and warn "Invalid type blah blah blah". This will make life easier for those coming from a spreadsheet, or importing data. As the program matures, I can reevaluate what to do with invalid data as a default and what options to give the user.
Additionally, because SQLite allows arbitrary data in any column, adding support for e.g. formulae is greatly simplified.
Formulas is a big one for me. As a programmer I often use Excel to generate insert/update SQL statements for example. So I'd need formulas with not just numerical but also string manipulation functions.
Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
How does this sound? Formulae are supported, but the cell (not column) must be set as the Formulae datatype. The first formula that I am supporting is SUM, as it is easy to code in SQL. What are the other most common formulae that you use? I'll only implement the integer / float formulae at first, but I'll add string manipulation later.
> Also filtering is essential.
Filtering, of could. I've already got a hidden column for all rows isDisplay.
> So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
Perhaps instead of filtering, you'd like to see outliers or the range of values?
> Formulae are supported, but the cell (not column) must be set as the Formulae datatype
As long as it's not tedious to set a few thousand that sounds great.
> What are the other most common formulae that you use?
I mostly use SUM, COUNT and IF (if-then), along with functions to check if a string value is a valid number. Also string formatting of numbers and dates (for concatenating with text).
I've also used lookups, ie find the row matching this and extract the value from the given column in that row. Though I'm not super happy with the way Excel does that, surely some room for improvement.
> Perhaps instead of filtering, you'd like to see outliers or the range of values?
In addition. Sometimes I just want to view all the values matching X, other times I want to quickly see any outliers. Definitely ranges, especially for numbers (just larger than 0 for example, or between 5 and 10).
Sounds like a very interesting project, if you got a link I'd be interested in tracking progress. If not, I'd be happy if you did a Show HN when you're ready :)
While I'm at it... One thing I find annoying in Excel is that it's rather tedious to reference an absolute cell ($B$2) vs a relative cell (C3), especially when I need to reference a handful of each.
The way I work, it would have been better to mark a cell itself as being a "constant" or "input parameter", so that when I reference the cell it is treated as an absolute reference when pasting.
That is, if I reference cell B2 in a formula and paste that formula in a different cell, it is B2 itself that determines if it's a relative or absolute reference, not the way I typed it in the formula.
It's not a huge deal, but it would be one of those nice things that makes life more enjoyable.
Would it not be tedious to mark each cell as constant or input parameter? Perhaps it would be preferable to just ask the fine user when copying a cell with a formula?
I almost always have <5 such cells in a sheet, typically just 2-3, so I don't think it would be that tedious given you only have to set that once.
In comparison, I often have formulas with over 20 cell references and/or multiple formulas referencing the "constants", so getting asked for each would indeed be tedious.
That’s awesome! Where can we subscribe for any announcement? ;)
To me, a critical one is easy plots. Checking how an arbitrary column changes as a function of an arbitrary other column by adding a scatter plot in less than 5 seconds is fantastic.
Formulae are also very useful. Adding numerical derivatives or integrals by just putting a formula in a new column is very useful as well. The point is not to have publication-quality, highly accurate numbers, but just quick and dirty operations to see if it warrants further investigation.
I am happy to discuss my use cases if you are interested (it might be going a bit out of topic for this thread).
I'll see what cross-platform plotting libraries I can include. Thank you for the idea, I agree it is important. Yes, I am very interested in your use cases! My Gmail username is the same as my HN username.
We are talking about scientists, which are for sure able and used to work through different tools during their career. And we are in 2022. We have good tools that with investment could be a much better fit. Said that, I don't know what one cannot do with i.e RStudio to use Excel for science. In a second moment, python, pandas and notebook are pretty accessible too..
> We are talking about scientists, which are for sure able and used to work through different tools during their career. And we are in 2022.
Scientists are not superhuman. Just like anybody, they’ll jump through a lot of hoops if they think the results justify it, but they are sometimes quite resistant to change for the sake of change, and sometimes even to change itself.
One can be a great chemist or know all there is to know about how purple long-tailed fruit flies from Siberia and have no clue about how computers work. Or be very proficient in a given piece of complex software to process NMR spectra and barely able to operate Outlook. But these people all make do with Excel.
> In a second moment, python, pandas and notebook are pretty accessible too
It’s much heavier, the IDEs are much more complex than Excel, and quite a lot of people on Earth are not natural programmers. Startup time, learning curve, steps to get a useful graph to check a trend, etc. All friction adds up. I’ve seen it countless times: when you show them the results of a complex workflow, they are excited. They start getting distracted when you talk about architecture, and they’re lost when you go into things like pandas and scipy. Then they nod politely, keep doing their stuff in Excel, and call you when they need a bit of wizardry for a paper.
In short, they are regular users, even if the software they use can be highly specific. Ease of use and lack of friction are paramount.
Going to piggyback off this comment to ask - does anyone know a lightweight GUI tool to edit/modify SharePoint Lists? Only way I ever found was through the browser which is painful on large data.
In an alternate universe we have nice specialised GUI front ends on things like netCDF or SQLite that enable what you say. Or hell, just a spreadsheet-like view over a real database (but with actual names as columns). All the GUIs I have seen are really not up to the task for typical scientific data. They are clunky, heavy, slow, and difficult to use for visualisation. We are not writing new front ends for every type of data, and nobody seems to have written a decent one that could be universal.
Myself (not genomics, but Excel is also some kind of universal medium here as well), I store my data in SQLite files (extracts and summaries anyway; complete datasets take several terabytes), which makes retrieving complex information a breeze. But it needs to be documented and you need to be comfortable with the command line and do any kind of visualisation as a supplementary step. I know of a couple of colleagues doing the same, but we don’t use quite the same format, so data exchange is problematic. I use this setup mostly because I need it to work on remote HPC clusters in addition to a bunch of local workstations, and Excel is out of question there.
> The problem is that databases do not include a GUI for data viewing.
Let's say that I have PyCharm open right now, I'm importing Qt and Sqlite. How would you like your GUI to function? Seriously, write for me a detailed spec and a detailed workflow, and I'll get to work on it already. My Gmail username is the same as my HN username if you'd prefer to collaborate offline.
This invite goes for anybody else who [ab]uses Excel even though they are versed in SQL.
> If I then want to share a dataset with a colleague and email it to them, the easiest friction-free way is to attach an .xlsx file. Sending them an email with attachments of SQlite .db file + executable app for Windows/macOS is much more cumbersome. The alternative of sending them a link to a cloud-based "database-as-worksheet" SaaS platform just creates another set of problems. An all-in-one db+gui tool like MS Access also isn't really an option since it doesn't have the same powerful GUI data manipulation as Excel.
The alternative is to load the sqlite DB into Excel via PowerQuery and then share the file, which will maintain type safety of all columns through the excel data model.
This provides much better GUI data manipulation too, as you can define relationships between the data in the model e.t.c.
The problem is I would guess less than 1% of Excel users actually understand this functionality, but it is absolutely core to doing proper analysis in excel (not saying you don't use it - you probably do - but lots of users don't!).
Absolutely! I would add though that you really need to follow a tutorial/book to make everything make sense in it. I read a book by Rob Collie - but any well reviewed book on DAX should do it. The book “Supercharge Excel - When you learn DAX…” looks like it covers similar information but more up to date.
The analysis pattern is usually importing data and cleaning it in PowerQuery, then building relationships between tables in the data model view, and then analysing it via a ‘PowerPivot’ Table.
PowerPivot then has new functionalities compared to a regular pivot table (eg measures) that allow for automatically calculating columns based on context and some additional capabilities you don’t get in excel.
I do this all the time. Excel is a fantastic data GUI for ad-hoc reporting.
My typical quick turn-around process is: type SQL in text editor, test sql in database, create a view, connect to the view from excel, use native excel features to display whats needed.
Usually I create a summary page as well which uses sum-ifs and such on the query result for the high level detail rather than go through the SQL process for it separately
> An all-in-one db+gui tool like MS Access also isn't really an option since it doesn't have the same powerful GUI data manipulation as Excel.
What's missing from a GUI like MS Access or Libreoffice Base? In your example, you can input your comments/annotations (even something like "highlight these rows as having problems") in a separate table without touching the original data, then use a database query/view to look at both seamlessly. It's only a bit more involved than raw editing on a spreadsheet, and it inherently avoids accidental data loss/corruption.
You are not supposed to have a GUI view when you have 100K+ entries. Excel misguides people to think that database management is easy. The reality is it is not possible to have a GUI view of middle-large databases with millions of entries because it is computationally impossible
Would also like to know, seems like a perfect use case for the Flyweight design pattern - just get the total count of records for when needed (e.g. size of scrollbar) and show the stuff that should be on screen at any given moment, loading more in dynamically. Throw in allowing doing operations on ranges that are off screen and you should be good.
One thing I've learnt over the years, is that users do not care about the inherent complexity of your problem.
Like water, they will always choose the path of least resistance. It is why people would rather copy-paste documents than learn git, despite versioning being inherently complex. It is why people complain about android, but only use 1st party preinstalled apps or freemium ad-infested crap.
Excel works and it is easy. It does not matter how hard the underlying problem is. I hate having to use excel too, but I have found myself periodically relying on it when timelines get too narrow and having a ready-made interactive dashboard is convenient.
That being said, I can't imagine using it for a use-case where the rows-of-interest are greater than a few dozen.
a few thousand or more is practical, it depends on what the data is, if the data is inherently non-relational (a master parts list, a user list, a bunch of logged data points) then all is well, it breaks down based on how big the data set is.. and what the dataset is.
100k+ is pretty manageable in the Excel GUI. When you get above 1M then it starts getting dicey. I have a junior dev on my team that is very good at python. I only consider him "jr" in the language/tech he's assigned to work on daily, in python i consider him pretty solid.
When we need data work done on csv's larger than I can ask an Analyst to do in Excel I give it to him to write some python against. Finding and exploiting his python skills has won the guy a couple bumps in base pay.
Very much agreed - and the upper limit is a little more fuzzy, and has more to do with file size, record complexity (how many columns), and what you want to do with the data.
At some point the overall pokeyness of excel when dealing with large datasets overcomes the inertia of "everyone already knows it" and "we'd need a environment to spin up something more complex".
I’m probably outdated on this, but old Excel format have an upper limit of 65536 rows and the new format has a limit of 1M rows. So that’s where I would estimate the limit of upper bound. Could be less, can’t really think it’s possible to have more.
It's not computationally impossible; it's a hard problem that nobody has solved because they haven't found the niche to monetize it.
A user only has a tiny screen relative to the size of the backing data. The hard part is efficiently fetching the data to populate that screen. But it's not unsolvable; just tricky. Approaches like building realtime indexes speculatively based on likely next user query could help.
Google Sheets actually works a bit like this already, as it's backed by an online datastore.
Non-tech people are used to all-in-one data mindset (load everything into memory manner). The only query type they have experienced is a dumb full-text search. Most people don't have know filter keywords for google search, twitter search etc. Even though they are used to scrolling concept (view portion of data), they have no idea of querying to see 1 interested thing at a time.
I think the big point missing in all the comments that suggest linking to the db file and distributing is that it's read-only. Perhaps this is the intent too, but often Excel books are shared for other people's input /additions/ corrections to data. When it's linked to a backend database that's no longer trivial. Whereas just using base rows/ columns it's immediately read & write access.
But this is also a problem with using Excel for this. Let two other people edit it, and now you've got 3 different versions of the data. Which one is correct?
Having a single database and giving two other people access to it keeps the data centralised and keeps a single source of truth.
> The problem is that databases do not include a GUI for data viewing.
Exactly. It baffles me that such a tool still doesn't exist (though elsewhere someone claimed that MS Access is like this; I'm not familiar with it).
Keeping this in the cloud, with a web-based Excel-like interface, where you can share it with anyone you choose, but keep a single source of truth, I think that would be incredibly useful and solve this Excel-misuse issue.
I am not at all a programmer, but I remember that at least in early version(s) MS Access (circa 1994, Windows 3.1 times) was well behind Paradox in usability.
The are a ton of benefits to enforcing data integrity like data types, foreign keys, etc., but it also adds a ton of friction. Users encounter tons of frustrating errors while simply copying and pasting things, because certain values aren't allowed in certain columns.
I think you'd need human-readable datatypes displayed beneath each column name, adjustable by just clicking it and changing it in the drop-down, and massive flexibility out of the box. You shouldn't throw hard errors - just visually mark the invalid values red or pink or whatever, and let the user fix then before writing them to the database.
I wish there were modern record oriented databases and processing tools - I've used some of the tools on zOS and that whole mindset of "even the system tools are record aware" so you can use the equivalent of basic UNIX tools to do reporting and data analysis, is pretty powerful.
We could build something that is both as powerful as excel, and easier to use, yet designed for average users to manipulate very large data sets - we just have to chose to do that.
Python has footguns in it, PHP has a confusing standard library, perl is complex, and bash is missing some of the data processing primitives needed.
Microsoft Excel itself can connect to databases (MS SQL, PostgreSQL, Oracle, etc). I think you need to have set up the database table(s) elsewhere.
Microsoft Access provides a GUI to any database (including PostgreSQL etc), and (IIRC) the ability to create new tables. It allows editing in a table-like way (rows are locked during editing, if the database supports this), or in a form-like way. Queries can be made in a text/SQL-like way, with a GUI, or in a form-like way. For all this, it supports data types (number, date, lookup-from-another-table etc).
At my previous job, the research scientists had several tools built in Access. It was a very fast way to develop a UI, and the IT industry is less efficient now this is no longer commonly known or understood.
I think Access is Microsoft's best software. It's a very powerful tool, but was also very accessible. You can drag-and-drop to create multi-table/view queries without understanding SQL, then switch the mode and see the SQL. Once you have the query, you can drag-and-drop to create a form (to edit the data) or a report (to format each row as a page to print out etc).
It's a real shame, because Excel is great for taking quick notes when you have data, but you're not yet sure how you'll want to organize it, or what you'll want to do with it long term. You start by just jotting down numbers. But then you have everything there an an easy to digest form. You can create pivot tables to sort the data. Graphing is easy. The data gets stuck there because it's so easy to use and it's already there.
> you should be able to tell it whether a certain column is a date or not,
Not only is this possible, but the tool to do it is located in best location (large menu, center of the screen on the first ribbon tab). It take literally 2 clicks to do it once your cells or columns are selected. The only problem with this tool is that you have to use before copying your data and it can be frustrating if you forget to do it. If you want to import a file instead of copy-pasting the data, it's only like 1 or 2 extra clicks to set the data type for a column during the import.
And btw, the CSV format was intentionally designed to NOT have type information imbedded in the file itself. The application that is reading the CSV file must know the datatype for each column. For a versatile tool like excel, they is no perfect way to implement it and there will always be a fraction of users of have to override the choices made by by the software. For advanced users who use it everyday, you learn very quickly if the type of data you are normally working with will require you to force it or if excel will understand it correctly.
It looks to me like almost all of the anti-Excel comments on HN (including yours) are made by people who never or extremely rarely uses it and don't know what it can or can't do. It's typical that most if not all of the "missing features" listed by people on NH have been part of excel for at least a decades.
I love it when excel decides a bunch of digits is a date or decides to remove leading 0000s from a very important number that needs 0000s. Or maybe it wants to make a bunch of digits into scientific notation. I would prefer if excel left my cells alone or just asked what I wanted to do.
Good initiative, but there are hundreds of used languages, I hope they put a bit more effort into it than just testing the English dates cases. I caught one bad example, for any French speakers, in the article itself:
Plenty of gene names are partial/full backronyms that don't really bear any relation to their function. For example the R in MARCH stands for "RING Finger", RING means "Really Interesting New Gene" [0]; finger here is some substructure that I guess looks superficially like a finger on the protein. That's an astronomy-worthy acronym if ever there was one. You could probably avoid a lot of this if the naming conventions avoided using protected words, but then they're a lot less catchy and easy to remember. Also I don't think Excel has a list, but it might be useful to make one.
Part of the problem, highlighted in the article: Users don't set the data type of the column off of "General". So it tries to figure out the data type on entry. Setting the column to use a specific type ("Text"), keeps it from trying to use heuristics to figure out that something is a date.
Something more recent is the introduction into Excel of Power Query, which lets you import a CSV and apply arbitrary transformations (such as applying a type), before it hits the workbook, so if you need to pull in a CSV, you can do so, and it will always be imported the same way.
Does anyone here use Excel in any capacity on production data in the biosciences? If so, did you go to college for the biosciences, or are you supplying work using a tool you learned under another discipline?
I'm always fascinated to learn how Excel makes its way into unusual places.
Not in biotech, but have worked with people who use a tremendous amount of excel, especially in biology. A lot of the people still using Excel either came before bioinformatics libraries got really really good, had to basically teach themselves how to use a computer to deal with bio data, worked in a lab that had protocols in place for data that used Excel, and/or haven't gotten around to teaching themselves R. You have to keep in mind that Excel is extremely good at being a visual representation of data. R, Python (only really adopted in recent years), and Matlab (which I don't see too often in bio) are all excellent for manipulating the same data as Excel, but if you wanted to quickly scroll through your RNA sequencing counts, Excel provides a fast, (almost) out-of-the box way to get a "big picture" view of your data (I haven't met anyone doing bioinformatics in SAS or STATA, but I know people who have used those software in academia for other fields).
Even if no one is directly manipulating the data in Excel, if you don't import the data correctly, or forget to _not_ save the data, you'll end up fucking it with Excel's auto-formatting. These subtleties lead to things as mentioned in the article, but aren't things that the ordinary person learns except through mistakes. Nobody is born knowing what tools to use or how to use them, but Excel is one of the first pieces of software that deal with data for a lot of people, and as such one of the first things they turn to when faced with a new problem.
Looks more like the scientists are choosing the wrong tool to do their job. Sure you can use excel, to get a sense of the data, but for god sake, there are better tools for that - or even contribute to an existing open source one to do what you need. I'm quite sure scientists could get some grants to hire actual software engineers to come up with a better solution, based on the open source options existing there.
> I'm quite sure scientists could get some grants to hire actual software engineers to come up with a better solution
Uh ... no. Grants rarely ever support SWE outside of a core application. No granting agency would support writing a new open source tool that effectively replicates what is available in market today.
You are right in that genomic scientists chose poorly here. Excel isn't the right tool for the job, but there are very few options that could work, and the others required some assembly ... which they couldn't get money to fund. And the other potential solutions were not ubiquitous.
For them, renaming genes is the easier solution than switching workflows to new (to be assembled) tooling. Remember, scientists are people too ... they will opt to take paths of lower resistance even when they are suboptimal.
The error is to assume that we have a tool available to do it, aka Excel. If they assume that there is no tool available, then a tool have to be found/developed.
> Be conservative in what you send, be liberal in what you accept.
This is why I think that's wrong: if you allow people to be sloppy with how they do things, they'll do it, and then make it part of their workflow, product, religion or whatever, and now everyone is stuck with it.
Be absolutely explicit with what you accept and refuse to deal with crap. Then you will only ever have to maintain a simple validator and the code that deals with good data, rather than having to have an incredibly hairly validator that leaks into your logic at every level, followed by cementing your bugs into everyone's implementations.
Reality adapting to software (instead of the other way around) seems to be more and more common. Just today, my local library sent out email informing users that, while they agree that they shouldn't charge late fees for non-work days, the software company they use doesn't want to introduce such feature into the software, so they have no way but to charge for those days.
Excel allows you to force a specific type during file import or directly on the worksheet with literally two clicks once the region is selected. You can also create templates if you want all your new documents to use a specific type.
The scientists are not adapting to the software, they are adapting to the incompetence of people in their research groups who refuse to learn how to use their main work tool and/or don't want to do the extra 2 clicks it takes to select "text".
142 comments
[ 3.2 ms ] story [ 227 ms ] threadBut yes, just double-clicking the CSV from Explorer, using the legacy 'open this as a sheet' functionality, experiencing data loss and then complaining about it (and the state of Excel, MSFT and The World in general) on social media is much, much more fun...
Its easy to say its people being dumb, but at this point I really wish excel just wasn't so confident in itself and actually asked you during the default open operation what you want to do.
Something people do not understand is that type information in CSV files is conveyed out of band. The application that is reading the CSV file must know the datatype which effectively turns each CSV file into an application specific format.
Thanks for writing that! I don't think I've seen the reason for my (partial) dislike of CSV put into words that clearly before.
Any change to Excel's 'open a worksheet' logic would break so many workflows it's just not funny anymore. I'm not kidding if I say I suspect it would significantly impact several countries' GDP for a while.
Even the (sometimes comically inadequate) heuristics that Excel uses to auto-determine field types can't be updated, for very similar reasons. Backwards-compatibility is... interesting...
My preferred work flow would be to open the csv, change some formatting, then reinterpret the original data.
It would require keeping two sets of data for each cell, but that seems to already be the case since F2-enter on each cell would accomplish exactly that. Last I tried.
I wonder what is the ratio of people dealing with dates versus genes is... Probably very substantial on favour to those who deal with dates. So things just to work for them is likely better option.
It should be tough to have such a name. The most important systems like tax/insurance/airline booking are often the most unkind systems. If you're not familiar with computers, it's almost impossible to imagine the potential cause of problems is your name.
[0] https://www.bbc.com/future/article/20160325-the-names-that-b... > These unlucky people have names that break computers
Any tool that seeks to replace it would need to be as easy to use, as flexible. And preferably fewer bugs.
The problem is that the paradigm for spreadsheets works well for many projects, even if the implementations are crap.
[1] https://www.explainxkcd.com/wiki/images/5/5f/exploits_of_a_m...
"Nil" is probably safe, because the lisp curse will actually work in your favor.
Is this supposed to be a joke? Since when does excel support CSV files? Yes you can import and export CSV files but that is just there to check a box. That feature doesn't actually work. Just import and export .xlsx files in your applications directly.
CSV is such a bad format because it's not even a standard, there is RFC4180 but most people have never heard of it. CSV is complicated enough that anyone who thinks they can implement it will get it wrong on their first attempt but simple enough that people believe they can implement it on their first attempt.
It's the second button on the data tab, you are still spreading misinformation.
You're being hyperbolic and your .xlsx advice doesn't apply to other systems we don't control that only offer .csv files.
Examples... my credit card website and Ebay only offer csv downloads of data. I use MS Excel to import those csv files all the time and it works well enough.
Yes, I'm aware of potential data-conversion flaws with importing csv files. (My previous comment: https://news.ycombinator.com/item?id=25017116 )
All those caveats with csv are irrelevant when the system that has the data people want only offers csv. Using Excel's feature to import csv -- and being aware of the format dangers -- is more practical than manually retyping all the data from scratch.
This is the crux of the issue. Yes, Excel could do better with support for CSV. (OpenOffice and LibreOffice habdle this better, for example.) But no, genetics is not some niche use-case for this better support. It's just one example of many. I do analytics of another sort for a bank, and we run into this problem all the time with those pesky 16-digit account numbers.
But, while Microsoft might do well to make changes to address their poor implementation, someone somewhere will implement the next best thing and screw it up, too.
- Plain text file
- Supports formatting, cell types, etc
- Does not support full spreadsheet features like formulas
You could call it CHU.
Now you just need to make Excel accept it.
I think it's already defined?
<input type="text">, <input type="number">, <input type="date">
> currency
You probably need to use the microdata format https://schema.org/Offer
Excel misuse is sadly rampant and one of my main frustrations at my previous project. Excel is popular for this sort of misuse because when you open it, it presents you with an empty table and you can immediately start typing. This invites tabular data. But Excel inevitably fucks up because it lacks proper data types, data validation, or foreign keys, and loves making assumptions about what you mean. This makes it a terrible and harmful choice for any sort of serious data. I even proposed a new project for an Excel-like frontend backed by a database exactly for these sort of situations. Because I do get what attracts people to Excel for this sort of thing. And most people don't realise what a terrible choice it is.
And there aren’t good “data browsers” that have as low a learning curve.
I’ve been especially looking for a JSON browser/editor since excel doesn’t do that well and I’m unsuccessful trying to explain how to use basic text editors to people who can’t do basic functions like open files that aren’t associated with a program.
If someone is trained to the point of working on genetic data at this level, should they not also have been trained to a reasonable level in domain appropriate software and tools?
I’ve done some travel to Africa and am amazed at the ingenuity/batshit workflows that exist to get work done and live life.
There’s many times where I don’t want to do anything more than open, sort, filter, and never see the file again. And I’d like something better than Excel, but haven’t found it.
Maybe 70% the time, vi or BBEdit or shell commands work but otherwise excel.
For anything of significance, I use Python notebooks or dedicated data environment.
If there was a better tool, I think people would train. But there’s not, that I’m aware of, so Excel sticks around.
I use MS Excel extensively and create new .xlsx files every week even though I know databases like SQLite, MySQL, and did consulting for Oracle RDBMS. The problem is that databases do not include a GUI for data viewing.
And even though I also know programming tools like C++ Qt and C# WinFroms to slap in front of databases, starting with a blank Excel grid is faster and easier than wiring up a datagrid UI control to a database and compiling an app.
If I then want to share a dataset with a colleague and email it to them, the easiest friction-free way is to attach an .xlsx file. Sending them an email with attachments of SQlite .db file + executable app for Windows/macOS is much more cumbersome. The alternative of sending them a link to a cloud-based "database-as-worksheet" SaaS platform just creates another set of problems. An all-in-one db+gui tool like MS Access also isn't really an option since it doesn't have the same powerful GUI data manipulation as Excel.
The scenario of "I just sent you an xlsx where the rows highlighted in red are problems and if you can just add your notes to column K, that would be great. Thanks!" -- is not easy in other tools that are not spreadsheets.
People (including programmers skilled in databases) constantly "misuse" Excel because it's the most practical way to get work done compared to the friction of alternative tools.
You don't need to write your own GUI for databases. Loads exists.
> "I just sent you an xlsx where the rows highlighted in red are problems and if you can just add your notes to column K, that would be great. Thanks!"
This is a pretty common use-case, and neatly demonstrates a few of the reasons Excel is so popular. Sharing a self-contained DB with a colleague that they can view + edit with software they likely already have, modifying the schema easily on the fly, highlighting some rows. And that's not to mention the programmability - from having a simple "=SUM(...)" cell, to hacking some VBA or the newly introduced Lambda (https://www.microsoft.com/en-us/research/blog/lambda-the-ult...)
I wouldn't personally build anything important with Excel as a sort of DB, but I understand why some people would want to
1) Those utilities are not typically included in the workstation image of laptops/desktops unlike MS Excel which is already part of Office 365. Millions are already familiar with the GUI of Excel.
2) The datagrid viewers in those tools are not powerful and feature-rich like Excel. They are often missing features that are taken for granted by Excel users:
- formatting any arbitrary row or column with bold/italics and change the font color or background cell color.
- pivot the data via drag & drop UI (instead of manually writing a SQL cross-tab query)
- hide rows or collapse rows into outlines
- cut rows 37 to 52 and paste them above row 5. That type of behavior is not easy in generic database viewers because most tables -- by typical design of RDBMS table row ids -- do not consider the visible spatial ordering of rows the way the end user wants to see them on the screen unless one adds an extra column to the table such as "gui_view_order_id". Excel has user specified row ordering as default out-of-the-box behavior.
- ... tons of other GUI features like formulas, spell check, etc
sqlite's lack of verifying that your data fits in your defined schema is by far my biggest problem with sqlite.
But the real nice thing is that SQLite will allow spreadsheet-like behaviour by allowing one to enter an invalid data type. I could check that in Python or I can store it and warn "Invalid type blah blah blah". This will make life easier for those coming from a spreadsheet, or importing data. As the program matures, I can reevaluate what to do with invalid data as a default and what options to give the user.
Additionally, because SQLite allows arbitrary data in any column, adding support for e.g. formulae is greatly simplified.
Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
As long as it's not tedious to set a few thousand that sounds great.
> What are the other most common formulae that you use?
I mostly use SUM, COUNT and IF (if-then), along with functions to check if a string value is a valid number. Also string formatting of numbers and dates (for concatenating with text).
I've also used lookups, ie find the row matching this and extract the value from the given column in that row. Though I'm not super happy with the way Excel does that, surely some room for improvement.
> Perhaps instead of filtering, you'd like to see outliers or the range of values?
In addition. Sometimes I just want to view all the values matching X, other times I want to quickly see any outliers. Definitely ranges, especially for numbers (just larger than 0 for example, or between 5 and 10).
Sounds like a very interesting project, if you got a link I'd be interested in tracking progress. If not, I'd be happy if you did a Show HN when you're ready :)
Right now I've started a Git repo with a roadmap but I've not uploaded any code yet as I'm still deciding on the architecture:
https://github.com/dotancohen/structuresheet
I'm old school, so ctrl+c, shift+end (maybe tweak selection area with cursors if Excel is being dumb) and ctrl+v.
The way I work, it would have been better to mark a cell itself as being a "constant" or "input parameter", so that when I reference the cell it is treated as an absolute reference when pasting.
That is, if I reference cell B2 in a formula and paste that formula in a different cell, it is B2 itself that determines if it's a relative or absolute reference, not the way I typed it in the formula.
It's not a huge deal, but it would be one of those nice things that makes life more enjoyable.
In comparison, I often have formulas with over 20 cell references and/or multiple formulas referencing the "constants", so getting asked for each would indeed be tedious.
This is not "filtering", this is faceted search.
I am also a big fan of faceted search, and I found the occasional writing of Simon Willison’s on the topic very informative.
https://simonwillison.net/2018/Oct/4/datasette-ideas/#Facet_...
But yeah great feature, and thanks for the reference.
To me, a critical one is easy plots. Checking how an arbitrary column changes as a function of an arbitrary other column by adding a scatter plot in less than 5 seconds is fantastic.
Formulae are also very useful. Adding numerical derivatives or integrals by just putting a formula in a new column is very useful as well. The point is not to have publication-quality, highly accurate numbers, but just quick and dirty operations to see if it warrants further investigation.
I am happy to discuss my use cases if you are interested (it might be going a bit out of topic for this thread).
https://github.com/dotancohen/structuresheet
I'll see what cross-platform plotting libraries I can include. Thank you for the idea, I agree it is important. Yes, I am very interested in your use cases! My Gmail username is the same as my HN username.
Python / Qt / SQLite
I don't see any part that gives a strongly-typed properties.
Do I miss something?
Scientists are not superhuman. Just like anybody, they’ll jump through a lot of hoops if they think the results justify it, but they are sometimes quite resistant to change for the sake of change, and sometimes even to change itself.
One can be a great chemist or know all there is to know about how purple long-tailed fruit flies from Siberia and have no clue about how computers work. Or be very proficient in a given piece of complex software to process NMR spectra and barely able to operate Outlook. But these people all make do with Excel.
> In a second moment, python, pandas and notebook are pretty accessible too
It’s much heavier, the IDEs are much more complex than Excel, and quite a lot of people on Earth are not natural programmers. Startup time, learning curve, steps to get a useful graph to check a trend, etc. All friction adds up. I’ve seen it countless times: when you show them the results of a complex workflow, they are excited. They start getting distracted when you talk about architecture, and they’re lost when you go into things like pandas and scipy. Then they nod politely, keep doing their stuff in Excel, and call you when they need a bit of wizardry for a paper.
In short, they are regular users, even if the software they use can be highly specific. Ease of use and lack of friction are paramount.
Myself (not genomics, but Excel is also some kind of universal medium here as well), I store my data in SQLite files (extracts and summaries anyway; complete datasets take several terabytes), which makes retrieving complex information a breeze. But it needs to be documented and you need to be comfortable with the command line and do any kind of visualisation as a supplementary step. I know of a couple of colleagues doing the same, but we don’t use quite the same format, so data exchange is problematic. I use this setup mostly because I need it to work on remote HPC clusters in addition to a bunch of local workstations, and Excel is out of question there.
This invite goes for anybody else who [ab]uses Excel even though they are versed in SQL.
https://sqlitebrowser.org/
The alternative is to load the sqlite DB into Excel via PowerQuery and then share the file, which will maintain type safety of all columns through the excel data model.
This provides much better GUI data manipulation too, as you can define relationships between the data in the model e.t.c.
The problem is I would guess less than 1% of Excel users actually understand this functionality, but it is absolutely core to doing proper analysis in excel (not saying you don't use it - you probably do - but lots of users don't!).
Do you have any recommendations for getting started with this?
https://powerquery.microsoft.com/en-us/excel/
The analysis pattern is usually importing data and cleaning it in PowerQuery, then building relationships between tables in the data model view, and then analysing it via a ‘PowerPivot’ Table.
PowerPivot then has new functionalities compared to a regular pivot table (eg measures) that allow for automatically calculating columns based on context and some additional capabilities you don’t get in excel.
My typical quick turn-around process is: type SQL in text editor, test sql in database, create a view, connect to the view from excel, use native excel features to display whats needed.
Usually I create a summary page as well which uses sum-ifs and such on the query result for the high level detail rather than go through the SQL process for it separately
What's missing from a GUI like MS Access or Libreoffice Base? In your example, you can input your comments/annotations (even something like "highlight these rows as having problems") in a separate table without touching the original data, then use a database query/view to look at both seamlessly. It's only a bit more involved than raw editing on a spreadsheet, and it inherently avoids accidental data loss/corruption.
Why not?
Like water, they will always choose the path of least resistance. It is why people would rather copy-paste documents than learn git, despite versioning being inherently complex. It is why people complain about android, but only use 1st party preinstalled apps or freemium ad-infested crap.
Excel works and it is easy. It does not matter how hard the underlying problem is. I hate having to use excel too, but I have found myself periodically relying on it when timelines get too narrow and having a ready-made interactive dashboard is convenient.
That being said, I can't imagine using it for a use-case where the rows-of-interest are greater than a few dozen.
When we need data work done on csv's larger than I can ask an Analyst to do in Excel I give it to him to write some python against. Finding and exploiting his python skills has won the guy a couple bumps in base pay.
At some point the overall pokeyness of excel when dealing with large datasets overcomes the inertia of "everyone already knows it" and "we'd need a environment to spin up something more complex".
A user only has a tiny screen relative to the size of the backing data. The hard part is efficiently fetching the data to populate that screen. But it's not unsolvable; just tricky. Approaches like building realtime indexes speculatively based on likely next user query could help.
Google Sheets actually works a bit like this already, as it's backed by an online datastore.
Having a single database and giving two other people access to it keeps the data centralised and keeps a single source of truth.
Exactly. It baffles me that such a tool still doesn't exist (though elsewhere someone claimed that MS Access is like this; I'm not familiar with it).
Keeping this in the cloud, with a web-based Excel-like interface, where you can share it with anyone you choose, but keep a single source of truth, I think that would be incredibly useful and solve this Excel-misuse issue.
MS Access was the usurper of dBase's crown. And then Excel took over ...
https://en.wikipedia.org/wiki/Paradox_(database)
I am not at all a programmer, but I remember that at least in early version(s) MS Access (circa 1994, Windows 3.1 times) was well behind Paradox in usability.
The are a ton of benefits to enforcing data integrity like data types, foreign keys, etc., but it also adds a ton of friction. Users encounter tons of frustrating errors while simply copying and pasting things, because certain values aren't allowed in certain columns.
I think you'd need human-readable datatypes displayed beneath each column name, adjustable by just clicking it and changing it in the drop-down, and massive flexibility out of the box. You shouldn't throw hard errors - just visually mark the invalid values red or pink or whatever, and let the user fix then before writing them to the database.
It's open source and hosted in the cloud. It has an excel like interface that you can share and work on with others.
It does exactly what you mentioned. It's a cloud hosted, database tool with an excel like interface and collaboration features.
It is also open source and can be self hosted, but you can use the oficial website to use it directly without having to use it yourself :)
We could build something that is both as powerful as excel, and easier to use, yet designed for average users to manipulate very large data sets - we just have to chose to do that.
Python has footguns in it, PHP has a confusing standard library, perl is complex, and bash is missing some of the data processing primitives needed.
Actually I would change that from your answer to
"Access didn't come with the cheapest version of Office"
Microsoft Excel itself can connect to databases (MS SQL, PostgreSQL, Oracle, etc). I think you need to have set up the database table(s) elsewhere.
Microsoft Access provides a GUI to any database (including PostgreSQL etc), and (IIRC) the ability to create new tables. It allows editing in a table-like way (rows are locked during editing, if the database supports this), or in a form-like way. Queries can be made in a text/SQL-like way, with a GUI, or in a form-like way. For all this, it supports data types (number, date, lookup-from-another-table etc).
At my previous job, the research scientists had several tools built in Access. It was a very fast way to develop a UI, and the IT industry is less efficient now this is no longer commonly known or understood.
I think Access is Microsoft's best software. It's a very powerful tool, but was also very accessible. You can drag-and-drop to create multi-table/view queries without understanding SQL, then switch the mode and see the SQL. Once you have the query, you can drag-and-drop to create a form (to edit the data) or a report (to format each row as a page to print out etc).
Tuesday: New solution is released to much user enthusiasm
Thursday: Users don't get it and are already back in Excel
You can, very easily
Not only is this possible, but the tool to do it is located in best location (large menu, center of the screen on the first ribbon tab). It take literally 2 clicks to do it once your cells or columns are selected. The only problem with this tool is that you have to use before copying your data and it can be frustrating if you forget to do it. If you want to import a file instead of copy-pasting the data, it's only like 1 or 2 extra clicks to set the data type for a column during the import.
And btw, the CSV format was intentionally designed to NOT have type information imbedded in the file itself. The application that is reading the CSV file must know the datatype for each column. For a versatile tool like excel, they is no perfect way to implement it and there will always be a fraction of users of have to override the choices made by by the software. For advanced users who use it everyday, you learn very quickly if the type of data you are normally working with will require you to force it or if excel will understand it correctly.
It looks to me like almost all of the anti-Excel comments on HN (including yours) are made by people who never or extremely rarely uses it and don't know what it can or can't do. It's typical that most if not all of the "missing features" listed by people on NH have been part of excel for at least a decades.
and MARS tweaked to MARS1.
[0] https://pubmed.ncbi.nlm.nih.gov/24928685/
Something more recent is the introduction into Excel of Power Query, which lets you import a CSV and apply arbitrary transformations (such as applying a type), before it hits the workbook, so if you need to pull in a CSV, you can do so, and it will always be imported the same way.
I'm always fascinated to learn how Excel makes its way into unusual places.
Even if no one is directly manipulating the data in Excel, if you don't import the data correctly, or forget to _not_ save the data, you'll end up fucking it with Excel's auto-formatting. These subtleties lead to things as mentioned in the article, but aren't things that the ordinary person learns except through mistakes. Nobody is born knowing what tools to use or how to use them, but Excel is one of the first pieces of software that deal with data for a lot of people, and as such one of the first things they turn to when faced with a new problem.
Uh ... no. Grants rarely ever support SWE outside of a core application. No granting agency would support writing a new open source tool that effectively replicates what is available in market today.
You are right in that genomic scientists chose poorly here. Excel isn't the right tool for the job, but there are very few options that could work, and the others required some assembly ... which they couldn't get money to fund. And the other potential solutions were not ubiquitous.
For them, renaming genes is the easier solution than switching workflows to new (to be assembled) tooling. Remember, scientists are people too ... they will opt to take paths of lower resistance even when they are suboptimal.
https://news.ycombinator.com/item?id=30986868
This is why I think that's wrong: if you allow people to be sloppy with how they do things, they'll do it, and then make it part of their workflow, product, religion or whatever, and now everyone is stuck with it.
Be absolutely explicit with what you accept and refuse to deal with crap. Then you will only ever have to maintain a simple validator and the code that deals with good data, rather than having to have an incredibly hairly validator that leaks into your logic at every level, followed by cementing your bugs into everyone's implementations.
The scientists are not adapting to the software, they are adapting to the incompetence of people in their research groups who refuse to learn how to use their main work tool and/or don't want to do the extra 2 clicks it takes to select "text".
Or anything with autocorrect and autoformat.