5 ms·

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

by mcv 5y ago

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.

prepend 5y ago
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.
- tanbog10 5y ago
  "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?
  - overthemoon 5y ago
    I'm continually shocked at how technically incompetent academics can be. Otherwise brilliant people with the most batshit technical workflows I've ever seen.
    - dotancohen 5y ago
      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.
    - prepend 5y ago
      People are very inventive to get their job done. I’ve done some travel to Africa and am amazed at the ingenuity/batshit workflows that exist to get work done and live life.
  - prepend 5y ago
    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.
jasode 5y ago
>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.
- JustAQ 5y ago
  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.
  - smcl 5y ago
    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-ultimatae-excel-worksheet-function/ 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
Symbiote 5y ago
> Excel-like frontend backed by a database 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).
civilized 5y ago
Monday: "Excel is terrible, use my new solution, it'll blow your socks off!" Tuesday: New solution is released to much user enthusiasm Thursday: Users don't get it and are already back in Excel
pelasaco 5y ago
classical situation where the tool defines the workflow and not the other way around..
thesuitonym 5y ago
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.
listenallyall 5y ago
> you should be able to tell it whether a certain column is a date or not You can, very easily
- deleted 5y ago
  [deleted]
max599 5y ago
> 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.

by mcv 5y ago

prepend 5y ago

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.

tanbog10 5y ago
"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?
- overthemoon 5y ago
  I'm continually shocked at how technically incompetent academics can be. Otherwise brilliant people with the most batshit technical workflows I've ever seen.
  - dotancohen 5y ago
    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.
  - prepend 5y ago
    People are very inventive to get their job done. I’ve done some travel to Africa and am amazed at the ingenuity/batshit workflows that exist to get work done and live life.
- prepend 5y ago
  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.

jasode 5y ago

>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.

JustAQ 5y ago
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.
- smcl 5y ago
  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-ultimatae-excel-worksheet-function/ 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

Symbiote 5y ago

> Excel-like frontend backed by a database 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).

civilized 5y ago

Monday: "Excel is terrible, use my new solution, it'll blow your socks off!" Tuesday: New solution is released to much user enthusiasm Thursday: Users don't get it and are already back in Excel

pelasaco 5y ago

classical situation where the tool defines the workflow and not the other way around..

thesuitonym 5y ago

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.

listenallyall 5y ago

> you should be able to tell it whether a certain column is a date or not You can, very easily

deleted 5y ago
[deleted]

max599 5y ago

> 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.