Excel is a local maxima, which sucks because it’s not good enough. Ordinary people can use Excel, which is great, but then the date type is actively harmful, which is insane. It mangles zipcodes in spite of it having been made by a US corporation for its whole existence! Like, I get it, sometimes you mangle foreign conventions due to unfamiliarity, but all of New England has its zipcodes mangled. That’s bad! And then because Excel is a local maxima, new products like Numbers and Sheets clone it instead of searching for a new maxima. It’s a pity because we can definitely do better.
I’ll take a stab at some things I think would make it much better for many of its uses:
Allow clearly separated display and editing views*: the current system involves hiding cells or referencing cells outside of the current view which I think could be handled better by having separate display and editing views. You can basically do this now but it’s really cumbersome.
A mechanism for running commands on sets of data and either replacing it in place or inserting the results somewhere: the current idiom is to write a bunch of formulas, copy, and the paste as values, which is horribly broken.
Better delineation of tables within Excel which would also provide better guard rails against things like sorting a single column when you meant to sort a whole table.
Better handling for non-numeric data, this is touched upon in your complaint about zipcodes - Excel presumes that everything is fundamentally a number which I think it inherits from primarily financial spreadsheet programs.
Better tools for creating custom data types - related to the above.
Fundamentally, I’d say that my complaint about it is that Excel really, really wants to be a financial spreadsheet program when many (perhaps most) users want it to be a tool for working with generic tabular data.
* More broadly, I’d say this and several of my other points boil down to “Excel doesn’t do a great job at allowing the user to control complexity” and this becomes more clear as the volume of data manipulated in it increases. I think that’s one of the differences between it and, say, visidata. I prefer visdata for my own use but the reality is that many of the people I interact with simply won’t learn to use tools such as it rather than Excel (for a variety of reasons, some of which are fair).
A mechanism for running commands on sets of data and either replacing it in place or inserting the results somewhere: the current idiom is to write a bunch of formulas, copy, and the paste as values, which is horribly broken.
The powerquery feature is really good for this, though its transformation dsl is weaker than excel formulates.
Better tools for creating custom data types - related to the above.
I’m hoping the new “linked data types” will help with this! Right now you can make custom data types but it’s pretty cumbersome. It’s pretty new though and I think they’re still improving it.
Not disagreeing at all, but curious what you think some of the big changes would be if a great designer did a total rethink, but incorporating the good stuff that works?
Take a look at Quantrix Modeller. Lotus had two spreadsheet products:
123, which was a VisiCalc clone. It used a rectangular grid because they thought that it would appeal to accountants.
Improv, which had a clean separation of data and formulae and used pivot tables as its core data type. You’d define a new column as a single formula, rather than copying and pasting. This is the one that accountants actually liked.
Excel is a 123 clone, as are most other spreadsheets. Quantrix Modeller is, as far as I know, the only surviving Improv clone. They have some great videos about why this model is better. It’s less error-prone, easier to change, and so on.
When most people say that they want a spreadsheet, what they actually want is a database with a rich set of numerical library routines.
I guess there are niches where you can still get away with this, but it’s become (even in the enterprise space, where it used to rule) less common to the degree that I often see products without at least some general pricing guidance published get pushed to the bottom of procurement lists simply because no one can be faffed to talk to a bunch of sales people to get quotes just to do a comparison.
I’m not imagining a big redesign. Just basic stuff: add types for timestamps, civil times, durations, locations, currency. Have a difference between something’s type and its display. Fix the 1900 leap year bug FFS. Default to not clobbering the next cell when something doesn’t fit. You could still have freeform A1 cells but you should push users towards using it like a database with proper rows and columns as much as you can. Design the app as though it were the most commonly used tool in business and science analysis and not whatever Dan Bricklin happened to think of in 1979.
It’s a funny bit of program synthesis from popl ’11. The real content is in the search algo, but the general idea is to search for programs in a string manipulation DSL and use one that applies.
Excel reminds me a lot of when I hear about bespoke programming languages that only have global variables, or that one architecture that had a fixed size stack of up to 10 characters. There’s so much power and things going on and potential but the base idea of “all of this goes on an infinite grid” is soooooooo bizarre in this day and age to me.
Like OK I can go to a name manager(?) and name physical areas in my infinite grid (??) to store values that are also presentation (???).
This reminds me of Rail, an esoteric language where you are literally drawing control flow in a 2 dimensional space to move calculation around. To me, Excel’s model feels just as silly. Just a big 2d array.
Meanwhile everyone tries to remake Excel because its so big that we all seem to miss the fact that when people use Excel they have to work around the fact that you’re on a global grid (yes I know about worksheets). Meanwhile there is a potential MVP that just … that just introduces the idea of storing values in names and referring to things that way.
We have the technology! We somehow got everyone to learn the box model with CSS! Why are we not making spreadsheet programs at a bit of a higher level of abstraction?
(I think it’s good and cool to learn about how to use Excel, just want a version that knows about scoping or something)
Wow, I wish I had watched this a long time ago. I’ve done my taxes in Google Sheets for 3 years and using named ranges would have made it so much easier. Some of the things he claims don’t work in Sheets seem to have been improved in the 7 years since the talk, but unfortunately looks like there’s still no proper table support.
Microsoft has official tutorial sheets. I’d recommend going through the one on formula, then after that walk through the ribbon and the list of formula and then look any buttons or formula that seem interesting. That’s what I did!
The author rags on Google Sheets for not supporting these things, but no mention is made of LibreOffice’s Calc which does have at least most of the things mentioned. For example documentation for Define Names covers the first tidbit of the article.
Another favorite feature of mine is VLOOKUP, which is essentially a SQL JOIN for your spreadsheet. Pivot tables can be used for data aggregation, so if you combine them you’ve pretty much got yourself a relational database.
I switched from python scripts for ad hoc ans throw away scripts as my first reflex to try to solve those situations at work with GSheet or Excel. Most of the the time I have to match and validate some datasets on an ad hic basis. GSheet is enough 80% of the time when there is no big issue that a bit of regexreplace, vlookup and some magic sprinkles around. Python is now for when datasets are really messy and I have to begin to seriously modify them or begin to do fuzzy matching, etc. I also saw my partner cutting down a manual painful process from two weeks to three days by learning more Excel and power query and etc without no programming background and no interest to learn programming neither.
I think power query interface and some other tools is something to explore as a way to bring more programming power in small bits besides the no code trends etc. There is a sweet spot in the tabular form that seems to make it easier to abstract for people with no programming experience. Reading a lot about APL/J/K and seeing the same kind of parralel than the author is making reflects on how those approaches could be fully merged together.
It doesn’t mention pivot tables! Pivot tables let you do a lot of the statistical calculations you could do with R or Pandas or SQL, but in a graphical way and much more quickly. Surprisingly, as far as I know, no other spreadsheet has them even though they’re really a killer feature.
LibreOffice Calc has them too. Others have noted Google Sheets and Apple Numbers. What other speadsheets were you thinking of exactly? Maybe before being “surprised” that no other software except the thing you use has something at least do a modicum of research to see if it just your knowledge that is lacking or really every all the other software.
Maybe before being “surprised” that no other software except the thing you use has something at least do a modicum of research to see if it just your knowledge that is lacking or really every all the other software
VisiCalc’s reign might be over.
https://youtu.be/ICp2-EUKQAI
Excel is a local maxima, which sucks because it’s not good enough. Ordinary people can use Excel, which is great, but then the date type is actively harmful, which is insane. It mangles zipcodes in spite of it having been made by a US corporation for its whole existence! Like, I get it, sometimes you mangle foreign conventions due to unfamiliarity, but all of New England has its zipcodes mangled. That’s bad! And then because Excel is a local maxima, new products like Numbers and Sheets clone it instead of searching for a new maxima. It’s a pity because we can definitely do better.
I’ll take a stab at some things I think would make it much better for many of its uses:
Fundamentally, I’d say that my complaint about it is that Excel really, really wants to be a financial spreadsheet program when many (perhaps most) users want it to be a tool for working with generic tabular data.
* More broadly, I’d say this and several of my other points boil down to “Excel doesn’t do a great job at allowing the user to control complexity” and this becomes more clear as the volume of data manipulated in it increases. I think that’s one of the differences between it and, say, visidata. I prefer visdata for my own use but the reality is that many of the people I interact with simply won’t learn to use tools such as it rather than Excel (for a variety of reasons, some of which are fair).
The powerquery feature is really good for this, though its transformation dsl is weaker than excel formulates.
I’m hoping the new “linked data types” will help with this! Right now you can make custom data types but it’s pretty cumbersome. It’s pretty new though and I think they’re still improving it.
Maximum. ‘Maxima’ is the plural🙂
Not disagreeing at all, but curious what you think some of the big changes would be if a great designer did a total rethink, but incorporating the good stuff that works?
Take a look at Quantrix Modeller. Lotus had two spreadsheet products:
Excel is a 123 clone, as are most other spreadsheets. Quantrix Modeller is, as far as I know, the only surviving Improv clone. They have some great videos about why this model is better. It’s less error-prone, easier to change, and so on.
When most people say that they want a spreadsheet, what they actually want is a database with a rich set of numerical library routines.
No thanks.
I guess there are niches where you can still get away with this, but it’s become (even in the enterprise space, where it used to rule) less common to the degree that I often see products without at least some general pricing guidance published get pushed to the bottom of procurement lists simply because no one can be faffed to talk to a bunch of sales people to get quotes just to do a comparison.
Quantrix Modeller sales demo video
Hear, hear!
I’m not imagining a big redesign. Just basic stuff: add types for timestamps, civil times, durations, locations, currency. Have a difference between something’s type and its display. Fix the 1900 leap year bug FFS. Default to not clobbering the next cell when something doesn’t fit. You could still have freeform A1 cells but you should push users towards using it like a database with proper rows and columns as much as you can. Design the app as though it were the most commonly used tool in business and science analysis and not whatever Dan Bricklin happened to think of in 1979.
I am confused when you say it is a local maxima yet think small improvements would fix it - that is a contradiction.
That flash fill example is wild. Given:
"(abc)", "[def]" → "(df) [ac]""{ghi}", "(jkl)" → "{jl} (gi)"Excel can autofill
"[mno]", "<pqr>" → "[pr] <mo>". How would you implement that kind of pattern recognition?It’s a funny bit of program synthesis from popl ’11. The real content is in the search algo, but the general idea is to search for programs in a string manipulation DSL and use one that applies.
That’s really neat! Thanks for the link. (If it’s not been submitted to Lobsters already, you should post it. :) )
Excel reminds me a lot of when I hear about bespoke programming languages that only have global variables, or that one architecture that had a fixed size stack of up to 10 characters. There’s so much power and things going on and potential but the base idea of “all of this goes on an infinite grid” is soooooooo bizarre in this day and age to me.
Like OK I can go to a name manager(?) and name physical areas in my infinite grid (??) to store values that are also presentation (???).
This reminds me of Rail, an esoteric language where you are literally drawing control flow in a 2 dimensional space to move calculation around. To me, Excel’s model feels just as silly. Just a big 2d array.
Meanwhile everyone tries to remake Excel because its so big that we all seem to miss the fact that when people use Excel they have to work around the fact that you’re on a global grid (yes I know about worksheets). Meanwhile there is a potential MVP that just … that just introduces the idea of storing values in names and referring to things that way.
We have the technology! We somehow got everyone to learn the box model with CSS! Why are we not making spreadsheet programs at a bit of a higher level of abstraction?
(I think it’s good and cool to learn about how to use Excel, just want a version that knows about scoping or something)
Seeing all those videos of people making games in Excel has made me agree: Excel is cool.
@hwayne What are your top recommended resources or practices for quickly learning Excel?
You Suck at Excel by Joel Spolsky is polarizing, but I thought it was hilarious.
Wow, I wish I had watched this a long time ago. I’ve done my taxes in Google Sheets for 3 years and using named ranges would have made it so much easier. Some of the things he claims don’t work in Sheets seem to have been improved in the 7 years since the talk, but unfortunately looks like there’s still no proper table support.
Microsoft has official tutorial sheets. I’d recommend going through the one on formula, then after that walk through the ribbon and the list of formula and then look any buttons or formula that seem interesting. That’s what I did!
The author rags on Google Sheets for not supporting these things, but no mention is made of LibreOffice’s Calc which does have at least most of the things mentioned. For example documentation for Define Names covers the first tidbit of the article.
I’ve never used Calc or whatever the MacOS office spreadsheet is.
Another favorite feature of mine is VLOOKUP, which is essentially a SQL JOIN for your spreadsheet. Pivot tables can be used for data aggregation, so if you combine them you’ve pretty much got yourself a relational database.
Something I heard just recently was that SPJ worked hard during his time at Microsoft to get functional programming into Excel; you can hear the story in this podcast: https://open.spotify.com/episode/2vJ0blId04xsVmQ8Mrt1bc?si=QcQFIWvCRb-baRP56p2OwA
I was reading a paper on build systems and I chuckled when I got to the statement that Excel was a build system.
https://www.microsoft.com/en-us/research/uploads/prod/2018/03/build-systems.pdf
I switched from python scripts for ad hoc ans throw away scripts as my first reflex to try to solve those situations at work with GSheet or Excel. Most of the the time I have to match and validate some datasets on an ad hic basis. GSheet is enough 80% of the time when there is no big issue that a bit of regexreplace, vlookup and some magic sprinkles around. Python is now for when datasets are really messy and I have to begin to seriously modify them or begin to do fuzzy matching, etc. I also saw my partner cutting down a manual painful process from two weeks to three days by learning more Excel and power query and etc without no programming background and no interest to learn programming neither.
I think power query interface and some other tools is something to explore as a way to bring more programming power in small bits besides the no code trends etc. There is a sweet spot in the tabular form that seems to make it easier to abstract for people with no programming experience. Reading a lot about APL/J/K and seeing the same kind of parralel than the author is making reflects on how those approaches could be fully merged together.
It doesn’t mention pivot tables! Pivot tables let you do a lot of the statistical calculations you could do with R or Pandas or SQL, but in a graphical way and much more quickly. Surprisingly, as far as I know, no other spreadsheet has them even though they’re really a killer feature.
Google Sheets has them, although they keep changing the name. I think it’s called data explorer now?
Apple’s Numbers also have them (not from much time, tbh)
LibreOffice Calc has them too. Others have noted Google Sheets and Apple Numbers. What other speadsheets were you thinking of exactly? Maybe before being “surprised” that no other software except the thing you use has something at least do a modicum of research to see if it just your knowledge that is lacking or really every all the other software.
Wow, rude.