4 ms·

The simplicity and flexibility of Excel make it great for one-offs and random hack jobs that don't require a new application or full-fledged report. At the same

The simplicity and flexibility of Excel make it great for one-offs and random hack jobs that don't require a new application or full-fledged report. At the same time, it has some minor idiosyncrasies that can be infuriating and make me wary of trusting it in the hands of average end-users who need to enter/analyze important data. Automatic type conversion is my favorite. I can't even count the number of times I've received Excel spreadsheets where data was completely lost because of it. Leading zeroes at the beginning of your account number? Excel will gladly chop those off for you. Order number looks like a date because they used the year as a prefix? No worries, Excel will change that to a standard date and completely forget the original format. Maybe I'm just crazy, but I don't think a business-oriented application should favor convenience that much more than data integrity.

afterburner 14y ago
"No worries, Excel will change that to a standard date and completely forget the original format." I hate this particular one so much.
dan1234 14y ago
I've run into those type conversion problems before. The data is usually still there, it's normally that Excel has decided to use a certain display format for the cell (such as a date). Simply set the formatting back to "General" and all is fine. Usually.
- kyllo 14y ago
  "General" is not what you want. General enables the automatic type conversion. "Text" turns the type conversion off.
- Irregardless 14y ago
  It's possible I've missed some setting somewhere in the tangled web of menus, but I'm fairly certain the data is lost in the case of both those conversions. For example: Type 09-2012 into a cell and hit Enter. It will probably turn into Sep-12 (or some other variation depending on your default date settings). If you convert that to Text or General, it turns into 41153 -- the number of days since 1/1/1900. Excel recognizes all dates from 1/1/1900 through 12/31/9999, so it happily converts anything from 01-1900 to 12-9999 into a date. You can create a function to convert them back, but it would have to be based on the assumption that the format was originally ##-#### (which you may or may not know for certain depending on where the spreadsheet is coming to you from).
jackalope 14y ago
This is why I'm continually amazed that Excel is used in business at all. I use a command line program to record my hours and can report the time I've worked on a project to the second. Unfortunately, my Excel-based timesheet sucks at math, and shaves off time when it converts my HH:MM:SS totals to the decimal figures (HH.x) required by payroll. It's so bad, sometimes the values displayed in the columns don't add up to the displayed total if you enter them in a calculator. No sweat, I only get paid for the displayed total, and at the end of the year I compute the difference and add it to my last timesheet. I can't imagine trusting my business finances to a program that can't deliver a reasonable amount of precision. I wonder how many fortunes are made by people well-placed enough to exploit Excel's weaknesses ("Hmmm, Excel shows that we made only $10,000,000 at our bake sale. What should I do with this leftover $700,000?" or "I can use Excel to show you that I owe you less money than I actually do.").
- mattmanser 14y ago
  Let's be clear, this is a type conversion issue for seconds, Excel's not going to start adding up normal numbers strangely. And the point of this article and the point he's trying to drive home is that it lets non-programmers actually use their computers for computing. For most businesses a home-brewed excel spreadsheet vs a $50,000 custom program that any of us here wrote? The harsh reality is that the Excel version written by Jane from accounting who's the Excel whizz or even the smart college temp will probably be better and cheaper than anything we could ever give them.
  - taproot 14y ago
    Until Jane leaves for greener pastures. I do understand what you're saying though, there is a time, place, and trade offs for everything related to computing. (imho) Most 'businessy' systems can and should start out as spreadsheets, it lets the business people solve the problems with process design, and prove its real-world value without bringing a costly developer in. The developers (like myself) should be there to take the codified mess that results, clean it up, improve usability, bring in more stability, and accountability. - Sadly, that is rarely how things go.
Gustomaximus 14y ago
Depending on how you want to use the data, you may be able to get around this problem by adding a ' infront of the number. It won't show on the cell display and acts as an instruction to hold leading zeros.
Gustomaximus 14y ago
Depending on how you want to use the data, you may be able to get around this problem by adding a ' infront of the number. It won't show on the cell display and acts as an instruction to hold leading zeros.
EEGuy 14y ago
>Automatic type conversion is my favorite. Amen to that!! Yes, "Clippy" (Excel) gets a little too enthusiastic at times, but the missing piece here is metadata. So Clippy does what Clippy can without metadata, and the results can be laughable at times. My fix for that, _if I'm sourcing the data_, is to source it as tab-delimited (not CSV-delimited) from the clipboard into an empty sheet area twice: With the first paste, I 'visually' correct the columns' incorrectly guessed data types, but Clippy oddly doesn't attempt to reformat the data. No worries, I just select all, hit DELETE, then do my second paste using the same starting cell as my first paste. Clippy doesn't interfere this time, and my data comes up all beautifully typed as I want it. I don't know why, but the DELETE key doesn't kill the data types for cells, and I'm glad it doesn't! Not visible, not obvious, but very useful.

afterburner 14y ago

"No worries, Excel will change that to a standard date and completely forget the original format." I hate this particular one so much.

dan1234 14y ago

I've run into those type conversion problems before. The data is usually still there, it's normally that Excel has decided to use a certain display format for the cell (such as a date). Simply set the formatting back to "General" and all is fine. Usually.

kyllo 14y ago
"General" is not what you want. General enables the automatic type conversion. "Text" turns the type conversion off.
Irregardless 14y ago
It's possible I've missed some setting somewhere in the tangled web of menus, but I'm fairly certain the data is lost in the case of both those conversions. For example: Type 09-2012 into a cell and hit Enter. It will probably turn into Sep-12 (or some other variation depending on your default date settings). If you convert that to Text or General, it turns into 41153 -- the number of days since 1/1/1900. Excel recognizes all dates from 1/1/1900 through 12/31/9999, so it happily converts anything from 01-1900 to 12-9999 into a date. You can create a function to convert them back, but it would have to be based on the assumption that the format was originally ##-#### (which you may or may not know for certain depending on where the spreadsheet is coming to you from).

jackalope 14y ago

This is why I'm continually amazed that Excel is used in business at all. I use a command line program to record my hours and can report the time I've worked on a project to the second. Unfortunately, my Excel-based timesheet sucks at math, and shaves off time when it converts my HH:MM:SS totals to the decimal figures (HH.x) required by payroll. It's so bad, sometimes the values displayed in the columns don't add up to the displayed total if you enter them in a calculator. No sweat, I only get paid for the displayed total, and at the end of the year I compute the difference and add it to my last timesheet. I can't imagine trusting my business finances to a program that can't deliver a reasonable amount of precision. I wonder how many fortunes are made by people well-placed enough to exploit Excel's weaknesses ("Hmmm, Excel shows that we made only $10,000,000 at our bake sale. What should I do with this leftover $700,000?" or "I can use Excel to show you that I owe you less money than I actually do.").

mattmanser 14y ago
Let's be clear, this is a type conversion issue for seconds, Excel's not going to start adding up normal numbers strangely. And the point of this article and the point he's trying to drive home is that it lets non-programmers actually use their computers for computing. For most businesses a home-brewed excel spreadsheet vs a $50,000 custom program that any of us here wrote? The harsh reality is that the Excel version written by Jane from accounting who's the Excel whizz or even the smart college temp will probably be better and cheaper than anything we could ever give them.
- taproot 14y ago
  Until Jane leaves for greener pastures. I do understand what you're saying though, there is a time, place, and trade offs for everything related to computing. (imho) Most 'businessy' systems can and should start out as spreadsheets, it lets the business people solve the problems with process design, and prove its real-world value without bringing a costly developer in. The developers (like myself) should be there to take the codified mess that results, clean it up, improve usability, bring in more stability, and accountability. - Sadly, that is rarely how things go.

Gustomaximus 14y ago

Depending on how you want to use the data, you may be able to get around this problem by adding a ' infront of the number. It won't show on the cell display and acts as an instruction to hold leading zeros.

Gustomaximus 14y ago

EEGuy 14y ago

>Automatic type conversion is my favorite. Amen to that!! Yes, "Clippy" (Excel) gets a little too enthusiastic at times, but the missing piece here is metadata. So Clippy does what Clippy can without metadata, and the results can be laughable at times. My fix for that, _if I'm sourcing the data_, is to source it as tab-delimited (not CSV-delimited) from the clipboard into an empty sheet area twice: With the first paste, I 'visually' correct the columns' incorrectly guessed data types, but Clippy oddly doesn't attempt to reformat the data. No worries, I just select all, hit DELETE, then do my second paste using the same starting cell as my first paste. Clippy doesn't interfere this time, and my data comes up all beautifully typed as I want it. I don't know why, but the DELETE key doesn't kill the data types for cells, and I'm glad it doesn't! Not visible, not obvious, but very useful.