you think zip codes are a more common use case than numbers?

These days I'd disagree. Once upon a time explicitly column delimited data with leading 0 padding may have been common, but I suspect in most software written in the last 20+ years leading 0s if included are more likely relevant parts of the data rather than Hollerith card default punches.

Probably it’s just different usage - when I use excel is mostly to store a table of raw data where I want to store the actual data, not what excel thing the data is. I’m using it as the equivalent of a database, but simple to update and share with other people (even not technical folks) or to open a csv and quickly hide/filter some data.

So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`?

That's... not why spreadsheets were invented.

> when I use excel is mostly to store a table of raw data where I want to store the actual data, not what excel thing the data is. I’m using it as the equivalent of a database,

Then use a database program, not a spreadsheet!

However, if there are no database programs that fit your needs, and you end up using a spreadsheet instead, don't complain that the spreadsheet acts like a spreadsheet instead of a database. It's not the tool's fault that you're the one using the wrong tool for the job.

If you didn't change anything else, just made A1 and B1 text cells, then adding them would still return 6912.

> That's... not why spreadsheets were invented.

It's decades later. Why they were invented is not the priority.

> Then use a database program, not a spreadsheet!

Databases only have a fraction of the capabilities of a spreadsheet. That's not a reasonable response.

Edit: found https://retrocomputing.stackexchange.com/questions/20460/wha..., suggests it was ALGOL-68.

The looser your typings the more it matters, and spreadsheets barely even have types.

Alternatively, if you don’t want excel to perform addition on underlying numeric values but instead only on defined data types then the formula could result in NaN which would signal to the user that the explicit data type was not compatible with addition.

I know, complaining like this probably doesn’t make sense. It’s a minor issue to begin with and I’m probably an edge case as far as excel users go. It doesn’t make sense for MS to tailor its product for my type of user.

There’s nothing you can do in a spreadsheet that an RDBMS can’t do.

Programmers often object to spreadsheets because they’re too “brittle”. I don’t think it’s an unreasonable objection.

Most spreadsheet users don’t exhibit the rigor necessary to impose a schema on their spreadsheet-based data, nor to use features like named ranges to abstract away the physical location of data in the spreadsheet from its semantic meaning. Data validation is an afterthought, too. Spreadsheets invite silent errors when rows or columns are inserted or removed, data is copied/pasted, etc. It takes more effort, in my experience, to keep the formulae in a spreadsheet working in the face of modifications versus a database.

A database explicitly separates the data from logic (at least, typically— getting into queries that act conditionally based kn queried data blurs that line a bit) and provides a strong schema and enforced validation at the time data is added or modified. You’re not going to get wrong answers from a database query because somebody added a row at the bottom of a range that isn’t covered by a SUM(), for example.

There's a reason lots of MBA types get issued high-performance, high RAM machines- because asking them to learn SQL or whatever AND asking them to learn how data structures and query syntax work instead of just doing things a little more slowly and throwing more performance at Excel makes a lot of sense.

Not saying it's perfect, but please don't hate on Excel et al because they aren't the perfect world way of doing things.

Using them for business processes is a recipe for sadness. People don’t have the discipline to use them for that, and the tools have too many shortcomings.

In terms of “exploring data” I’ve been in way too many meetings where different users have worked on forks of the same original spreadsheet and come up with contradictory answers.

Re: slowing people down - I’ve had the fortune to show a few “MBA types” how to do JOINs in lieu of the eldritch horrors they’d created attempting to re-implement JOIN with VLOOKUP. The productivity gains were significant. Excel is using a screwdriver to drive nails in some very glaring cases.

> In terms of “exploring data” I’ve been in way too many meetings where different users have worked on forks of the same original spreadsheet and come up with contradictory answers.

You shouldn't use a notepad of SQL statements for processes either!

Once certain things are figured out and planned around for repeated use, they need to be transformed into a proper maintainable form, probably involving careful code with error detection. But that's not a spreadsheet vs. SQL issue. If SQL causes fewer problems it's probably because someone that knows SQL is more likely to have programming knowledge/discipline, not something about SQL itself.

> Re: slowing people down - I’ve had the fortune to show a few “MBA types” how to do JOINs in lieu of the eldritch horrors they’d created attempting to re-implement JOIN with VLOOKUP. The productivity gains were significant. Excel is using a screwdriver to drive nails in some very glaring cases.

It's great to use a JOIN when it's appropriate. But a lot of the time there's still no real need for a database, and you want all the other tools a spreadsheet has. Or there is a good case for a database, but the best answer is having some queries that feed the spreadsheet.

The French government believed that Covid cases had stopped increasing, because their federated Excel spreadsheet was silently truncating data at 1 million rows. Accidents like this happen all the time.

Access should be based on Excel, only adding sheet types: single-record forms, SQL reports, pivot tables.

In fact, pivot tables are the proof that Excel is a database tool.

WTF? Are you saying Toyota don't normally put seatbelts in their cars? Like... what???

If they were text, I'd expect it to end up as `12345678`, personally. + being reasonably commonly overloaded to mean string concatenation.

It was created to count money. So it would make sense to defaults field to number unless it obviously isn't.

People are just using a tool that isn't designed for your current purpose.

I think probably the best change it can do is don't alter the data at input. Instead, alter it at display and compute. Then it won't actually corrupt your data anymore.

Then for a wrong field type, you just need to change the type, and the correct data will be displayed as expected.

Zip codes are not a weird edge case either.

If I type 000012 why would you think I wanted 12?

Second, how would you handle international addresses? For many businesses, a program that can't do that would be useless.

What’s painful is when a file has phone numbers and excel helpfully converts them all to scientific notation.