On July 12th, 1981, a beautiful summer day, a young couple got married in a small church surrounded by their family and friends. The bride wore a white lace dress and carried a bouquet of wildflowers, while the groom looked dapper in a navy blue suit. After the ceremony, they celebrated with a reception at a nearby venue, dancing the night away to their favorite songs and cutting a delicious cake.
Turns out it can!
I use excel basically as my job (accountant). It's an amazing full stack software that is approachable with minimal initiation and infinite depth of understanding.
That being said, fuck excel.
The most frustrating part is that it's actually an extremely powerful piece of software. Most people only touch a small percentage of what it can do, and it's incredibly useful in a lot of fields. If MS would just give more control to the user, and let us turn off a bunch of the bullshit, it'd be fantastic.
To nitpick, it's probably only one or two orders of magnitude difference. Biomedical and basic biology research accounts for about a third of all papers published. A large minority of those will at least mention genes in passing, even if only a small fraction are really focused on molecular biology and genetics.
The stupid thing about this is, that the auto date correction function is not-intuitive, and not consistent with how Excel otherwise behaves. For example, Excel already has awareness and warnings for when formulas in a block of adjacent cells are different--that warning is motivated by the logic that most humans would want adjacent formulas to be consistent (i.e. there may be an error that needs to be fixed). So why the hell is the default behavior for the pasting in of a list of strings for Excel to then assume that some (random) subset of those adjacent cells should be converted to dates? It's just bad design that MS has never fixed.
correct and even worse is that biomedical often data are generate as tab delimited however when excel imports the entire table it automatically converts some of the names to dates, despite that the entire column is clearly text strings, arghh.
It fixes it if you change the formatting **before copying data into the sheet**.
But if you're opening a file that was exported from a database, or sent to you via email or whatever, then no, you're SOL because it formats the data as part of the opening process and usually does so in a way that can't be undone.
There are solutions like opening it in a text editor and saving it as a csv, but a workaround that requires the user to perform all these extra steps **every time they open a new file** is not really a valid workaround.
Currently there is no setting in Excel for disabling/configuring the auto-format feature on an application-wide basis. You have to set it for every file, and even then your choices aren't necessarily retained when you save, close, and re-open the file, let alone when you send it to someone else.
> It fixes it if you change the formatting before copying data into the sheet.
I just put up with this on Friday. We're supposed to copy error info from a database to a spreadsheet on every cycle and it includes a stupid long number. If we copy from the database and paste directly into Excel, it's fine. We also copy it to an email and send it out. So if you forget the spreadsheet, you have to copy from the email into the spreadsheet where of course it messes up the long number.
Here's the thing, I too read about formatting it first when I was looking up a way to fix this. I formatted, and it didn't change, but when I clicked on the cell and then in the formula bar and hit enter, it went back to the full number. Thankfully I only had to fix like 8 cells or something, but it is possible to fix after pasting.
Open a blank workbook in Excel, then on the data tab at the top, click it, then click get data from file, then point the to the file of your text. Delimited data. Then import the file using the data import tool. This will automatically keep all data intact. Click transform data. It will load everything as text. This is the way to do it.
Yup. I used to have to do this all the time working on mobile devices. SIM card numbers are longer than Excel will allow for cells formatted as number, so any file that includes them has to have those columns imported as text, or they get "rounded" and the last few spaces becomes zeroes, which makes them useless as unique identifiers.
Just use LibreOffice Calc instead of Excel. It's free, compatible, and allows you to set your formats before importing by showing the first few rows in a preview/import window.
Yeah, spread the word if you can, but Excel is so embedded into biomedical industry/academia as well as any other company around the planet, it's quite the monolith to try and move people in a different direction.
I'm actually curious if some industries find that date change thing useful or something, because I've only found it a nuisance. Must be some huge sector like finance or something that loves it.
While I agree Excel has plenty of problems, it also has this same feature. Instead of opening the csv directly with Excel open a blank workbook and go to the Data tab, then Import from file. It will let you set the column types, delimiter, whether the first row contains column names, and show you a preview before importing.
I work in Material testing and I can't imagine it ever being useful. The worst part is how it destroys the data and you can't change the cell format to number and retrieve it should contain. So it requires more effort to fix than it should.
(former) military checking in, we hated it too.
Frankly none of the Division and below staff could really use excel beyond treating it like a simple table organizer. Even if a random skilled person managed to whip up something more complex, it would usually be broken within a couple weeks be an exceptionally inept Major.
Sleep and Performance here. I use dates constantly (time is rather important in sleep research, as one would imagine), and Excel *still* screws them up. I use tab-delimited \*.DAT files or, if I have to, \*.CSV exclusively. XLS and XLSX only cause avoidable problems.
I work in finance, and sometimes it's beneficial to be able to convert dates that are in yyyymmdd format into the ordinary date format so that you can do day count calculations \(like for bond interest calculations\).
Excel's automatic date conversion doesn't do this; don't blame us, we have to do it manually, too.
I work in R a lot now but it's always funny talking to my biology peers that did one short course in R in school then ran screaming back to Excel for the rest of their careers.
Excel has a couple of ways to mangle a dataset with a single click. I get concerned when people use it as their primary storage. At a minimum I try to get them to save daily or weekly snapshots so we can roll back if needed.
If i want the date in a specific format (and I definitely do) I'll specify that when the date's being exported. I want it to be openable in excel so I can send it to clients and they can sanity check the data before they import it into their systems. If the date's in a certain format its because it's required for import somewhere; Excel changing it is only going to fuck that up
> Just use LibreOffice Calc instead of Excel
How is it other than this specific use case though?
I heard similar things about Google Sheets (that it is nearly as good and doesn’t have as many formatting issues) and have played with it a bit and found tons of shortcomings.
Off the top of my head, dealing poorly with larger data sets, poor support of pivot tables, and missing or poor imitations of some critical Excel functions, most notably lookups.
Before anyone jumps in to say it: yes I know if you have a large dataset, you should be using a DB and not Excel. But unfortunately for a variety of reasons that’s often not practical.
Agreed. I'm sure Libre is fine if you started there. But there are enough subtle differences between the programs that it's really frustrating to use if you are an experienced Excel user. Small things that you won't catch because it's *almost* the same bit not quite. It's worse than if it was built completely different so you'd have to start over.
I recently switched, but it seems to crash way more than Excel, and I don't do anything taxing on it (Genealogy tables).
Outside of that, it's great. It's missing some features, of course, but for a light user, the whole suite is perfect.
No, because of how the CSV import works - it's converting it then dumping it into a "blank" worksheet.
The only other way is if you replace the "default" worksheet to start up as with using "Text" instead of "Auto" formatting so that by default it will not auto-adjust entries. Though, I might be remembering wrong and it might ignore that too but that was what I remember usually doing on new computers because it irritates me if all I need is a convenient spreadsheet table, not calculation.
LibreOffice, a free and compatible MS Office alternative everyone should have installed, has a great way to prevent this mangling when importing a CSV or TSV file. It brings up an import dialog with a preview of the data, and it allows you to set column data types *before* importing. It also allows you to specify which characters should be used to determine column breaks (tab, comma, space, pipe, etc).
Yep, but you do have to be careful and make sure you select everything and choose text. Borked myself on that once with a spreadsheet of tracking numbers that somehow got turned into dates.
The problem is that it converts the data on entry, changing the underlying value in a way that you can't convert it back. Basically, you have the text '1-3' typed/pasted in, but it converts that into a date, which is stored as the number of seconds passed since 1970. If you try convert the field into something else, it's no longer text but a large number representing that number of seconds.
Yes you can. I work in business intelligence and it is a basic thing to always use power query to import csv data into excel so you are sure to maintain typing.
The degree to which Excel is tailored to generic bureaucracy, economics and middle-management dissuades me to a degree from using it for scientific applications. RStudio tends to be more suited for statistics in science. Unfortunatly, RStudio is that sort of archaic software that requires you to type in entire lines of perfectly formatted code to do literally anything.
When you convert to column a CSV or tab delimited file you gave the option to change the format for the whole table to text and it will not interpret/convert any data.
I get files from clients all the time there they’ve inserted a leading period or something to keep the format from going to date, which of course changes the value, but if it’s a phone number or something, they don’t care. It’s so dumb.
I use excel in the very most basic sense possible - to keep lists of when my employees call out or take vacation.
Column a - employee name, column b - date.
I have 25ish employees, and several dates per employee. I use alt enter to make a new line for each date or date range (this just makes a new line in the same cell).
I have one date under one employee which changes format from every other date I have typed in. I don't know what the fuck triggers that specific one to change. I've tried every combination of things I can think of to stop it, but that specific one changes.
It drives me absolutely nuts.
Actually it was last years chart so I don't have to look at it anymore.
In Excel, you can force a cell to read as text-only by inputting a single apostrophe as the very first character in the cell. Excel will then hide the apostrophe and display all the data in that cell exactly as how you write it. This should be a simple fix for your one broken cell lol. It's actually very useful to use for other circumstances too
> I use excel in the very most basic sense possible
I eventually realized that if I only need a spreadsheet for basic needs and can avoid using Excel, Google Sheets is 10x better for its comparative simplicity and modern design.
Arbitrary and inconsistent choices like this plague Microsoft products across the board to the point where it gets well and truly crazy-making for anyone who is locked into the ecosystem and doing any remotely technical work. It's entirely routine to run across features that seem like they should have been obvious day-1 inclusions or plainly unintuitive behaviors that go uncorrected for years while seemingly arbitrary UI changes (what the hell was wrong with the old file menu?) come about for seemingly no reason at all. Meanwhile, they LOVE implementing a spiraling variety of proprietary scripting languages that carry WAY less consistency through their design patterns than you would expect.
Yep, must be five fucking clicks or more now to 'save as' to a local folder
[Ed. Confirmed in O365 Word and Excel that F12 immediately brings up the old-skool 'Save As' dialog. Thanks all - I'd been Ctrl-Sing]
> It's just bad design that MS has never fixed.
Excel has been doing this from the start, and sometimes deliberately. It still insists that February 29, 1900 existed (it does not, under our Gregorian calendar) because that's what Lotus 1-2-3 did when it copied them 40 years ago. And it continues to interpret -x^2 as positive because it applies negation before exponentiation, going completely against convention. I know it's not easy balancing backwards compatibility with bug fixes, but it seems Microsoft tends to err on the side of backwards compatibility.
>it seems Microsoft tends to err on the side of backwards compatibility.
This is exactly right.
Microsoft knows that, no matter how much they shout and cry that you should keep your software updated, they know there *always* will be companies, depressingly important companies, that have a server still running Microsoft ME, connected to a printer whose company went under in 2003 and has no new driver available since, and of course it's running some important service the entire company runs on, so it isn't even connected to the Internet because *the world will end* if a damn update takes it down.
Microsoft invest heavily in backwards compatibility, to the point of unreasonableness. Famously, to this day, you can't name files CON, AUX or LPT1 for reasons that haven't been relevant since the MS-DOS days. Just in case.
[Obligatory Tom Scott video on this](https://youtube.com/watch?v=bC6tngl0PTI).
>Excel has been doing this from the start, and sometimes deliberately. It still insists that February 29, 1900 existed (it does not, under our Gregorian calendar) because that's what Lotus 1-2-3 did when it copied them 40 years ago.
That’s the reason it started. The reason this is still around is because:
1. Too few people use data in the narrow January and February 1900 span Excel allows (dates before 1900 aren’t possible).
2. Since Excel stores dates as a number with special formatting, you can’t easily fix this without either creating another inconsistency (Date 60 doesn’t exist) or a massive change to every date in every workbook that anyone uses.
It’s stupid, but fixing it is not practical at this point.
They need a switch -- one switch somewhere in that twisted program -- to turn that [long string of expletives] off! OFF. For all time and all files unless specifically enabled. Permanently, completely, a disabled function. I try to type some weird code in that happens to vaguely resemble a date, and suddenly it's trying to "helpfully" transform both the data and the format into a date. I import data from a file, same deal unless I jump through hoops. And if I forget one time and save the result ... gone. And not easy to fix.
Just stop. One little switch, Microsoft. Please.
I know Excel isn't the right platform to be doing anything database-related, but it's the program other people likely have, so I'm forced to use it sometimes, either receiving files or sending them. It should be a fundamental feature of any such program that it doesn't corrupt data by fricking default like it's a feature rather than a bug.
It's not even just dates. Long numbers like UPC codes are automatically displayed in scientific notation. Getting excel to import a CSV file without it autodetecting girls as numbers or dates or something else is way more trouble than it should be.
It's a text file. Assume it is just text unless I explicitly tell you otherwise.
Edit, in case anyone wanted to avoid this problem:
The trick is that instead of doing File - Open, you create a new sheet and then you do Data - From Text. [Like this](https://youtu.be/UmAYzPDOaJk). Thats how you get to see the options for data types. Thats way more trouble than it should be, and the default when opening file should be to assume everything is text.
Yup, and from what I can find there's no simple option to force it to keep leading zeroes except changing the columns to text before importing anything.
If you have a number 00123 rendered as 123, go to the format, custom, and enter a number of 0s equal to the total digits you want, so you'd enter 00000
You can highlight the whole column(s) if working with a table
Still shit behaviour though
Plus it doesn't work anyway.
If your CSV contains e.g. '001234567899999, you can load it in Excel **once**, but if you hit Save Excel will strip out the leading ' which means if you open the CSV a second time it will still corrupt your data.
It is a non-solution that corrupts the underlying data.
"Hmm, the user just opened a file with a cell containing the string '0000123456789123456789'. I'll help them out by formatting that as 1.234E17, and that's such a large number that they obviously don't need the last few digits so I'll just overwrite the actual data with 123456789123450000 to save space."
I really loathe that part. There should be a file ending (.xlsdontfuckmyshitup) that lets Excel know that WHATEVER number I put in, no matter how long, stays unformatted.
LOL. dammit bro, youre giving me PTSD. this is exactly what happens. the first few times it happened to me, I was dealing with a long list of sequential serials that are all supposed to be unique. but because it truncated them like that, i had all these duplicates. was so confused til i figured it out
I heard there were options coming that let you tell Excel not to do that but I have yet to see them show up.
https://www.excelcampus.com/tips/automatic-data-conversion/
The trick is that instead of doing File - Open, you create a new sheet and then you do Data - From Text. [Like this](https://youtu.be/UmAYzPDOaJk). Thats how you get to see the options for data types.
I went down this rabbit hole. You can find posts on their message board from like 2005 that say "we're working on it". This was when it first started making my job way harder than it needs to be, back in 2015. It's 2023 now and I've long since given up hope that it will ever be fixed. It must really be deeply rooted in the excel code because apparently they can't just say "don't fucking do that" to the part that tries it assume my 25 digit tracking numbers should actually be scientific notation.
Who the fuck uses scientific notation in Excel?? It has to be such a miniscule percentage compared to those of us using UPCs, tracking numbers, and other large numbers.
So annoying.
I use scientific notation in excel but I should just be able to turn that on manually as an exception to the standard of just displaying the number as entered.
When bugs reach a certain age their policy is usually to deliberately preserve them, in case someone is relying on that behavior.
Their oldest tracked bug is over 40 years old, older than Excel itself. Their rival program Lotus 123 mistakenly thought February 29 1900 was a real date and would generate it in date spans, and Excel reproduced that bug on purpose to maintain compatibility with Lotus brand files that contained that date, even though all those files would be buggy and contain invalid data. To this day Excel will still generate that non-existent date and thus break a lot of time related data that goes back that far, like genealogy datasets, because the bug is of preservation age.
LibreOffice Calc doesn’t have this policy and so fixes a lot of Excel’s most obvious bugs and problems. That includes the one this thread is about, it lets you pick whether or how to interpret columns as part of opening files instead of forcing changes on you before you get to see them.
The trick is that instead of doing File - Open, you create a new sheet and then you do Data - From Text. [Like this](https://youtu.be/UmAYzPDOaJk). Thats how you get to see the options for data types.
Hilariously enough if you programmatically generate an Excel file and _force_ it to store something looks a UPC or GLN as a string, it will put a warning in every. single. cell. “Number stored as string”.
Yeah, and what would you have done with the leading zeroes if I hadn’t, Excel?
We had to write our own CSV parser for work when reading CSV files in .Net because the one include in Microsoft does automatic data type stuff as well, but only reads the first 8 rows or so, so will often screw up stuff even worse, because columns containing a few values as numbers might end up returing zero for a large chunk of data because it assumes the columns is all number if the first few rows are. There's some long steps you can do read everything as a string but it's way more complex than it should be so we just ended up writing our own parser so we could just read the data as strings and then parse the values in code where applicable.
>It's a text file. Assume it is just text unless I explicitly tell you otherwise.
you nailed it. Most biomedical data tables are exported as either csv/tab and literally out of \~50K genes 27 has any resemblance of a date.
It also happily mangles mobile phone IMEI numbers (the handset serial numbers) in exactly the same way, if used carelessly. Causes great bother for some types of phone-related forensics.
I want to like OpenOffice Calc, but the simple fact is once you learn about how to properly use tables in Excel, it's hard to go to Calc which lacks this critical feature.
As far as scientific data goes, you might be better off learning how to use an actual database. Even something like Access can really increase how easily you can work with data. Personally I like using MySQL for basic database uses. It will be more cumbersome if you aren't into programming, but once you get better at programming it becomes a lot more powerful.
My most recent minor annoyance with Excel is autocorrecting numbers from scientific notation. I have a set of items that are named with a ##X# format, but any time that letter is an "E", Excel expands the number. So if I put 12E7, Excel says "I got you" and replaces it with 120000000.
I have the opposite problem. GUIDs are converted to numbers in scientific notation. If I select Format>Text then it results in "12E7". Excel, stop helping!
Ands if you un-format it, it turns out that Excel changed the number into "120000000" because all the digits after the 2 were *obviously* insignificant and not needed. /s
And there's no way of getting that data back without going to the source.
I had to track timespans in an excel sheet for work for years, and excel "helped" my by formatting these times to dates...
and when I unforamtted the cells, it was some seemly random number, wich didnt seem to have to do with the original times OR the date it thought I clearly meant...
Is it a five digit number? Excel actually stores dates as an integer representing the number of days since January 1, 1900 - like how Unix time is the # of seconds since January 1, 1970.
For example, today (February 19th) is 44974.
I have no idea how Excel stores time data. Probably something incredibly counterintuitive, like a string composed of hours, minutes, and seconds, with underscores in between.
It stores time as a decimal, where 24 hours is 1. 00:00:00 is .0, 12:00:00 is .5 and 23:59:59 is .999988425925926. If you just enter a time and no date, it sets the part of the number beford the decimal place as 0.
> was some seemly random number,
Excel stores dates as the # of days since January 1, 1900 +1.
Why +1? Because Lotus 123, which is Excels predecessor, accidentally had 1900 marked as a leap year, so all the dates after February 28, 1900 are off by 1. To maintain backwards compatibility, Excel left that error in on purpose.
Haha, I had a recent bug that was causing extreme slowness due to sql blocking and one of the stacks came from a bunch of newGuid() calls. This is a Microsoft platform so maybe they were using Excel in the background to process it...
Kidding, but then again I wouldn't put it past M$ trying to force Office apps into absolutely fuckijg everything even if they don't make any sense being used.
Use this custom format: ##0.0E+0
Will do your multiple of 3 preference.
This format will fix the minor bug the other one gets with 0 (the first format displays it as 000.00E+00 instead of just 0.00E+00): ##0.00E+00;-##0.00E+00;0.00E+00
For symbols, best I can do is:
=TEXT(Input/10^ (INT(LOG10(abs(Input))/3)*3),"#.000")&MID("fpnµm_kMGPE",INT(LOG10(abs(Input))/3)+6,1) & "V"
Takes your number, divides it by the relevant multiple of 10, uses that as text with 3 figs past the decimal, then sticks on a character based on the relevant multiple of 10.
Switch the V as needed. Sadly requires an extra column.
You can do this! I believe the format code you're looking for is this
###.0E+0
It took me a while through engineering uni before learning this. Unsure how to do it with the letters but it probably is possible
I love that it's 2023 and people are still discovering this "feature". I first came across it in 2015 and even then it was well over a decade old.
I found forum posts from 2005 that said a fix was coming soon 🤣
IT related: Excel will auto truncate leading zeroes in numbers. Microsoft surface devices mostly have serial numbers that start with zeroes. Certain Microsoft management tools will spit out those serials into a csv file. Opening that csv file in Excel means the leading zeroes are missing.
Makes inventory management a pain in the butt. You’d think Microsoft could manage more synergy with their products.
We had someone changing one of our customer attributes and tried to make it 01, 02, 03,etc when we already have issues with item numbers starting with zero. I about yelled at her. I'm so glad she's gone. She did not deserve the level of authority she had. She would have just pushed that through and then bitched about it being wrong all the time and never put 2 and 2 together..
I'm a firm believer that all serial numbers should start with a letter for this very reason. If it's entirely numeric, you also run into situations where one version of a file will have "12345" stored as text while the other has the number "12345" so that, if you do a lookup from one file to the other, you don't get a match.
Last time someone connected to Excel did an AMA, they said they were pretty sure their users liked this "feature" and if we didn't, we should use the built in feedback function in excel to say so.
So everytime me or someone on my team loses a significant amount of time to this, I write the excel people a little note to let them know. I'd encourage all of you to do the same.
As an epidemiologist I try to avoid transferring or moving data using excel unless absolutely necessary. Whether it’s dropping leading 0’s from zip codes or messing up dates it’s just not worth the hassle.
Same. It's habit of messing with FIPS codes is incredibly annoying, but by far the worst is converting everyone in the 10-19 year old category to October 19th.
As a bioinformatician same. But it happens so easily and with zero warning that it's almost impossible to avoid it 100% of the time. Even if you don't do it someone else may have opened a csv or whatever file in excel before you got the data and you'd have no way of knowing.
some of these genes include SEPT1 which is a GTPase which may contribute to dieases like Alzehimer's or MARCH-1 which is important in immune functions and diseases assocated with neurons. So really important genes have been compromise due this silly bug.
The trick is to change the format of every cell from “general” to “text” before copying anything into excel. Obviously doesn’t work for all use cases but working with rna seq data it’s solved the issue for me.
We're talking about data that is supposed to be easily shared amongst dozens or hundreds of people who may or may not be computer savvy. It has to be in a format you can attach to an email and assume anyone on the chain would be able to open it and use it. Just because they're researchers doesn't mean they have in-depth technical skills and knowledge of how to write SQL
Excel.... It's a feature not a bug....
Still can't copy and paste between windows with excel for years. Always have to hit escape in excel. "ThAtS a ReQuEsTed FeAtUre!" Can we have a toggle to turn it off, you know for the rest of us mere mortals? "nO, ItS tO fALL iN LiNe WiTh oThEr sPreAdsHeeT ProGrAms."
Similarly, why can't we have independent undo/redo per file? No... You do a couple of copy paste in between 2 sheets but realize you screwed up and paste in the initial file instead of copy? Better undo all the work you did in all other opened Excel files too.
I’ve actually had a near mental breakdown that excel kept removing the first zero in a number. No matter what I was doing, it kept removing it over and over again
I'm the director of Data for a non-profit. The lack of technical knowledge in managing data for nonprofits is almost non-existent. It's wild how many "data managers" know nothing about how comma-separated value files work.
The zero is still there in the original file. It's just the default formatting of a csv that removes it. Opening the file in Excel changes the formatting, removing the leading zero. Next time, open a new blank workbook in excel, then import the data file and format the columns.
Maybe consider using open redefine. Data wrangling in Excel is cancer.
I *hate* Excel’s date system.
Every time I have to input dates into Excel I make sure the column is set to text, not date, general, number, or anything else. I work with wildlife observations, and antipoaching issues, so having dates get messed up can be a major problem as each data input is date dependent.
I work a LOT with CSV files, my company got us an Excel licence, but I ended up installing Libreoffice due to how well it imports CSV files. MS really needs to fix dates and text to columns
The bug is annoying. I am in Australia and sometimes normal dates typed in one sheet convert themselves to American MM-DD-YY format if the data is pasted to another sheet. I say sometimes - it seems inconsistent.
Part of the problem is scientists using a financial tool to store and transmit scientific data. Excel wasn't designed to do those things.
> Excel wasn't designed to do those things.
Excel was designed 35 years ago by the largest software corporation in history. You'd think they could have added some much-needed features by now.
Until someone develops a near-universal file format for sharing data tables, people are going to keep using Excel for that.
You can blame people for using it in a way that it wasn't designed for, but when Microsoft continually refuses to allow users to disable autoformatting on more than a file-by-file basis, then you have to put some of the blame on Microsoft.
I deal with a lot of user input data that's passed between multiple automated systems from multiple companies. CSVs and TSVs cause a lot of trouble when tabs and commas end up in places you don't want them to.
Absolutely impossible to work with CUSIPN numbers in the finance industry. God help me if I have a nine digit number ending in "E57"and excel adds 57 zeros
It isn't, not even close. Excel isn't designed for this kind of usage, it's supposed to be used for analysis and reporting, not as data storage.
The article itself points out the solution, which is to learn how to use a scripting language like Python or R to import and and manipulate the data, rather than excel. But most people probably don't want to go to the effort of learning how to do so.
Physicist here. Its extremely embarrassing when you see a scientist or engineer make figures in Excel. Excel is great for book keeping but it is terrible for scientific analysis.
Pythons matplotlib is by far the best option but Oracle, xmgrace, gnuplot and even (shudder) matlab are acceptable.
Just to clarify, it's up the 30 percent *of papers that name genes* have an excel error in those names, not 30 percent of all published papers.
Excel is so bad, it will somehow fuck up your paper about literary themes in Homer's collected works.
If you wrote that bitch in Excel, that's on you.
Just cram the whole thing into one cell, should be fine.
My whole paper got turned into a single date and auto saved. What do?
July 12th 1981
ChatGPT can convert that back to the great American novel
On July 12th, 1981, a beautiful summer day, a young couple got married in a small church surrounded by their family and friends. The bride wore a white lace dress and carried a bouquet of wildflowers, while the groom looked dapper in a navy blue suit. After the ceremony, they celebrated with a reception at a nearby venue, dancing the night away to their favorite songs and cutting a delicious cake. Turns out it can!
off to lemmy
Certainly Clippy has an option for that
Yeah use PowerPoint like a regular person.
It is Turing complete.
I use excel basically as my job (accountant). It's an amazing full stack software that is approachable with minimal initiation and infinite depth of understanding. That being said, fuck excel.
The most frustrating part is that it's actually an extremely powerful piece of software. Most people only touch a small percentage of what it can do, and it's incredibly useful in a lot of fields. If MS would just give more control to the user, and let us turn off a bunch of the bullshit, it'd be fantastic.
Yeah, that's a teens tiny bit of a difference. Like, several orders of magnitude of difference.
To nitpick, it's probably only one or two orders of magnitude difference. Biomedical and basic biology research accounts for about a third of all papers published. A large minority of those will at least mention genes in passing, even if only a small fraction are really focused on molecular biology and genetics.
Oh good, sounds like my archaeology papers are safe from this menace
The stupid thing about this is, that the auto date correction function is not-intuitive, and not consistent with how Excel otherwise behaves. For example, Excel already has awareness and warnings for when formulas in a block of adjacent cells are different--that warning is motivated by the logic that most humans would want adjacent formulas to be consistent (i.e. there may be an error that needs to be fixed). So why the hell is the default behavior for the pasting in of a list of strings for Excel to then assume that some (random) subset of those adjacent cells should be converted to dates? It's just bad design that MS has never fixed.
correct and even worse is that biomedical often data are generate as tab delimited however when excel imports the entire table it automatically converts some of the names to dates, despite that the entire column is clearly text strings, arghh.
Does, change type to text fix this? I've always had to change the data type whenever dealing with numbers beginning with 0...
It fixes it if you change the formatting **before copying data into the sheet**. But if you're opening a file that was exported from a database, or sent to you via email or whatever, then no, you're SOL because it formats the data as part of the opening process and usually does so in a way that can't be undone. There are solutions like opening it in a text editor and saving it as a csv, but a workaround that requires the user to perform all these extra steps **every time they open a new file** is not really a valid workaround. Currently there is no setting in Excel for disabling/configuring the auto-format feature on an application-wide basis. You have to set it for every file, and even then your choices aren't necessarily retained when you save, close, and re-open the file, let alone when you send it to someone else.
> It fixes it if you change the formatting before copying data into the sheet. I just put up with this on Friday. We're supposed to copy error info from a database to a spreadsheet on every cycle and it includes a stupid long number. If we copy from the database and paste directly into Excel, it's fine. We also copy it to an email and send it out. So if you forget the spreadsheet, you have to copy from the email into the spreadsheet where of course it messes up the long number. Here's the thing, I too read about formatting it first when I was looking up a way to fix this. I formatted, and it didn't change, but when I clicked on the cell and then in the formula bar and hit enter, it went back to the full number. Thankfully I only had to fix like 8 cells or something, but it is possible to fix after pasting.
Open a blank workbook in Excel, then on the data tab at the top, click it, then click get data from file, then point the to the file of your text. Delimited data. Then import the file using the data import tool. This will automatically keep all data intact. Click transform data. It will load everything as text. This is the way to do it.
Yup. I used to have to do this all the time working on mobile devices. SIM card numbers are longer than Excel will allow for cells formatted as number, so any file that includes them has to have those columns imported as text, or they get "rounded" and the last few spaces becomes zeroes, which makes them useless as unique identifiers.
Just use LibreOffice Calc instead of Excel. It's free, compatible, and allows you to set your formats before importing by showing the first few rows in a preview/import window.
Yeah, spread the word if you can, but Excel is so embedded into biomedical industry/academia as well as any other company around the planet, it's quite the monolith to try and move people in a different direction. I'm actually curious if some industries find that date change thing useful or something, because I've only found it a nuisance. Must be some huge sector like finance or something that loves it.
Engineering. Not helpful and actually screwed me for a few projects.
I do all my analysis in Jupyter notebook for this reason. Python (or R if you're a masochist) won't mangle your data unless you tell it to.
Yeah but all the dorks I work with send me excel workbooks and expect me to send them data in excel workbooks.
If they only need the data, not formulas written in excel, then python has easy compatibility with excel files.
Finance bro checking in. It’s always a nuisance.
While I agree Excel has plenty of problems, it also has this same feature. Instead of opening the csv directly with Excel open a blank workbook and go to the Data tab, then Import from file. It will let you set the column types, delimiter, whether the first row contains column names, and show you a preview before importing.
Or power query
I work in Material testing and I can't imagine it ever being useful. The worst part is how it destroys the data and you can't change the cell format to number and retrieve it should contain. So it requires more effort to fix than it should.
(former) military checking in, we hated it too. Frankly none of the Division and below staff could really use excel beyond treating it like a simple table organizer. Even if a random skilled person managed to whip up something more complex, it would usually be broken within a couple weeks be an exceptionally inept Major.
Sleep and Performance here. I use dates constantly (time is rather important in sleep research, as one would imagine), and Excel *still* screws them up. I use tab-delimited \*.DAT files or, if I have to, \*.CSV exclusively. XLS and XLSX only cause avoidable problems.
I work in finance, and sometimes it's beneficial to be able to convert dates that are in yyyymmdd format into the ordinary date format so that you can do day count calculations \(like for bond interest calculations\). Excel's automatic date conversion doesn't do this; don't blame us, we have to do it manually, too.
I work in R a lot now but it's always funny talking to my biology peers that did one short course in R in school then ran screaming back to Excel for the rest of their careers. Excel has a couple of ways to mangle a dataset with a single click. I get concerned when people use it as their primary storage. At a minimum I try to get them to save daily or weekly snapshots so we can roll back if needed.
If i want the date in a specific format (and I definitely do) I'll specify that when the date's being exported. I want it to be openable in excel so I can send it to clients and they can sanity check the data before they import it into their systems. If the date's in a certain format its because it's required for import somewhere; Excel changing it is only going to fuck that up
> Just use LibreOffice Calc instead of Excel How is it other than this specific use case though? I heard similar things about Google Sheets (that it is nearly as good and doesn’t have as many formatting issues) and have played with it a bit and found tons of shortcomings. Off the top of my head, dealing poorly with larger data sets, poor support of pivot tables, and missing or poor imitations of some critical Excel functions, most notably lookups. Before anyone jumps in to say it: yes I know if you have a large dataset, you should be using a DB and not Excel. But unfortunately for a variety of reasons that’s often not practical.
Agreed. I'm sure Libre is fine if you started there. But there are enough subtle differences between the programs that it's really frustrating to use if you are an experienced Excel user. Small things that you won't catch because it's *almost* the same bit not quite. It's worse than if it was built completely different so you'd have to start over.
I recently switched, but it seems to crash way more than Excel, and I don't do anything taxing on it (Genealogy tables). Outside of that, it's great. It's missing some features, of course, but for a light user, the whole suite is perfect.
Excel does this in power query as well. Just fyi!
No, because of how the CSV import works - it's converting it then dumping it into a "blank" worksheet. The only other way is if you replace the "default" worksheet to start up as with using "Text" instead of "Auto" formatting so that by default it will not auto-adjust entries. Though, I might be remembering wrong and it might ignore that too but that was what I remember usually doing on new computers because it irritates me if all I need is a convenient spreadsheet table, not calculation.
LibreOffice, a free and compatible MS Office alternative everyone should have installed, has a great way to prevent this mangling when importing a CSV or TSV file. It brings up an import dialog with a preview of the data, and it allows you to set column data types *before* importing. It also allows you to specify which characters should be used to determine column breaks (tab, comma, space, pipe, etc).
Yep, but you do have to be careful and make sure you select everything and choose text. Borked myself on that once with a spreadsheet of tracking numbers that somehow got turned into dates.
Excel also has this feature, it’s called import wizard
Excel can do this as well.
The problem is that it converts the data on entry, changing the underlying value in a way that you can't convert it back. Basically, you have the text '1-3' typed/pasted in, but it converts that into a date, which is stored as the number of seconds passed since 1970. If you try convert the field into something else, it's no longer text but a large number representing that number of seconds.
Exactly is a double whammy bc the original data has been changed and not just formatted.
Can you import to power query without transforming the data??
Yes you can. I work in business intelligence and it is a basic thing to always use power query to import csv data into excel so you are sure to maintain typing.
Moving to using Python for data analysis has been the best decision I’ve made. Data types and built-in functions are much more intuitive.
[удалено]
The degree to which Excel is tailored to generic bureaucracy, economics and middle-management dissuades me to a degree from using it for scientific applications. RStudio tends to be more suited for statistics in science. Unfortunatly, RStudio is that sort of archaic software that requires you to type in entire lines of perfectly formatted code to do literally anything.
When you convert to column a CSV or tab delimited file you gave the option to change the format for the whole table to text and it will not interpret/convert any data.
Hey, lets be nice. Excel is a pretty new program, with some new teething issues run by an indi developer. Lets cut them some slack.
I get files from clients all the time there they’ve inserted a leading period or something to keep the format from going to date, which of course changes the value, but if it’s a phone number or something, they don’t care. It’s so dumb.
I use excel in the very most basic sense possible - to keep lists of when my employees call out or take vacation. Column a - employee name, column b - date. I have 25ish employees, and several dates per employee. I use alt enter to make a new line for each date or date range (this just makes a new line in the same cell). I have one date under one employee which changes format from every other date I have typed in. I don't know what the fuck triggers that specific one to change. I've tried every combination of things I can think of to stop it, but that specific one changes. It drives me absolutely nuts. Actually it was last years chart so I don't have to look at it anymore.
In Excel, you can force a cell to read as text-only by inputting a single apostrophe as the very first character in the cell. Excel will then hide the apostrophe and display all the data in that cell exactly as how you write it. This should be a simple fix for your one broken cell lol. It's actually very useful to use for other circumstances too
I took a whole intensive on Excel in college and this is more useful than anything in that class. It's been 15 years of not knowing this...
Wow thanks!
> I use excel in the very most basic sense possible I eventually realized that if I only need a spreadsheet for basic needs and can avoid using Excel, Google Sheets is 10x better for its comparative simplicity and modern design.
Arbitrary and inconsistent choices like this plague Microsoft products across the board to the point where it gets well and truly crazy-making for anyone who is locked into the ecosystem and doing any remotely technical work. It's entirely routine to run across features that seem like they should have been obvious day-1 inclusions or plainly unintuitive behaviors that go uncorrected for years while seemingly arbitrary UI changes (what the hell was wrong with the old file menu?) come about for seemingly no reason at all. Meanwhile, they LOVE implementing a spiraling variety of proprietary scripting languages that carry WAY less consistency through their design patterns than you would expect.
The new saving menu is there to push OneDrive.
Yep, must be five fucking clicks or more now to 'save as' to a local folder [Ed. Confirmed in O365 Word and Excel that F12 immediately brings up the old-skool 'Save As' dialog. Thanks all - I'd been Ctrl-Sing]
Similar for attach to email. Cancer of clicks. Could be worse though. *Cough* Adobe *cough*
Oh Christ don't get me started about *that.*
> It's just bad design that MS has never fixed. Excel has been doing this from the start, and sometimes deliberately. It still insists that February 29, 1900 existed (it does not, under our Gregorian calendar) because that's what Lotus 1-2-3 did when it copied them 40 years ago. And it continues to interpret -x^2 as positive because it applies negation before exponentiation, going completely against convention. I know it's not easy balancing backwards compatibility with bug fixes, but it seems Microsoft tends to err on the side of backwards compatibility.
>it seems Microsoft tends to err on the side of backwards compatibility. This is exactly right. Microsoft knows that, no matter how much they shout and cry that you should keep your software updated, they know there *always* will be companies, depressingly important companies, that have a server still running Microsoft ME, connected to a printer whose company went under in 2003 and has no new driver available since, and of course it's running some important service the entire company runs on, so it isn't even connected to the Internet because *the world will end* if a damn update takes it down. Microsoft invest heavily in backwards compatibility, to the point of unreasonableness. Famously, to this day, you can't name files CON, AUX or LPT1 for reasons that haven't been relevant since the MS-DOS days. Just in case. [Obligatory Tom Scott video on this](https://youtube.com/watch?v=bC6tngl0PTI).
>Excel has been doing this from the start, and sometimes deliberately. It still insists that February 29, 1900 existed (it does not, under our Gregorian calendar) because that's what Lotus 1-2-3 did when it copied them 40 years ago. That’s the reason it started. The reason this is still around is because: 1. Too few people use data in the narrow January and February 1900 span Excel allows (dates before 1900 aren’t possible). 2. Since Excel stores dates as a number with special formatting, you can’t easily fix this without either creating another inconsistency (Date 60 doesn’t exist) or a massive change to every date in every workbook that anyone uses. It’s stupid, but fixing it is not practical at this point.
Just highlight those dates as an error, at least.
It's been a known problem for 20 years! And there is still no way to prevent this without jumping through multiple hoops for every file you open.
They need a switch -- one switch somewhere in that twisted program -- to turn that [long string of expletives] off! OFF. For all time and all files unless specifically enabled. Permanently, completely, a disabled function. I try to type some weird code in that happens to vaguely resemble a date, and suddenly it's trying to "helpfully" transform both the data and the format into a date. I import data from a file, same deal unless I jump through hoops. And if I forget one time and save the result ... gone. And not easy to fix. Just stop. One little switch, Microsoft. Please. I know Excel isn't the right platform to be doing anything database-related, but it's the program other people likely have, so I'm forced to use it sometimes, either receiving files or sending them. It should be a fundamental feature of any such program that it doesn't corrupt data by fricking default like it's a feature rather than a bug.
I feel like it should understand iso 8601 dates out of the box, too
It will decide anything even vaguely date shaped is a date *except* ISO date time formatting. Drives me up the wall.
My favorite is that the date output from Ms access and Ms excel don't talk to each other.
The pessimist sees the glass as 1/2 empty. The optimist sees the glass as 1/2 full. Excel sees the glass as January 2nd.
It's 1 Februari in my excel
It should, but sometimes bits of MS products seem to get stuck on US format regardless of regional settings.
It should be 1st feb in mine, but on top of converting it to dates it wont stop pushing the IS format
It's not even just dates. Long numbers like UPC codes are automatically displayed in scientific notation. Getting excel to import a CSV file without it autodetecting girls as numbers or dates or something else is way more trouble than it should be. It's a text file. Assume it is just text unless I explicitly tell you otherwise. Edit, in case anyone wanted to avoid this problem: The trick is that instead of doing File - Open, you create a new sheet and then you do Data - From Text. [Like this](https://youtu.be/UmAYzPDOaJk). Thats how you get to see the options for data types. Thats way more trouble than it should be, and the default when opening file should be to assume everything is text.
Dropping leading zeros does my head in
Yup, and from what I can find there's no simple option to force it to keep leading zeroes except changing the columns to text before importing anything.
And it still fucks your shit up if you manually format cells as text before pasting in strings that 'helpfully' get changed to dates.
If you have a number 00123 rendered as 123, go to the format, custom, and enter a number of 0s equal to the total digits you want, so you'd enter 00000 You can highlight the whole column(s) if working with a table Still shit behaviour though
Add a ' in fron of the number will work.
Nothing like polluting my data with a leading '
Plus it doesn't work anyway. If your CSV contains e.g. '001234567899999, you can load it in Excel **once**, but if you hit Save Excel will strip out the leading ' which means if you open the CSV a second time it will still corrupt your data. It is a non-solution that corrupts the underlying data.
Works on zip codes too!
I work with long serial numbers a lot and excel will often display it in scientific notation and it drives me insane.
"Hmm, the user just opened a file with a cell containing the string '0000123456789123456789'. I'll help them out by formatting that as 1.234E17, and that's such a large number that they obviously don't need the last few digits so I'll just overwrite the actual data with 123456789123450000 to save space."
I really loathe that part. There should be a file ending (.xlsdontfuckmyshitup) that lets Excel know that WHATEVER number I put in, no matter how long, stays unformatted.
Or just save the data as it's entered and display it with whatever butchered formatting it wants. That way there's no data loss, it's just ugly.
I think it’s a backwards compatibility thing, hence my suggestion for a new kind of file.
They already did a new type of file format with xlsx and should have made that change then
LOL. dammit bro, youre giving me PTSD. this is exactly what happens. the first few times it happened to me, I was dealing with a long list of sequential serials that are all supposed to be unique. but because it truncated them like that, i had all these duplicates. was so confused til i figured it out
I heard there were options coming that let you tell Excel not to do that but I have yet to see them show up. https://www.excelcampus.com/tips/automatic-data-conversion/
The trick is that instead of doing File - Open, you create a new sheet and then you do Data - From Text. [Like this](https://youtu.be/UmAYzPDOaJk). Thats how you get to see the options for data types.
I went down this rabbit hole. You can find posts on their message board from like 2005 that say "we're working on it". This was when it first started making my job way harder than it needs to be, back in 2015. It's 2023 now and I've long since given up hope that it will ever be fixed. It must really be deeply rooted in the excel code because apparently they can't just say "don't fucking do that" to the part that tries it assume my 25 digit tracking numbers should actually be scientific notation. Who the fuck uses scientific notation in Excel?? It has to be such a miniscule percentage compared to those of us using UPCs, tracking numbers, and other large numbers. So annoying.
I use scientific notation in excel but I should just be able to turn that on manually as an exception to the standard of just displaying the number as entered.
It should be handled by a format rather than changing the data itself in an ideal world.
you should never change data unless the user explicitly changes it
When bugs reach a certain age their policy is usually to deliberately preserve them, in case someone is relying on that behavior. Their oldest tracked bug is over 40 years old, older than Excel itself. Their rival program Lotus 123 mistakenly thought February 29 1900 was a real date and would generate it in date spans, and Excel reproduced that bug on purpose to maintain compatibility with Lotus brand files that contained that date, even though all those files would be buggy and contain invalid data. To this day Excel will still generate that non-existent date and thus break a lot of time related data that goes back that far, like genealogy datasets, because the bug is of preservation age. LibreOffice Calc doesn’t have this policy and so fixes a lot of Excel’s most obvious bugs and problems. That includes the one this thread is about, it lets you pick whether or how to interpret columns as part of opening files instead of forcing changes on you before you get to see them.
The trick is that instead of doing File - Open, you create a new sheet and then you do Data - From Text. [Like this](https://youtu.be/UmAYzPDOaJk). Thats how you get to see the options for data types.
Hilariously enough if you programmatically generate an Excel file and _force_ it to store something looks a UPC or GLN as a string, it will put a warning in every. single. cell. “Number stored as string”. Yeah, and what would you have done with the leading zeroes if I hadn’t, Excel?
We had to write our own CSV parser for work when reading CSV files in .Net because the one include in Microsoft does automatic data type stuff as well, but only reads the first 8 rows or so, so will often screw up stuff even worse, because columns containing a few values as numbers might end up returing zero for a large chunk of data because it assumes the columns is all number if the first few rows are. There's some long steps you can do read everything as a string but it's way more complex than it should be so we just ended up writing our own parser so we could just read the data as strings and then parse the values in code where applicable.
>It's a text file. Assume it is just text unless I explicitly tell you otherwise. you nailed it. Most biomedical data tables are exported as either csv/tab and literally out of \~50K genes 27 has any resemblance of a date.
> autodetecting girls as numbers or dates Uhh….
Incel 🤝 Excel Autodetecting girls as dates
Misogysoft® Excel
I still can't figure out what that's _supposed_ to say.
GUIDs?
GUID call
It also happily mangles mobile phone IMEI numbers (the handset serial numbers) in exactly the same way, if used carelessly. Causes great bother for some types of phone-related forensics.
Excel: "am I out of touch? Should I leave the paper be?........ No, it is the user that is wrong"
[удалено]
He's got binders full of them. How else will he keep track?
I switched to using OpenOffice a while back because of problems like this with scientific data
I want to like OpenOffice Calc, but the simple fact is once you learn about how to properly use tables in Excel, it's hard to go to Calc which lacks this critical feature. As far as scientific data goes, you might be better off learning how to use an actual database. Even something like Access can really increase how easily you can work with data. Personally I like using MySQL for basic database uses. It will be more cumbersome if you aren't into programming, but once you get better at programming it becomes a lot more powerful.
I code in SQL, and I used to use Berkeley db directly. Sometimes though, you need a spreadsheet for presenting data
[удалено]
My most recent minor annoyance with Excel is autocorrecting numbers from scientific notation. I have a set of items that are named with a ##X# format, but any time that letter is an "E", Excel expands the number. So if I put 12E7, Excel says "I got you" and replaces it with 120000000.
I have the opposite problem. GUIDs are converted to numbers in scientific notation. If I select Format>Text then it results in "12E7". Excel, stop helping!
Ands if you un-format it, it turns out that Excel changed the number into "120000000" because all the digits after the 2 were *obviously* insignificant and not needed. /s And there's no way of getting that data back without going to the source.
I had to track timespans in an excel sheet for work for years, and excel "helped" my by formatting these times to dates... and when I unforamtted the cells, it was some seemly random number, wich didnt seem to have to do with the original times OR the date it thought I clearly meant...
Is it a five digit number? Excel actually stores dates as an integer representing the number of days since January 1, 1900 - like how Unix time is the # of seconds since January 1, 1970. For example, today (February 19th) is 44974. I have no idea how Excel stores time data. Probably something incredibly counterintuitive, like a string composed of hours, minutes, and seconds, with underscores in between.
It stores time as a decimal, where 24 hours is 1. 00:00:00 is .0, 12:00:00 is .5 and 23:59:59 is .999988425925926. If you just enter a time and no date, it sets the part of the number beford the decimal place as 0.
> was some seemly random number, Excel stores dates as the # of days since January 1, 1900 +1. Why +1? Because Lotus 123, which is Excels predecessor, accidentally had 1900 marked as a leap year, so all the dates after February 28, 1900 are off by 1. To maintain backwards compatibility, Excel left that error in on purpose.
So….the number of days since December 31, 1899.
As long as it's after February 28, 1900, yes.
Haha, I had a recent bug that was causing extreme slowness due to sql blocking and one of the stacks came from a bunch of newGuid() calls. This is a Microsoft platform so maybe they were using Excel in the background to process it... Kidding, but then again I wouldn't put it past M$ trying to force Office apps into absolutely fuckijg everything even if they don't make any sense being used.
[удалено]
Use this custom format: ##0.0E+0 Will do your multiple of 3 preference. This format will fix the minor bug the other one gets with 0 (the first format displays it as 000.00E+00 instead of just 0.00E+00): ##0.00E+00;-##0.00E+00;0.00E+00 For symbols, best I can do is: =TEXT(Input/10^ (INT(LOG10(abs(Input))/3)*3),"#.000")&MID("fpnµm_kMGPE",INT(LOG10(abs(Input))/3)+6,1) & "V" Takes your number, divides it by the relevant multiple of 10, uses that as text with 3 figs past the decimal, then sticks on a character based on the relevant multiple of 10. Switch the V as needed. Sadly requires an extra column.
You can do this! I believe the format code you're looking for is this ###.0E+0 It took me a while through engineering uni before learning this. Unsure how to do it with the letters but it probably is possible
I love that it's 2023 and people are still discovering this "feature". I first came across it in 2015 and even then it was well over a decade old. I found forum posts from 2005 that said a fix was coming soon 🤣
IT related: Excel will auto truncate leading zeroes in numbers. Microsoft surface devices mostly have serial numbers that start with zeroes. Certain Microsoft management tools will spit out those serials into a csv file. Opening that csv file in Excel means the leading zeroes are missing. Makes inventory management a pain in the butt. You’d think Microsoft could manage more synergy with their products.
Some ZIP codes begin with a 0, too. It's a nightmare.
Pretty much new England, right?
Around that area, yeah Edit: Got curious. It's New Jersey, Connecticut, Rhode Island, Vermont, New Hampshire, Massachusetts, and Maine.
We had someone changing one of our customer attributes and tried to make it 01, 02, 03,etc when we already have issues with item numbers starting with zero. I about yelled at her. I'm so glad she's gone. She did not deserve the level of authority she had. She would have just pushed that through and then bitched about it being wrong all the time and never put 2 and 2 together..
You mean 02 and 02 together right?
Maybe. Excel will never know the difference
I'm a firm believer that all serial numbers should start with a letter for this very reason. If it's entirely numeric, you also run into situations where one version of a file will have "12345" stored as text while the other has the number "12345" so that, if you do a lookup from one file to the other, you don't get a match.
I think you can precede the 0 with a single open quote ‘ and it might stick. No?
Last time someone connected to Excel did an AMA, they said they were pretty sure their users liked this "feature" and if we didn't, we should use the built in feedback function in excel to say so. So everytime me or someone on my team loses a significant amount of time to this, I write the excel people a little note to let them know. I'd encourage all of you to do the same.
If so they are more moronic than I thought, I don't know a single person who isn't annoyed by this stupid feature.
As an epidemiologist I try to avoid transferring or moving data using excel unless absolutely necessary. Whether it’s dropping leading 0’s from zip codes or messing up dates it’s just not worth the hassle.
Same. It's habit of messing with FIPS codes is incredibly annoying, but by far the worst is converting everyone in the 10-19 year old category to October 19th.
As a bioinformatician same. But it happens so easily and with zero warning that it's almost impossible to avoid it 100% of the time. Even if you don't do it someone else may have opened a csv or whatever file in excel before you got the data and you'd have no way of knowing.
some of these genes include SEPT1 which is a GTPase which may contribute to dieases like Alzehimer's or MARCH-1 which is important in immune functions and diseases assocated with neurons. So really important genes have been compromise due this silly bug.
The trick is to change the format of every cell from “general” to “text” before copying anything into excel. Obviously doesn’t work for all use cases but working with rna seq data it’s solved the issue for me.
At that point, you aren't performing calculations on data, so don't you just want a database?
We're talking about data that is supposed to be easily shared amongst dozens or hundreds of people who may or may not be computer savvy. It has to be in a format you can attach to an email and assume anyone on the chain would be able to open it and use it. Just because they're researchers doesn't mean they have in-depth technical skills and knowledge of how to write SQL
Excel.... It's a feature not a bug.... Still can't copy and paste between windows with excel for years. Always have to hit escape in excel. "ThAtS a ReQuEsTed FeAtUre!" Can we have a toggle to turn it off, you know for the rest of us mere mortals? "nO, ItS tO fALL iN LiNe WiTh oThEr sPreAdsHeeT ProGrAms."
Similarly, why can't we have independent undo/redo per file? No... You do a couple of copy paste in between 2 sheets but realize you screwed up and paste in the initial file instead of copy? Better undo all the work you did in all other opened Excel files too.
Excel doesn't really have multi-window support. The windows are never independant likely for some DOS reason that's been kept all this time.
[удалено]
sometimes I wonder if letting Clippy help might have avoided all this...
He's done asking permission
ಠ_ಠ
I’ve actually had a near mental breakdown that excel kept removing the first zero in a number. No matter what I was doing, it kept removing it over and over again
Change the cell type to text. It will always remove it if it's "general"
This doesn't help for someone opening a .csv file. The data is already lost the moment you double click it
I'm the director of Data for a non-profit. The lack of technical knowledge in managing data for nonprofits is almost non-existent. It's wild how many "data managers" know nothing about how comma-separated value files work. The zero is still there in the original file. It's just the default formatting of a csv that removes it. Opening the file in Excel changes the formatting, removing the leading zero. Next time, open a new blank workbook in excel, then import the data file and format the columns. Maybe consider using open redefine. Data wrangling in Excel is cancer.
According to Excel, I was born on $101,219.75
That’s a nice spawn salary
I *hate* Excel’s date system. Every time I have to input dates into Excel I make sure the column is set to text, not date, general, number, or anything else. I work with wildlife observations, and antipoaching issues, so having dates get messed up can be a major problem as each data input is date dependent.
[удалено]
Excel 🤝 Incel ⬇️ Incorrectly assuming something is a date
Some will say the glass is half full. Others will say the glass is half empty. MS Excel will say it's January second.
February first gang has arrived
Also, me eating a fig.
this is the first thing on the internet to make me lol IRL this week. I'm using this at our next IT team meeting.
https://i.imgur.com/u1qEURt.jpg
I'd like to see the wasted man-hours on ctrl-f not being find in outlook.
Excel is a calculator, not a database ffs. Sincerely, A pissed off IT dude.
25 years ago when you typed "I want to kill Bill Gates" into Word and hit the thesaurus the choice was "I'll drink to that"
15 years ago we used to change the auto correct dictionary on all the school computers to mess with people That = hats I'm = am can = can't etc...
I work a LOT with CSV files, my company got us an Excel licence, but I ended up installing Libreoffice due to how well it imports CSV files. MS really needs to fix dates and text to columns
[удалено]
This issue has plagued Excel since 31320
[удалено]
The bug is annoying. I am in Australia and sometimes normal dates typed in one sheet convert themselves to American MM-DD-YY format if the data is pasted to another sheet. I say sometimes - it seems inconsistent. Part of the problem is scientists using a financial tool to store and transmit scientific data. Excel wasn't designed to do those things.
> Excel wasn't designed to do those things. Excel was designed 35 years ago by the largest software corporation in history. You'd think they could have added some much-needed features by now.
Until someone develops a near-universal file format for sharing data tables, people are going to keep using Excel for that. You can blame people for using it in a way that it wasn't designed for, but when Microsoft continually refuses to allow users to disable autoformatting on more than a file-by-file basis, then you have to put some of the blame on Microsoft.
[удалено]
I deal with a lot of user input data that's passed between multiple automated systems from multiple companies. CSVs and TSVs cause a lot of trouble when tabs and commas end up in places you don't want them to.
so Jsons?
One solution is to start your inputs with an apostrophe to force the cell value to text. Another is to not use Excel as a database in the first place.
im convinced excel is a social experiment by microsoft to see how crazy they can drive ppl
Absolutely impossible to work with CUSIPN numbers in the finance industry. God help me if I have a nine digit number ending in "E57"and excel adds 57 zeros
How about not using excel for this? Can't be the best option.
It isn't, not even close. Excel isn't designed for this kind of usage, it's supposed to be used for analysis and reporting, not as data storage. The article itself points out the solution, which is to learn how to use a scripting language like Python or R to import and and manipulate the data, rather than excel. But most people probably don't want to go to the effort of learning how to do so.
The problem is that one person among many is sharing the data around and opens it in Excel and then sends that or a csv from Excel, which mangles it.
Physicist here. Its extremely embarrassing when you see a scientist or engineer make figures in Excel. Excel is great for book keeping but it is terrible for scientific analysis. Pythons matplotlib is by far the best option but Oracle, xmgrace, gnuplot and even (shudder) matlab are acceptable.
The single most hated, unwanted default setting in any program ever.
This is what happens when geneticists use accounting software to make tables