> I cannot remember the last time I had some long number and thought to myself, "You know what will be helpful, convert this number into scientific notation!"...
Okay, so set formats appropriate to what is useful for your data.
Defaults don't cover all cases, otherwise there would be no non-default options.
> It's asinine Excel has this behavior with any large number. Try to work with a list of EAN/UPC codes...
Codes aren’t numbers they are strings, even if the code is a sequence of digits. They should be formatted as text. For actual numbers that shouldn't be displayed in scientific missions, the appropriate numeric format does that.
> it'll wreck every single one every single time
If its any code that can be safely stored as a number, it won't wreck it. It’ll just look bad until its fixed. If it, say, has significant leading zerores, yes, it’ll wreck it, because excel defaults to dealing with numbers, not text, and there is a difference between text made up of digits and actual numbers.
> unless you take significant care to guard against it via formatting, special characters that trick it into using a string, etc
Knowing the shape of data you are entering and selecting a column and applying an appropriate format (including “text” as an option) before you enter any data (usually, to a whole column) isn’t that arduous.
