But when someone sends you a CSV file (a very common format for database exports and EDI), Excel does the type conversions automatically when you open it. You don't get a chance to change the cell format to Text beforehand. The ' workaround is a huge time-waster if you are dealing with a large amount of data, plus it screws up the file for use outside of Excel.
There really needs to be an option to turn off all type conversion globally for all files in Excel.
The real problem arises when you ask someone to send you data in CSV format. If, in between exporting it from their database and sending it to you, they happened to open and save it in Excel, you will get corrupted data. Usually the sender is blissfully unaware of what Excel's automatic type conversion does to their data.
CSV has been made unreliable as a format for data exchange between companies (aka EDI) largely because Microsoft decided that CSV files should always be opened in Excel by default in Windows. At the very least they should turn automatic type conversion off for CSVs.
Excel ignores double quotes around the fields, it just uses them to escape commas inside the fields.
The official way to do it is to insert a single quote at the beginning of every field that you don't want auto-converted. In practice this is a time-wasting pain in the neck and ruins your data for use outside of Excel.
There really needs to be an option to turn off all type conversion globally for all files in Excel.