[jhbadger]

Not to mention the famous mangling of gene names that look like dates. Yes, I know scientists shouldn't be using Excel -- I agree. But I'm a computational biologist. Experimentalists know how to use exactly three programs 1) Microsoft Word 2) Microsoft Excel 3) Microsoft Powerpoint.

[Neeek]

I find myself working with four letter codes to identify wards and facilities at my work, inevitably I have to compare my SQL output to someone's spreadsheet. No excel, I didn't mean March 02, just give me MAR2 like I asked. Drug codes that start with a '.' are another headache, they will be auto formatted as a decimal number. Worse is when they want to put info from a spreadsheet in to the database, no one understands the need for consistent field formatting :'(

[coroxout]

I feel your pain. We have a lot of ID values with leading zeroes, which Excel loves to strip, and data with numbers in parentheses, which Excel's CSV loader thinks should be negative numbers (this is apparently common in book-keeping).

I keep getting sent spreadsheets which were made from CSV data by people who didn't know how to set the columns to text - to be fair, if the file extension is .csv then Excel won't even let them set the column formats - and having this latter category turn up as -ve numbers not only looks weird but ruins the sorting. Even 1, 11, 2 is better than sorting "backwards".

[mistermann]

Have you tried bringing in with PowerQuery instead? Much more flexible.

[Neeek]

I haven't tried pulling Excel -> SQL with PowerQuery yet, only really dabbled in presenting data the other way. Do you know if this works easily with a document that utilises a lot of macros and VBA? Or are we still talking flat CSV.

[mistermann]

What are you talking, Excel with VBA is importing/consuming the data, or it is the datasource for something else?

I assume Excel with VBA is importing/consuming the data in which case Power Query / Power Pivot would likely solve most of your problems. Refactoring a large complicated existing spreadsheet to use PQ rather than your existing peocess, hard to say how tough that would be.

[jgamman]

i was reading a paper about how Excel gets some standard statistical tests wrong too - the US Stats Association i think did a compare/contrast with a range of packages. some of these bugs have been there since 2002 or so. i lived in Excel for many years but wasn't a power user - i got to like it but sadly, it got used for everything even when it really, really shouldn't have.

[intended]

I read an article on that, but it was clearly mentioned that this was an issue with older versions of excel (excel 03 07? iirc).

[jgamman]

this link seems quality and you're right - seems like the excel team took it seriously in... 2010. that's still pretty bad!

http://www.practicalstats.com/xlsstats/excelstats.html