There are so many boneheaded things wrong with Excel that they can't fix because they must stay bug-compatible.

So I keep dealing with text getting mangled into dates and iso8601 dates getting mangled into God knows what and papers getting examined and people finding out "oops your data got screwed up by some counterintuitive behavior of excel and your findings are all wrong".

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.

also, prepare for the user to suffer some sort of existential crisis or breakdown, possible tantrum after you show them how to do these tasks automatically with a simple excel formula.

I asked them why they do this, but haven't received a reply, perhaps because my question was in an attached gzipped Postscript document.

I found it interesting, fun and great. Still, I would stick to using Excel for the personal analysis of data and not as the language/tool on which software is developed: data & programming logic intermixed, the default way to index cells, the difference between normal cells and array cells, and tables, etc., the fact that some things are just not possible to do fully programmatically without creating your own VB functions and therefore asking recipients of what looks like data to accept running arbitrary code in their machines, etc.

I could also just use MATLAB but I'm more used to using python.

Sure, SQL and most environments can tell you distinct values of a column and their counts, but they are much slower and more cumbersome to explore with. I often start out with broad filters then go through many more specific ones depending on what I see from the first filter.

About a year ago Excel added =SWITCH, which is effectively {Logic Test | Output} pairings executed until one is true. Many if/then/else nestings could be eliminated with it.

This might sound like a hack, but being able to see intermediate results makes debugging complex expressions easier too, so in a sense it is working as intended.

Ctrl+Tilda toggles show/hide formulas, which helps identify input cells when you inherit a messy spreadsheet.

Also the number formats and locale settings can cause quite a bit of trouble, if its not English/US. Alot of data warehouse setups output files with U.S formats even in Europe.

It's not perfect, I wish it would automatically format that way, but it certainly makes maintenance easier.

https://insider.office.com/en-us/blog/let-names-in-formulas-...

And it's a hack, but you can put text comments inside an N() function and add them into your formulas.

https://support.microsoft.com/en-us/office/n-function-a624ca...

There is no other tool of which I am aware that enables the common person to sit down at a computer and make the computer do useful real world work work for them in a flexible way than Excel. I have used it for budgets, lists, form entry, and reports. In addition, it provides a very smooth on ramp into programming via the record macro button, which will write the VBA function that does what you are doing in the GUI. Most of my VBA functions started out as record macro, and then were edited to generalize. Truly an amazing piece of software.

If the change happens at all. The IT department might say "we don't have the time to do that," or "other users depend on the app being like this so we can't change it to be like that," or even "why do you need that, your justification isn't good enough."

Excel is the non-programmer or non-admin equivalent of "I'll just bang that out in [php | python | perl | powershell]." We've all done it, we will all continue to do it, and yes it will look like an absolute mess...that probably winds up powering a Fortune 50 company.

I get that most of us live in a land of "Of course I have an IDE and compilers / REPL" at work.

You know what IDE and compiler IT can't take away? And the last option that's always available at any company?

Excel.

(For those cursed to work in Windows enterprise land, most of what is being talked about here is that Powershell is essentially .Net, e.g. '$DateTime = New-Object System.DateTime -ArgumentList 2015, 10, 10')

https://mcpmag.com/articles/2015/11/04/net-members-in-powers...

Many are spoiled by having their own devenv nowadays.

Ironically cloud computing is going back to those days, where the cloud IDE and shell only allows for IT validated tools.

As a service owner of some fairly significant technology services, every time one of my colleagues “fixes” some business problem with an enterprise IT system, it’s measurably worse than a well-defined excel driven process.

My favorite was when our travel and expense system moved to some Oracle monstrosity, and we literally hired full time staff to data enter expenses that were submitted on paper because field service folks were spending 5-10 hours a week on expense submission.

Another great example is when almost anything is “improved” by some sort of quick and dirty interim servicenow task.

But then IT of bigger companies treats Access as not a real DB and often disregards it with the same contempt as Excel.

My beef is that tech folk have a tendency to make themselves the customer, or maximize convenience to IT over whatever the actual problem is. Internally, the politics of the company tend to dictate this stuff. In the marketplace, software is usually much better!

Tech folks: That's a business use case

Business users: This is something I do 500x a day, this I do 10x a day, this I do once a month

PMs are supposed to be the ones to do this work, but they tend to be technically clueless and blinded by project timeline spreadsheets.

BAs usually don't take the time to actually ask the user "Why are you doing this?" and get all the nuances.

IT says no to or unnecessarily complicates acquiring a needed product? Fine. In Excel it goes.

It never matters how un-auditable the mess is. That never enters the calculus.

A week would be brilliant. Months or years is more realistic. And every change has to go through five levels of management and business analysts before it goes to some clueless people at an outsourcing company and costs insane money.

Them: "We need you to do X with our data."

Me: "Okay, where do you see the data in the (enterprise ERP) system, I'll find the tables from there."

Them: "We use Excel because the system doesn't have a place for this data."

Me: "We upgraded 10 years ago. There's a place for it. How is office X getting the data they need from you?"

Them: "Oh we just share an excel file each week and they add their own stuff to it. Here's 300 weekly files for the last 5 years. We really need the analysis in a few days."

Me: ::tries to concat 300 excel files, discovers 50 variations on column arrangements, names, data types etc., tells department--:: "You aren't getting this in a few days. The good news is that you don't have to use Excel anymore because when I'm done, your data will be formatted & loaded into the ERP and all you'll have to do is put new stuff there too, and all analysis & reporting will be automated."

Them: "But it's the full-time job of one of our staff to handled all of this stuff in Excel."

Me: "Have you ever though that you needed more help in your department? Well, congratulations, you just got one 'new' full time employee."

People at least then generally walk away happy, except for me as I stitch together 50 variations on 300 different excel files. That's a slight exaggeration though: Most of these offline shadow processes aren't quite that large, and staff turnover is usually fast enough that such a process doesn't age 10 years past its point of obsolescence.

1) Poorly trained users that don't know how to use the system for their needs

2) A poorly chosen system (or system implementation) that doesn't fill the needs of the users.

2-b) This includes systems that technically do the thing users need it to do, but in such a bad/slow/sloppy way that no one wants to use it.

I'm not sure which is more prevalent... I've had to say "Your system will do that for you" plenty of times, but I've also been on the other side creating shadow systems myself. One such was an ad-hoc data integration using python's splinter library to automate data download from a system w/o an API to a very kludgy "select .... from dual union" series of SQL statements to populate some of the data needed for a daily analysis. The ETL folks were already in integration hell on over-scoped projects, and also not accustomed or tooled up to run arbitrary python code-- especially any code they didn't write-- to power a process.

I’ve been in a position before where talking to IT to get something small made means absolutely tonnes of work producing business cases, requirements documents, asking for funding, only to be told the huge bill to produce something internally isn’t worth paying, for something that takes a few hours to build and implement yourself sitting next to the team that’s going to use it. Ok it’s not supportable, but it’s airtable!

In my current role as a ops/logistics consultant, when I talk about IT change to clients a lot of the time the question comes back as “can we find a way to do this without talking to our IT team? You can’t get anything done through them without it costing at least X”.

Local parts of the MNC's end up using the centrally required system, as well as their own local shadow system.

Then implementation comes along and what were claimed to be configurations are more like "You just rewrite this COBOL module, but updates will break it so you'll need to rewrite it for every update." And those shared customizations just never materialized. In one case the actual "built in" functionality shown via screenshot slides literally didn't exist in the actual product. ::ahem Oracle:: and resulted in a lawsuit for it, among other things like like deadlines they didn't meet and & refused to complete without being paid an additional exorbitant fee, even though it was a fixed-price contract.

Also yes, there are IT departments that take a request that's basically "Please create this database trigger" and run it through multiple layers of review & approval & business case justification, ROI, etc over the course of months for something that would take about 10 minutes to implement in a test environment, another hour to sufficiently test, and another hour to pass to UAT so they can confirm it's working as desired, and push to production.

So you're absolutely right: I missed a causal category for shadow systems. Honestly I don't even think shadow systems are inherently bad in some cases: I just think there should be formal documentation lodged with IT so there's institutional awareness of it, and the departure of a single employee who created it doesn't cripple a department. This crippling of a department is something I've actually seen.

> Me: ::tries to concat 300 excel files, discovers 50 variations on column arrangements, names, data types etc., tells department

Had to laugh seeing as how familiar this is in my company.

What else do we have that’s this powerful? And don’t say Airtable, although they clearly “get it” - it’s not about files, it’s about building mini products.

It wasn't a mini app, it was a complex application used in "production" for at least 5-10 years. It's likely it is used today as well.

When I saw this, I was blown away.

As an example, there's a popular idle game called "ngu idle", full of complex overlapping and intertwined progression systems.

Someone made this spreadsheet to help you optimize your play. Some of the worksheets in this have very carefully considered UI and complex data flows. Its totally inscrutable if you've never played the game, but its more complex and well crafted than many apps on my phone:

https://docs.google.com/spreadsheets/d/1S1JXe3kZeqzxBOVXMo2-...

There were teams of people whose job it was to produce one report over a timeframe and they had the process to a fine art.

The main problem came when members of said teams left. Macros locked behind passwords, magic numbers in formulas, the wheels could come off very fast.

If Excel had a more robust functionality for versioning and documentation it could be even more of a powerhouse than it already is.

I've seen something like that, that took a whole weekend to produce a report. I suppose it does the job, as long as the accountant doesn't mistake 30 for 0.03 (30%) in a formula.

“And thus we see that people with prolonged exposure to nonfree software need special help.” *Commits sin against the Church of Emacs with proprietary DuckDuckGo JavaScript*

Emacs has ~10 spreadsheet modes, including one for free-form calculation and one for reading Excel files (https://www.emacswiki.org/emacs/SpreadSheet).

Right, which is why I tried to address the next claim:

> while emacs might be a valid alternative for some, it is not for most users

I think a distro like Spacemacs but with cua-mode / UX targeting Office users instead of with evil-mode / UX targeting vi users could make it fairly viable for many or even most users, it seems that it already is for many users, like the secretaries I mentioned in my other comment, who considered themselves non-programmers if not non-power users (https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu...).

This is frustrating for people working in an Excel shop who know how to code. Most of them realize their coworkers don't want to learn to code. They just wish their skills could be useful.

I can’t speak to what gets maintained in Excel, but compared to some Win95 program, it’s in much better shape.

Likewise the C code might eventually be written in K&R C still, make assumptions of byte ordering on the MIPS and memory access semantics of workstation it was originally targeted for, and with luck use SGIPro extensions.

Airtable is basically MS Access for the current age and it's absolutely amazing.

When dealing with it, I realized what makes Airtable so wonderful and different from excel: It's typed! Each column has a type which allows airtable to build this easy to use featureset.

I think that's why something like airtable is great. It gives you the best of both worlds. All the data is backed by a simple spreadsheet, and it has an API so you can create the more custom UIs to help direct users with common tasks.

If it didn't cost a fortune to create and modify CRUD apps, Excel wouldn't be so popular.

Why does Airtable, which is Excel in the cloud with a few trinkets cost so much and do so little, despite 100+ million having been poured into it?

Because developing software is a nightmare every step of the way.

Until that's addressed, we are stuck.

They have their downsides. But they allow a non-programmer express some logic and arithmetic in an interactive, iteratively built, visual reactive program.

This is nearly unparalleled in the modern software landscape.

I have built many personal finance and analytical tools for my own use with Excel and Google Sheets in few mins and have extended their features as needed from there.

I have been using some of these sheets for years and are better to my needs than buying an app or subscribing to a SaaS web product.

We had a programming language that matched that description ...

Visual Basic 6 -- and Microsoft killed it because it wasn't making them enough money.

Having used VB back in the day, the only thing missing are a couple of people still not getting that VB.NET with Forms is just the same deal, even Me is available.

And the hot code reload in VB.NET is nowhere near as useful as it was in VB6--and that was how common people debugged code.

I can go on and on.

VB.NET was not a useful replacement for VB6. I still know tons of businesses running VB6 code and, when they finally have to migrate, it won't be to VB.NET.

Interactive .NET sessions have been working for several Visual Studio releases, at least all the way back to 2010.

The only issue is the anti-VB.NET atittude from some VB circles.

Office/Excel clientside => SharePoint "Office Server" serverside => then write apps integrating with SharePoint.

SharePoint is an application platform with Excel services on top of .Net. (so that multiple people at the same time can work on an Excel sheet via a web front-end)

It is ment to make the step from local application to server application.

It has auto import / workflows / api / taxonomy / search / etc ...

Google Sheets gives Excel a real run for its money. It's less powerful in many ways (example: getting data is more complicated) but makes up for it with the ability to collaborate and run multi-platform. Sheets is one of the more underrated products in the Google Apps portfolio.

Disclaimer: I reached the awe stage on Excel many years ago.

If I could only use one piece of software forever it would be emacs. I find it sad that Excel would be considered in the same light. It makes me think of caged animals at zoos.

They shouldn't, but it's amazing.

The most common use case is via seed document. People grab the seed doc, modify for their specific case, input data, get results, stuff into Powerpoint, done, next.

Excel files in onedrive don’t work very well. Some features are disabled (even merging cells), so very quickly you now have various versions plus a version on onedrive. You may think there is one master file but there probably isn’t.

Google sheets are better (and plotting and pivot tables are much easier to use) but there’s always someone who downloads as excel and makes a new version. I almost think google drive with much more friction to export would be better.

:D