Ask HN: What are the biggest problems with spreadsheets? - filipstachura
======
s_kilk
In my experience working in some "speadsheet heavy" businesses:

\- complecting 'data' and 'presentation of data' together

\- lack of tools to help with 'correctness'

\- lack of version control

\- horrifying and error-prone programming model, both hard to write and hard
to reason about.

\- spreadsheets full of pivots and charts rapidly become the 'truth' of a
business, but without any checks and balances to ensure that what they show is
actually true, leading to confusion and great badness.

~~~
noxToken
> horrifying and error-prone programming model, both hard to write and hard to
> reason about.

I don't remember where I read this, but I heard that the basic Office Suite
(Word, Excel, PowerPoint) is written with office workers in mind first. That
is, Word isn't written for authors, Excel isn't written for database
managers/big data specialists, and PowerPoint isn't written for motivational
speakers.

I think the idea of the basic Office Suite is that a user could grasp the
basic functionality of the software without having to undergo any training.
Second to that, tools and other functionality for those with specific needs
(e.g. pseudo-database functionality, basic data visualization in Excel) could
use the software where the project was small enough. Excel was given great
programming features without a proper programming language to support it.

This isn't to say that these programs are not feature packed or bad. They're
kitchen sinks that want to make everyone happy.

~~~
collyw
The problem is that they are too easy to use. As such Excel gets used for
everything data related. Access would be a far better choice in many of the
cases I have seen, but it just doesn't make any sense unless you have had some
sort of introductory course on databases.

------
dragonwriter
The biggest problem with spreadsheets is that they are often used in lieu of
more appropriate tools because of poorly considered IT policies and
organizational practices which leave no other tools available to users and
fail to provide adequate support for users on some tasks for which programming
is required and other tools are more suitable.

~~~
nekopa
Actually, I think this is the biggest strength of spreadsheets, that no one
has taken advantage of yet. Most of what you term as poor IT policies is just
what (in my personal experience) just users finding something that works for
them. And software developers don't seem to take note of this.

You are right that there are lots of better tools for tasks users use excel
for, but 2 which spring to mind for me that devs haven't paid attention to are
form/table design and databases:

Form design: It used to (ok, still does) drive me crazy when I see excel used
to make flyers and forms to be printed out. But for the users what could be
easier than drawing a box on the screen, adding a line weight, and then
drawing other boxes inside that using the convenient spreadsheet grid? Why
hasn't someone designed an app that works that simply?

Databases in excel are another monstrosity, but for a lot of users, all they
want is a simple lay of listing things which they can then sort, and maybe sum
a few totals on. Firing up a complete RDBMS is over kill for this use case.

So I see the biggest problem with spreadsheets as why aren't we learning from
the way people use them and putting this into action?

~~~
davelnewton
There _are_ tools that do that, even more simply than Excel does. That's a
_separate_ issue, e.g., app discovery, corporate approval in some cases, and
people unwilling to use the best tool for the job.

~~~
filipstachura
Or maybe those tools lack some features and that's why people decide to
improvise with spreadsheets?

~~~
davelnewton
... No, my comment is _specifically_ targeted at the example of making simple
boxes, lines, and text. There are trivial tools for making diagrams where the
barrier of entry is lower, and the ease of use is higher. I provided reasons
why those tools aren't being used more.

~~~
joshuapants
The barrier to entry may be lower if you consider a user who has never used
either software and is trying to make a decision. But if you have a user who
has used Excel for the past 2 decades and is comfortable with it, low barrier
to entry is still higher than just "go with what I know."

~~~
davelnewton
... Which is one of the reasons I listed for not using it, and unrelated to
features.

------
Bostonian
In spreadsheets you often repeat the same formula many times. In a programming
language you would not write

C2 = B2/B1 - 1

C3 = B3/B2 - 1

C4 = B4/B3 - 1

ad infinitum. You would store the B's and C's in arrays and write a loop, or
use array operations in languages that supported them.

In Excel, if you have stock prices in column B, you compute stock returns in
column C by entering "=B2/B1 - 1" in cell C2 and copying the formula for the
whole column. But nothing stops you from inadvertently changing the formula in
cell C100.

~~~
pc86
Not exactly a fix to what you're referring to, but if the data is formatted as
a table the formula is copied to every cell automatically.

~~~
ForEnglandJames
Have used exactly this feature in a freelancing gig to help contain data
entry/formula copying errors.

I just wish it were less obtuse to set up.

------
jaaames
-Write locks/file ownership. Bob maintains the master schedule spreadsheet and no one else is allowed to touch it. Mary gets given access and leaves it open and goes to lunch and Bob can't update it for the rest of the afternoon.

-Multiple sources of the truth. I've seen spreadsheets used in lieu of professional estimating tools for pricing jobs, and been abused because I wasn't use the "latest version" of the spreadsheet that got cc'ed around at 11pm called estimate July 24 v11a LATEST.xlsx

-Fudge factors and spaghetti formulas. You can take a nearly complete data set and essentially manipulate it to tell any story with inconsistent formulas, hidden columns, references from other sheets etc.

-VBA extends capabilities but is has some serious performance issues, doesn't implement objects correctly, doesn't handle errors well, code can't be version controlled.

-Gets used to do a poor job of purpose built software that the business has licenses for, like estimating/scheduling/MRP because of a lack of training.

-Business logic usually isn't apparent or documented and has to be reverse engineered to be understood.

-Propagates silos within businesses as they decentralise data and tend to diverge.

~~~
martinwnet
Does Google Sheets not solve a lot of these issues?

------
jtfairbank
Hospital staffing schedules are created and distributed via excel. It's really
bad... and part of why healthcare is so expensive.

I started ReSchedule Med [0] to tackle this problem, starting with medical
education (because they have the most complicated schedules).

[0]: [https://reschedulemed.com](https://reschedulemed.com)

------
tmaly
I have generated thousands of reports in the form of excel spreadsheets. Here
are my biggest gripes

1\. lack of good meta data so I can process spreadsheets after they have been
reviewed. Right now I use named ranges with a keyword but its very limiting.

2\. lack of a good build in scripting language that is easily to use, has good
3rd party libraries, and makes automation easy. I am not big on excel macros,
it would be nice to have a lua or python built in as an addon.

3\. difficult/not straight forward to interact with external data sources. It
would be great if I could easily pull data in from a web service. I guess if
#2 was there, this might be easier.

~~~
Velox
Depending on what you are trying to "script" with it, Add-Ins for Office might
be good to check out? They are more focused on user interaction rather than
scripting currently, but may be useful? They are written using HTML and
JavaScript: [https://msdn.microsoft.com/en-
us/library/office/fp142185.asp...](https://msdn.microsoft.com/en-
us/library/office/fp142185.aspx)

------
brudgers
Product Owner Perspective: Spreadsheets are extremely powerful, highly
flexible, ubiquitous and very easy to use. If that's a problem then we need
more problems.

It is trivial to make a trivial spreadsheet but the last real competitor to
Excel was Borland Quattro Pro in the early 1990's. Google has to give away
their product.

~~~
SteveLAnderson
Person that works with a product owner that likes spreadsheets perspective:
Spreadsheets are an incredibly painful way to track product work. We really
track our work other ways that actually help us, then, we duplicate that
tracking by pasting it into the spreadsheet so that you are happy.

~~~
brudgers
Tracking product work is just one use case. The power of spreadsheets is their
flexibility and general applicability. I would be sympathetic to your plight,
but I had a boss who wrote letters in Lotus 123 in the DOS days. There was no
place from which to copy and paste because there was no multi-tasking. The
only upside is that Lotus 123 was better than edlin.

------
debacle
There needs to be an easier way to transition from spreadsheets to an
application. A spreadsheet that was based on programming principles rather
than business principles would be fantastic.

Also, a spreadsheet that was much more scriptable for someone who didn't want
to learn VB.NET would be a dream, as a programmer.

Disclaimer: I don't generally pay for software out of my own pocket.

~~~
marekr
Do you mean transition from specific spreadsheets you would already have for
that task? Or in a wider sense, e.g. as a company - gradually transition from
using spreadsheets to using applications for some of the tasks?

~~~
debacle
I mean I should be able to take a spreadsheet, properly configured, and turn
it into a CRUD app through some sort of wizard.

Or, as a programmer, I should be able to massage an existing spreadsheet (to
confirm data types, maybe add some validations) and have that be able to be
wrung through a CRUD app generator.

People use spreadsheets because they combine business logic, data, and a
simple user interface into a single document. 99% of spreadsheets should
actually be CRUD apps (in my experience, at least) but it's not worth the
hassle of going that route until you already have close to 30 MB of
unstructured, manually edited data.

