It's been a while, but for example name-mangling can be circumvented by prefacing an entry with a single quote (analogous to how the r character raw string marker is used in Python). You can probably also change the automatic type coercion, buried somewhere in the settings, since adding that single quote prior to the string mangling might be difficult in Excel. (But data entry is often manually done anyways with these kinds of setups.)

Usually in cases like these, the problem is that the user isn't aware of the feature they need to solve the problem they're having. (Granted, Excel's whole appeal is easy onboarding for non-technical users.) Save for obvious limitations like data size or overly intricate business logic better suited for an actual language, Excel is well-suited for a broad range of business and academic use cases.

Maybe for manual entry, but not when importing a foreign format like one if the many CSV variants that we run into.

> buried somewhere in the settings

This is a huge problem, as where such settings exist they are by their very nature user-local. Back to our old frenemy CSV for one (sadly not hypothetical) example: too many times our clients shuffle data (exported from various places) around in text format and someone along the chain has the wrong locale set so when ISO8601 dates get silently converted it is to a format the next users version isn't expecting but silently assumes is correct because by some "miracle" the ambiguous day/month number combination is "valid" both ways around. The result is then imported into our system which is blamed for its answer not making sense...

Use Get and Transform Data -> CSV

You can control the typing per column when you import, split columns, etc.

I still see people getting date and numeric fields mangled by excel on analysis processes that take literal days to solve because it ends up recursing over all the cells multiple times, and yet even showing them a simple Python or whatever program that does the same thing in minutes, and the result is still put in excel, the next time they need something similar ... Here's another excel abomination.

These aren't dumb people. They've been trained by Excel that the world is awful, slow, and error prone.

I think that's pretty much what they were going for with MS Access, it seems like Access hasn't matured nearly as much as its long life should have allowed. Possibly because it would eat too much into SQL Server. They have to keep its feature footprint too small to appeal to users who only barely need something like SQL Server.

As for Excel == slow, yeah-- I actually do like Excel, it's in my toolbox, but waiting 5 minutes for a vlookup against a few hundred thousand records to complete because I need one single column in another sheet is maddening when it would take nearly no time at all if I loaded the data into Python -> Pandas.

I replied elsewhere in the thread about me working on a new spreadsheet alternative that’s like a reactive Haskell. (Attempting to preserve community norms by not self-promoting more than once per submission.)

One thing that I’m planning is that for any table/array/tree etc. that exceeds a certain size, the data itself and any outputs of functions that work on that data will be offloaded into a real database as rows which can be indexed. Either via SQLite, using PostgreSQL, or even BigQuery (but I don’t trust Google much), this would let users transparently grow their data sets from 1,000 rows to 100,000 to 1,000,000 rows without having to suddenly switch languages or representations. (As an online product this is easier to do, but I think a desktop app equivalent could do it too.) Array functions would in the end be streaming functions rather than literally loading all data in memory.

In Inflex tables are literally arrays of records, but as the language is purely functional and statically typed, the compiler has a lot of freedom to rewrite code into more efficient representations (e.g. streaming), as done in Haskell or SQL.

For the sort of stuff Access does you should do it within Excel, which now has the ability to build a fully relational dataset using PowerQuery and PowerPivot.

But yeah, excel can be slow! It’s not designed for big datasets without PQ but inevitably gets used for it because it’s easy and available. I can still do a lookup in excel quicker than I can write one in pandas even if it takes 5 mins. As a hint, the new XLookup strictly dominates Vlookup and depending on how the data is structured/sorted can be many times faster according to the search parameters.

They have already done this (although not with SQLite), see “PowerQuery / Get and Transform” which is a full type safe data cleaning/transformation interface, MCode language, and then you can link this typesafe data into excel and then into PowerPivot + DAX / Measures.

All of the above is already built into excel as standard, just nobody seems to know about it. Open excel and click data, get and transform, open PowerQuery editor.

Like new+custom data types: https://www.microsoft.com/en-us/microsoft-365/blog/2020/10/2...

And LAMBDA for custom functions: https://www.microsoft.com/en-us/research/blog/lambda-the-ult...

I get the impression that in recent years Microsoft's become more willing to make fundamental changes to Excel. Still early days but I'm cautiously optimistic that things are getting better.

I first read those thinking "cool, I'll have to let nerdy me have a play with all that". I actually often use Excel for various bits & bobs, and must (as a database guy and ex-Jack-of-all dev) shamefully admit to finding it useful and sometimes really liking it. But...

Then my mind moved on to "oh hell, what holes are our clients going to dig for themselves if they catch wind of some of this?".

Even when talking about existing functionality that you might want to use along with these new tools they say "It took us some time to understand the precise rules that drive this behavior". It took Microsoft's research people some time to understand the ins and outs of features in Microsoft Excel and they talk about this with apparent pride in a paper published by Microsoft.

I look forward to being witness to the minting of brand new expletives, when the reporting team are handed a gargantuan KPI calculations workbook that uses these tools, and asked to reproduce it in our environment (but make it work properly as "there are a few bits that occasionally give odd results and have to be adjusted manually sometimes" - not that they'll mention those oddities until we've banged or head against them a couple of times or in UAT they ask "but how do we tweak the financial sprocket count result for dept X only, when Y happens?"), which they assume we can do that in practically zero time...

I haven’t added a date type yet, but it will be a distinct type (not just a string), with explicit import. Pretty much similar to the Haskell time API.

If you like the idea I would welcome any criticism. I have 50 things that I think the product needs, but your input would help.

They likely aren’t bugs. Some may have been. But they’re now expected behaviour. Moreover, by and large, they’re how non-programmers see the world.

1) Copy some cells. Type something into another cell before pasting it. Your copy buffer is gone, you can't paste what you copied.

2) You copied some cells, pasted them. You arrow over a few cells to inspect a formula and then hit enter: You just pasted over the surrounding area with whatever you've copied previously.

3) You copied some cells, you pasted them somewhere. Now you want to insert a new column, but when you right click the option is missing, replaced with "insert copied cells". The clear the copy buffer you go to random empty cell, type a space to clear the buffer (see #1 above) and now you can insert a new column.

On the plus side though, copy/pasting formatting is pretty useful, so is transpose.