It completely screws up numbers if the column is a number and the number is over 15 digits long.
I work in insurance in Brazil, by standards of our regulatory body, claims numbers must be a string of 20 numbers (zfill(20) if needed). You can’t imagine the amount of times excel had fucked me up rounding down the claim numbers, this is one of the first things I teach to my interns and juniors when they’re working with the claims databases.
Sure, but sometimes you need to do a pivot table and get transformed, or you while working you accidentally made it a number, or just because excel being excel it import the number as scientific notation, or any other case.
The row limitation seems, to me, like an actually-good thing. Excel is for data where you might conceivably scroll up and down looking at it and 1M is definitely beyond the ability of a human even to just skim looking for something different.
An older version of Excel could only handle 64k rows and I had a client who wanted large amounts of data in Excel format. “Oh sorry, it’s a Microsoft limitation,” I was thrilled to say. “I have no choice but to give you a useful summarization of the data instead of 800k rows (each 1000 columns wide) of raw data.”
Some time ago, I heard a story of CS and Econ professors having lunch together. The Econ professor was excited that Excel was going to release a version that blew out the 64k row limit. The CS professor nearly choked on his lunch.
Dependence on Excel has definitely caused bad papers to be published in the Econ space, and has had real world consequences. There was a paper years ago that stated that once a country’s debt gets above 120% of GDP, its economy goes into a death spiral. It was passed around as established fact by the sorts of politicians who justify austerity. Problem was, nobody could reproduce the results. Then an Econ undergrad asked the original author for their Excel spreadsheet, and they found a coding error in the formulas. Once corrected, the conclusion disappeared.
Some interesting facts about excel I learned the hard way.
Not really related to what you said, but I’m still sore about the bad data import that caused me days of work to clean up.
I work in insurance in Brazil, by standards of our regulatory body, claims numbers must be a string of 20 numbers (zfill(20) if needed). You can’t imagine the amount of times excel had fucked me up rounding down the claim numbers, this is one of the first things I teach to my interns and juniors when they’re working with the claims databases.
Could you import the column in as text to preserve it?
Sure, but sometimes you need to do a pivot table and get transformed, or you while working you accidentally made it a number, or just because excel being excel it import the number as scientific notation, or any other case.
The row limitation seems, to me, like an actually-good thing. Excel is for data where you might conceivably scroll up and down looking at it and 1M is definitely beyond the ability of a human even to just skim looking for something different.
An older version of Excel could only handle 64k rows and I had a client who wanted large amounts of data in Excel format. “Oh sorry, it’s a Microsoft limitation,” I was thrilled to say. “I have no choice but to give you a useful summarization of the data instead of 800k rows (each 1000 columns wide) of raw data.”
Some time ago, I heard a story of CS and Econ professors having lunch together. The Econ professor was excited that Excel was going to release a version that blew out the 64k row limit. The CS professor nearly choked on his lunch.
Dependence on Excel has definitely caused bad papers to be published in the Econ space, and has had real world consequences. There was a paper years ago that stated that once a country’s debt gets above 120% of GDP, its economy goes into a death spiral. It was passed around as established fact by the sorts of politicians who justify austerity. Problem was, nobody could reproduce the results. Then an Econ undergrad asked the original author for their Excel spreadsheet, and they found a coding error in the formulas. Once corrected, the conclusion disappeared.