Hacker News Reader

Comment by corry | Hacker News Reader

Excel elicits some equivalent of Stages of Grief in technical people. First, some version “it’s so dumb the world runs this way, I’ll just port a few of <use case> into a modern CRUD web app and people will prefer it since it will reduce errors and be in the cloud”. Then, “oh hmmm the flexibility Excel brings to the user is really hard to replicate in my CRUD app. But I’ll try.”. Then despair - “arrrg if only these dumb users would realize that the flexibility and power of Excel lead to lots of problems! Use my simple CRUD app, please!!!”. Then acceptance (and awe) - “Excel is UI, a DB, and business logic all wrapped in 1 thing that everyone already knows how to use.”

Not saying Excel is perfect. Lots of us have built or are building products that do a better job at specific workflows. But surely it is one of the best pieces of software we have, and if I could only use 1 piece of software forever, it’s probably Excel.

View on HN

My problem with Excel isn't the principle of spreadsheet software, but that it's bad spreadsheet software. It has a bad expression language and a bad API and bad type-safety and bad automatic type coercions and bad range behavior and bad copy-paste functionality...

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 one of those things where it's bad, but it's the best we have, especially given that in some of the flaws you mention, they're pretty much copied by alternatives who want to be compatible with Excel.

> that they can't fix because they must stay bug-compatible

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.

Do you know of one that does it better in terms of flexibility, data management, programmability, etc.?

Not just automatic type coercion. But automatic DESTRUCTIVE type coercion.

Comically with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty, yet seem to have made no effort towards anything like that.

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.

with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty

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.

Access hasn’t matured because the “easy to make database and app” concept has moved to a focus on PowerApps / Sharepoint. It wasn’t/isn’t a strategic platform and isn’t designed for data analysis.

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.

I suspect your example is only for context, but just in case it saves you time in future, if you can sort the data first then you can use something like: if(vlookup(value,range,1,TRUE)=value, vlookup(value,range,column,TRUE), error_marker). The TRUE returns next nearest match, the IF ensures you have an exact match, and it takes a few seconds to lookup over hundreds of thousands of rows.

> 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.

> they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty

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.

What’s wrong with its copy paste? It’s missing a few things but what’s there is great. The type situation is terrible I agree. I can’t even count the number of times I’ve screwed up a currency conversion or not known what currency a cell was. Why can’t it know that dollars are dollars and when I multiple my euros per dollar I get euros?

Copy/paste can be really weird. Take these three scenarios:

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.

https://web.archive.org/web/20160725070440/http://discuss.fo...

I don't accept that explanation as sufficient considering that Google Sheets handles this just fine.

I think you can also clear the copy buffer instantly by pressing Esc.

Thanks! I don't know if my google-fu has been lacking when I've tried to find a solution before, but I'd never found that info before.

Ah these are fair points. Have probably gotten so used to them by now that I don’t get annoyed by them.

Case in point, one of my favorite Excel Things: https://i.imgur.com/eVbcpGn.png

That's fantastic! Thanks for sharing!

Actually, the researcher who works on this is a pioneer in the field of “example based programming”, which is pretty cool stuff. But it does occasionally produce garbage when given garbage (what did you expect February to map to?). In the space of simple programs that map Jan to Janet Jackson, that one doesn’t seem too bad!

What's that feature called?

So, from my previous career working with spreadsheets, I've noticed that most aberrant behaviors in Excel are easily circumvented through slightly-above average features the average user just isn't aware of.

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.

> You can probably also change the automatic type coercion

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...

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

Use Get and Transform Data -> CSV

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

Unfortunately, people working for our clients are not trained to do this. Or are but take the quicker option and hope that it works.

FWIW, fixes/improvements for some of those fundamental issues are coming and/or available in preview releases.

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.

Oh my [deity], what are they creating?!

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’m working on a (very beta) product that hopes to address a subset of your complaints about spreadsheets. https://inflex.io/ Based on your phrasing you might have already heard of it. In particular my two blog posts mirror what you said: https://inflex.io/blog

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.

I really like your approach. I will give it a try!

If you really want to hit your face on a desk, wait until you encounter a user who doesn't know how to tabulate or sum anything in Excel, and uses it as a big grid-based text editor, manually using a desk calculator to sum up columns, or perform various other formulas on some selection of cells.

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'd almost prefer to see this behavior than the handful of people I've encountered who do this same exact manual math in ..... Word.

For many people they’re all just digital paper - and they work with it like they would with a notebook.

Why the OS doesn't allow you to select some text and perform system-wide actions on it is a mystery. Calculate, Upper/Lower/Sentence case, Search, Dictionary, etc etc

Like this? https://www.muo.com/tag/add-useful-options-mac-os-x-services...

Seems so, https://www.gingerbeardman.com/services/

I encountered exactly this when my brother went to film school. The students would rotate roles and it was his turn to be the producer. One day I came home from work and he was having problems with his Excel-based budget sheet. I noticed that his row of sums at the bottom of one sheet had hard-coded values rather than formulas. Thinking he had inadvertently pasted over them, I told him, "Look, you killed your formulas." "What are formulas?", he asked. Turns out that he had been tallying up the columns with a calculator and then typing those totals into Excel.

My first job had a coworker who didn't know how to sort the spreadsheet. She printed it out and cut it into strips, then sorted them while watching TV at home in the evening, then taped it back together. I was shocked and in the decade or so after that I have yet to run into anything like that again.

My introduction to Office/office horror was a colleague writing her message in PowerPoint, attaching it to an email in Outlook and sending it through. It turned out to be a standard practice in the academic institution I was in.

Not quite the same, but in several community email lists, sending emails via a JPG or DOCX is the norm. Type it up in Word, and attach it to the email.

My institution's administration does this. Every message from them is typed in Word, then attached to an email whose body says "Please read the attached message". They do this for every message, no matter how trivial.

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

A loong time ago when I first encountered spreadsheets, I thought it would be a neat way of making the monthly reports, just adding up columns and rows of a 30x30 table. My boss was not happy about the use of "sum" and made me add up the rows and columns on a desk calculator to check that they were right.

Also, if you think this type of user is going extinct as the new generation who grew up with computers will intuitively understand how to use the basic features of common software.....wait until you see a 24 year old with a bachelors degree doing stuff like this. Turns out watching youtube on an ipad from 2 years old to adulthood does not confer magical computer skills!

A 24-year old was born in 1997. iPad was released in 2012. Your comment does not make sense :)

Building on this, when purist engineers trash Excel, I point out that Excel cell formulas are the only case of Functional Programming going truly mainstream.

This is the talk that I first saw that builds on that: https://www.youtube.com/watch?v=0yKf8TrLUOw

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 use Excel in my data processing workflow. I'll grab a small subset of the data to play around with in excel to see what it is I actually need out of it. Then, I'll do all of the real processing in python along with matplotlib for any charts I need to produce. Excel could almost entirely handle my use cases but there is usually some bit manipulation I need to do because my data is usually telemetry encoded as ASCII. Also making if-else statement in Excel is exhausting.

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

Excel is exceptionally good at telling you what the shape of your data is. I love being able to just turn something into a table and then see the distinct values as a filter.

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.

Another great tool for data exploration is visidata! This is a good example of what it can do. https://www.youtube.com/watch?v=N1CBDTgGtOU

Visidata is great. My main problem is that I use it so infrequently so it's hard to build up the muscle memory to use it really effectively.

Isn't all excel data tabular in shape?

>Also making if-else statement in Excel is exhausting.

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.

It looks to me datasette (https://datasette.io/) could fill the same need: load the data into SQLite and explore it with a host of tools and visualisations, all profiting from the power of SQL

I thought I was the only one who did this kind of ribbing!

If the functional programming language requires that each expression must be exactly one line with no comments, I'll pass thanks. Long Excel formulas make Regex look like poetry.

If you have a long expression, you can break it into multiple ones by storing some intermediate results in another cell. You can also hide such cells or put them in another sheet.

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.

3D Excel would be intriguing. A dimension on which to store intermediate calculations.

Sounds like https://en.wikipedia.org/wiki/Lotus_Improv

I would love just getting the ability to resize the Evaluate Formula dialogue box text area.

Formula -> Trace Dependencies/Precedents is also super handy! I have a short VBA loop that applies it to all selected cells. It makes it very easy to visually spot one cell reference that's wrong, because 20 cells connect to corresponding cells with parallel lines, then one isn't parallel.

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

The most painful thing for me, but not to ordinary users, is that the formulas are localized in the user's language.

+1 The devil has many names. VLOOKUP and SVERWEIS (German) are two of them.

This is a big one, especially if your edition isn't English/US.

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.

You can put the comments in any of the plethora of cells you're not using, or in a Note or a Comment.

On longer formulas, I do Alt+Enter while editing the formula to get a new line, then 4 spaces for an indentation. Just like that, you can keep track of start/end parenthesis.

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

The new Let() function gives you multi row formulas, with local variables.

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...

Look up Power Query, which has been part of Excel for a while.

Yea, power query is fantastic! Only caveat is you can’t (yet) use the editor on Mac, though it’s coming.

Simon Peyton Jones, one of the creators of Haskell, has been on that point for awhile too (https://www.slideshare.net/kfrdbs/peyton-jones). I think it's his main area of research working at Microsoft actually.

I think a lot of the love for Excel by office workers comes down to empowerment. Workers like being empowered to solve their own problems. Excel does very well. You as a non-programmer office worker can quickly make a spreadsheet that solves your problem. You can update it on your schedule as your problem changes or to explore new possibilities. You give that up as soon as you go to a CRUD app. Need to change something, put in a a request to IT who might get around to it in a week if you are lucky. Wheat would have been a few minute feedback loop with Excel is a multi-week slog.

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.

Exactly.

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.

Well, I observed the opposite effect when a complex Excel spreadsheet was replaced by an Access DB that allowed a small company to discover that they had extra assets for like 200K USD. And the original Excel author realized how much more sense the data made once he saw visualization of data constraints and relations.

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

Oh totally.

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!

I think this can be distilled as "most tech folk are extremely bad at valuing each business use case separately."

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.

> Need to change something, put in a a request to IT who might get around to it in a week if you are lucky.

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.

Moreover, in most non-software companies, IT has covered their asses by prohibiting users from accessing programming tools.

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.

Ah but most secretly have Powershell which is super powerful.

And .NET compilers actually, living on C:\Windows\Microsoft.NET\Framework\

My IT department contacted me because their "security" software alerted them that I ran a Powershell command.

I've contracted in places where cmd is blocked, but Powershell is not. The mind boggles.

(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...

I have the experience of having worked in UNIX shops where $HOME was mounted noexec, everyone got development rights over access groups and we only got to use official IT tools.

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.

A point to note on Excel's empowerment I have discovered. The main reason for its ubiquity is because it's mostly already there and doesn't require the odious purchase order process to be navigated and then an odious IT onboarding process navigated. So fuck that, let's just cram another turd into Excel. That sort of overlaps with your IT request thing but at a higher level I think.

We economists call this rational, as folks are minimizing their costs.

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.

You could also say that it's IT externalising their costs. They don't want to be responsible for another system, so all the work and maintenance gets pushed on to end users.

Indeed!

" put in a a request to IT who might get around to it in a week if you are lucky."

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.

I love excel, it's fantastic. I also hate excel, it causes me so many problems. Usually in the way of proliferating "shadow systems" of this sort:

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.

Now we have Airtable and Smartsheet, where you can have a proliferation of undocumented shadow IT systems core to your business processes AND pay by the user every month for the privilege! That's progress, right?

My general theory of shadow systems is that they're usually the product of either:

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.

Or 3 - Users have a new requirement outside of any existing solution and perceive building their own solution in Airtable as being quicker, easier and cheaper than dealing with the people in IT.

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”.

Shadow systems are prevalent in large MNC's where systems are decided centrally without being able to take into consideration local needs.

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

I've been involved in the selection & implementation of multiple systems like this, and there was a reasonable effort to include stakeholders from functional areas. After the RFP responses and selection of potential vendors, they each come in we had basically a week-long conference with sessions for functional areas, technical areas, implementation, etc before selecting the vendor. Even then, the problem wasn't lack of consideration for local needs: The problem was that pretty much every question asked of the vendor's demo team was "Sure we can do that, it's just a configuration!" or "That's a customization that a few of our customers have implemented, we can piggyback on their work to do the same for you!"

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.

> 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

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

I just realized something. Riffing on how Excel is UI/DB/biz logic in a huge unbounded zone, I think the reason it’s so popular is that people use it to build PRODUCTS. They don’t call them products, don’t sell them, but a well-built and complex spreadsheet is basically a mini app.

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.

I knew an accountant that built an accounting software in Excel. He had different tables for different invoices, and Excel spreadsheet would take that data, do whatever accounting needs to be done and prepare all the reports that need to be sent to various institutions. If I remember correctly, he had a Excel file for each customer he had and just used Excel for all.

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.

I don't think its even that rare.

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-...

I once seen one used in risk evaluation for line of credits

@saberdancer: "When I saw this, I was blown away."

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.

You've ironically made the same mistake yourself :D

The joke in the aviation industry is "Which enterprise software runs the most airlines?.. And the punch line is Excel.

Some people I work with have made very complex Excel "machines" that have been in use by many people for decades. One of the new engineers spent about six months porting one over to MATLAB and there were all kinds of magic numbers in the formulas that turns out were there to account for some bugs in some test equipment we bought 20 years ago. We had purchased a new system that would output data that wasn't compatible with his old model so the new engineer ported it over and now we get clean, consistent results. This is for antenna measurements so there's really been little advances in decades in the fields. The advances only really exist for antenna simulations.

The backbone of many statistical institutions such as the Office for National Statistics is Excel. I worked on porting a lot of complex Excel systems to Python that had been around for 5+ years.

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 worked with a bunch of huge Fortune 1000 companies that do similar things. Model each contract in its own excel file then every month put in that month’s data and have the excel model spit out how much you owe in the form of a formatted invoice.

Long time ago I found myself as the sole point of contact between 200 managers+workers office & regional HR office. I had recently came across excel, so made a simple sheet with names & staff numbers. That sheet, over the time of 3 years, evolved into a mammoth of sheet. sheet with db_ prefix listed names, joining date, title, salary, timebase, country of origin, skills, driving license number, expiry & many such. Other sheets pull up some columns, & I manually add D for day shift in date column, or N for night, H for holiday, F for Friday. Another one pulls this data per week for D only or N only or both. Another one pulls data & prefills the leave request sheet for individual staff. At any time I change the dropdown week value, & I can print a summary of man hours, who worked, how much expenses on HR. No where in the sheet I used VBA because of that macro dialog box.

It's interesting that nobody's really leaned into Excel as a RAD tool. Once you have your spreadsheet functioning how you'd like, feed it into a "despreadsheeter". This could be, at a basic level, locking and hiding every possible cell, to running it in an embeddable Excel-like runtime without a lot of UI clutter and foot-guns, up to parsing the formulas, providing a form editor to build the inputs into attractive screens, and spitting up Python/C#/Java/a native binary.

Have you tried microsoft access?

I believe that generating database schema from excel would require an AI. In wordprocessor you can have heuristics like "user bolded whole paragraph and added newlines - consider that a heading" to guess the document structure but hardly so in spreadsheet.

Filemaker Pro is such a product but it looks like they just released the last version. Print companies use it like crazy but I've not seen it used anywhere else.

It used to be the Access of Apple shops.

Yeah news flash. Not everything has to be called a fucking product. Good Lord you MBA types

While much different and not as accessible as Excel, R Shiny is also a very rapid way to built data "products".

> if I could only use 1 piece of software forever, it’s probably Excel

“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).

Emacs also has a user interface that has a much steeper learning curve than Excel.

That seems pretty irrelevant to the specific quote I was addressing, but in general it’s unfortunate there’s no tech-illiterate focused distro AFAIK, because it seems like CUA-mode would get you far enough to appeal to a lot of pretty mild power users (eg. there are stories of non-technical secretaries learning to program without realizing it by learning Emacs macros).

The specific quote exists as part of a broader context in this thread in which the accessibility of any proposed alternative to excel is relevant. Making it relevant to point out that while emacs might be a valid alternative for some, it is not for most users.

> The specific quote exists as part of a broader context in this thread in which the accessibility of any proposed alternative to excel is relevant.

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...).

Well I guess we're splitting hairs at this point. No need to go another few rounds, I'll just thank you for pointing out Spacemacs, which looks pretty interesting.

The painful thing about Excel is that it's a self-fulfilling prophecy. It's built on extensive UX research that tries to support the existing corporate culture and workflow. In so being it reinforces that norm, which generally encodes the assumption: the people handling this data don't understand how or want to write code. It's extremely difficult to build a spreadsheet that gives those people as much power as Excel does.

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.

Excel is great until you need to fix/extend an app that is now integral to all company operations that was made 20 years ago by that guy in sales that was good at computers and has long left.

Sure, but that has nothing to do with Excel. You could say the same thing about basically any legacy application on any stack.

Yes,it doesn’t have anything to do with Excel, except Excel is there, has been there for 25 years and keeps getting updated and supported.

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

It does have something to do with it in the sense that it locks code in a proprietary format that does not allow code traceability and source co trol, and does not favor best practices like code comments and commit comments. That does make maintenance harder.

Replace "Excel" with "Computers" and I agree

It just occurred to me that the situation is isomorphic to any runtime environment. I cannot imagine transplanting a shell script, C program, Java program (OK, maybe except for a Java program), Win32 app (OK, maybe that also), or a web-app from 20 years ago onto a modern version of the runtime and expect it to work without any major changes or fixes to account for the inevitable cases where the outside world simply decided that "we do not do things this way" any more. Cross-site scripting, password strength, political and timezone changes, many of those trip up applications too.

Shell and C should be fine. To be honest any pure computation should be fine. Your interactive HyperCard stack if you get the right emulators would be ok. MAME, sure. But anything depending on the persistence of networked resources will not work. Other than Phil Greenspun all the endpoints have changed. CGI with no external dependencies or Ajax should work except it got pwned along the line.

Except most likely it wasn't bash shell, or maybe it was, but it was written on an HP-UX and never ported to anything else.

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.

Then you realize it's pretty amazing that an app that was built 20 years ago by a non-programmer without any assigned budget fulfilled the needs for so long.

This is literally what I spent my last two weekends doing. Not as something I wanted to sell, but for one of my own personal spreadsheet use cases. Thought I could build a web app to suit my needs better, but after some 30+ hours working on it, I'd rather just go back to the spreadsheet.

I always hated excel. Then I met Airtable.

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 a crud app is great for "directing" the user through the common changes they will make to the data, like approving a document by clicking a button for example. But a simple tabular view of your data is really great too if you can get away with it. Sometimes it's not that useful, if for example the data is hundreds or thousands of columns wide.

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.

The real grief is tooling and infrastructure for software development itself.

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.

Spreadsheets are one of the original "bicycles for the mind". They empower users to do what users want to do, more efficiently.

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.

So much true.

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.

> Then acceptance (and awe) - “Excel is UI, a DB, and business logic all wrapped in 1 thing that everyone already knows how to use.”

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

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

They did not kill anything, they brought them into VB.NET, and I can tell many VBA users are quite happy to transition into VB.NET, as I experienced in a couple of life sciences corporations.

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.

Except that all the little controls and plugins DIDN'T transition to VB.NET--and they're what made VB6 useful to common people.

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.

Not enough money to buy little controls from Telerik, ComponentOne, GrapeCity,....?

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.

Yup, I write data analysis tooling but still one of the first things I get asked is "can I have all the raw data in a CSV?".

Never display data in a tabular format without sortable columns, and a link to the raw data as CSV/json, preferably CSV, and if possible a link to the SoR for the data source.

SharePoint / Office is overlooked by the developers you write about here.

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 ...

> ...if I could only use 1 piece of software forever, it’s probably Excel.

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.

It's the What-If and Goal-Seeking behaviors of Excel that CRUD app developers consistently overlook.

My main problem with Excel is it does many things but it's never the best tool for any job.

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.

For most people in most companies, the choice is between hacking something together in Excel over the weekend that will mostly work (with a few bugs); or to give your project to the IT department and wait at least six months for them to fail anyway.

Excel is an amazing piece of software if you are disciplined enough to use it well. Hell, I've even solved some Advent of Code problems on it: it's really powerful when you enable iterative calculation

As I always like commenting when I see these kinds of comments: many thanks for (in a general sense) providing consulting/freelancing/general small business work well into my future.

I second that. While it has plenty of flaws and is getting very dated, Excel has been a phenomenal tool in the land of the analysts and others who actually need to get work done and move on.

I had a big OS migration project, where we moved all the RHEL machines to Ubuntu. We had a big set of Python scripts that would go out and query a bunch of databases and logging system to get the current state of the migration each day, but we published the results as a spreadsheet that was emailed rather than some website. It worked very well to get people data they needed and to put it in a form where they could ask their own questions of the data without talking to anyone else. It worked better than I expected, having not used excel for decades.

Excel ate my data four times in the past week. I prefer LibreOffice; I think it is much easier to control. Also I can tell LibreOffice not to eat my data and it listens to me.

Excel spreadsheet is still underrated even in 2021. Regardless of the fact that governments and whole companies use it religiously.

There's whole business that run on Excel.

They shouldn't, but it's amazing.

Yes, but Google Sheets or shared Excel files in OneDrive. Otherwise the magic that is Excel gets lost in various versions in people's email attachments. Collaboration + Spreadsheet though truly is a versatile tool.

This isn't the problem you think it is.

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.

In principle, I agree.

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.

You hit the nail right on the head there.

Feels like he's confusing "Excel" for "spreadsheet program with scripting abilities"

Can you think of another “spreadsheet program with scripting abilities” that is already installed on every business computer on the planet?

Google Sheets is for the many companies that use Google. It’s definitely not ubiquitous, but then Excel isn’t either (my company only has a few Excel licenses for the people who are customer facing, everyone else gets Google Sheets).

It's more like conflating spreadsheet program with scripting abilities into just being Excel.

Excel is the JavaScript of nonprogrammers.

Reply on news.ycombinator.com