Humans vs. Microsoft Excel: The Quest For Smart Tools
futureofwork.glider.com
futureofwork.glider.com
Excel already has a lot of this crap. It already requires an act of god to prevent excel from changing your string of digits into a number or date. It already shows you little exclamation point icons when your formulas omit adjacent rows or are different from other formulas in the same row/column.
A tool will never make it possible for dumb people to solve hard problems easily. It's like trying to design a knife that makes it impossible to cut yourself. Nobody with any kind of a clue would want that knife.
A tool should be straightforward and intuitive, but it shouldn't aim to be smarter than it's user.
This is why I recommend using programming based tools for data analysis. Any non-programming tool has to find a balance between the number of features offered and the complexity/ease-of-use of the tool. With programming tools, you merely have to find the right package (or build your own) which essentially results in getting the exact set of features that you need to solve your problem.
The problem is that not everyone is a programmer. A "programming" tool might be great for you, but when you show it to co-workers for them to work with, they'll inevitably ask "Can I get this in Excel?". Excel+VBA allows for customization when the "complexity/ease-of-use" balance is out of sync with your needs, but to everyone else it's still just Excel.
With this, those that can program have the option to solve the problem their way, while allowing those that don't program the means to solve the problem the way their used to.
I get a little confused about the programmer/non-programmer dichotomy. If you are capable of implementing a complex model in excel, you are probably more than capable of learning a programming language. If you are just tallying up a few numbers to throw into a report or presentation, then yeah, no need to switch. As I mentioned in another post, it probably has to do with exposure and motivation.
Check out Options: Formulas: Error checking rules. It's almost as if Microsoft has been continually developing this software for years and years and they have seen many common mistakes. ;)
Your analysis is too low-level and nitpicky for this high level idea playground.
Excel is still a dumb tool. He's absolutely right.
We have ways of making smart tools. It's called "programming" and "database engineering." That part's a little too hard right now, and part of why it's hard is because if you try to make it easy to mould a flexible program to try to define what your data means and how it should work, it becomes so generic as to be as unusable and amorphic as a cloud of smoke.
I've tried this, moving from a space where Excel was predominantly used, and trying to capture the process into an application. We tried to keep all the customizability and malleability as Excel: but I'm now convinced that was a huge mistake. It led us into genericland.
It is possible to build applications that work for these processes, using the tools of programming and good data design. But that's why we have thousands of different applications all trying to solve these different problems, all of which you could probably represent in Excel in some way. The fact that all those applications exist means that people want something smarter than Excel. It proves the case.
But we haven't yet bridged the gap between Excel's extreme flexibility and an Application's intelligence and process fit. That's because it's really difficult to make that work; to fit all the pieces together into something that makes sense in both realms. It's really hard!
The solution will be a user interface masterpiece. I'm convinced of that. And it will be layered, like an onion, allowing people to build any application they need just by telling the computer about their data and how it fits together and what it should allow them to do. Someday we'll have a system—some sort of super-Rails—that makes this so easy that anyone can do it. Someday Programming will be an ancient art, something that only your Grandfather did, like crafting your own tools or woodworking or making jams and jellies.
Someday that will be true. But the way we get there is by thinking about the difference between Dumb Flexible Excel and Smart Rigid Applications and how we bridge that gap, because it's difficult and it's possible. We have to think at this high level, way up in the clouds—and more people should, and there's no reason to shoot them down.
Life is full of tradeoffs. Sometimes the general, permissive loosey-goosey system is what you want. Sometimes it isn't. It takes judgement to decide which is which and when to switch.
I believe it is possible. I don't think anyone has quite come up with how, yet.
It's the simplicity on the other side of complexity. It's the next frontier. We may yet reach it.
Einstein can't travel faster than light, Maxwell can't reverse entropy and I can't change that different people are good at different things.
Optimism is the grease of evolution and economics. Without lots of people exploring fruitless plains of minima we'd never find unexpected maxima. But almost all who try will fail and those who work with the known good maxima will probably succeed.
Convergence vs coverage -- another irreducible tradeoff.
I'm also a realistic optimist, and you're right, it's necessary but often fruitless. But the way I see it, we're headed in that direction pretty surely. Ten years ago we couldn't imagine software doing anything different from Excel, and Excel (or, er, Lotus 123) was the most powerful way to solve any problem, and they were indeed amazing. But they have this problem of being unstructured. Now we have Rails and other frameworks that make it easier to make custom logic to solve problems in specific controlled ways. They're making it easy to let programmers specify how a tool works. We're in the woodworking and craftsman stage of software: we need people who can build the tools and painstakingly design every detail.
We may always need that, and we may always value it. But I can envision a future where there's some in-between: some way to let the computer be extremely smart about the problems we're trying to solve with these tools. I think frameworks like Rails are one giant leap away from being usable not by programmers, but by people just illustrating rules and relationships.
Would it be too generic? Would the tradeoffs be too great? Maybe. But I am optimistic these are problems we can solve and not great unsurmountable rules of the universe.
I know because I did it once. I made something that worked for everyone and allowed you to define complex multi-dimensional relationships between data. It would cover almost any business need, and in a stricter smarter way than excel. There were details that we missed and my business partner was an asshole; those were the problems. The software and the idea were sound and, in fact, incredible. I think the trivial problems are solvable. So, forgive me my optimism, but it's based on experience.
But the problems with Excel and general spreadsheet modeling limitations offer such a great opportunity for improvement that would impact just about every business out there. I wish I saw more people tackling this, or I had a great idea to do it myself. The problems I see are around barrier to entry, in that it must be usable in just a couple minutes, simple enough for non-programmers to learn, and equally or more efficient to get a basic model functioning. Otherwise I just don't see wide adoption despite possible maintenance, accuracy, and reliability benefits.
James Kwak has written some smart stuff about this (http://baselinescenario.com/2013/04/18/more-bad-excel/), and I'm interested in what Data Nitro (datanitro.com) is doing.
Unfortunately, by increasing the power and capability of what a careless user can do, while expanding the learning curve and moving Python code to a different place than any cell-level functions or global VBA functions are stored... all of this just makes the potential for issues of human failure far greater.
Not sure how this is relevant to my concern, though.
The goal behind templating would be to prevent complex formulas from being entered in tiny cells, and to leverage a userbase that has "been there, done that." Do you think that would shorten new hire ramp up in using existing complex models etc?
Edit: by the way, you're right (in my opinion) to emphasize barrier-to-entry as a great danger in this area. If you look at the history of attempts to innovate in the spreadsheet space (most famously Lotus Improv), that is the reef on which they tend to run aground.
I worked in a place that used both, and the programmers were successful in preaching the benefits and converting some of the excel users, where it made sense. However, I imagine a large majority of analysts using excel in the corporate world do not have that exposure and don't realize there are other alternatives out there, or if they do know about the alternatives, they get quickly turned off from them when they try to import/manipulate the data. I don't think any tool, by itself, is going to push individuals to change. People need to be exposed to the benefits of approaching data analysis as a small software/programming project over the long term. It is short term thinking that pushes people to use excel in the first place.
The difference between the two paradigms is much deeper than data import/export, and exposing most spreadsheet users to scripting languages (even with good import/export) won't convert them, it will just turn them off. I know this from hard experience, having built a domain-specific scripting language on a previous project that was trying to make software for petroleum economic modelers (i.e., sophisticated but non-programmer business experts). Every feature request we got boiled down to, "make it be like Excel, complete with an interactive grid UI".
It isn't short-term thinking that drives this kind of user to build complex models in Excel. It's that Excel fits how their minds work. Partly that's because spreadsheets are intuitive for many people, and partly it's because spreadsheets been mass-adopted for so long that they're utterly entrenched. Trying to get spreadsheet users to think more like programmers is a sure way to keep them stuck on Excel forever.
Here's an example of how different the thinking is. In a programming language one first builds a computation abstractly (code), then instantiates it on concrete data (input) and looks at the results (output). Spreadsheet users don't do that. They start with a concrete example, build the formulas they need to get that particular example working, and then (if they need a similar example) copy what they did and tweak it. In other words, a spreadsheet user is never looking at a pure abstraction; they are always looking at a concrete example, even when the computations they're building up are complex. If you showed them all the formulas at once, they would recoil. I think this low tolerance for abstraction is one reason why spreadsheets fit the minds of non-programmers better, and also why it is so difficult for those of us who are comfortable with abstraction (and thus with programming languages) to appreciate how different spreadsheets are.
My experience with SAS and R suggests it is possible to somewhat re-create the typical excel workflow. The SAS IDE allows you to open the data sets created in your code, which means you can write code one line (well, more accurately, one data step or procedure, as it is called in SAS) at a time and inspect the intermediate results at each point. Similarly, RStudio allows you to inspect the values of data frames and variables, once again allowing the user to step through the code writing process and get relatively immediate feedback.
Regardless, there are going to be a large portion of excel users that won't or can't switch, and there are also a number of use cases where excel makes perfect sense (smaller data, simpler summaries), but I have personally 'converted' a few analysts, and think that, with more exposure, a not insignificant portion of excel users could be convinced to make the leap.
the flexibility, ease of use, speed is astounding. applying colors to cells and then being able to sort by color still blows my mind - not the feature per se, but the fact that they included it and solved a common problem (how many times did a client mark something by color in a big spreadsheet...).
excel is mobile, works offline. replacing it with some webapp or too much logic just shows the complete misunderstanding of its appeal.
This immediately brings up questions: What tools exist for building robust spreadsheets? How can we encourage people to use them? Should we make better ones?
Won't comment on the "masterpiece" thing, but I like SQL Server better. Only their Natural keyboard and mice are better than SQL Server.
> Large pieces of the economy run on Excel, finance, controlling, etc. are unthinkable without it.
Now, that's scary.
I do have another idea, which is to bring the concept of testing to the Excel world.
I think that the combination of:
- a framework for building up test cases that can be run against the data as part of the saving process (think rSpec)
- an English-like, indented grammar for defining user stories that can potentially generate code for tests (think Cucumber)
- a strong push for a culture of testing in the Excel community that would have to include Microsoft
I dunno, honestly. Could be crazy. The culture might never accept testing.
But then again, one of the basic ways to sell something is to give people insurance against humiliating themselves. It's possible that this thinking would make a lot of sense to the twice shy, highly risk-averse board members of the major financial institutions and governments of the world.
Ever read "Specification By Example? That approach along with some of your suggestions could be used to prove your testable specification spreadsheet concept.
The market awaits for you.
Building a complex program is hard. It's untenable if you don't approach it properly. This is true whether you're using Excel, Python, LISP, or anything else. Better tools can go a long way to help, but the ultimate answer is educating people on how to properly engineer systems.
On that last point - at DataNitro[1], we let people script Excel with Python, which is a better tool than VBA or than doing things by hand. The result is that for a given level of complexity, you see fewer errors and more robust spreadsheets; but when you give people more power, they naturally build more sophisticated spreadsheets. All else being equal, this might result in more, not fewer errors.
The great thing about introducing a new tool, though, is that there's also a chance to introduce new ways of working. Suddenly, people start building spreadsheets that work with version control and run unit tests. This helps tremendously with robustness and reliability.
I used CakePHP, and it was really easy to whip up a complete CRUD web-app with domain-specific information and DB tables, allow people to collaborate through a web interface, and be happy users.
Some apps are tougher than others, I'll admit.
As for data-entry, ideally, that's not something that humans should be doing for anything but the most trivial concern. If you have huge columns of financial data or test results or what have you, then having someone type it into a computer is silliness of the highest order.
Examples from the S12 YC batch alone:
. VBA isn't great, so DataNitro is letting you use Python.
. Grid is (mobiley) replacing the basic list-making use case of Excel.
. (Stretch example:) Financial modeling of big personal finance decisions is a nice use case of Excel, but too hard for most people, so SmartAsset bakes those kinds of models into an on-site calculator.
. Statwing is replacing pivot tables, which are clunky, devoid of statistics, do no error checking, etc.; in a few clicks Statwing will automatically choose, run, and interpret a statistical analysis, all the while looking for and alerting users to outliers or (some) other data issues. [I'm a cofounder. Also: https://www.statwing.com/demo]
I'd expect that soon someone will come along soon and pick off the financial modeling use case in a way that's a bit more generalized than SmartAsset. Really tough UI challenge, but I think doable.
Backend data connectivity options are virtually non-existant. Excel 2011 has what appears to be a 10+ year old version of MS Query.
Some 3rd party financial plugins are Windows dependent, and I believe some of the Microsoft analysis additions are as well.
Basically I'm saying it will get much, much worse before it gets better.
Basically, the normal rules apply. If you want safe spreadsheets, you have to plan and verify.
The reason this doesn't happen is because firstly, spreadsheets are very accessible. Every working large system evolves from a working small system, as John Gall observed. Excel et al make that evolution relatively easy.
The second is that spreadsheets enable non-programmers to work with concrete concepts instead of the abstract ones that programming languages encourage. It's easy to visualise locations on a sheet when you are looking at the sheet.
Finally, spreadsheets support calculation by propagation. They are an excellent declarative environment. Most programming languages do not support this model of programming (though we're groping towards it with concepts of binding).
[2] http://www.fast-standard.org/document/FastStandard_01b.pdf
http://readwrite.com/2013/06/11/excel-is-an-art-form-these-b...
It applies for many software. From HTML to 3D modeling.