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!
https://www.microsoft.com/en-us/microsoft-365/blog/2012/08/2...
But I’d be fascinated what kind of system spec is required to get good performance on those kind of numbers. I’ve been on a Mac for years, where Excel is crippled by limitations.
But hey, to each their own. Good on ya if Excel tables are what you need.
I usually end up assigning names to nearly everything, single-cell constants, user input fields, computed lists, etc, etc
If anything add a 'ActiveSheet.Calculate' call to the end of your second macro and give it a test run?
What a love hate relationship I have with it.
On the one hand, it is great for doing a quick and dirty analysis with data not in a DB, on the other, it mangles data and translates the keywords/function names.
You can already do both of those in Excel:
- data types: 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-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.
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...
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.
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.
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.
For python, just think of installing it or setting up virtual environments.
Like the original article says, “spreadsheets are the original low code”
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.
Something that combined Airtable, excel, and maybe a more userfriendly (and more restricted) version of darklang for defining formulae could be really slick.
https://youtu.be/0nbkaYsR94c?t=2268
Although no validation/types
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.