Excel will allow certain auto data conversions to be turned off
insider.microsoft365.com
insider.microsoft365.com
This has been a massive bugbear of mine. Particularly when it inexplicably chooses USA date formats even when faced with a column containing values like 15-07-75. It would frequently convert half the values into US date format where possible and leave others like above unconverted.
Edit: I wonder, does '1-1' count as a 'continuous string of letters and numbers'? I still don't want '1-1' to be converted to a date.
From what I can tell, these are all opt-in via settings. So it won't stop unaware users from accidentally messing up csvs
Anyone done this? Open a .csv in excel to fix/edit a item, then save it without realizing that it autoformatted a bunch of columns. Now it doesn't work in the parent program anymore.
If someone had told me that out of context I wouldn’t even question it like “Oh, that makes sense.”
[1]: https://www.theguardian.com/politics/2020/oct/05/how-excel-may-have-caused-loss-of-16000-covid-tests-in-england“The problem of Excel software (Microsoft Corp., Redmond, WA, USA) inadvertently converting gene symbols to dates and floating-point numbers was originally described in 2004.”
“RIKEN identifiers were described to be automatically converted to floating point numbers (i.e. from accession ‘2310009E13’ to ‘2.31E+13’).”
Until the day where everyone is computationally savvy and can do their processing in Python/R/Julia, it is the state of the world. As a matter of fact, HUGO agreed to rename some of the worst offender genes to Excel-friendly formats[0].
[0]https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
* NEVER modify the text I type into a cell.
* Parse, interpret, format the cell according to the "data type" that's detected or chosen by the user
* In the grid, display the data according to the interpretation above
* In the formula textbox, show me exactly what I typed in, unmodified.
In your proposal, Excel would need to keep around a) the value of a cell, b) its format, and c) whatever you originally typed in, with c) being potentially more than a)+b), without very much benefit. And remember, Excel was released in 1987 (years before Windows 3.1) on machines that had less storage than they have today. I'd say the design decision made back then was the right one.
This is already kind of the case. If you change the date rendering format, the "real" date text in the cell stays the same. The only issue is that Excel decides what an acceptable "real" value is and modifies my input to match that.
One extremely aggrevating thing it does repeatedly is strip leading 0s off phone numbers (local phone numbers here follow thw format 0XX-XXX-XXXX), we constanly need to work around it by remembering to add a dash in the middle.
I hope anybody who is thinking of implementing a 'computer knows best' feature with ML reflects on how annoying this feature has been over the years.
And someone in the process open the file, and hop excel remove all the zero at he beginning fo the number
I've lost a lot of time from this...
E.g.
1 + "hello" = "1hello"
1 - "hello" = NaN
Clearly excel has offered value far in excess of this annoyance. The competitors such that they existed aside from google sheets clearly fail to deliver sufficient value even in hypothetical absence of this bug.
And isn't LibreOffice Calc closer to Excel than Google Sheets in terms of features?
I guess my point it is: it is not features. It is humans. MS cracked the code on that one. Get people familiar with their stuff and the rest will follow.
> I guess my point it is: it is not features.
Discoverability of commands, customization, as well as the stability of UI are all features. As would be "full UI compatibility with Excel sans stupid bugs like data conversion"
I haven't had Excel installed on my work laptop for the current or previous job. Though if I didn't have access to Sheets and had to use Libre by default, I'd petition for Excel in a second...
Fun.
- https://www.bbc.com/news/technology-54423988
- https://www.researchgate.net/figure/Screen-shot-of-Microsoft...
- https://theconversation.com/excel-autocorrect-errors-still-p...
- etc.
A problem that's been around for years is finally addressed.
This also creates a connection to the CSV file and you can also easily refresh data. With pivot tables this makes it possible to build simple reporting solutions. Just place updated csv files to known location, refresh data and tables.
Also I believe excel for mac either doesn't have this feature or it's not as feature rich as the windows counterpart.
Problem with importing is that laypeople are not familiar with PowerQuery and it can be overwhelming for lot of users.
It didn't make it less shitty. The problem is on the between the keyboard and the chair. If users take the time to be familiar with the wizard and PowerQuery, a lot of miscorrection would be avoid in the first place.
Oh. Now it stopped asking, and just converts everything.
CSV is a data interchange format. When you open and hit "save" on one, unlike Excel's XLS/XLSX format, they cannot even store information about cell formats. So all this "feature" does is cause irreversible data loss to CSV.
There is absolutely no excuse, and never was an excuse, to ever auto-convert a CSV. I always felt like they kept doing this to under-cut CSV in order to force users to use an XLS/XLSX instead. But even with this sabotage Microsoft lost this war and yet continues to destroy data.
It is great I can turn this off, but until the people I'm sending the file to have also done so, it isn't enough. Still a high risk someone in the chain will cause data-loss.
This confused me: (and is 2309 = 23H2?) (is Win10 supported? Why does it require latest Win 11?)
~~~~ excerpt ~~~~
Windows: Version 2309 (Build 16808.10000) or later
Mac: Version 16.77 (Build 23091003) or later
I'm glad they finally introduced this option. Unfortunately the previous behavior is still the default. If someone sends you data and does not know about this, your data will still be subject to the old conversion rules.
Those are the ones I use most frequently and these keystrokes are now deeply embedded in my muscle memory.
BTW, I would take slight issue with your expectations of what a human might expect... I actually think the standard CTRL+V paste does what 90% of Excel users expect.
[Edited to add: this is on Windows, didn't read your comment properly about your experience on a Mac. My apologies.]
Compact and laptop keyboards; sometimes excluded, often poorly/inconsistently sized/positioned.
There's a much better chance of Ctrl+Backspace being a consistent movement independent of keyboard layout, so I can appreciate where the parent is coming from.
I'm not saying there aren't scenarios where ctrl + backspace would be useful, however the majority of the time I'd argue that delete is available and should be used.
Is the desire for a ctrl + backspace chord coming from some other system where this is the standard keying?
It's very common in IDE's, even FireFox supports it if you type words in the address bar, press Control Backspace and the behavior happens there.
Asking ChatGPT about the origins of this, it points to Control+W originating from Unix Terminals, and how it's been adopted by most IDE's as Control+Backspace.
Microsoft has very poor support for it in their tools, and I use MS products for the bulk of my work.
Moving to non-Microsoft products like Google Sheets, and viola, Control+Backspace works.
Even muscle memory stuff like Control+Shift and left arrow to select previous words; also not supported by Excel, but it is by Google Sheets and IDE's.
And conversely people will then more frequently wonder why their supposed numbers and dates cause errors in formulas.
Yes, VSCode with an appropriate plugin is IMHO better than excel at this, but some people (e.g. business analysts) will automatically reach for excel and have to be walked through setting up VSCode and the plugin.
Ids are not really numbers, even if they look like them.
Excel is broken, terrible crap.
Both of those are true at the same time. There is surely a market opportunity there.
It would be cumbersome to always have it enabled, so it could certainly be disabled by default. But Microsoft could market the feature with suggestive tooltips.
One example is if I have the fornula `=A1+B1` in cell C1, I can go to a separate worksheet and generate a constraint like `=MUST(W1!C:C<1337)`; then Excel would flag any rows where the calculation is false (≥1,337).
Of course, this kind of goes into treating derived cells as constrained types, but it seems sanity is achieved with the easier checks.
Constraints or properties are nice in that they are not unit tests; they could be added at the "moment of instantiation" like an object constructor--but in this case, the violation occurs as a post hoc check. It has to happen first.
You might say, "I always triple-check my models and ensure worksheets are equal in multiple ways." Maybe it's possible to do it already. Sometimes, quality is about introducing frictive utility with minimal overhead.
The problems solved are usually not handled with only with an integer primitive, but hand in hand with a domain component that makes us pause and go, "Okay, I guess a person's age won't be MAX_VALUE or negative."
=IF(EXACT(ValueA;ValueB); ValueA; "Mismatch!")
Pretty useful! https://help.libreoffice.org/latest/en-US/text/scalc/01/0512...
The next step may be Don't Repeat Yourself (DRY), so even N nearly duplicate formulae for N rows is N-1 more times than needed.
It's not so bad with one column, but after rearranging a dozen columns and copy-pasting some corrections, the question becomes, "Is everything still working okay, or do I need to skip lunch?"
There's ways around, like a `CURRENT_ROW()` function. That makes it generic.
Understandably, it can be a hassle to type extra functions all the time. Boilerplate for one-liners isn't fun; the whole point is rapid iteration and prototyping.
Just saying, if a model is important enough to keep around and maintain, put in some pragmatic checks--just like your example.
Power Apps are pushing into AI direction. And it does use AI to parse excel file. Moreover Power Apps on itself has PowerFx engine that uses Excel formulas for app + more.
It takes some getting used to, but you can pretty easily create a named range for an individual cell by modifying the value immediately left of the formula bar. You can also setup a table to hold data (insert -> table).
Tables can be renamed and allow formulas like =sum(tbl_salaries[salary]).
With named ranges, your formula can look like =purchase_price*sales_tax
Not to mention the courage to recommend an app to replace the workbook.
If the user elects to add a check, then expand on constraints and such.
If the user selects no, remind the next user that "inadvertent modifications could result in indeterminate results."
Eventually, someone receiving the attachment enables it, and a discussion starts for the group as a whole.
One camp may deride the change: it will never be useful, and data is always in bounds. The other may point out some assumptions that were unclear, and now a check exists. Adding it cost nearly nothing, but coverage reduces chances of regression.
I do exactly that double-checking in Excel with conditional formatting.
If I enter a blood pressure reading that is over 250 or under 35, the cell turns bright red.
One could criticise that instead of 1. trying to determine the type of a column in CVS, then 2. treating all values of the column as instances of that type, Excel would go through row by row and decide ad-hoc which values to auto-convert. That might have been done because people don't just store relations in CSV, but use it as a lingua franca format to move things between applications.
The designers of Excel were not idiots, and tried to build a tool usable by the average user.
Mine was one of the early COVID test results lost when someone ran medical data through Excel. As expected, the account numbers didn't survive.
In German locales the parameter separator is ; rather than , which makes copying code from others a nightmare.
Then again, these issues have been known for decades, so a lot of things like your best practice are around for a reason…
Excel is still the easiest way to look at tabular data, even if it isn’t part of the production workflow. And sadly, even if you save the file as txt, Excel would always mangle certain fields.
So yes… users have been working around Excel-isms for years.