3 ms·
Excel is great and versatile, yet lacks some critical features. - It's easy to load data from database or API without VBA, yet impossible to write updates back
by janci 5y ago
Excel is great and versatile, yet lacks some critical features.
- It's easy to load data from database or API without VBA, yet impossible to write updates back without VBA. With VBA it's still messy string concatenation of SQL queries.
- VBA is security nightmare
- Version control is bad. Distributing spreadsheets in emails and collecting changes back is nightmare. Sharing a file on network drive lacks fine-grained permission control.
- It's hard to maintain proper normalized relational data model. It's impossible to abstract the normalized model from the user (i.e. show labels in selectboxes but store ID's)
- It's locale dependent. Date formatting, column separator (comma/semicolon separated), even function names. Unusable in international data exchange.
- It mixes formatting, logic and data. Impossible to make reusable blocks. New lambda functions help somewhat.
And so much more.
- dfox 5y agoExcel is locale dependent in a weird way. The internal model (which includes the function names) is completely locale independent and only the UI is internationalized (according to OS locale) and localized (according to Excel language version). This is not a bad construction, but breaks down spectacularly when you start to use Excel as any kind of real programming or data analysis environment, because then you get to the cracks in the user-visible “API surface” where the internal/external format distinction is somewhat smeared.
- ogogmad 5y ago> Impossible to make reusable blocks. The click and drag down feature results in a kind-of code reuse. It bypasses the need to consciously name things. Is this generalisable? For small scale code, these concerns may be overkill.
- somat 5y agoClick and drag down is the opposite of code reuse, it is effectively a one time code generator. In fact it is one of the main reason I have largely abandoned spreadsheats, the behaviour I actually want is found in sql views. Calculate all items in column with one expression vs calculate items in column with unique expressions that were copy, pasted then transformed for each and every row. The other main reason I abandoned spreadsheets is row level integrity. too many times data that goes together in a row has drifted apart(sort on column subset is main culprit), another inherent problem solved by using a relational database. the solution to having excel at every desk is to have postgres at every desk. the code will be just as bad but the data integrity will be better.
- mint2 5y agoBypassing the need to consciously name things is one of the worst parts of excel. Imagine taking a data pipeline and renaming all columns and variables alphabetically and then asking someone to check the business logic Also it pushes people to include excessive parts of formulas into a single calculator rather than breaking them into comprehensible and testable chunks.