Comment by narush | Hacker News Reader

Comment by narush | Hacker News Reader

Fantastic blog post. I highly recommend reading it in full, and also checking out the work of the European Spreadsheet Risks Group, and Felienne Hermans specifically (referenced in the original post). I've been working on a spreadsheet startup [1] for the past 8 months or so and those folks have a large amount of absolutely upsetting-but-helpful research on spreadsheet usage/errors.

After the past few years working with spreadsheet power users, here's how I like to think about them: 1. Most spreadsheets are just used a lists/trackers [2]. 2. Some spreadsheets are very calculation heavy, and are better understood as complex software projects (usually modeling something; s/o to those PE mfs) than as anything else. 3. Spreadsheets make the transition between (simple list/tracker) and (complex software project very fluid. This flexibility usually means that the (complex software project) that is created is buggy as hell.

Spreadsheets give not-super-technical users an incredible visibility into their data. Spreadsheets give not-super-technical users a way to program data transformations in what I would argue is the most generally intuitive way that exists. Spreadsheets give not-super-technical users tools to build software without introducing them to proper software development methods. Ya know, like maybe a test. Or a code review. Or no global variables. Etc.

If you want to see how we're attacking the spreadsheet problem, check us out. Feedback highly appreciated! [1]

[1] https://trymito.io/hn [2] https://www.joelonsoftware.com/2012/01/06/how-trello-is-diff...

>Spreadsheets give not-super-technical users a way to program data transformations in what I would argue is the most generally intuitive way that exists

I don't think spreadsheets are a good way to program data transformations at all. Data transformations are inherently a pipeline/function abstraction, and even non-technical users understand that. Spreadsheets don't offer a good way to decompose a data transformation process and inspect each step for correctness. This alone makes them prone to calculation errors. Also, tables are an afterthought to spreadsheets, therefore even trivial joins are always a pain in Excel.

I have a good practical experience with building visual pipeline-based data transformation tools [1] for non-technical users and they have been a success.

[1] https://easymorph.com

> Data transformations are inherently a pipeline/function abstraction, and even non-technical users understand that. Spreadsheets don't offer a good way to decompose a data transformation process and inspect each step for correctness.

They absolutely give you that ability; you break the transformation across many cells. Spreadsheets are probably the most successful functional reactive programming paradigm set of languages out there.

>you break the transformation across many cells

In theory, yes. But the reality, nobody does that unless an error is apparent and needs to be investigated. The vast majority of calculations in Excel spreadsheets are done using cumbersome, multi-line, unreadable formulas that try to pack as much logic as possible into one expression. Such obscure formulas are the main reason why lots of business-critical Excel spreadsheets contain grave errors, many of which are are never discovered.

It is like programming, there are people that keep doing goto and methods that take two screens in Excel, and there are those that bothered to learn lambda, powerquery and addons.

> good way

But it is an accessible way. Your average Excel user can perform any number of useful transformations with simple formulas, and then throw that into a pivot table for useful aggregations (which is also a transformation).

> tables are an afterthought to spreadsheets

Excel power users have been solving this with VLOOKUPs for decades.

> therefore even trivial joins are always a pain in Excel

Trivial to a trained user. Excel has more advanced data modeling capabilities, which includes implicit joins when creating PivotTables from tables with relationships.

https://support.microsoft.com/en-us/office/create-a-data-mod...

Lookups in excel are fine, at least now we have XLOOKUP but there's something depressing about an excel model which contains multiple massive grids of lookups. I find such things are much easier in a real join, where you can get your data into long format then write a single line of code to manipulate meaningful named columns.

The link leads to an article on PowerQuery which is a Power BI product. PowerQuery can surely do joins, but it doesn't operate within Excel's data model - cells and sheets. It's an external tool from the Power BI family from which integrates with Excel to a certain extent.

In a similar fashion you could've posted a link to running an SQL join query from Excel (which is surely doable for a trained user). Technically it's possible, but it would be wrong to pretend like the join is done in Excel.

> but it doesn't operate within Excel's data model

Power Query is an Excel add-on, and has been around much longer than Power BI and has nothing to do with it. This has been possible for almost a decade, since Excel integrated a robust in-memory columnar database capable of dealing with millions of rows.

> it would be wrong to pretend like the join is done in Excel

The join is done in Excel, I don't need to pretend. That you think Excel is just a bunch of sheets and cells is your own mistaken mental model of what the program is and can do.

Here's another reference:

"Now that Excel has a built-in Data Model"

https://support.microsoft.com/en-us/office/create-a-relation...

Can you point me to an article that shows a simple way how to do a simple SQL-like join (no aggregation) of two regions in the same spreadsheet? Two source tables, and one result table - all in the same spreadsheet.

Something that is as simple as this: https://www.youtube.com/watch?v=RYCtoRTEk84

I sent you two links with information, including images that show the process.

Here's a video of the process:

https://support.microsoft.com/en-us/office/merge-queries-and...

Here's another video:

https://www.youtube.com/watch?v=BV3srtI20Bo

Thank you. I didn't know this. I wouldn't call the process easy, but it's relatively straightforward.

sometimes all you have is a hammer and a screw though. Not everyone is a computer scientist.

Sometimes I want to do something quickly, and I dump 20,000 rows from a db query into csv/excel and fiddle. Tableau is often nicer for pure visualisations.

The downside is that something useful you make keeps getting extended and added on to. It is hard to identify when you should switch and do that extra work to move it. And then, you know, tech debt forevs

Spreadsheets provide a spatial mapping (cells) to represent data transformations over time. Users are more comfortable working w/ spatial paradigms, as humans have a better sense of location in space than in time. Thinking in terms of "transformations over time" is difficult to reason about, specially in programming.

The point about spatial representation is spot on. The problem is that in Excel calculations tend to be presented as huge unreadable formulas with no intermediate steps. Thus the spatial representation actually only covers the first and the last step, but rarely something in between.

The fact that you had to use so many big words to explain why spreadsheets aren't a good way for users to "write some numbers here and then calculate some numbers from those first numbers over here" says to me that your definition of "good" varies a fair bit from the definition these users are using.

Joins are great and relational databases are super useful but these peoples' problems are 99% solved by "store a list, maybe do some arithmetic."

I don't know why we think the user is some child and try to abstract away any and all complexity from their job, which ends up leading to some proprietary solution with huge inefficiencies somewhere compared to a flat file and a script in python or R. Software like excel is often seen as a way to do stuff you could do in R but without having to write code.

Imo that thinking is wrong because it makes this assumption that doing anything at all with a gui is easy and frictionless. I don't know how to do anything in excel. If I have to look up how to make a plot in excel, I will have to find some article on the internet of how to do that in my particular excel version. Excel is tough software to use that only becomes easier after you spend time with it (hint hint, just like programming). If I don't know how to make a plot in R, I might also have to find an article. It's no different, no harder, usually easier in fact, to just do the job in R. All these common tasks in R or excel are all 5 min articles worth of information you have to cram in your head.

It's not hard to code, children do it. I'd love to see spreadsheet power users realize that they could be working with much simpler things like parsing plain text files with a few lines of code rather than trying to open some macro heavy spreadsheet on the shared network drive that takes 5 mins just to open on the work issued workstation. I'd guess it would be up to business programs and accounting programs and all these other college majors to actually teach classes in python and R, rather than what they do now which is teach classes in excel. Imo the entrenchment in excel is rooted in ignorance to other (often simpler) options available with python or R, than in any actual critical proprietary features offered by excel. Microsoft is probably pretty happy that their software is so baked into these different academic curriculums, keeping it alive in industry going forward for an entire working persons career after college.

People aren't learning excel because Universities are part of some Microsoft conspiracy. They are learning it themselves, or from each other.

My mother self-learnt excel, first as a to-do list, then a money tracker, then she learned some simple equations to keep track of weekly spending.

How in R would someone do that? With a nice graphical view? I agree Excel has many many issues, but people use it because, in my experience, it super easy to use and in particular let's you easily mix data and code.

People are also learning excel because that's the only way to program in a big company without going through "official IT", which won't listen to what you have to say if you're not a $100 000 project they can offload to a third-party.

> People aren't learning excel because Universities are part of some Microsoft conspiracy. They are learning it themselves, or from each other.

At least in germany children are often forced to learn excel in school because the official curriculum includes "office software" which is an euphemism for "microsoft product training". This has certainly not developed from necessity. Even someone who thinks school should only be a preperation for the job market would agree that employees who are able to help themselves are better than ones who know how to use a specific version of Excel.

I'd rather everyone was taught Excel than a very minor set of people learn spreadsheets by themselves. Barely anyone uses spreadsheets in UK it seems

I'm really not against teaching spreadsheat software since it's a really useful tool. I just don't like that it's thought very microsoft specific and in depth. Students could benefit way more from some basic computer (science) literacy.

>It's not hard to code, children do it.

It is hard to code, and average children don't do it. The fact that exceptional children do it more than they do other adult things is because coding doesn't require a lot of expensive equipment and you can afford to do it as a kid.

Yes, and also I think also because it is possible for it to be a self-isolated system; you don’t need as much greater context of the world to make sense of its rules.

I strongly disagree with this. It is easy to code and any child or adult can do it. The typical problems we solve with code are hard, and can only be solved with code.

Consider the quadratic formula. It's trivial to write this code. That's not a hard problem to teach anyone to solve with code. Or calculating compound interest. Or solving payroll and basic accounting.

All these things are done with spreadsheets as the abstraction instead of code because of the myth that "code is hard." Code is not hard, it's just we use it to solve problems that are so hard we can't do it with spreadsheets.

The steps needed to convert the quadratic formula to code are absolutely not trivial, unless you're a geek. If I went to my mother trying to tell her to code a quadratic formula, she'd have no idea how to do it, even if I explained everything.

Spreadsheets are hard too.

Wow, I don't know how you arrived at the conclusion that coding is somehow easier than excel. Excel/Google sheets is tabular wysiwyg and about as simple as it gets... even for children who get acquainted with tabular data relatively early (reading simple scientific graphs, etc)

> I'd guess it would be up to business programs and accounting programs and all these other college majors to actually teach classes in python and R, rather than what they do now which is teach classes in excel. Imo the entrenchment in excel is rooted in ignorance to other (often simpler) options available with python or R, than in any actual critical proprietary features offered by excel.

Every time a business user sits at a computer, are they going to spend 10 minutes getting it done, or 30 minutes trying to figure out how to get pip env configured so they can maybe eventually get it done? If by some miracle they are supernaturally gifted at programming and succeed after some weeks, they now have:

  - a nonstandard file format that will eventually break 
  - no real time feedback of formatting or calculation as they input/manipulate data 
  - no charting 
  - no GUI 
  - no "undo" button 
  - no import wizards 
  - no support (but I guess someone light years ahead of any dev talent I have ever seen wouldn't need it)

And all of this for a single use case.

Microsoft (and I am no fan) has the best tool for data manipulation that exists for most of humanity. Excepting the awful ribbon UI redesign, you could take any Excel 97 user, put them in front of the latest M365 version, and they could get the same work accomplished in the same amount of time.

You cannot say the same thing about almost anything else in the tech industry. Excel is terrible in a lot of ways. But it's a mostly reliable tool, has worked approximately the same way for 25 years, so people are going to use it and teach it. There's no point in wasting someone's valuable time with the horrifying fragility of the python/R/whatever ecosystem.

there are languages that have better support with dependencies though, e.g. Julia (every time I have to debug dependency issues in some other person's Python code/requirements.txt, I find myself wishing it had been written in Julia). I'm not disagreeing with the overall tenor of your post, but I do think there's a medium that's possible to strike here: maybe something like a simplified DSL in Julia, or something like https://www.visidata.org/

> Microsoft (and I am no fan) has the best tool for data manipulation that exists for most of humanity.

Sigh... "Microsoft has". Time was, spreadsheets was a generic category of software, made by lots of different software makers. Nowadays, it feels like people not only don't know this, but can hardly even conceive of the possibility that "spreadsheet" could mean something other than "Excel". At least I think that more people are still able to think of, say, word processors or presentation software as not necessarily Microsoft products; the spreadsheets battle seems to have been their most crushing victory in the "Office" or "productivity software" wars.

It's sad not only for the IT world in general or from a free-market perspective, but for spreadsheets in specific, too: Who knows what stuff like Quattro Pro or Lotus Improv would be capable of now, if they were still around?

True, however Microsoft is not to blame for the management errors of others, and there are other spreadsheets on the market still.

Except instead of trying to be the C++ of Spredsheets, they target Go like capabilities, and then wonder why business keeps adopting Excel.

>>> It's not hard to code, children do it.

I think a paradox of programming is that we know it's easy, yet we also know that a certain fraction of people -- perhaps a majority -- will never grasp how to do it effectively. And the ones who can do it well enough to get paid for it, aren't sticking around in low paying administrative jobs.

And coding well enough to really replace a spreadsheet without making something even worse is yet a higher level of programming skill.

Given the virtually ubiquitous knowledge that programming skill is of market value, I think the existing proportion of people who can program is indicative of how many people are potentially capable of it. I don't know a single manager or engineer who doesn't think that learning a little bit of Python would be useful.

> It's not hard to code, children do it.

My wife knows R and Python. 90% of the pain of any new project is in environments and dependencies. Some weird error message shows up because there are multiple python executables on her machine or it isn't speaking with jupityer properly and we are on a wild goose chase of googling and command lining.

For excel, you open it and run.

Even if you know how to code, setting up a coding environment has enough "wtf does this mean" roadblocks for nonexperts that it becomes a terrible experience.

I never thought I'd be someone to do this but 'mito' is slang for pathological liar in French, i.e. short for 'mythomane' haha, kind of funny for something that's supposed to be a persistent source of truth. Names don't matter at all, but just wanted to comment on this. Lol!

“European Spreadsheet Risks Group” is the most European thing ever.

Reply on news.ycombinator.com