5 ms·

My problem with Excel isn't the principle of spreadsheet software, but that it's bad spreadsheet software. It has a bad expression language and a bad API and b

by Pxtl 6y ago

My problem with Excel isn't the principle of spreadsheet software, but that it's bad spreadsheet software. It has a bad expression language and a bad API and bad type-safety and bad automatic type coercions and bad range behavior and bad copy-paste functionality... There are so many boneheaded things wrong with Excel that they can't fix because they must stay bug-compatible. So I keep dealing with text getting mangled into dates and iso8601 dates getting mangled into God knows what and papers getting examined and people finding out "oops your data got screwed up by some counterintuitive behavior of excel and your findings are all wrong".

ineedasername 6y ago
It's one of those things where it's bad, but it's the best we have, especially given that in some of the flaws you mention, they're pretty much copied by alternatives who want to be compatible with Excel.
JumpCrisscross 6y ago
> that they can't fix because they must stay bug-compatible They likely aren’t bugs. Some may have been. But they’re now expected behaviour. Moreover, by and large, they’re how non-programmers see the world.
toss1 6y ago
Do you know of one that does it better in terms of flexibility, data management, programmability, etc.?
Akronymus 6y ago
Not just automatic type coercion. But automatic DESTRUCTIVE type coercion.
netflixandkill 6y ago
Comically with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty, yet seem to have made no effort towards anything like that. I still see people getting date and numeric fields mangled by excel on analysis processes that take literal days to solve because it ends up recursing over all the cells multiple times, and yet even showing them a simple Python or whatever program that does the same thing in minutes, and the result is still put in excel, the next time they need something similar ... Here's another excel abomination. These aren't dumb people. They've been trained by Excel that the world is awful, slow, and error prone.
- ineedasername 6y ago
  with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty I think that's pretty much what they were going for with MS Access, it seems like Access hasn't matured nearly as much as its long life should have allowed. Possibly because it would eat too much into SQL Server. They have to keep its feature footprint too small to appeal to users who only barely need something like SQL Server. As for Excel == slow, yeah-- I actually do like Excel, it's in my toolbox, but waiting 5 minutes for a vlookup against a few hundred thousand records to complete because I need one single column in another sheet is maddening when it would take nearly no time at all if I loaded the data into Python -> Pandas.
  - Closi 6y ago
    Access hasn’t matured because the “easy to make database and app” concept has moved to a focus on PowerApps / Sharepoint. It wasn’t/isn’t a strategic platform and isn’t designed for data analysis. For the sort of stuff Access does you should do it within Excel, which now has the ability to build a fully relational dataset using PowerQuery and PowerPivot. But yeah, excel can be slow! It’s not designed for big datasets without PQ but inevitably gets used for it because it’s easy and available. I can still do a lookup in excel quicker than I can write one in pandas even if it takes 5 mins. As a hint, the new XLookup strictly dominates Vlookup and depending on how the data is structured/sorted can be many times faster according to the search parameters.
  - jamses 6y ago
    I suspect your example is only for context, but just in case it saves you time in future, if you can sort the data first then you can use something like: if(vlookup(value,range,1,TRUE)=value, vlookup(value,range,column,TRUE), error_marker). The TRUE returns next nearest match, the IF ensures you have an exact match, and it takes a few seconds to lookup over hundreds of thousands of rows.
  - chrisdone 6y ago
    > but waiting 5 minutes for a vlookup against a few hundred thousand records to complete because I need one single column in another sheet is maddening when it would take nearly no time at all if I loaded the data into Python -> Pandas. I replied elsewhere in the thread about me working on a new spreadsheet alternative that’s like a reactive Haskell. (Attempting to preserve community norms by not self-promoting more than once per submission.) One thing that I’m planning is that for any table/array/tree etc. that exceeds a certain size, the data itself and any outputs of functions that work on that data will be offloaded into a real database as rows which can be indexed. Either via SQLite, using PostgreSQL, or even BigQuery (but I don’t trust Google much), this would let users transparently grow their data sets from 1,000 rows to 100,000 to 1,000,000 rows without having to suddenly switch languages or representations. (As an online product this is easier to do, but I think a desktop app equivalent could do it too.) Array functions would in the end be streaming functions rather than literally loading all data in memory. In Inflex tables are literally arrays of records, but as the language is purely functional and statically typed, the compiler has a lot of freedom to rewrite code into more efficient representations (e.g. streaming), as done in Haskell or SQL.
formercoder 6y ago
What’s wrong with its copy paste? It’s missing a few things but what’s there is great. The type situation is terrible I agree. I can’t even count the number of times I’ve screwed up a currency conversion or not known what currency a cell was. Why can’t it know that dollars are dollars and when I multiple my euros per dollar I get euros?
- ineedasername 6y ago
  Copy/paste can be really weird. Take these three scenarios: 1) Copy some cells. Type something into another cell before pasting it. Your copy buffer is gone, you can't paste what you copied. 2) You copied some cells, pasted them. You arrow over a few cells to inspect a formula and then hit enter: You just pasted over the surrounding area with whatever you've copied previously. 3) You copied some cells, you pasted them somewhere. Now you want to insert a new column, but when you right click the option is missing, replaced with "insert copied cells". The clear the copy buffer you go to random empty cell, type a space to clear the buffer (see #1 above) and now you can insert a new column. On the plus side though, copy/pasting formatting is pretty useful, so is transpose.
  - phonon 6y ago
    https://web.archive.org/web/20160725070440/http://discuss.fogcreek.com/newyork/default.asp?cmd=show&ixPost=3479 https://web.archive.org/web/20160725070440/http://discuss.fo...
    - ineedasername 6y ago
      I don't accept that explanation as sufficient considering that Google Sheets handles this just fine.
  - beowulfey 6y ago
    I think you can also clear the copy buffer instantly by pressing Esc.
    - 6y ago
Lammy 6y ago
Case in point, one of my favorite Excel Things: https://i.imgur.com/eVbcpGn.png https://i.imgur.com/eVbcpGn.png
- globular-toast 6y ago
  That's fantastic! Thanks for sharing!
- singhrac 6y ago
  Actually, the researcher who works on this is a pioneer in the field of “example based programming”, which is pretty cool stuff. But it does occasionally produce garbage when given garbage (what did you expect February to map to?). In the space of simple programs that map Jan to Janet Jackson, that one doesn’t seem too bad!
- atombender 6y ago
  What's that feature called?
spreadsheetuser 6y ago
So, from my previous career working with spreadsheets, I've noticed that most aberrant behaviors in Excel are easily circumvented through slightly-above average features the average user just isn't aware of. It's been a while, but for example name-mangling can be circumvented by prefacing an entry with a single quote (analogous to how the r character raw string marker is used in Python). You can probably also change the automatic type coercion, buried somewhere in the settings, since adding that single quote prior to the string mangling might be difficult in Excel. (But data entry is often manually done anyways with these kinds of setups.) Usually in cases like these, the problem is that the user isn't aware of the feature they need to solve the problem they're having. (Granted, Excel's whole appeal is easy onboarding for non-technical users.) Save for obvious limitations like data size or overly intricate business logic better suited for an actual language, Excel is well-suited for a broad range of business and academic use cases.
- dspillett 6y ago
  > You can probably also change the automatic type coercion Maybe for manual entry, but not when importing a foreign format like one if the many CSV variants that we run into. > buried somewhere in the settings This is a huge problem, as where such settings exist they are by their very nature user-local. Back to our old frenemy CSV for one (sadly not hypothetical) example: too many times our clients shuffle data (exported from various places) around in text format and someone along the chain has the wrong locale set so when ISO8601 dates get silently converted it is to a format the next users version isn't expecting but silently assumes is correct because by some "miracle" the ambiguous day/month number combination is "valid" both ways around. The result is then imported into our system which is blamed for its answer not making sense...
  - phonon 6y ago
    > Maybe for manual entry, but not when importing a foreign format like one if the many CSV variants that we run into. Use Get and Transform Data -> CSV You can control the typing per column when you import, split columns, etc.
ripley12 6y ago
FWIW, fixes/improvements for some of those fundamental issues are coming and/or available in preview releases. Like new+custom data types: https://www.microsoft.com/en-us/microsoft-365/blog/2020/10/29/connect-to-your-own-data-with-more-new-data-types-in-excel/ https://www.microsoft.com/en-us/microsoft-365/blog/2020/10/2... And LAMBDA for custom functions: 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 get the impression that in recent years Microsoft's become more willing to make fundamental changes to Excel. Still early days but I'm cautiously optimistic that things are getting better.
- dspillett 6y ago
  Oh my [deity], what are they creating?! I first read those thinking "cool, I'll have to let nerdy me have a play with all that". I actually often use Excel for various bits & bobs, and must (as a database guy and ex-Jack-of-all dev) shamefully admit to finding it useful and sometimes really liking it. But... Then my mind moved on to "oh hell, what holes are our clients going to dig for themselves if they catch wind of some of this?". Even when talking about existing functionality that you might want to use along with these new tools they say "It took us some time to understand the precise rules that drive this behavior". It took Microsoft's research people some time to understand the ins and outs of features in Microsoft Excel and they talk about this with apparent pride in a paper published by Microsoft. I look forward to being witness to the minting of brand new expletives, when the reporting team are handed a gargantuan KPI calculations workbook that uses these tools, and asked to reproduce it in our environment (but make it work properly as "there are a few bits that occasionally give odd results and have to be adjusted manually sometimes" - not that they'll mention those oddities until we've banged or head against them a couple of times or in UAT they ask "but how do we tweak the financial sprocket count result for dept X only, when Y happens?"), which they assume we can do that in practically zero time...
chrisdone 6y ago
I’m working on a (very beta) product that hopes to address a subset of your complaints about spreadsheets. https://inflex.io/ https://inflex.io/ Based on your phrasing you might have already heard of it. In particular my two blog posts mirror what you said: https://inflex.io/blog https://inflex.io/blog I haven’t added a date type yet, but it will be a distinct type (not just a string), with explicit import. Pretty much similar to the Haskell time API. If you like the idea I would welcome any criticism. I have 50 things that I think the product needs, but your input would help.
- sdeframond 6y ago
  I really like your approach. I will give it a try!

by Pxtl 6y ago

ineedasername 6y ago

It's one of those things where it's bad, but it's the best we have, especially given that in some of the flaws you mention, they're pretty much copied by alternatives who want to be compatible with Excel.

JumpCrisscross 6y ago

> that they can't fix because they must stay bug-compatible They likely aren’t bugs. Some may have been. But they’re now expected behaviour. Moreover, by and large, they’re how non-programmers see the world.

toss1 6y ago

Do you know of one that does it better in terms of flexibility, data management, programmability, etc.?

Akronymus 6y ago

Not just automatic type coercion. But automatic DESTRUCTIVE type coercion.

netflixandkill 6y ago

Comically with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty, yet seem to have made no effort towards anything like that. I still see people getting date and numeric fields mangled by excel on analysis processes that take literal days to solve because it ends up recursing over all the cells multiple times, and yet even showing them a simple Python or whatever program that does the same thing in minutes, and the result is still put in excel, the next time they need something similar ... Here's another excel abomination. These aren't dumb people. They've been trained by Excel that the world is awful, slow, and error prone.

ineedasername 6y ago
with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty I think that's pretty much what they were going for with MS Access, it seems like Access hasn't matured nearly as much as its long life should have allowed. Possibly because it would eat too much into SQL Server. They have to keep its feature footprint too small to appeal to users who only barely need something like SQL Server. As for Excel == slow, yeah-- I actually do like Excel, it's in my toolbox, but waiting 5 minutes for a vlookup against a few hundred thousand records to complete because I need one single column in another sheet is maddening when it would take nearly no time at all if I loaded the data into Python -> Pandas.
- Closi 6y ago
  Access hasn’t matured because the “easy to make database and app” concept has moved to a focus on PowerApps / Sharepoint. It wasn’t/isn’t a strategic platform and isn’t designed for data analysis. For the sort of stuff Access does you should do it within Excel, which now has the ability to build a fully relational dataset using PowerQuery and PowerPivot. But yeah, excel can be slow! It’s not designed for big datasets without PQ but inevitably gets used for it because it’s easy and available. I can still do a lookup in excel quicker than I can write one in pandas even if it takes 5 mins. As a hint, the new XLookup strictly dominates Vlookup and depending on how the data is structured/sorted can be many times faster according to the search parameters.
- jamses 6y ago
  I suspect your example is only for context, but just in case it saves you time in future, if you can sort the data first then you can use something like: if(vlookup(value,range,1,TRUE)=value, vlookup(value,range,column,TRUE), error_marker). The TRUE returns next nearest match, the IF ensures you have an exact match, and it takes a few seconds to lookup over hundreds of thousands of rows.
- chrisdone 6y ago
  > but waiting 5 minutes for a vlookup against a few hundred thousand records to complete because I need one single column in another sheet is maddening when it would take nearly no time at all if I loaded the data into Python -> Pandas. I replied elsewhere in the thread about me working on a new spreadsheet alternative that’s like a reactive Haskell. (Attempting to preserve community norms by not self-promoting more than once per submission.) One thing that I’m planning is that for any table/array/tree etc. that exceeds a certain size, the data itself and any outputs of functions that work on that data will be offloaded into a real database as rows which can be indexed. Either via SQLite, using PostgreSQL, or even BigQuery (but I don’t trust Google much), this would let users transparently grow their data sets from 1,000 rows to 100,000 to 1,000,000 rows without having to suddenly switch languages or representations. (As an online product this is easier to do, but I think a desktop app equivalent could do it too.) Array functions would in the end be streaming functions rather than literally loading all data in memory. In Inflex tables are literally arrays of records, but as the language is purely functional and statically typed, the compiler has a lot of freedom to rewrite code into more efficient representations (e.g. streaming), as done in Haskell or SQL.

formercoder 6y ago

What’s wrong with its copy paste? It’s missing a few things but what’s there is great. The type situation is terrible I agree. I can’t even count the number of times I’ve screwed up a currency conversion or not known what currency a cell was. Why can’t it know that dollars are dollars and when I multiple my euros per dollar I get euros?

ineedasername 6y ago
Copy/paste can be really weird. Take these three scenarios: 1) Copy some cells. Type something into another cell before pasting it. Your copy buffer is gone, you can't paste what you copied. 2) You copied some cells, pasted them. You arrow over a few cells to inspect a formula and then hit enter: You just pasted over the surrounding area with whatever you've copied previously. 3) You copied some cells, you pasted them somewhere. Now you want to insert a new column, but when you right click the option is missing, replaced with "insert copied cells". The clear the copy buffer you go to random empty cell, type a space to clear the buffer (see #1 above) and now you can insert a new column. On the plus side though, copy/pasting formatting is pretty useful, so is transpose.
- phonon 6y ago
  https://web.archive.org/web/20160725070440/http://discuss.fogcreek.com/newyork/default.asp?cmd=show&ixPost=3479 https://web.archive.org/web/20160725070440/http://discuss.fo...
  - ineedasername 6y ago
    I don't accept that explanation as sufficient considering that Google Sheets handles this just fine.
- beowulfey 6y ago
  I think you can also clear the copy buffer instantly by pressing Esc.
  - 6y ago

Lammy 6y ago

Case in point, one of my favorite Excel Things: https://i.imgur.com/eVbcpGn.png https://i.imgur.com/eVbcpGn.png

globular-toast 6y ago
That's fantastic! Thanks for sharing!
singhrac 6y ago
Actually, the researcher who works on this is a pioneer in the field of “example based programming”, which is pretty cool stuff. But it does occasionally produce garbage when given garbage (what did you expect February to map to?). In the space of simple programs that map Jan to Janet Jackson, that one doesn’t seem too bad!
atombender 6y ago
What's that feature called?

spreadsheetuser 6y ago

So, from my previous career working with spreadsheets, I've noticed that most aberrant behaviors in Excel are easily circumvented through slightly-above average features the average user just isn't aware of. It's been a while, but for example name-mangling can be circumvented by prefacing an entry with a single quote (analogous to how the r character raw string marker is used in Python). You can probably also change the automatic type coercion, buried somewhere in the settings, since adding that single quote prior to the string mangling might be difficult in Excel. (But data entry is often manually done anyways with these kinds of setups.) Usually in cases like these, the problem is that the user isn't aware of the feature they need to solve the problem they're having. (Granted, Excel's whole appeal is easy onboarding for non-technical users.) Save for obvious limitations like data size or overly intricate business logic better suited for an actual language, Excel is well-suited for a broad range of business and academic use cases.

dspillett 6y ago
> You can probably also change the automatic type coercion Maybe for manual entry, but not when importing a foreign format like one if the many CSV variants that we run into. > buried somewhere in the settings This is a huge problem, as where such settings exist they are by their very nature user-local. Back to our old frenemy CSV for one (sadly not hypothetical) example: too many times our clients shuffle data (exported from various places) around in text format and someone along the chain has the wrong locale set so when ISO8601 dates get silently converted it is to a format the next users version isn't expecting but silently assumes is correct because by some "miracle" the ambiguous day/month number combination is "valid" both ways around. The result is then imported into our system which is blamed for its answer not making sense...
- phonon 6y ago
  > Maybe for manual entry, but not when importing a foreign format like one if the many CSV variants that we run into. Use Get and Transform Data -> CSV You can control the typing per column when you import, split columns, etc.

ripley12 6y ago

FWIW, fixes/improvements for some of those fundamental issues are coming and/or available in preview releases. Like new+custom data types: https://www.microsoft.com/en-us/microsoft-365/blog/2020/10/29/connect-to-your-own-data-with-more-new-data-types-in-excel/ https://www.microsoft.com/en-us/microsoft-365/blog/2020/10/2... And LAMBDA for custom functions: 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 get the impression that in recent years Microsoft's become more willing to make fundamental changes to Excel. Still early days but I'm cautiously optimistic that things are getting better.

dspillett 6y ago
Oh my [deity], what are they creating?! I first read those thinking "cool, I'll have to let nerdy me have a play with all that". I actually often use Excel for various bits & bobs, and must (as a database guy and ex-Jack-of-all dev) shamefully admit to finding it useful and sometimes really liking it. But... Then my mind moved on to "oh hell, what holes are our clients going to dig for themselves if they catch wind of some of this?". Even when talking about existing functionality that you might want to use along with these new tools they say "It took us some time to understand the precise rules that drive this behavior". It took Microsoft's research people some time to understand the ins and outs of features in Microsoft Excel and they talk about this with apparent pride in a paper published by Microsoft. I look forward to being witness to the minting of brand new expletives, when the reporting team are handed a gargantuan KPI calculations workbook that uses these tools, and asked to reproduce it in our environment (but make it work properly as "there are a few bits that occasionally give odd results and have to be adjusted manually sometimes" - not that they'll mention those oddities until we've banged or head against them a couple of times or in UAT they ask "but how do we tweak the financial sprocket count result for dept X only, when Y happens?"), which they assume we can do that in practically zero time...

chrisdone 6y ago

I’m working on a (very beta) product that hopes to address a subset of your complaints about spreadsheets. https://inflex.io/ https://inflex.io/ Based on your phrasing you might have already heard of it. In particular my two blog posts mirror what you said: https://inflex.io/blog https://inflex.io/blog I haven’t added a date type yet, but it will be a distinct type (not just a string), with explicit import. Pretty much similar to the Haskell time API. If you like the idea I would welcome any criticism. I have 50 things that I think the product needs, but your input would help.

sdeframond 6y ago
I really like your approach. I will give it a try!