10 ms·
Excel will allow certain auto data conversions to be turned off
- turtleyacht 3y agoIt would be nice if Excel had a builtin "double bookkeeping" feature: given a formula, ensure results are also calculated or accounted for in another place. 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."
- deleted 3y ago[deleted]
- atoav 3y agoI am no excel wizard (have been using Libreoffice for a decade now) but I guess you can do that already (in it's own row) =IF(EXACT(ValueA;ValueB); ValueA; "Mismatch!") So you basically just compare two other rows (which you can hide if you like) and if they are exactly the same you display the value, otherwise you display an error.
- pests 3y agoThen color code the row based on that output
- turtleyacht 3y agoThank-you. Yes, it's already there in Excel. 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.
- badwolf 3y agoIf I'm doing a lot of moving around of things, and wanting to keep more complicated formulas working, I'll usually just use named ranges for my own sanity (and as a convenience for whoever may need to take over a workbook after me) - eg, ```=SUMIF(TransactionProductSold,"Product1",TransactionAmount)```
- wjnc 3y agoI wish someone built a tool to extract any model from Excel and help annotation and conversion to code with clean separation of input, logic, output. The amount of creepy legacy Excel is killing my organization.
- DaiPlusPlus 3y agoI know it's a fad, but isn't that the exact kind of thing that AI-enthusiasts claim ChatGPT/LlaMa/CoPilot will be capable of doing within a year or two?
- spacemanspiffii 3y agoI mean, some are claiming that AI will be capable of anything. But I don't think extracting Excel formula's is currently a focus of LLM applications? Do you know of startups or other attempts at exploring this?
- jve 3y agoThe elephant in the room, Microsoft PowerApps: https://powerusers.microsoft.com/t5/Webinars-and-Video-Gallery/Create-Power-Apps-from-Excel-with-data-Microsoft-AI-Copilot/td-p/2177009 https://powerusers.microsoft.com/t5/Webinars-and-Video-Galle... 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.
- HPsquared 3y agoYou think Excel is bad, just think of the legacy those will leave behind in 20 years... Anything cloud or SaaS-related, good luck trying to dust that off down the line!
- spacemanspiffii 3y agoYes I recognize this problem in my organization as well. I think this is feasible, and tools in this direction exist already, like https://formulas.readthedocs.io/en/stable/doc.html https://formulas.readthedocs.io/en/stable/doc.html. I think one challenge is that the variable names in Excel (B3, B2-10 for a list) are not easily converted to descriptive names.
- robertlagrant 3y agoI like the idea of suggestive tooltips.
- turtleyacht 3y agoYes, it could just be "Hi there, you're using a formula depending on another formula. Do you want to add a check on these rows?" 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.
- badwolf 3y agoYou could even have a helpful office themed avatar that can provide those tooltips. Make it a fun paperclip!
- RecycledEle 3y ago> generate a constraint like `=MUST(W1!C:C<1337)`; then Excel would flag any rows where the calculation is false (≥1,337). 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.
- extr 3y agoThis is great. It's actually surreal to see such a longstanding annoyance finally addressed.
- TMWNN 3y agoIndeed. Every time I opened a .csv and had Excel courteously convert all UPCs into scientific notation, I wondered "Who would ever want that to happen?" Finally there is a way to avoid this.
- hypercube33 3y agoI just want it to auto size cols when I paste data instead of rows
- rwmj 3y agoI'd like the scrollbar to work sensibly, and not race ahead uncontrollably to show me the hundreds of empty rows below the spreadsheet.
- vegetable 3y agoImport CSVs into excel; don't double click to open. Been telling this to colleagues since excel 2007 days; still have never managed to get anyone to follow this advice.
- boxed 3y agoIt's not really fixed at all.
- stirlo 3y ago> Convert a continuous string of letters and numbers to a date. 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.
- rukuu001 3y agoNot just you. Perhaps no other software feature has caused more hours of lost productivity than Excel auto-formatting whatever to a date. 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.
- n4r9 3y agoYes, I had big issues with Excel converting a number range like "1-3" to "3rd January" when importing property data. Eventually switched to Libre Office which works much more intuitively.
- jimmySixDOF 3y agoOne of the worst things is trying to work with hours as a value in excel it will convert them into time and dates and mess up in csv. Avoid.
- barbazoo 3y agoExcel isn’t a csv editor I’d say. Better to use a text editor.
- mc32 3y agoThe other one is converting large or long strings of numbers into scientific notation…
- zrobotics 3y agoAnd leading zeros, which is awesome when postal codes have them and shipping software uses csv imports. Somebody only has to open the file once, and it isn't immediately obvious unless people know to expect that.
- paule89 3y agoStill remember that dna data was saved and edited inside of excel and somehow those huge strings of data were randomly false in part, because of automatic data conversion to arbitrary data formats.
- calrain 3y agoI just want them to support Ctrl+Backspace to wipe the contents of a cell...
- curiousgal 3y agoOr Ctrk-A when you're inside a text box ffs
- mhuffman 3y agoI just want paste to work as a human might expect. Normal paste in excel ... brings color and font information in, ctrl+shift+v which should just be plain ... does something else? Idk wtf it does, but it paste strings from further back in the history. At least for me on mac
- _v7gu 3y agoNormal paste (paste values) exists but is a bit convoluted: ctrl+v -> ctrl -> v
- qsi 3y agoALT+E, S, V is paste as values. ALT+E, S, T is paste formats. ALT+E, S, F is paste formulas. 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.]
- throwaway35705 3y agoThis works until you try have to use Excel localized in languages other than English, since Microsoft thought having localized shortcuts was a good idea.
- mminer237 3y agoNot a solution for Mac, but PowerToys includes an option to make Ctrl+Shift+V plain text paste globally.
- Neil44 3y agoThat leading zeros thing is such a pain in the ass. Silly default IMO.
- cm2187 3y agoAnd to all developers: don't use leading zero in a numeric id stored as text unless you are vicious!
- mbreese 3y agoIt’s really sad that we all basically have to design code and data fields around the behavior tools like Excel. R will do the same thing for numeric fields (strip leading 0s), so it’s not just Excel here. But in the R case, you rarely read/write to the same file, so it’s less of an issue. Then again, these issues have been known for decades, so a lot of things like your best practice are around for a reason…
- uxp8u61q 3y agoIt's really sad that the tool you design should adapt to its users rather than its users having to adapt to your tools?
- snarkypig 3y agoIt's really sad that all the data interop I write has to deal with Excel at some point, even if it's never supposed to interact with end users at all
- mbreese 3y agoMore like we can’t name things (eg. gene names) the way scientists want because of needing to work with the data in Excel. So instead of OCT4 we get a different name because Excel mangled the name into a date for decades. 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.
- gertrunde 3y agoIf you want to be particularly evil, vary the number of leading zeros so that sorting gets completely broken...
- noobermin 3y agoSo not that libreoffice calc is as feature-ful as excel but AFAIK it gives you options on how to autoconvert data every time you import or copy and paste table like data into it including not converting.
- justsomehnguy 3y agoIt's not fully there, but at least it allows you resize the import dialog.
- aquir 3y agoGreat! I am saying in the last 10 years: all I need is a button in Excel called "I am a power user, don't do anything unless told" We are getting there :)
- FabHK 3y agoIf you enter 10 into a cell, you want it to be treated as a text until you explicitly convert it?
- rzzzt 3y agoI guess the gene name changes will not be reverted? https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
- emn13 3y agoMaybe excel can autoconvert the new names to the original ones? Worth a feature request, I'm sure.
- gilcot 3y agoIt's too late, that wrong tool already did much harm. Also read: - https://www.bbc.com/news/technology-54423988 https://www.bbc.com/news/technology-54423988 - https://www.researchgate.net/figure/Screen-shot-of-Microsoft-Excel-spreadsheet-illustrating-errors-caused-by-default_fig4_8493191 https://www.researchgate.net/figure/Screen-shot-of-Microsoft... - https://theconversation.com/excel-autocorrect-errors-still-plague-genetic-research-raising-concerns-over-scientific-rigour-166554 https://theconversation.com/excel-autocorrect-errors-still-p... - etc. A problem that's been around for years is finally addressed.
- Tempest1981 3y agoWhich version of Excel has this? This confused me: (and is 2309 = 23H2?) (is Win10 supported? Why does it require latest Win 11?) ~~~~ excerpt ~~~~ This feature is available to all users running: Windows: Version 2309 (Build 16808.10000) or later Mac: Version 16.77 (Build 23091003) or later
- MilaM 3y agoIt looks like they are rolling it out in stages. I have Excel for Mac on two devices. One has it already, the other one not yet. It's also marked as "BETA". On macOS you can find it in Preferences and then in the "Edit" category. 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.
- mqus 3y agothos os the excel version, displayed under file->account (i had the same issue)
- Tempest1981 3y agoThanks, I get it. I guess I lost track of the naming after Office 365 arrived, vs Office 2010/2016/2019. Still not sure I can explain the order. Or the Mac versions.
- vxNsr 3y agoI have it on my personal pc already but my work machine doesn't yet have it.
- bussiere 3y agoI have worked several times on serveral projects with excel files containing barcode number like 0754... 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...
- rzzzt 3y agoAdd a ' prefix to them so they are treated as text. We've had problems with many-digit integers being converted to floating point approximations instead.
- master-lincoln 3y agoHow did people keep up with that? Is there really no competitor who gets this right? These unexpected conversions must have cost billions in problems they created or fixing time they needed. And now after so many years they just remove some types of auto conversion and leave the others? Sounds like some kind of Stockholm syndrome...
- HPsquared 3y agoNot just Excel, this affects a LOT of dynamically typed programming environments. JavaScript is rife with this sort of thing.
- master-lincoln 3y agoI don't think that's a fair comparison. Here we talk about a tool that fucks up your data once you load it. With javascript there is no such thing. There are some auto type conversions when passing a wrong type into a function that can yield unexpected results. But I would argue this is different as it's an environment for power users only, and there is types at play that should make you as a programmer aware. E.g. 1 + "hello" = "1hello" 1 - "hello" = NaN
- rzzzt 3y ago
- DrScientist 3y agoAt last. 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.
- martin_a 3y agoHey Microsoft, want to know what the REAL feature would be? If ALL conversions were turned off by default...
- tgv 3y agoI don't think you want that. I just cancelled conversion when opening a text file, and all floats were text. But not the integers. Oh. Now it stopped asking, and just converts everything.
- Someone1234 3y agoOr at the very least turned off by default for CSV. 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.
- A4ET8a8uTh0 3y agoGood grief, yes. We have an entire set of procedures that actually state not to open the newly generated CSV files, because of this.
- unnah 3y agoTalking about under-cutting the value of CSV as interchange format... Instead of "comma-separated values", Excel treats CSV as "values separated by locale-specific characters". In continental European locales Excel CSV files are actually semicolon separated, and entirely incompatible with UK or American CSV files, or CSV files in non-Excel software.
- leokennis 3y agoI don’t even care that Excel by default wil transform “00001234” into “1234”. It seems like a sensible default. But…if only I could figure out how to turn that back to “00001234” for the few times I do not want it. And I don’t mean one instance, as double clicking the cell usually does it. I mean for 10,000 numbers at once.
- donatj 3y agoDamn near 50 years late. Did the memo that literally everyone hates this feature, and it’s spoiled many scientific studies and brought down multiple businesses just get delivered to their developers?
- verve_rat 3y agoThey put it in a spreadsheet, but the ticket number got turned into a date, so it never got processed.
- donatj 3y agoFrankly, I don’t even doubt this could be the real answer. If someone had told me that out of context I wouldn’t even question it like “Oh, that makes sense.”
- corbezzoli 3y agoThey put it into a spreadsheet, but the bug list exceeded 65,536 rows so it was silently dropped.[1] [1]: https://www.theguardian.com/politics/2020/oct/05/how-excel-may-have-caused-loss-of-16000-covid-tests-in-england
- codeulike 3y agoIts annoying, but to be fair we dont hear about the people auto-date-detection has helped or the time it has saved because those people mostly remain blissfully unaware of it
- jasonladuke0311 3y agoQuite an asymmetry there
- codeulike 3y agowhy?
- Veserv 3y ago
- michaelcampbell 3y agoI've said for years that whenever Microsoft guesses what I want, it's wrong. My context is as a software developer, so I get that I'm not their target demographic with an office suite, but I stand by my point.
- oezi 3y agoAnd when will they finally allow to change formula language to be independent of display language? In German locales the parameter separator is ; rather than , which makes copying code from others a nightmare.
- n_u_l_l 3y agoI wish there was a tool to convert csv to Excel. Something like Flatfile but for desktop use.
- zacharynewton 3y agoAbout time... a big win for geneticists.
- OldHunter69X 3y agoBy the Nine Divines! I'm still just a spreadsheet initiate, but that auto formatting was steadily becoming my nemesis. I can hardly conceive of the headaches this has caused in people who make serious use of them. It's hard to believe it took this long to implement a feature that seems like it should have been there from the start.
- giancarlostoro 3y agoI wouldn't get too excited, its yet another way Excel can mess up your data without realizing. Clippy never died, he just silently lives in Excel, messing with all the Clippy haters.
- SideburnsOfDoom 3y agoThis will help us, not because we worked with genetics, we have far more mundane requirements: occasionally inspect csv import files, that contain externally relevant ids, of the format "00012345678912345". Turning those into "1.2345E13" is no help at all. 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.
- jpalomaki 3y agoWith CSV files you get much better experience on Windows when you use "Data -> From Text/SV" instead of just opening the CSV file. You get a proper preview, can tweak the field types, set decimal separators etc. 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.
- Foobar8568 3y agoThat's Power Query, which is basically an ETL, and with PowerPivot (xVelocity engine ), you have basically a powerfull self service BI couple with an actual spreadsheet.
- soco 3y agoUh, my experience is that the opened CSV file will get all converted - dates formats changed, trailing zeroes trimmed, umlauts broken, you name it. So "better" means at maximum "less shitty".
- vxNsr 3y agoThis isn't opening the csv file directly, it's importing it into an existing (though possibly not yet saved) workbook. I can attest that it works correctly though it is kind of annoying if you don't want to do more than just some simple manipulation. Also I believe excel for mac either doesn't have this feature or it's not as feature rich as the windows counterpart.
- Isthatablackgsd 3y agoThere is a difference between double clicking the CSV file vs importing from within. Importing via Get Data > CSV will bring up PowerQuery which only made a copy of the source file and allows me to modify the data without affecting the original file. If I made a mistake after transforming, I can go back to PowerQuery (without importing again) and undo the step then adjust the modifications. That is the beauty of PowerQuery. Even in the wizard, it does allow me to set the data type of the columns before opening. Users generally skip that step ahead and get down to the data. 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.
- rabbits_2002 3y agoInsane that it has taken this long.
- aqfamnzc 3y agoIt blows my mind that the behavior in spreadsheet software is not what I would expect: * 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.
- FabHK 3y agoAs it is, Excel needs to keep around a) the value of a cell, b) its format (ignoring formulae, comments, etc.). 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.
- altruios 3y ago36 years is a long time to make these headaches go away...
- kahirsch 3y agoIt's not 1987 any more and Excel doesn't use the file format that it used back then. The price of main memory and long-term storage has declined by many orders of magnitude since 1987.
- FabHK 3y agoWell, yes, obviously, but do you realise what a headache it is (would be) if the default behaviour of Excel changes? How many people would complain if things that used to work suddenly stop working with a new version?
- Eddy_Viscosity2 3y agoThe problem was that this default behavior was unchangable. Like, let me get into settings and turn this shit off (like they are now doing), they could have done this decades ago.
- ajm-codes 3y agoI work technical support at a startup where we have a self-service Uploader for some data imports. Can't count how many hours and emails I spent going back and forth to tell people how to turn auto conversion to scientific notation off. Long overdue feature/setting for them to release.
- FabHK 3y agoI think the "oh, finally" here is somewhat overblown. It would be much more annoying to lots of people if Excel did not recognize a number as a number, or a date as a date. This convenient conversion feature could always be overridden, if I'm not mistaken, by either pre-formatting cells as text (when you're entering manually), or designating a column as text (when importing, and converting from, a CSV file). 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.
- JonChesterfield 3y agoExcel is wildly popular. Good chance it's the dominant programming tool by user count. Excel is broken, terrible crap. Both of those are true at the same time. There is surely a market opportunity there.
- decafb 3y agoOne would think. I have yet to find a spreadsheet program that doesn't mimic Excels behavior for automatic conversion up to the point of making it non-optional.
- washadjeffmad 3y agoGreat! Now solve production data being destructively edited by management opening random files in Excel. 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.
- arjunnvl 3y agofinally, leading zero's in Zipcodes can stay where they are...
- layer8 3y agoInconveniently, they are only doing this now, after the last non-subscription version of Office. And conversely people will then more frequently wonder why their supposed numbers and dates cause errors in formulas.
- ushtaritk421 3y agoCan’t believe it has taken this long to add these features
- mulmen 3y agoWill these settings be stored in the workbook file or in the application? Will fresh installs and naive users still silently mangle data?
- Gelob 3y agoI hate when excel converts UPCs to decimal and no amount of format will fix it other than to insert a colin before the UPC which is apparently the official answer on what to do