Excel has the nasty idea to modify what you type in the cell. If it seems date-like or number-like, it will convert the content, possibly corrupting interesting details in the process.
You can of course counteract this with a quote or with formatting, but you have to remember to do this consistently.
I understand why they do it for a spread sheet: It is optimized for calculating. But this tendency makes excel untrustworthy for general processing.
Sheets isn't consistently the better option, but it does shine for specific use cases. The three main reasons I'll reach for Sheets is:
- Heavy text munging. The builtin regex functions have no equivalent in Excel without dropping down into custom
- Quick and dirty data pulls and third party integrations. Especially any that involve Google services (since the auth process is less painful). I used to use both VBA and App Script pretty extensively, and could switch between the two without much issue. But I use neither as frequently as I used to, and it's significantly easier to ramp back up and ship when I use App Script rather than VBA. Excel supports Javascript now, but are only usable if you can ensure it's going to be used exclusively in newer versions of Excel.
- Sheets I create for others (especially shared by multiple users) that I know I'll need to maintain and support later on. The access controls, change auditing & revert capabilities, and standardized/centralized execution environment all remove entire classes of support needs and the associated cognitive overhead when triaging issues.
That said, there are certain times I prefer to use Excel.
- Pivot charts are amazing and have no equivalent in Sheets.
- Pivot tables are far more powerful than their Sheets counterpart.
- PowerBI is fantastic (except for the lack of Excel for Mac support. Which can still view the results of PowerBI, but can't do any editing).
- When connecting to internal data sources. Getting access to data sources, for business teams, is a royal pain in the ass. Legitimately so, since it's rare for a business team to have a resource with a technical enough skillset to truly be trusted with direct access to anything. Getting access that's reachable outside of the intranet (where App Script would run) is virtually impossible.
There's a gene called Septin-6, abbreviated Sept6. You can tell when gene expression data has been through an Excel cut/paste cycle, because Sept6 been converted to September 6. Oh Excel, you think you're so smart.
You can of course counteract this with a quote or with formatting, but you have to remember to do this consistently.
I understand why they do it for a spread sheet: It is optimized for calculating. But this tendency makes excel untrustworthy for general processing.