[tehwalrus]

I continue to be terrified at how spreadsheets are used in business applications. Squashing data that shouldn't be in tables into tables "so you can work with them" was the worst thing we ever taught non-programmers to do. We should have taught them how to program instead.

(you can get close to programming in a spreadsheet if you know what you're doing with Insert > Name > Create, and one day I'd like to see a spreadsheet that lets you write your macro functions in Python rather than VB, but nobody is taught to use Excel that way except people who also program.)

[_delirium]

It's not so much Excel's tables that people want, as its dataflow programming. Excel is a widely available environment that lets you define data slots in terms of functions on other data slots, with the whole chain updated live as values change (no "manual" update logic, just functions of cells with auto-update). The table layout is just a default way to view the slots. Until very recently that programming style was not widely available elsewhere, especially with a GUI. The only other semi-widely used system I can think of that sort of has that functionality is Mathematica (where you can link slots in a notebook), but Mathematica is more niche and expensive than Excel.

Heck, even without the GUI requirement, "real" programming languages have only very recently added competitive functionality, with the exception of Common Lisp, which had the Cells package ages ago. Now it's getting more common to find various kinds of dataflow/reactive/data-binding constructs in mainstream languages other than Excel, but it's quite new.

[tehwalrus]

In terms of auto-update, sure. But I was talking mostly about how to structure your data - for most business activities, lists of objects with properties (or dictionary keys) are far superior to cells, and collections of cells, with obscure names.

How much easier would spreadsheets be to understand if you had

    sum([widget.price for widget in catalogue])

instead of

    SUM(F3:F405)

?

Teaching people to think more about their data structures, rather than teaching them to squash everything into a table even if it doesn't really fit into one, would dramatically expand their skills in this type of analysis!

[PeterisP]

"Dramatically expand their skills" and "easier to understand" are opposite directions; ease of use means getting some results with as little skill expanding as absolutely possible.

For immediate results and learning, your proposal spreadsheets would be harder to understand - if your data fits on a single screen (not ...F405), then you can just point your finger at it and say "Here! here are the widget prices!", but any structured model would (a) require thinking about what a right structure would be for the situation (that's the hard to use part, takes effort), (b) require understanding about what makes a proper data model (that's the hard to learn part, many people won't know how) and (c) makes it nontrivial to update that model (that's the hard to maintain part - less chance to break stuff, but more effort to do it properly).

For reusable datasheets and estabilished processes it's an entirely different situation, and for that there are not-Excel apps or named ranges within excel; but creating an usable dataflow and a reasonable structure is a much, much more difficult skill than using excel currently. It may be easy for you, but not for the general population; heck, I see heaps of second year CS students that have no clue about how to structure their data, so requiring that can't make spreadsheets easier to understand.

[dmethvin]

You can essentially do what you're asking in Excel using named ranges. Getting novices to use advanced Excel features is just as difficult as getting novices to use advanced features in any other language.

[tehwalrus]

Indeed, I mentioned this in my original post (child of the article) - such features are only taught to users so advanced that they are (more than likely) also programmers (or certainly should be). I think this is a shame.

[icebraining]

one day I'd like to see a spreadsheet that lets you write your macro functions in Python rather than VB

That's LibreOffice :) Though it's not "rather", but "besides".

[tehwalrus]

Interesting. I will have to try this when I have time!

[lifeisstillgood]

Anecdotally, trillions of dollars daily are moved around through chains of excel spreadsheets. It's been an eye opener for me :-)