
Humans vs. Microsoft Excel: The Quest For Smart Tools - turoczy
http://futureofwork.glider.com/microsoft-excel-the-quest-for-smart-tools/
======
ajscherer
The author said it himself, these errors were caused by humans, not excel. I
don't think making excel "smart" enough to prevent people from making dumb
mistakes is a good idea. In fact I think that would transform excel into
microsoft word, which is the only thing that could make life worse for your
typical competent office worker.

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.

~~~
thiele
There is low hanging fruit in the problems to solve though. For example,
finding and highlighting outliers in a column. It's a relatively simple
feature but yields big benefits to users.

~~~
pyoung
Except it isn't that simple. Your definition of an outlier may be completely
different from some one else's (95th percentile, median/regression method, or
simple max/min, some of these are already possible in excel). Then, how is the
information displayed? Highlighting cells? Well if you have +10k rows of data,
than that won't be too useful. Creating a summary in another column/sheet will
add to the already cluttered 'results' spreadsheet, etc...

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.

~~~
hamofgobelgope
I know people bag on VBA, but I'm a big advocate of effectively utilizing
Excels tools along side using VBA to customize where necessary. This solves
the problem in your first comment (users can define their own statistical
limits and methods), while keeping the simplicity of excel and its tools for
other users.

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.

~~~
pyoung
I have used VBA here and there, and try not to bag on it too much. There are
some cases where using it can make sense, but in general, it seems to be a
compromise.

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.

~~~
boomzilla
Excel is a tool, and it's a really good tool for what it does, which is for
applications that require: \- very fast iteration cycles \- a particular data
model: grid of numbers which is very common in business world \- support for
everything under the sun: persistence (just hit save :), dialog UI, math
formula, stats models, string functions, date/time functions (better than even
Java's Joda), internationalization, localization, utf 8, and plenty more

------
roin
I work with an army of very smart people who need to build incredibly complex
models to drive our business. Like most companies, we turn to Excel. It's the
only widely used application with a low enough barrier to entry that "non-
technical" people can build legitimately complex (if not always robust)
models.

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/](http://baselinescenario.com/2013/04/18/more-bad-excel/)), and I'm
interested in what Data Nitro (datanitro.com) is doing.

~~~
peteforde
I think Data Nitro is really cool, and as a data geek the idea of having a
real language available inside the Excel environment gets me very excited.

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.

~~~
BrianEatWorld
Is there a reason you don't use one of the current data analysis languages
like R, Stata, SAS, etc?

~~~
peteforde
I use those languages frequently, when they are the right tool for the job.
Well, R and Stata, at least — but also Processing and D3 and Google Maps...

Not sure how this is relevant to my concern, though.

------
pinaceae
disagree completely. MS Excel is a masterpiece in software engineering,
definitely the best piece of software MS ever created. Large pieces of the
economy run on Excel, finance, controlling, etc. are unthinkable without it.

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.

~~~
AdamFernandez
This. It's important to remember that from the perspective of millions of
corporate users (not the hacker crowd), this is is THE tool. When you think
about it, Microsoft really is Excel. That is their core. Why is it difficult
for corporations to switch from Windows? Office. Why Office? Excel. If anyone
attempted to create something that not only matched Excel (with all the messy
file compatibility issues and macros), but surpassed it, they would be
striking at the heart of Microsoft.

~~~
thiele
Agreed. Although you could replace 'Excel' with 'Word' and still be just as
correct.

~~~
thomasf1
Word isn´t the problem... Converting from Word to something else is some pain
for users. Converting from Excel (especially Excel Macros) to something else
is in the Millions of $ for developers/re-engineering of those Macros...

------
peteforde
This is a problem I've spent a lot of time pondering. The author makes several
suggestions; one in particular (a GitHub for datasets) is an idea I felt
strongly enough about that I founded a start-up called BuzzData to address
this concern. Unfortunately, we did not find our critical mass and the product
has since successfully pivoted into an entirely different product called
LookBookHQ.

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.

~~~
vladsanchez
Started following LookBookHQ. =D

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.

------
karamazov
The fundamental problem with big spreadsheets is that they're complex pieces
of software, and they're being designed by people who haven't studied software
engineering.

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.

[1] [https://www.datanitro.com](https://www.datanitro.com)

~~~
maxcan
As a dynamically typed language, how does Python specifically yield fewer
errors than VBA? Or even compared to C# or F#?

------
samspenc
At a previous job, I tried to convince them (and succeeded in some cases) to
build a domain-specific web app instead of sharing and e-mailing ever-changing
Excel docs.

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.

------
6d0debc071
I dare say that the problem is less about Excel being dumb than that if you're
interested in the underlying logic Excel hides all that from you the minute
you click off a cell. There's black box programming and then there's just
silliness.

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.

------
glaugh
I think it's impossible to replace Excel en masse, so lots of folks are
starting to pick off bits of it to replace. And hopefully in doing so they'll
invest in making those bits a lot smarter than they are in Excel.

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

~~~
Estragon
I suggest you change your statwing demo to something more positive.

------
coderguy123
Having worked in nyc financial world, I can say excel is here to stay - at
least for foreseeable future. Heck, the only reason I have windows running in
parallel in my MBA is excel. We use to have what we called "excel test" for
all quants and analyst positions. We use to QA our excel sheets just like any
other software application. I am surprised that many companies don't do that -
it only makes sense.

~~~
pinaceae
why? excel runs perfectly fine in its OSX version. which differences have you
encountered?

~~~
bsg75
VBA, which was removed in Excel 2008 and returned in Excel 2011, is not fully
compatible with Windows versions, where most users author code.

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.

~~~
omni
You nailed it. I would use less generous language than you did to describe
VBA's deficiencies in Mac Excel 2011. Huge parts of what could be considered
the stdlib are totally missing in the Mac version. The language doesn't even
have native dictionaries.

~~~
bsg75
I doubt MS wants Mac Office to be on feature parity, otherwise there is one
less reason for businesses to keep buying Windows.

------
cmwelsh
You can turn Excel spreadsheets into web applications quite easily with Google
Apps Script. We wrote a REST interface to a massive multi tab Excel file
recently and it only took like an hour, as opposed to days of reverse
engineering the calculations (the client did not know what the equations in
the file were because the employee who created it left).

Basically I'm saying it will get much, much worse before it gets better.

------
jacques_chester
There are entire organisations and conferences about the dangers of
spreadsheets[1] and there are now programming standards for develop
spreadsheets[2].

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

[1] [http://www.eusprig.org/](http://www.eusprig.org/)

[2] [http://www.fast-
standard.org/document/FastStandard_01b.pdf](http://www.fast-
standard.org/document/FastStandard_01b.pdf)

------
SiVal
If you are amused at what autocorrect does for writing, imagine the hilarity
of economic decisions based on "smart" spreadsheets.

------
justinlilly
Excel will always be used BECAUSE its so free-form.

[http://readwrite.com/2013/06/11/excel-is-an-art-form-
these-b...](http://readwrite.com/2013/06/11/excel-is-an-art-form-these-
beautiful-images-are-proof#awesm=~o8w9EUvUTJDPlC)

------
zvrba
Hmm... Excel-like front-end to Haskell?

~~~
jacques_chester
For inspiration, consider SIAG:

[http://siag.nu/siag/](http://siag.nu/siag/)

------
justinlilly
Typed fields sound neat!

------
vinchuco
We need to think beyond the limitations of tools no matter how pervasive their
use. It reminded me of :
[http://vimeo.com/66085662](http://vimeo.com/66085662) (you can skip to the
demo)

------
bliker
I got theory. There is a limit what you can do with WYSIWYG editors. Once you
reach the limit, the software starts getting in your way, hiding your errors
and making you work harder.

It applies for many software. From HTML to 3D modeling.

