6 ms·

I agree with a lot of the points raised here. I think many of the problems with spreadsheets are due to the software rather than the users. As mentioned in the

I agree with a lot of the points raised here. I think many of the problems with spreadsheets are due to the software rather than the users. As mentioned in the article, its hard to slowly iterate from a small manageable spreadsheet to an larger software solution. For example, Excel would be a lot more usable and maintainable for me if there was a way to make a special "data sheet" in which data types are forced to be consistent within columns and there was a concept of column names. Still GUI-based and user-friendly. That would encourage a logical seperation between data entry, data output, and computations. In my experience, the main challenge of helping users with spreadsheets is when they create spaghetti code that mixes data and computation together.

redler 5y ago
I'm not sure about type enforcement, but there is the concept of "named ranges" you can apply to columns. So instead of C1:C99 you can refer to PRICES.
Ajedi32 5y ago
I've had similar thoughts in the past. Databases are very machine-friendly, but too static and inflexible to match the usability of spreadsheets. Spreadsheets are extremely user-friendly, but too inconsistent and unconstrained to be efficient for programmatic access. It seems like there should be a way to combine the two. Maybe a minimal set of optional constraints (like a separation between data and code) like you proposed would be a good starting point. Make tables a first-class citizen backed by an embedded SQLite database (or something similar); let users write real SQL to query tables in formulas, maybe update the file format a bit to make it easier for programs to parse and access concurrently. Could be an interesting project...
- jandrese 5y ago
  Isn't this what FileMaker Pro is all about?
  - reidjs 5y ago
    Yep! I personally like Airtable as well, slightly better UX (but expensive for large teams)
- _jal 5y ago
  Honestly, what's missing is a GUI builder for Postgres that non-programmers can use. Others have mentioned Filemaker and Access, and I think that's exactly right - non-programmers can understand datatypes, that's not the issue. The issue is a UI they can use and (more importantly) iterate on themselves. One of the major strengths of spreadsheets is "touchability" - your stuff is right there. Psql is the opposite - nothing is visible without the right incantation, and non-programmers can't do much about that.
- andrew_jef 5y ago
  I've read that nocodb is aiming to solve the issue that you mentioned https://www.nocodb.com/ https://www.nocodb.com/
AtlasBarfed 5y ago
This article is dead on from an analysis/criticism/insight: devs are called in when it's a sinking ship and don't see all the cargo the ship has hauled. Why hasn't microsoft or someone taken the basic spreadsheet model to a shared-database scalable one? The UI is basically set at this point. A naive schema (filename, tab, x, y, value) is what an excel sheet is. It's not like we are dealing with "impedence mismatch" and even shared editing can be reasonably handled with database transactions (or RAFT if you want to get really big/distributed) I think the lack of this is a sign of Microsoft Office completely owning the space and not wanting to innovate at all. And the huge amount of effort it would take to replicate excel-level operations in a database application server is nontrivial. But man, you could have an API for doing excel operations against a database schema, and export to excel... And as you said, you could do lots of schema based options in databases that aren't natural to excel.
- andrewlgood 5y ago
  How would you put guardrails on the spreadsheets to make sure spreadsheets do not accidently damage the database performance through bad calls? In the hierarchy of skills, SQL is significantly less prevalent that spreadsheets.
  - bhl 5y ago
    Perhaps you could come up with a list of commonly-used functions and write optimized SQL functions for them, such that users start off learning with that API and gradually learn SQL when they're more comfortable.
  - edmundsauto 5y ago
    Excel data is usually on a scale that DBs have no problem with, even doing full table scans. I am assuming this for something that starts life as a spreadsheet, not a database first where the sheet is initialized via a query.
LegitGandalf 5y ago
Tables are probably the most overlooked feature of Excel. Why use tables? * Each column is uniquely named - no more wondering if you are referencing the right cell, no more thinking about "to $ or not to $" * The table's rows and columns are reliably discovered by pivot tables - no more wondering if the entire dataset is referenced by the pivot * New columns that are formulas are automatically applied to every row * Tables have names, so it is easy to understand which table a pivot is referencing The true, reliable and sane power of excel lies in Tables + Pivots + Charts. If you drive most of the problem solving into those paradigms you will keep hair!
- tomnipotent 5y ago
  Most people have no clue these features exist, or that Excel even has advanced data modeling that supports all sorts of goodies including strict types and joins/merges/appends. It's not as accessible as just basic sheets, but I've had no problem crunching millions of rows of data in Excel with sub-second response times.
  - piyh 5y ago
    Powerquery is great, but too bad it breaks with version upgrades.
  - jjtheblunt 5y ago
    isn't excel limited to just over a million rows?
    - tomnipotent 5y ago
      Excel 2013 and later has a columnar database capable of handling millions of rows, but not through the standard sheets interface so it loses a lot of the utility people are used to.
      - jodrellblank 5y ago
        How? Through what interface, or by what feature name to Google?
sparsely 5y ago
I think Airtable is starting to get us there, but it's a long way to go. Something that combined Airtable, excel, and maybe a more userfriendly (and more restricted) version of darklang for defining formulae could be really slick.
- bdcravens 5y ago
  Airtable is pretty, but lacks simple functionality that spreadsheets have had for 30 years (like aggregates across rows). Airtable is more of a replacement for Access than Excel.
- laGrenouille 5y ago
  Airtable is a great example of what I would like to see. Basically, the ability to create sheets that are are like airtable-like that users can reference as usual in other general-purpose excel sheets.
gbrown 5y ago
Be careful what you wish for - I think you might have just reinvented MS Access.
- testudovictoria 5y ago
  I was going to say that I have a small, very limited amount of experience with MS Access. I agree; the middle ground between Excel and databases sounds very similar to Access. We had been using Excel for org charts like most places. We wanted to add grouping, metadata, and neatly be able to extend/add information with or without constraints. This was much before I knew anything database related, and Access seemed to be more powerful than Excel. Having generated forms to fit the data model was much more user friendly and a lot less error prone than adding a new row to an Excel sheet. People criticize Access for being a dumb database or a Excel with too much heavy lifting. It occupies a specific space as a DB on rails.
  - gbrown 5y ago
    I was mostly being glib, but I've had some bad experiences with Access over the years. I actually think lightweight database and scripting utilities in Excel could be good, but it would be susceptible to some of the design traps that Access steps on.
    - infogulch 5y ago
      What are the biggest design traps that Access fell into? If someone were rebuilding spreadsheets with more structured design like Access, what should they avoid?
hnlmorg 5y ago
> For example, Excel would be a lot more usable and maintainable for me if there was a way to make a special "data sheet" in which data types are forced to be consistent within columns and there was a concept of column names. You can already do both of those in Excel: - data types: https://support.microsoft.com/en-gb/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249 https://support.microsoft.com/en-gb/office/apply-data-valida... (though you'd also need to lock the spreadsheet as otherwise someone could remove the validation on the cells) - column naming: https://smallbusiness.chron.com/give-name-columns-excel-73447.html https://smallbusiness.chron.com/give-name-columns-excel-7344... (you can name individual cells and ranges too. Which means you can actually start to write formulas that look more like C with name variables, very loosely speaking of course) There's a surprising amount of hidden functionally in Excel. Personally I think that while ribbon bar might have made core features a lot easier for some, it's made a lot of the more advanced tools harder to discover.
asdff 5y ago
There is so much excel voodoo involved to do things with that software. I still believe that the learning curve of excel is no harder than the learning curve of doing the same exact thing in R or python, plus you'd end up having the data and the formulas in different places which brings loads of benefits (for instance, git). People are just familiar with excel because thats what they used to make a chart in science class in 7th grade since 1995, but they really could have learned to make the same chart in 7th grade with a language like python too, if it were only taught python instead of excel in school. And then we'd have a generation of workers fluent in a language like python rather than fluent in the very limited use case by comparison Excelese, and we would no doubt reap the benefits in our GDP. Its like we are limiting the knowledge of fire among our tribe when we don't really have to, it's perfectly learnable.
- antipaul 5y ago
  I dunno, any programming seems a different beast than a spreadsheet tool. For python, just think of installing it or setting up virtual environments. Like the original article says, “spreadsheets are the original low code”
  - asdff 5y ago
    Python is preinstalled on macs at least, that's a decent chunk of personal computers. You have to pay for excel. And you don't need to bother with virtual environments to fiddle with a flat file.
    - bnj 5y ago
      I think an important part of the battle for attention is that excel is visible as a GUI that inexperienced users can open from an icon and see and manipulate. A simple GUI utility with Python input in one pane and data input in the other might put them on more equal footing.
- watertom 5y ago
  Return on investment, essentially a zero barrier to entry, and immediate gratification, and it's wildly flexible. It takes about 10 minutes to learn the basics a spreadsheet, and what you get back is immense. You couldn't get python up in running in 10 minutes, then you need to learn the language, the syntax, and how to structure a program, frankly I'm tired just starting to type out what needs to happen even before you can do *ANYTHING*. Some people can get YEARS of productivity from those first minutes with a spreadsheet. The return on that initial 10 minute investment justifies spending more time to learn the more esoterica aspects of a spreadsheet, but even then, most people don't want to be bothered with learning how to "program". Why do so many people start playing guitar/piano/drums/etc, but so few finish? Because it requires a significant up front investment, no immediate gratification, and a long, slow return on that significant up front investment, learning music is somewhat flexible, but you need to be highly skilled in order to exploit the flexibility.
jbverschoor 5y ago
Data tables: https://youtu.be/0nbkaYsR94c?t=2268 https://youtu.be/0nbkaYsR94c?t=2268 Although no validation/types
- dragonwriter 5y ago
  Excel has, IIRC, had data validation longer than it has had tables (even if you count the time before Excel 2007 when tables were called lists),and its had both (with the same inclusion) for >20 years.

redler 5y ago

I'm not sure about type enforcement, but there is the concept of "named ranges" you can apply to columns. So instead of C1:C99 you can refer to PRICES.

Ajedi32 5y ago

I've had similar thoughts in the past. Databases are very machine-friendly, but too static and inflexible to match the usability of spreadsheets. Spreadsheets are extremely user-friendly, but too inconsistent and unconstrained to be efficient for programmatic access. It seems like there should be a way to combine the two. Maybe a minimal set of optional constraints (like a separation between data and code) like you proposed would be a good starting point. Make tables a first-class citizen backed by an embedded SQLite database (or something similar); let users write real SQL to query tables in formulas, maybe update the file format a bit to make it easier for programs to parse and access concurrently. Could be an interesting project...

jandrese 5y ago
Isn't this what FileMaker Pro is all about?
- reidjs 5y ago
  Yep! I personally like Airtable as well, slightly better UX (but expensive for large teams)
_jal 5y ago
Honestly, what's missing is a GUI builder for Postgres that non-programmers can use. Others have mentioned Filemaker and Access, and I think that's exactly right - non-programmers can understand datatypes, that's not the issue. The issue is a UI they can use and (more importantly) iterate on themselves. One of the major strengths of spreadsheets is "touchability" - your stuff is right there. Psql is the opposite - nothing is visible without the right incantation, and non-programmers can't do much about that.
andrew_jef 5y ago
I've read that nocodb is aiming to solve the issue that you mentioned https://www.nocodb.com/ https://www.nocodb.com/

AtlasBarfed 5y ago

This article is dead on from an analysis/criticism/insight: devs are called in when it's a sinking ship and don't see all the cargo the ship has hauled. Why hasn't microsoft or someone taken the basic spreadsheet model to a shared-database scalable one? The UI is basically set at this point. A naive schema (filename, tab, x, y, value) is what an excel sheet is. It's not like we are dealing with "impedence mismatch" and even shared editing can be reasonably handled with database transactions (or RAFT if you want to get really big/distributed) I think the lack of this is a sign of Microsoft Office completely owning the space and not wanting to innovate at all. And the huge amount of effort it would take to replicate excel-level operations in a database application server is nontrivial. But man, you could have an API for doing excel operations against a database schema, and export to excel... And as you said, you could do lots of schema based options in databases that aren't natural to excel.

andrewlgood 5y ago
How would you put guardrails on the spreadsheets to make sure spreadsheets do not accidently damage the database performance through bad calls? In the hierarchy of skills, SQL is significantly less prevalent that spreadsheets.
- bhl 5y ago
  Perhaps you could come up with a list of commonly-used functions and write optimized SQL functions for them, such that users start off learning with that API and gradually learn SQL when they're more comfortable.
- edmundsauto 5y ago
  Excel data is usually on a scale that DBs have no problem with, even doing full table scans. I am assuming this for something that starts life as a spreadsheet, not a database first where the sheet is initialized via a query.

LegitGandalf 5y ago

Tables are probably the most overlooked feature of Excel. Why use tables? * Each column is uniquely named - no more wondering if you are referencing the right cell, no more thinking about "to $ or not to $" * The table's rows and columns are reliably discovered by pivot tables - no more wondering if the entire dataset is referenced by the pivot * New columns that are formulas are automatically applied to every row * Tables have names, so it is easy to understand which table a pivot is referencing The true, reliable and sane power of excel lies in Tables + Pivots + Charts. If you drive most of the problem solving into those paradigms you will keep hair!

tomnipotent 5y ago
Most people have no clue these features exist, or that Excel even has advanced data modeling that supports all sorts of goodies including strict types and joins/merges/appends. It's not as accessible as just basic sheets, but I've had no problem crunching millions of rows of data in Excel with sub-second response times.
- piyh 5y ago
  Powerquery is great, but too bad it breaks with version upgrades.
- jjtheblunt 5y ago
  isn't excel limited to just over a million rows?
  - tomnipotent 5y ago
    Excel 2013 and later has a columnar database capable of handling millions of rows, but not through the standard sheets interface so it loses a lot of the utility people are used to.
    - jodrellblank 5y ago
      How? Through what interface, or by what feature name to Google?

sparsely 5y ago

I think Airtable is starting to get us there, but it's a long way to go. Something that combined Airtable, excel, and maybe a more userfriendly (and more restricted) version of darklang for defining formulae could be really slick.

bdcravens 5y ago
Airtable is pretty, but lacks simple functionality that spreadsheets have had for 30 years (like aggregates across rows). Airtable is more of a replacement for Access than Excel.
laGrenouille 5y ago
Airtable is a great example of what I would like to see. Basically, the ability to create sheets that are are like airtable-like that users can reference as usual in other general-purpose excel sheets.

gbrown 5y ago

Be careful what you wish for - I think you might have just reinvented MS Access.

testudovictoria 5y ago
I was going to say that I have a small, very limited amount of experience with MS Access. I agree; the middle ground between Excel and databases sounds very similar to Access. We had been using Excel for org charts like most places. We wanted to add grouping, metadata, and neatly be able to extend/add information with or without constraints. This was much before I knew anything database related, and Access seemed to be more powerful than Excel. Having generated forms to fit the data model was much more user friendly and a lot less error prone than adding a new row to an Excel sheet. People criticize Access for being a dumb database or a Excel with too much heavy lifting. It occupies a specific space as a DB on rails.
- gbrown 5y ago
  I was mostly being glib, but I've had some bad experiences with Access over the years. I actually think lightweight database and scripting utilities in Excel could be good, but it would be susceptible to some of the design traps that Access steps on.
  - infogulch 5y ago
    What are the biggest design traps that Access fell into? If someone were rebuilding spreadsheets with more structured design like Access, what should they avoid?

hnlmorg 5y ago

> For example, Excel would be a lot more usable and maintainable for me if there was a way to make a special "data sheet" in which data types are forced to be consistent within columns and there was a concept of column names. You can already do both of those in Excel: - data types: https://support.microsoft.com/en-gb/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249 https://support.microsoft.com/en-gb/office/apply-data-valida... (though you'd also need to lock the spreadsheet as otherwise someone could remove the validation on the cells) - column naming: https://smallbusiness.chron.com/give-name-columns-excel-73447.html https://smallbusiness.chron.com/give-name-columns-excel-7344... (you can name individual cells and ranges too. Which means you can actually start to write formulas that look more like C with name variables, very loosely speaking of course) There's a surprising amount of hidden functionally in Excel. Personally I think that while ribbon bar might have made core features a lot easier for some, it's made a lot of the more advanced tools harder to discover.

asdff 5y ago

There is so much excel voodoo involved to do things with that software. I still believe that the learning curve of excel is no harder than the learning curve of doing the same exact thing in R or python, plus you'd end up having the data and the formulas in different places which brings loads of benefits (for instance, git). People are just familiar with excel because thats what they used to make a chart in science class in 7th grade since 1995, but they really could have learned to make the same chart in 7th grade with a language like python too, if it were only taught python instead of excel in school. And then we'd have a generation of workers fluent in a language like python rather than fluent in the very limited use case by comparison Excelese, and we would no doubt reap the benefits in our GDP. Its like we are limiting the knowledge of fire among our tribe when we don't really have to, it's perfectly learnable.

antipaul 5y ago
I dunno, any programming seems a different beast than a spreadsheet tool. For python, just think of installing it or setting up virtual environments. Like the original article says, “spreadsheets are the original low code”
- asdff 5y ago
  Python is preinstalled on macs at least, that's a decent chunk of personal computers. You have to pay for excel. And you don't need to bother with virtual environments to fiddle with a flat file.
  - bnj 5y ago
    I think an important part of the battle for attention is that excel is visible as a GUI that inexperienced users can open from an icon and see and manipulate. A simple GUI utility with Python input in one pane and data input in the other might put them on more equal footing.
watertom 5y ago
Return on investment, essentially a zero barrier to entry, and immediate gratification, and it's wildly flexible. It takes about 10 minutes to learn the basics a spreadsheet, and what you get back is immense. You couldn't get python up in running in 10 minutes, then you need to learn the language, the syntax, and how to structure a program, frankly I'm tired just starting to type out what needs to happen even before you can do *ANYTHING*. Some people can get YEARS of productivity from those first minutes with a spreadsheet. The return on that initial 10 minute investment justifies spending more time to learn the more esoterica aspects of a spreadsheet, but even then, most people don't want to be bothered with learning how to "program". Why do so many people start playing guitar/piano/drums/etc, but so few finish? Because it requires a significant up front investment, no immediate gratification, and a long, slow return on that significant up front investment, learning music is somewhat flexible, but you need to be highly skilled in order to exploit the flexibility.

jbverschoor 5y ago

Data tables: https://youtu.be/0nbkaYsR94c?t=2268 https://youtu.be/0nbkaYsR94c?t=2268 Although no validation/types

dragonwriter 5y ago
Excel has, IIRC, had data validation longer than it has had tables (even if you count the time before Excel 2007 when tables were called lists),and its had both (with the same inclusion) for >20 years.