4 ms·

So, from my previous career working with spreadsheets, I've noticed that most aberrant behaviors in Excel are easily circumvented through slightly-above average

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.
  - dspillett 6y ago
    Unfortunately, people working for our clients are not trained to do this. Or are but take the quicker option and hope that it works.

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.
- dspillett 6y ago
  Unfortunately, people working for our clients are not trained to do this. Or are but take the quicker option and hope that it works.