Or how it drops leading zeroes off of part numbers and no amount of custom formatting will convince it that something that starts 000#### should be displayed as is. Sure, we can pretend it’s text, but that’s annoying in its own way.
Other annoyances:
formulas that don’t update results automatically even when they’re configured to do so
the difficulty of creating a generic sheet to sheet reference in a calculation (“look up the value in a cell x on the worksheet three pages below this one”) I shouldn’t have to write a macro to do this.
And dear lord, don’t try to enter a 16 digit number. I had to batch load about 1000 membership cards for the theater I was on the board of and it would just reformat them as an exponent (or something, I can’t exactly remember). I had to jump through all sorts of pre-formatting hoops before finding the solution. And of course MS’s excel forum is utter garbage. “I understand that you’re trying to do X. Did you try turning it off and back in again? How about slapping a macro in there?” Ugh.
Also, there is some way to enter a csv and adjust how it displays before it fully imports (you can set the variables to commas, spaces, etc and adjust the grid lines). It’s one of those old-school settings I have to search for every time like the classic conditional formatting mode, but it is helpful once you excavate it. Unfortunately, it still will just auto-format to what it thinks is correct, so I usually have to go in, pre-format a column and then copy/paste values only. (ETA: Ah, I didn’t see @Bonivus_elderheart’s response. Pretty sure that’s the tool I’m remembering)
But once the data is in correctly, there’s nothing like it!
Excel, really? I mean, they had to rename a gene because when the biologists typed its name into a spreadsheet, Excel stubbornly turned it into a date…
My wife recently asked me what program she should use on her locked down by IT laptop to edit photos (i.e. add arrows and lines to a picture from her mobile phone) and I said “PowerPoint, then save as PNG” that’s the only thing left it’s good at…
I have that same problem. IT gave me launchers for a bunch of Adobe programs but no licenses so every time I want to redline a vendor pdf it’s off to PowerPoint.
You mean a lethal device that should be banned? I agree. But seriously, while I’m sure it’s great for doing accounting, which what it was designed for, in fields like science it is a terrible tool leading to corrupted data. As a computational biologist, I often deal with data sent to me in Excel, which many bench biologists use to store data and to do basic processing (because the ubiquity of Office many people who are not particularly into computing only know how to use Office and a web browser). But there are so many pitfalls for using it for important data that a actual scientific tool wouldn’t have. For example, it’s been known for years that Excel will silently turn many gene names into dates – and rather than actually fix the problem there are actual proposals to solve it by changing the names of genes!
It wasn’t my intention to hijack this discussion with my unseemly enthusiasm for spreadsheets, but here we are.
I absolutely understand your frustration, but I think changing the names of those genes was probably the right solution; the ubiquity of excel, the prevalence of old, incorrectly formatted data sets, legacy versions of the software, and a large contingent of the (FTA) “computationally illiterate” 1 would have rendered a patch fix moot. Sometimes our tools evolve to accommodate our work and sometimes our work has to accommodate our unevolved tools (see @anothernewbbaccount comment upthread).
I don’t really use Excel the way it was intended- I’m not an accountant and I don’t do large dataset analyses. For me, it occupies a niche between pen and paper calculation and apps I’d have to code but wouldn’t use often enough to justify the effort.
1 Not so proud member since forever; I’ve got a card around here somewhere
It would have been funny if it hadn’t been so infuriating.
For some people there seems to be an inverse correlation between technical skills and the ability to communicate effectively. Aspiring STEM graduates should be required to at least browse through The Visual Display of Quantitative Information before they get their diplomas.
Seriously, I get that macros do a lot to automate things in Excel, even if I do not have the expertise or inclination to do so myself.
But all too often ‘use a macro’ is shown as a fix for things that should not need fixing but do and which should be fixed in the application itself. An application that cannot do some of the simple things noted in this thread without the user having to learn how to write and use a macro leaves a lot to be desired. Macros having to be invented as work-arounds by users is a terrible advert for said application’s usability. @cannibalpeas is right to note how spine-shudderingly frustrating it is to read that the answer to your problem is…
The other problem is that macros break. I think that’s less of an issue now with Office Online, but holy crap 10 years ago it was massively frustrating. And honestly, things like conditional formatting and pivot tables handle a lot of what people write macros to do, but trying to figure them out can take hours.
macros!!
