MS Excel (all versions) has two characteristics that have me fed-up; so I'm hoping someone with broad spreadsheet experience can make some recommendations. The two problems:
1) MS Excel can not handle more than 65,000 records. I often need to be able to work with several hundred thousand records.
2) The way MS Excel formats cells--especially as related to text--is highly problematic. Example: I have a spreadsheet that was originally formatted as GENERAL. I add a column and format those cells as TEXT. I now define values as, e.g., 4/07..4/08.. etc. Excel converts these to dates. OR - someone sends me a data file that was originally formatted as all TEXT (perhaps because they wanted to retain leading zeros, or whatever). I want to convert some fields to NUMBER; so I can perform calculations. Excel shows that the cells are now formatted as NUMBER, but Excel still will not perform basic calculations. Even if I export the file as space delimited text and import that into a new spreadsheet, the cells are formatted as TEXT and I have the same problem. This becomes a real time-consuming problem.
When I read reviews, they always emphasize features but seldom give any insight to reliable basic functionality. My opinion is that Microsoft goes crazy with gimicks but fails to deliver on reliable bread-and-butter functionality. If anyone can recommend a spreadsheet package that does the basics reliably and without a hassle, I would like to hear from you.