
I was wrong about spreadsheets (2017) - mooreds
https://www.reifyworks.com/writing/2017-01-25-i-was-wrong-about-spreadsheets-and-im-sorry
======
halfeatenpie
I work in critical infrastructure planning. My organization builds software in
R, Python, and other programming languages customized for these major
organizations.

So many critical infrastructures, billions of dollars in planning, and just
systems are built out of Excel. It's amazing. You'd assume something that
services millions of people a day would have some more sophisticated and
customized solution, but you're wrong.

The reason is because most people know how to use Excel. Most people know how
to handle it, use it, modify it, build up from it. You don't need to get a
regular support contract from a company for your custom-built spreadsheets. If
something critical in that "Excel software stack" breaks (aka Microsoft
Excel), most of the time you just need to reset Excel. It's amazing. You don't
need a new server, you don't need support contracts, it's just like riding a
bike.

In my opinion, the limitation of Excel is actually when it comes to big-data
analytics. The way Excel handles large quantities of data is slow (due to the
nature of it's software structure). That's where we come in and build out
these models and systems. However, in the end, our data outputs will be fed
back into Excel, because that's what most people are used to.

I have deep respect for Excel. After all, Excel empowers so many users who
don't know how to code to provide amazing plots and perform major calculations
with ease.

~~~
ozim
Yeah it works and you don't need support contracts and then:

\- Fidelity's "Minus Sign Mistake": loss of $1.3 billion \- TransAlta
"Clerical Error": loss of $24 million \- Fannie Mae "Honest mistake": loss of
$1.3 billion

Then you get employee turn over where new employees don't get "arcane"
knowledge passed down by people who left and took their spreadsheet foo with
them.

Excel does not have "access control", "auditing", "change tracking". Just
getting work done is not enough.

~~~
mbesto
> \- Fidelity's "Minus Sign Mistake": loss of $1.3 billion - TransAlta
> "Clerical Error": loss of $24 million - Fannie Mae "Honest mistake": loss of
> $1.3 billion

Weigh that against <insert any company> makes $X Billion due to correct usage
of Excel multiplied thousands and thousands of times over. Survivorship bias
at it's finest.

Excel isn't perfect and has notable downsides, but work gets done all of the
time on them for the aforementioned reasons.

~~~
swiley
I’m not convinced there’s a such thing as “correct usage of excel.”

What a lot of companies do is just scare entry level employees into being very
careful. My friend’s girlfriend works at a place like this and people just
accept all the problems with manually editing large spreadsheets because
“that’s just life.”

Anything in excel is a hack and most of its users don’t know any better.

~~~
behringer
What's your solution? That the manager who wanted some report comes to you and
begs you to do it and in 6 months it's finally delivered and all wrong?

~~~
zelphirkalt
The manager goes to the people, who really know how to make a report (maybe
that is "accounting"?) and asks them to make the report taking into account
some single report specific things that they talk about. Then those people,
who know how to do it deliver. They might have someone in their team, who
knows how to work with dataframes, which would come in handy. Those people
store the program written to generate the report from the data and are ready
to modify when more is requested. Instead of doing code in Excel, they simply
write their code in whatever language they use to process spreadsheets or
other data sources.

If they have knowledgeable people there, it will take no longer than coding
something up in Excel and will be more reliable.

~~~
kungfugz
I guess no one has used Power BI for reporting... Link the data source, save
the view that "Manager A" wants every week or month, setup on premesis
gateway, and your new reports are there before your are asked for them again.
Always up to date. Then share your dashboard. Also, you guys should be ashamed
of yourselves. "not sure who does the reports, maybe finance?" Eceryone should
know how to make their own reports.

------
viraptor
My issue with spreadsheets is that they could be improved a lot with minimal
changes, not nobody seems to do it in popular office packages. Making them
more database-like and making table data first-class (at least you can make
named tables in excel on windows) could be used to push people a bit more
towards organised data, without changing how anything works.

Half of the mess that makes excel hell comes from the fact it's too easy to
put two tables of data + some random constants on a single sheet and refer to
them by H3. Now it's hard to add more data, hard to move anything, and hard to
create space which expands to the next row with existing formulas.

Airtable (and Access) implements this idea, but unfortunately sacrifices the
generic, free-form spreadsheet along the way.

I'd be even happy with clippy popping up with "It looks like you're adding a
new table in the same sheet. Would you like to learn about using multiple
sheets?"

~~~
tonygrue
I'd be over the moon to see one change to Excel. Native support for a language
other than VBA. Perhaps the CLR. Perhaps Java/Typescript. Just something
(optional) for programmers who want to use Excel and not want to deal with the
garbage that VBA is.

Some workarounds exist, but afaik they require collaborators to also have the
tool install, which is dead in the water.

I know MS has considered it, I'm still pretty surprised they've haven't
followed through.

~~~
dolmen
MS hasn't just considered it. They have done it. This is called Visual Studio
Tools for Office.
[https://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office...](https://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office#Comparison_with_VBA)

~~~
tonygrue
I've built plugins with VSTO (for Outlook) when I was at MS, but my memory is
that it’s dependent on a plugin being distributed and not available for native
use embedded into the excel workbooks.

You have to install Visual Studio, compile a plugin, register the plugin, and
then from VBA you can call out to that COM interface if you'd like. Even if
Excel users could overcome those hurdles, you still break the collaboration
flow (i.e. just sharing an excel file).

Looking a little deeper, it looks like they're starting to support Javascript
for the newer cross-platform add-in system (and also VSTO), but it looks like
you still have to distribute your JS add-in via a web-service as opposed to
being fully integrated into Excel and XLSM files.

The bit of just being able to share the XLSM file and users using Excel with
no other installs or special network access, is really the make or break for
me and non-professional programmer user scenarios I've seen.

------
elalaouifaris
I worked with excel for some years early in my career. I did not know how to
program back then beyond if else and for loops.

We used it for budget estimation at multi million / up to 100 m. I have seen
other uses in Corp world in many departments.

IMHO I think it’s good for specific use cases. But it is routinely abused
beyond that.

The key shortcomings for excel uses by non-programmers for key business
applications:

\- How do you test that your calculation is correct after update. The same
argument holds for business applications without tests?

\- how do you maintain the knowledge of the inner workings without having to
follow the arrows around cells. Probably there are good practices but how
likely are you to ensure them in your product / team.

\- the data / computation is spread in user space. In case your app is useful
many people would like to use it. How do you manage the updates and bug fixes
beyond it’s the user’s Responsibility

\- how do you avoid the black box effect: mission critical software that no
one knows how to touch inside without a full rewrite?

\- how do you convince stakeholders that they need to migrate to an adapted
solution while they have a working one now and often are oblivious to the
hidden costs of users copy pasting data / reformatting for hours sometimes to
fit an existing tool that is no longer adapted.

The data frame structure in R or python solves a large portion of these
issues. Yes you need training. It’s the same for excel if you want to avoid in
inferno machine case you need the same concepts.

Why not train for programming with data frames and have the option to
gradually extend to web app without a full it project that needs 3 levels of
validation.

~~~
ubermonkey
Data frames are going to be huge. This year I started using them for a
personal project -- python, jupyter notebooks, and pandas -- and was
astonished at how easy it was to get running, and how powerful.

I feel like lots of number people, once exposed to it, will realize that
there's a place to go _after_ Excel that's worthwhile.

------
ubermonkey
There's nothing WRONG with the spreadsheet model, and never has been. It's
immensely powerful and useful, and the growth in capability over the life of a
product like Excel is remarkable and enviable. Excel is perhaps Microsoft's
greatest product.

The problem with it -- and there IS a problem -- is really a problem of
applicability. Excel, like Lotus before it, is the first place many people
encounter the ability to create their own logical conditions.

For a huge subset of these people, it's also the LAST such tool they learn.
And now they have a hammer, so everything is a nail. I don't just mean finance
people who live and die by spreadsheets; I've met engineers who would solve
problems in Excel macros that would've been better attacked in perl or Python.

The other "problem" is the degree to which a horrifying number of
organizations end up depending on very, very complex spreadsheets full of
arcane and undocumented formulas and macros, and for which change control is
"save-as."

But, again, none of this is a problem with the product itself, or with the
idea of spreadsheets.

~~~
brootstrap
Yeah i agree with you. Excel is very powerful to some degree but like all
things it can be abused.

somewhere in a cube farm i can hear the shouts...

"Hey Karen did you get the latest budget sheet?"

"Is it budget_final_v2_2019.xlsx?"

"Damnit Karen that is from last week dear god dont tell me you sent that to
corporate!! We are on budget_final_FINAL_v4.xlsx"

~~~
tvanantwerp
I've been showing a team of economists working on some economic modeling how
to use git and R. They were amazed that they could keep working on a file
without creating backup copies, or worrying about team mates overwriting their
work. It's a work in progress, but they are eager to learn and understand the
value there.

------
mdorazio
The sheer number of everyday business use cases that can be solved quickly and
efficiently with spreadsheets is pretty astounding. Of course, there comes a
point when heavily-used spreadsheets become intricate behemoths on which core
business functions are run, and then things can get bad in a hurry. The
challenge many programmers face is thinking that _every_ business problem is
best solved with code, when in reality the majority are solved faster, more
efficiently, and more maintainably with a spreadsheet. Then the rest are worth
developing "real" solutions for.

~~~
chaostheory
> The challenge many programmers face is thinking that every business problem
> is best solved with code, when in reality the majority are solved faster,
> more efficiently, and more maintainably with a spreadsheet.

It's not like you can't do both. Excel supported VBA macros for a long time.
Now it supports JavaScript.

~~~
froindt
It still supports VBA. I've seen multiple manufacturing facilities which are
absolutely reliant on VBA macros to be able to produce and ship their
products.

------
6gvONxR4sf7o
For the last few years I've believed Excel is the most important piece of
software written, no hyperbole. The reason is that it's so damned accessible.
Software is eating the world, and letting people who don't write "true" code
still kinda write code opens up software's productivity and automation
benefits for a bunch of use cases and an insane amount of people.

For programmers, the analogy is python. It's almost never the best tool for
any specific thing, but it's a really solid tool for a ton of things. It's
easy enough to learn and has enough depth to keep learning. It's easy to
prototype a quick answer, and can not-to-painfully grow to a large complex
system. I think that's why you get people from all backgrounds using python.
It enables people who don't know much about software to start writing
software.

Excel's the same way, but with an even lower barrier (and probably lower
ceiling). It enables people who don't know anything about software to get many
of software's benefits. That's huge.

Just like python can great for a biologist whose focus is biology not
software, Excel is great for the accountant whose focus is accounting, not
software. It makes them a programmer, or at least close enough for many many
purposes.

------
mikekchar
My biggest problem with Excel as a "solution" is that document control is kind
of an afterthought. You've got X number of people in your organisation and
they each have these adhoc collections of Excel spreadsheets. Usually the
devil is in the details and you'll have one person who has calculated gross
revenue one way and another person who has done it another way. The two
approaches are never reconciled, but their output will invariably be compared
as if they are discussing apples and apples. I've seen so much analysis done
that was really just garbage because nobody understands where the data came
from and what it means.

It's not really fair to blame Excel -- it's a calculation tool. However your
solution space needs to address this problem and I very, very rarely see it
happen.

~~~
incongruity
It’s absolutely fair to blame excel. It obfuscates the code in favor of
displaying the calculated values - it’s _designed_ to hide the code - which,
along with a number of other design decisions, makes it really hard to build
an error free spreadsheet beyond trivial sized cases.

Further, its design makes reproducible data practices difficult - in contrast
to R or Python which do a lot to separate code from data - and let you re-run
the same code on new/updated data. Python and R (and other non-spreadsheet
tools) encourage practices that make keeping raw data pristine with work being
done on copies of the data. In contrast, it’s really easy to make mistakes
with Excel in ways you’ll never catch. Sorting within filtered columns is a
good example. Did you add another column after creating the auto filter?
Surprise, data in that column won’t sort with all the other data when you use
re-sort one of the original columns. Just like that, poof, silent data
corruption with no easy way of reverting if the error isn’t caught quickly.

~~~
mikekchar
Ironically org mode in Emacs would be better in that respect :-)

~~~
jacobush
I almost cracked the org-mode code. Almost. Used it for some sheets but now I
only use it for my journalling. Hopefully it will keep the embers hot until I
decide to pick it up for real again.

------
taneq
I feel the same way about Access, too. It doesn't matter whether they're "real
programming", it matters that they let real people solve real world problems
for themselves, who otherwise would have to pay real programmers real money to
develop real applications.

~~~
Someone1234
Has Access solved the corruption issue that plagued it for tens of years?
While you can connect it to a "real" database engine trivially, a common use-
case is via a single MDB file that could become damaged.

You could damage the MDB a number of ways including:

\- Leave Access running before shutdown (or power loss)

\- Intermittent network outage to the file server

\- Multiple users trying to access the same MDB (or anti-virus scans/locks,
even on another user's machine)

\- JET inconsistent versions / Access inconsistent versions / Patch Levels

The biggest headache though with Microsoft Access was never the product
itself. It was that the product didn't really have a natural evolution. You'd
start with a single MDB/single employee, but one day you'd need two employees
or more (and security, and more tables, and this and that), and while you
could migrate the MDB into a real database engine ($$$) and use Access as the
front end, the record locking was funky and scaled poorly (plus control was
limited).

The whole product felt a bit like a mouse-trap. A nice shiny piece of cheese,
that genuinely tasted good/worked well, but as soon as you tried to move it
SNAP. I don't dislike Access, but it was always painful when a business
outgrew it (whereas Fortune 500 companies live and die on Excel).

~~~
chaostheory
Access and FileMaker made sense before the advent of open source, specifically
Rails. Django, Postgresql, and MySQL. Both products make little sense now.

~~~
jacobolus
> _Rails. Django, Postgresql, and MySQL_

These are _much_ harder for non-programmers to use than Access. With Access,
almost completely non-technical people can set up their own database and make
the queries they need to answer their own questions. With Postgres accessed
from a general-purpose programming language, non-technical people need to hire
someone to help them with every basic task.

As far as I can tell Access has nothing to do with making websites, so it’s
unclear what Django or Rails has to do with anything.

~~~
chaostheory
> As far as I can tell Access has nothing to do with making websites, so it’s
> unclear what Django or Rails has to do with anything.

You can make apps with Django and Rails. They're not just "websites". Web apps
are also still useful even when they're not public facing.

> These are much harder for non-programmers to use than Access. With Access,
> almost completely non-technical people can set up their own database and
> make the queries they need to answer their own questions.

That's the thing. Access and FileMaker aren't typically run by non-technical
people. Yes, they aren't initially programmers, but they're usually technical
people. FileMaker & Access users, whether they realize it or not, become
programmers. I feel that you're confusing Access & FileMaker users with users
of Excel.

> With Postgres accessed from a general-purpose programming language, non-
> technical people need to hire someone to help them with every basic task.

A relational database is not a big leap from either Access or FileMaker.

I know all of this because I used to work closely with a team of these people,
and at some points I've even helped maintain their code.

~~~
jacobolus
> _I feel that you 're confusing Access & FileMaker users with users of
> Excel._

I know several people who use or used Access / Filemaker who were non-
technical with previous experience mostly consisting of Word / light Excel
use.

For example, my anthropologist parents used Access for analyzing their
manually gathered census data for a small rural village.

The volunteer docents at a local museum in my hometown used Filemaker for
managing the museum collection.

> _whether they realize it or not, become programmers_

Using the graphical tool in Access to construct queries does not require
becoming a proficient programmer.

> _A relational database is not a big leap from either Access or FileMaker_

These _are_ relational databases. They just have user interface affordances
intended for non-technical users. Postgres does not.

~~~
chaostheory
> For example, my anthropologist parents used Access for analyzing their
> manually gathered census data for a small rural village.

That's surprising. Most of the time, this is what Excel & Wordpress are used
for by non-techies, since both FileMaker and Access feel daunting to most of
them. Maybe this is exclusive to museums? This is anecdotal, but I've worked
in a lot of different industries, and in all of them everyone maintaining
FileMaker or Access were also knowledgeable enough to code in those platforms
ie. they were techies before they started using FM or Access

------
Mvandenbergh
It's interesting how often people's response to the manifest problems caused
by people using Excel in unfortunate ways is to either suggest tools with much
steeper learning curves or to suggest additional Excel features. 99% of these
problems can be solved by proper training and standards appropriate to the
problem and context at hand.

For instance: Always separate and label inputs, calculations, and outputs.

Document where source data has come from, where one cell has had an ad-hoc
adjustment made, what formulas do.

Use some type of version control and don't keep loads of concurrent versions
around floating on email and local hard drives.

If the spreadsheet loads data from external sources, try and make that load
automatic and live to prevent staleness.

Consistent formatting rules.

If data is tabular, put it in an Excel table. If data is tabular and we are
always doing the same queries on it, and it is large then we move it to a
database but that rarely happens.

Make it clear who owns spreadsheets and is responsible for keeping either/or
data & functions/formulas to work.

Do all of this first, only then start thinking of replacing Excel with
something else.

~~~
mulmen
Don't try to fix the user.

If we lived in a world where your suggestions were followed Excel would indeed
be an OK tool. Here in the real world however Excel has just the right
combination of power and usability to shoot off every left foot in a five cube
radius, and frequently does.

~~~
TeMPOraL
Note that the reason why Excel is popular is precisely because people "don't
try to fix the user", but instead develop software solutions tailored to
address (author's misunderstanding of) a specific use case users have. The
problem is, the specifics of that use case changes much faster than the
dedicated software can be updated.

Processes that are thoroughly understood are easy to automate. Those that
aren't, require competent people with flexible tools. Excel is a flexible
tool. Competence can be gained through training.

------
d--b
People here still don’t get it. It’s not that spreadsheet are great for non
programmers, they are great for programmers too.

The combination of UI and live calculation engine is unique.

How long does it take to make a pivot table with conditional formatting in
Python?

How long does it take to have input validation in JavaScript?

Sure maybe a couple hours. But it takes literally 2 seconds in Excel.

Couple Excel with a fonctional programming addin and you’ll beat a Python
programmer on 90% of data oriented tasks you may want to do.

~~~
renjimen
Hmm, I'm not sure about that! Formatting in Python, sure - because it's not
designed for outputting colours and type face. But the popular Python package,
Pandas, makes Excel-like tasks a breeze. I'll often help colleagues who can't
code to do something with their data that is nothing more than a single
"groupby" or "apply" expression in Python+Pandas.

------
thom
I always like these threads where programmers talk about tools like Excel and
say they’re fine for small stuff but grow out of hand, as if 90% of codebases
built by professional programmers aren’t exactly the same.

~~~
davedx
But professional programmers (emphasis on professional) are well aware of how
codebases can grow out of hand and employ entire categories of theories,
strategies and tools to keep their software maintainable, even when under
delivery/time pressure. Excel doesn't really have much in this category of
theories and tools.

~~~
fzumstein
You can actually unit test your Spreadsheets surprisingly easily with Python:
[https://www.slideshare.net/xlwings/automated-testing-of-
exce...](https://www.slideshare.net/xlwings/automated-testing-of-excel-
workbooks)

------
Someone1234
This video by Joel Spolsky taught me a lot of new Excel tricks/QoL
improvements:

[https://www.youtube.com/watch?v=0nbkaYsR94c](https://www.youtube.com/watch?v=0nbkaYsR94c)

It is amazing how rich the ecosystem is. I didn't know about pivot tables.
Excel has always impressed me, and continues to do so the more I learn.

~~~
shaklee3
The other classic example is the disgraced Martin schkreli:

[https://youtu.be/jFSf5YhYQbw](https://youtu.be/jFSf5YhYQbw)

------
simonh
I used to work at a bank that built a huge integrated application development
and deployment stack on Python. One aspect of it was a custom library
implementing a Directed Acyclic Graph, basically a way to ink up properties so
that when the results of one property changed, any dependent properties on the
DAG would automatically recalculate. This was explicitly implemented so that
we could directly translate the logic from Excel sheets into Python code.

A lot of our projects were built to accept an excel sheet as input, then
generate another Excel sheet as the output. The idea was the logic and data
manipulation and transformation should be in source control, but the Quants
would still have Excel available to build graphs, pivot tables and do
statistical analysis on the results.

The DAG also turned out to be pretty handy for implementing web applications
and all sorts of other apps though.

~~~
clausok
We also found using Excel as an output to be a powerful design pattern. Excel
is a great language for explaining the flow of calculations. We used to have
WPF screens where a portfolio manager / trader / analyst could right-click on
a number and select "show details" and he/she would get an Excel workbook
where that same number would be selected and they could see the derivation by
following the cell's precedents. It was not always feasible to show a complete
derivation, but we'd try to show a useful amount of elaboration. This was a
heavily used and loved feature, often leading to the users making edits or
additions to these detail sheets and giving it back to us as a spec. Building
the 'details' Excel books programmatically was a lot of work, but part of the
payoff was that we also used them as a nightly check on our primary system,
i.e., we'd automatically run 'show details' on everything and make sure the
'detail' result matches the primary system result. That comparison caught many
bugs. Kent Beck: "More important than tests vs types is the principle of
double checking. If you say something twice in independently derived ways,
you're more likely to be correct than if you just say it once. Tests are a
form of double checking. So are types."

------
mirimir
I love spreadsheets. When I'm working out a method for analyzing some dataset,
I just hack it out in Calc. Or if it gets too big, Excel. Once I've blocked it
out, I move to MySQL. But I can do any database operation in Excel, or even
Calc. Maybe not efficiently, but well enough.

But I've also learned that basic Linux tools (grep, sed, tr, awk, sort, uniq,
etc) are far more efficient for cleaning and preparing data for spreadsheets
or databases.

And then I use spreadsheets for final analytic steps, stuff that SQL doesn't
do efficiently, and for charting. I could learn Python and R, I suppose, but
SQL and Calc/Excel have always been enough. And Gumeric, sometimes, because it
can do some amazing charts that the others don't.

~~~
mkl
What Gnumeric charts are you referring to? The ones I could find [1] seem
pretty standard.

[1] [https://help.gnome.org/users/gnumeric/stable/sect-graphs-
ove...](https://help.gnome.org/users/gnumeric/stable/sect-graphs-overview-
types.html.en)

~~~
mirimir
I was thinking of min-max. You can select an area of cells as a series, and so
display numerous min-max lines. Also matrix and contour. Those aren't in Calc
5. Maybe they're in Excel now. I have an old Windows 7 version.

------
qrybam
Excel is powerful. It does so many things really well. Sharing data between
sheets in real-time isn’t one of them though (maybe I just haven’t found the
right tool, so we went ahead and built one).

I worked at a large fintech company a few years ago. During my interview Excel
popped up as a topic, the interviewer quipped “Excel is terrible!”, he was
referencing the heavy reliance of his customers on spreadsheets rather than
the “better” functionality they offered via their platform. A few years before
I would have agreed, but Excel really is amazing, it allows almost anyone to
just-get-things-done.

There are plenty of cases where Excel projects had grown to the point where
specialised software would be better for the business and the users... and if
you’re in an industry with heavy and advanced usage of excel (like the fintech
space), it’s a great place to mine solid ideas for a startup. Just don’t try
to recreate everything Excel does! Focus on areas it doesn’t excel in.

~~~
kgwgk
> Sharing data between sheets in real-time

Do you mean between sheets in two Excel files which are open in different
computers? Or did you encounter performance issues in a single computer?

~~~
qrybam
Two (or more) excel sheets running on different computers. Afaik it’s
impossible to link cells in real-time between different PCs natively.

------
Blackstone4
Having worked a decent amount in Excel, here are my key issues:

\- VBA Macros and Sheets are two distinct paradigms. Within an Excel file, it
is not always clear how the two interact and requires meaningful digging.

\- Once a numerical model has been calculated in a sheet, it is difficult to
scale it. Yes, it is possible to copy sheets but if you make a change or want
to do something 100's+ of times, it's a pain.

\- Data integrity is a problem. Opps I pressed the wrong key and I deleted
some data. Oh shit, I don't have Git to compare what was changed.

PS article dated 2017

------
jedberg
For a long while, my dad's job was as a consultant, and his job was to convert
Lotus spreadsheets to Excel spreadsheets (and sometimes small databases). That
was it. The spreadsheets he worked on were responsible for moving millions and
millions of dollars around.

One of his jobs was for a major movie studio updating their sheets that
calculated royalty payments. Every actor that ever worked on a show
distributed by that studio relied on the accuracy of that single spreadsheet
for their "money mailers".

------
chewxy
Funnily enough, excel is a programming language. I don't mean VB Script or
macros. I mean with the equations, ranges, and constraints - that's a
programming languages there.

A cell is simply a computational variable (as opposed to the notion of
variables in lambda calculus). A named range is a data structure (a struct).
The rest are term rewriting

~~~
matt-attack
Isn’t the most relevant distinction when comparing a spreadsheet to
programming is that it’s non-procedural? Writing spreadsheets reminds of
writing Makefiles. There’s no start or end. Just a bunch of declarations about
relationships. And it’s just...happens.

~~~
chewxy
There's a term that's gaining popularity in the last 15 years: reactive
paradigm of programming.

~~~
wutbrodo
Is what we're talking about any different from declarative programming? That
term has been in use for several decades

~~~
dymk
Reactive programs don't have to be declarative. You can express a reactive
program imperatively.

The defining feature of a reactive program is that relationships between
inputs and outputs are automatically tracked, and changing an input will
automatically update the dependent outputs.

Facebook had a whole experimental language (now abandoned) for writing
reactive programs with imperative code:
[http://skiplang.com/](http://skiplang.com/)

------
dvdsgl
This is exactly why we're building
[https://glideapps.com](https://glideapps.com)!

~~~
thundergolfer
Hey, cool idea.

At my company we've built a slack bot that sits in front of a google sheet to
manage transactions (eg. borrows) in our office library.[1]

I'd love to know whether this could be a Glide app, or whether we'd run into a
technical limitation where we can't have users scan a book's ISBN and have
that do a lookup in the sheet.

1\. [https://github.com/thundergolfer/library-management-slack-
bo...](https://github.com/thundergolfer/library-management-slack-
bot/issues/18)

~~~
dvdsgl
We don't have barcode scanning yet — it's a bit tricky to pull off as an iOS
PWA.

------
mooreds
I always say that spreadsheets are the saas startups worst enemy, or
opportunity. Oh wait, that was patio11:

[https://mobile.twitter.com/patio11/status/655674551615942657](https://mobile.twitter.com/patio11/status/655674551615942657)

~~~
TeMPOraL
Huh, I have the exact opposite feeling. If a SaaS app can be boiled down to a
specific spreadsheet + web UI bolted on top, it's making money by wasting
everyone's time.

Maybe I should get back to Twitter.

------
kfk
And as I grow older I get more and more convinced that Excel is overrated. I
ask myself, WHY in Excel so popular? And I keep coming back to the same
answer: because it's live. But we do have this concept in the programming
community (see links 1 and 2) and it's not theoretical. When I teach Python I
see people quickly understand everything that can be made live. Do this,
create a variable, then create a function, then let them play with the
variable and see how the output changes. This is also why SQL is so popular,
because you "declare" it and forget it and you see the changes live.

Don't get me wrong, I love Excel, but it has its problems and it definitely is
not a solution to everything data.

[1] PANE:
[http://joshuahhh.com/projects/pane/](http://joshuahhh.com/projects/pane/) [2]
LIVE: [https://2018.splashcon.org/track/live-2018-papers#event-
over...](https://2018.splashcon.org/track/live-2018-papers#event-overview)

------
stuart78
One other thing in defense of spreadsheets is that they are a tremendous
introduction to data manipulation and management for people who don’t think of
themselves as being ‘programmers’. Purpose built SaaS apps pre-solve problems,
and that can be great, but spreadsheets allow users to become owners and there
is a lot of growing that can start from that place.

------
jsilence
The European Spreadsheet Risk Interest Group estimates that >90% of
Spreadsheets contain errors/bugs.

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

~~~
lunchables
What percentage of software would you say contains bugs?

~~~
lmm
Clean error bugs are much less dangerous than silent data corruption.
Spreadsheets are like a memory-unsafe language in that respect.

------
linker3000
That's all very well and good, but can I make a plea for organisations to stop
using spreadsheets to produce tabled documents.

This seems to be endemic for information security surveys and would be fine
for questionnaires with preset answers, but when a question starts
"Describe..." and wants a full description of your software development cycle
from an InfoSec perspective, it's painful edit-wise - even if you can
copy/paste an answer from a different spreadsheet completed earlier.

~~~
Nerada
As the guy on the other end who has to read the responses; it's not fun here
either. Though, there's movement in the SaaS space that looks to solve a lot
of this. Proper text input fields, automated yearly requests for update, audit
logging and notifications (x party has responded, y party asks for
clarification on question z, etc).

Expensive though, but enterprise solutions always are...

~~~
lunchables
Smartsheet is a really great example of this.

------
alpaca128
> From there, you can calculate literally anything, and transmit not only the
> results of those calculations, but the actual environment itself, to anyone
> in the world, and expect that if they have a computer, they can replicate
> your results.

...unless they use Excel in a different language than you, in which case it
can't interpret the commands or sometimes even the syntax. Like with the
german version of Excel which requires ; instead of , as separator for
arguments, among other things. This alone makes me prefer pretty much
everything else.

------
hjk05
Your strengt is your weakness is your strength. Spreadsheets excel at ad-hoc
things because they aren’t systems, they don’t have restrictions and
limitations on what data you can pull in or where you store it or what you
calculate. They are horrible for all the same reasons, people end up spending
tons of time semi manually joining up data from different sheets and copy
pasting numbers from emails into calculations. The sheets take on a life of
their own as they are emailed around between people, and errors like crop up:
“oh, I think you have the version of the sheet that doesn’t work in
October[real example], that’s got fixed in a version that was sent to Peter,
check the mail stream to see who the original was sent to..” And they grow
over time without any obvious way of optimizing old dependent calculations so
you end up with sheets that do company wide economic calculations but take
10min just to open, so IT ends up setting up batchjobs to open it everyday,
copy in new data and generate PDF of results[also real example].

Of cause you can just put systems and rules around your spreadsheet practices
and start adding passwords and accompanying documentation of “what you must do
when using this document”. But When you get into these situations it’s almost
always a better decision to set up an actual system to handle your data.

------
makecheck
While it’s possible to “easily” write a computer program that is hard to
understand later, the gap is much wider with Excel: many more people can
create stuff, and the risk of being left with an undecipherable mess seems
greater.

I am also still, in 2019, risk-adverse to these complex file formats, even if
they are widely used. I can open a real computer program in dozens of editors
and run them on many platforms. Yet with Microsoft’s _own SharePoint solution_
, half the time Excel files can’t be opened: my web browser just _hangs_ and
then I _have to download and open the file in Excel_. That’s just crap.

For me, spreadsheets are also frustrating because they can make very poor use
of space (and this happens in some other user interfaces too). I shouldn’t be
forced to see only 3 numbers at once on a giant display just because they
_happen_ to be in cells that are a million miles away and separated by useless
empty/unused cells. This feels like what you’d see if web sites decided to
just dump their raw database tables onto the screen instead of presenting the
data usefully. My theory is that people are just really adaptable, to an
unpleasant degree; I’m amazed when I see people squint and tolerate absurd
truncation of data and other unhelpful displays.

------
quibbler
Maybe it works because it ofyen doesn't matter.

In cases where Data Science is a replacement for astrology ("just tell me some
reassuring mumbo jumbo to alleviate the burden of decision making), the
inevitable bugs may be irrelevant.

I appreciate the theoretical power of Excel. I just don't see how keeping
logic in Spreadsheets bug free could be possible. The logic is hidden away and
hard to get to. It is already hard enough to debug classical code.
Spreadsheets seem impossible.

------
thrwayxyz
I worked as a spreadsheet DevOps in a financial company. We used to have a
python script to spin up windows vms with the exact version of windows that
was known to be good, with the exact version of excel and then automatically
kickoff the computation.

The reson for this was someone once got the order of execution wrong and
screwed up a couple billion dollars worth of trades.

Excel is great until you have to maintain it. Same reason why you don't let
people build bridges with Lego.

~~~
Lukas1994
Many more stories like this: [http://www.eusprig.org/horror-
stories.htm](http://www.eusprig.org/horror-stories.htm)

------
thanatosmin
Is there anything like a spreadsheet to Python converter, with cells becoming
made up variable names? I wonder if it’d be useful for quickly noodling
together a set of expressions, then “baking” it into code.

~~~
phonon
[https://dirkgorissen.com/2011/10/19/pycel-compiling-excel-
sp...](https://dirkgorissen.com/2011/10/19/pycel-compiling-excel-spreadsheets-
to-python-and-making-pretty-pictures/)

------
The_suffocated
One major drawback of using spreadsheets is that it is harder to spot
calculation mistakes. A famous/infamous recent example is the Reinhart and
Rogoff incident in 2013, which involved their heavily cited paper "Growth in a
time of debt". When a research student named Thomas Herndon tried to replicate
the paper's findings, he found, after a careful inspection of the Excel
spreadsheet the two Harvard professors used, that the spreadsheet was ridden
with errors. Some of them were even in principle rather trivial, such as
mistaking the sum of one column as the sum of another. The incident was widely
reported in many business newspapers. Paul Krugman had also commented on it on
New York Times.

Had the steps of computations been written as a program, it might be easier
for the authors to discover their mistakes. With a spreadsheet, if you put
formulae inside data cells, you need to click all these cells one by one to
see if the formulae have been input correctly. This is tedious.

~~~
jaclaz
>With a spreadsheet, if you put formulae inside data cells, you need to click
all these cells one by one to see if the formulae have been input correctly.
This is tedious.

Not really-really, you can choose to show formulas in settings. (reading them
without making a mess of the formatting/size of cells is another thing, but
you can always make a copy and inspect formulas there, "ruining" the
formatting). It is still tedious, actually very tedious, but as tedious as
reviewing source code.

------
module0000
If spreadsheets work for your use-case or industry, then by all means keep
using them. Occasionally, I'll use one myself for some business purpose.
However...the problem with spreadsheets is that people routinely use them for
the _wrong_ reasons. Those wrong reasons are the cause of the spreadsheet
hate. It's not spreadsheets themselves that cause this, it's people with
limited knowledge and limited ambition to learn how to use the correct tool
for a task.

The saying _" when all you have is a hammer, everything looks like a nail"_
applies.

Often, someone using spreadsheets will suspect that their task may be better
accomplished in some manner of programming. This leads them to try to do it.
If they are in this situation, chances are, they are not a professional
programmer. They are going to have a poor experience, and likely fail. This is
due to sub-par or non-existent software engineering skills, and not due to
spreadsheets being the correct tool for every task.

------
punnerud
Spreadsheet is great and dangerous!

Try to find duplicates or do vlookup in a table/sheet with more than 10.000
rows. It will only look at the first part of your data, and skip a lot if you
don’t remember to sort your columns first.

Yes spreadsheet are a great start. I often ask people to “prototype” in Excel,
after that it is SQL that rules.

And remember: Pivot in Excel = ‘Group by’ in SQL

~~~
kgwgk
I don't know what do you mean by "find duplicates", but VLOOKUP has a option
to specify an exact match so you can use it even if the data is not sorted
(MATCH does as well).

------
astrobe_
Excel is a first order optimal strategy [1]. It is good for casual players,
but not good enough for pro players.

[1] [http://www.realityrefracted.com/2011/03/first-order-
optimal-...](http://www.realityrefracted.com/2011/03/first-order-optimal-
strategies.html)

------
ChrisCinelli
This morning I was just thinking how spreadsheets are a great tool that would
be worth an blog post.

In the past I built server dashboards on Google Sheets that consume live data
through JSON and automatically refresh.

I wrote a real time labeling system for ML to categorize images in less than a
day.

I even wrote a behavioral reinforcement system to build good habits that shows
in real time the results on an Android widget.

A spreadsheet is a relatively very simple tool that most of the people
understand (and often undervalue). It has a lot of flexibility in it and let
you build "good enough" interfaces for internal tools in a fraction of the
time that takes to build a web UI and they are are easy to iterate on.

You get a lot of value for time invested on a Google Sheet. They are free and
from a user prospective do not require any infrastructure to run them.

"Keep things as simple a s possible" is my motto.

------
stevewillows
Spreadsheets are the best -- specifically Google Sheets. The addition of QUERY
really allows Sheets to be superior to Excel, at least for the things I use it
for.

Combine that with an IMPORTJSON script, and you can create really clean,
structured data sets for nearly anything you can dream up. I've been tracking
movies and TV shows, for instance. Combining a few of the APIs out there to
get the best possible results really makes the set shine.

I don't code beyond modifying basic things, but I rarely find something I
cannot do with a spreadsheet.

[1]
[https://github.com/bradjasper/ImportJSON/blob/master/ImportJ...](https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs)

------
teddymax
I love spreadsheets. They are many people's first experience with programming.
Cells are essentially a functional programming environment.

People have a much easier time learning basic functional programming and
spreadsheets help a lot.

~~~
selotapemeasure
[https://github.com/Glench/Flowsheets-v2](https://github.com/Glench/Flowsheets-v2)

------
gclaugus
I've been doing Excel and VBA work for my internship this summer, and I have
to say, I have an immense amount of respect for the engineers behind Excel.
Excel is incredibly extensible, it has very nice built-in error handling for
mangled data (but horrible exception handling in VBA), and overall it is
simply a joy working with it.

Sure, it has some pain points, but for my use case (financial analysis), it
complements Bloomberg very well. Bloomberg's Excel add-in is very well-
engineered, and there is even a way to hook into Bloomberg through VBA. Cheers
to the MSFT devs who crafted this stuff.

------
zelphirkalt
I think spreadsheet usage can be done in good ways, but not when it is done in
Excel, because Excel is a typical MS lock in product.

Things that should be obvious to do when having something important in a
spreadsheet:

* Use well labeled (row name, column name, cell above, below, left of it, whatever) cells for in-between results. * Have some checking for mistakes formulas for cells to show you a warning when there seems to be some calculation or entry mistake - like assertions in normal programming. * Use plain text formats and use version control. Do not run around with 10 copies of the file named after what the index of the copy is. * Have explanations of the formulas and the reasoning behind them somewhere, maybe even best inside the spreadsheet. * Make only use of macros in there is no other way. Macros simply break things, at least in Excel. Only a few days ago I witnessed a case, where someone simply could not run some macro, even after reinstalling Excel, using an MS cleaning tool for "completely" removing Excel and various other attempts. And it only happened on that person's machine. The macro is not as reliable as plain text formulas. * Have your data elsewhere as well. Do not use a spreadsheet as your single database.

And those are only the few things that come to my mind, although I am not a
daily spreadsheet user. More frequent users might have many more guidelines.

I also recommend people to take a look at Emacs Org mode spreadsheets in
combination with various other Org mode functionality. Those can be quite
neat.

------
theclaw
I agree, as someone who spent quite some time building fancy admin dashboards
for our team, in my most recent project I chose to abandon this and just write
an Excel exporter and importer so they could edit the data in a familiar tool.
The flexibility of this was huge for our team - everyone in an office has some
experience of Excel and they were more productive with it than they ever were
with my previous data grids and clunky web based editors.

------
whyenot
Just don't try to use dates before January 1, 1900 (or 1904 on Mac). You can
work with dates in Excel 1,000 years in the future, but if you try to do that
for dates from the 1800s it will completely screw them up. I don't understand
why this problem still exists today. If you work in a museum, or anywhere else
where you deal with old dates, you have to constantly be on the lookout for
this "gotcha."

~~~
piokoch
This is not directly related to issues you see, but might shed some light:
[https://www.joelonsoftware.com/2006/06/16/my-first-billg-
rev...](https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/)
(plus this is one of the funnies Joel Spolsky's text and an amazing dive into
computers history).

~~~
whyenot
Thank you for sharing that, it's pretty interesting. I wouldn't have guessed
that it was to maintain compatibility with Lotus 1-2-3.

------
jacquesm
The spreadsheet is the original 'serverless' programming environment. It
allows relatively untrained people to augment their brains to levels that they
would otherwise definitely not achieve. This has obvious limitations but I
still believe that the spreadsheet is the 'killer app' for all of computing,
back when VisiCalc was first released the sales of small computers went up in
an incredible way.

~~~
anextomp
Serverless is really the wrong term to use here. That refers to a specific way
of handling cloud infrastructure (the point being that there are still
servers, you just don't care)

Excel is used for client-side only programs. There are no servers at all. All
programs originally were client-side only originally because networks didn't
exist.

~~~
DonHopkins
Google sheets (and other google docs) can be programmed in "serverless"
JavaScript that runs in the cloud somewhere. It's hellishly slow making sheets
API calls, though. Feels like some kind of remote procedure call. (Slower than
driving Excel via OLE Automation even, and that's saying something!) Then it
times out on a wall clock (not cpu time) limit, and breaks if you take too
long.

A CS grad student friend of mine was in a programming language class, and the
instructor was lecturing about visual programming languages, and claimed that
there weren't any widely used visual programming languages. (This was in the
late 80's, but some people are still under the same impression.)

He raised his hand and pointed out that spreadsheets qualified as visual
programming languages, and were pretty darn common.

They're quite visual and popular because of their 2D spatial nature, relative
and absolute 2D addressing modes, declarative functions and constraints,
visual presentation of live directly manipulatable data, fonts, text
attributes, background and foreground colors, lines, patterns, etc. Some even
support procedural scripting languages whose statements are written in columns
of cells.

------
hprotagonist
I would hate spreadsheets a lot less if they were testable.

Or didn’t “help” by silent type conversions that skew analyses (which you’ll
never notice because, uh, no tests)
[https://www.sciencemag.org/news/2016/08/one-five-genetics-
pa...](https://www.sciencemag.org/news/2016/08/one-five-genetics-papers-
contains-errors-thanks-microsoft-excel)

~~~
kyberias
What in spreadsheets is not testable? It's trivial to write a test that opens
the spreadsheet in excel and tests any formula calculations via Excel
Automation API.

~~~
hprotagonist
and where do i store my tests? how do i version them, and the data? can i put
it in travis?

how many people ever even try?

~~~
kyberias
You store them in the same place where you place other tests. It's just code.
You version the data as you version any other test data. Never used travis
myself, I guess it's some kind of CI.

Many people do this. It's perfectly normal.

Of course if you don't even use Excel in the first place, I don't know why
you're asking.

------
tome
A bit short on details. What exactly was it about this spreadsheet that made
it hard to recreate in a programming language? Personally I find this hard to
believe unless one of the key deliverables is "pretty much every parameter to
this calculation must be tweakable by the user with the results being
visualised instantly".

------
axilmar
Spreadsheets are great tools, but they are not suitable for every kind of
problem. I use Excel daily too, but I also use a lot of other programming
languages as well.

Most of the time, complex calculations are easier to do in a programming
language than in a spreadsheet, but viewing them in anything else than a
spreadsheet is a royal pain in the butt.

~~~
nsb1
This is one of the reasons that I use Google Sheets regularly for such tasks
at work. The API allows you to do all the hard work in your language of choice
and the post the output into a spreadsheet for viewing or other manipulation
better suited to spreadsheet-style interaction. We have implemented all sorts
of ad-hoc interactive sheets that can be viewed by anyone by emailing the
shared URL around. It's a powerful tool for transcending the code-to-reporting
boundary.

Love them or hate them, I think Google has done a great thing here. I'll be
sad when they decide to suddenly drop support for it ;)

------
tony-allan
A timely reminder that the current spreadsheet format is not the last word on
how to manage data and simple programs.

------
VBprogrammer
My first job out of University was working for a large US custodial bank (not
in the IT dept). We were responsible for monitoring corporate actions
happening on the securities held by their clients and accounting for the
proceeds.

Most of the work was done in some old IBM 390 terminal emulators. The work
flow was generally scrape a bunch of information from the terminal into Excel.
Reformat it and figure out any discrepancies. Copy some adjustments into
another workbook which would automatically enter the data into some screen or
other in the terminal emulator to fix the discrepancies.

Someone had built a COM object which could be scripted quite easily in Office
VBA. I found a few of the scripts they had written for it hadn't been
protected and quickly learned to write my own.

It was kinda fun in its own way.

------
tpaschalis
Spreadsheets _are awesome_ , and I think that another thing people are
dismissing is grep, awk and friends.

I get it that both spreadsheets and unix tools might not be trendy, but they
fulfill the Taco Bell Programming idea [1]; they provide simple, scalable, and
efficient solutions.

Summing data from column B for each unique value in column A would be as easy
as

`awk -F ',' '{a[$1] += $2} END{for (i in a) print i, a[i]}' my-input-
file.csv'`

Sure, it would take a few tries to get right, but could for example R do
something _this_ concise and dynamic that can run in parallel?

[1] [http://widgetsandshit.com/teddziuba/2010/10/taco-bell-
progra...](http://widgetsandshit.com/teddziuba/2010/10/taco-bell-
programming.html)

~~~
asutekku
Yes and no. That command is easy to understand/use only if you know what all
these symbols do but most excel functions are written in a way most people
will understand them without major difficulties.

Grep, awk and friends are powerful (only) in the right hands but they are not
at all at the same ballpark as excel.

------
ashutoshclappia
We were building our zero code app creation service and we also faced the
situation where the users needed to add complex logic, calculations etc. and
creating separate draggable modules and buttons for every requirement would
not really solve the problem.

So we added draggable blocks and provided autogenerated variables for all
elements just like the cell number in Excel. For all complex logic and
calculations, the users can simply write a formula the way they write in Excel
and complex apps can now be created without any coding language.

We also have huge respect for Excel.

If you are interested, you can try our software here:
[https://clappia.com](https://clappia.com)

------
qwerty456127
I can hardly imagine what is that which is easy in Excel but hard in Python
with Pandas and SqliteiteAlchemy/Pytable/whatever and matplotlib.

What really annoys me in Excel is they won't replace the fossil VBA with
Python, F# or a new language designed from scratch right for this. The VBA
environment feels fun to touch to have the feeling of time-travelling back to
the years of your childhood but it feels quite clumsy in actual programming.

Excel's plots feature also feels fairly weird. I could never make it to
produce exactly the plot I want. Perhaps that's because I haven't mastered it
but this means it is harder to master than matplotlib is.

~~~
Sahhaese
You lack imagination.

I'm a professional software developer and I open excel every day.

Let's say someone emails me a list of figures and I want to quickly add them
up?

Sure, I could write an incantation in awk but I can't then see if it's wrong
because maybe on one row they 'accidentally' put an extra blank column in
before the number by marking it with a letter, or there being an extra tab or
whatever might cause that.

It's far quicker and less error prone to pop open excel, paste it in and then
sum the column, and most importantly there is clear visual feedback if that
doesn't work. In awk or a programming environment you'd just either get an
incorrect figure and never know it was incorrect, or you'd get an error (e.g.
trying to add a letter and number) and then have to debug what should have
been an instant thing.

Excel shines for doing one-shot data processing.

~~~
qwerty456127
Sure, but he wrote something was a way to difficult to program while easy in
Excel.

------
giardini
Maybe.

Jocelyn Ireson-Paine has a deep knowledge of the problems and some solutions
for spreadsheets:

[https://johncarlosbaez.wordpress.com/2014/02/05/category-
the...](https://johncarlosbaez.wordpress.com/2014/02/05/category-theory-for-
better-spreadsheets/)

Paine has long worked with the European Spreadsheet Risks Interest Group
(eusprig.org) and has used category theory to develop a system in Prolog
called Excelsior about which he says "Excel lacks features for modular design.
Had it such features, as do most programming languages, they would save time,
avoid unneeded programming, make mistakes less likely, make code-control
easier, help organisations adopt a uniform house style, and open business
opportunities in buying and selling spreadsheet modules. I present Excelsior,
a system for bringing these benefits to Excel."

Examples of Excelsior:

"Less Excel, More Components: presentation to EuSpRIG 2008 [by] Jocelyn
Ireson-Paine":

[https://www.j-paine.org/eusprig2008/index.html](https://www.j-paine.org/eusprig2008/index.html)

"Excelsior: bringing the benefits of modularisation to Excel":

[http://j-paine.org/eusprig2005_pres/presentation.html](http://j-paine.org/eusprig2005_pres/presentation.html)

"Rapid Spreadsheet Reshaping with Excelsior: multiple drastic changes to
content and layout are easy when you represent enough structure":

[https://arxiv.org/pdf/0803.0163.pdf](https://arxiv.org/pdf/0803.0163.pdf)

Paine's home page:

[http://www.j-paine.org](http://www.j-paine.org)

Paine's Safer Spreadsheet twitter:

[https://twitter.com/SafeSpreadsheet](https://twitter.com/SafeSpreadsheet)

------
gasa7
(This might be a different take on the topic, but just a thought) I have just
dipped my toes in the corporate world and to be honest, i have not heard the
term 'Excel' in the workspace or otherwise. Actually I did but that was also
when its major pain point was discussed: Online capability. A customer needed
an on-the-go Daily Sales Report app. (what I learnt and is obvious as well is
that the dependency on the office/Excel expert to send the report is not
ideal)

My point is, the shift to "the cloud" has already begun right? Freshers today
will be up the hierarchy tomorrow and are they really going to use Excel?

------
tbabb
This is what I am currently working on: Making programming fluid and easy like
spreadsheets (or alternately, elevating the niche occupied by spreadsheets
into something that can be called "real programming").

(If this is your jam, hit me up.)

~~~
dvdsgl
Me too! What are you working on?

~~~
tbabb
It's a node-based programming language— think boxes and wires, or circuit
diagrams. and you?

~~~
dvdsgl
That sounds awesome! I'm working on glideapps.com

------
xvilka
There is LibreOffice Calc, which allows you to program it with Python[1]. Best
of the both worlds. I recently created a suggestion [2] to support also Julia
language in it. Hopefully they will consider it.

[1]
[https://wiki.documentfoundation.org/Macros/Python_Design_Gui...](https://wiki.documentfoundation.org/Macros/Python_Design_Guide)
[2]
[https://bugs.documentfoundation.org/show_bug.cgi?id=125728](https://bugs.documentfoundation.org/show_bug.cgi?id=125728)

~~~
fock
did you ever "program" in LibreOffice with Python. Last time I checked, the
API were veeery thin wrappers around the Java-API itself - I guess that this
is hindering adoption (and not not having bindings to X)

------
ilitirit
Our had sophisticated mathematical models written for our industry. These were
developed in the late 90's/early 2000's. When they tried to port these models
to "a more modern format" to programming language code, they realised that it
was far more complex than they had anticipated, and even small mistakes would
be disastrous for the company.

Their solution? Dozens of virtual servers around the world, each one hosting a
copy of Excel with a 20-30MB workbook, communicating with the outside world
via TCP/IP and COM interface code.

------
l0w
Spreadsheets are great. I don't know any alternatives to spreadsheets, that
does, what spreadsheets does, so well. And believe me, I have been searching
for a long time.

That said, like any other tools, spreadsheets has it's perks and it's
drawbacks. It's important to consider these drawbacks, when considering
spreadsheets.

The people at Stencila have a very good take on this:
[https://stenci.la/blog/introducing-
sheets/](https://stenci.la/blog/introducing-sheets/)

------
TrackerFF
My only real problem with spreadsheets is that once they get too big, there's
almost no turning back. There's no easy fix if someone screwed up at the
beginning.

Even though conventional programming languages are less visual, it's so, so
much easier to modify models.

But with that said, I do use spreadsheets for 90% of my daily needs, as far as
calculations go. Just type "sheets" into the search bar, and I can start
working (google sheets) in 2 seconds. It's even faster than firing up notepad.

------
qntty
Anyone have any advice on how to learn to use excel well? I've only ever down
simple column operations before, but I've seen spreadsheets that do crazy
complicated things.

~~~
eigenvalue
Yes, check out the two books by John Walkenbach, Excel 2013 Formulas and Excel
2013 Power Programming with VBA. Not too much has changed since those
versions-- I originally read the 2007 versions of them and it all transfers
over because Microsoft is incredibly good about maintaining backwards
compatibility.

------
telotortium
There was a new app posted here a few weeks ago that combined a Jupyter
notebook with a spreadsheet-like automatic recalculation. Does anyone here
know what it's called?

~~~
TeMPOraL
You mean [https://observablehq.com/](https://observablehq.com/)?

~~~
telotortium
Yes, that's it. Thanks!

------
s_T_e_v_o
I convert cell equations to visual basic so that I don't screw up the cells
and so I can share my results without giving away all my code.

There are also methods to animate simple graphics for the purpose of providing
high level presentations.

I 'learnt to code' by recording macros and then reworking the syntax to suit
my needs. After a while it becomes much less difficult than it appears at
first. And I will admit my code isn't a model of perfection, but it gets the
job done.

------
mulmen
Spreadsheets are fine. Excel is not.

Excel conflates the ideas of data and presentation. This leads to an entire
class of headaches that just aren't necessary. It's the desktop application
equivalent of the string 'null'.

If the spreadsheet layer (calculations and formatting) was separate from the
data layer (types and values) then we could all be happy.

Wrap that up with a UI that wasn't designed at an office in Redmond (or for a
web browser) and you'd really have a winner.

~~~
t0astbread
This sounds interesting but I don't quite understand. Is this idea documented
somewhere more detailed?

~~~
mulmen
Not really, maybe I should write something. It's a thought I frequently have
in the depths of Excel Hell. I thought for a long time about _why_ Excel is so
frustrating to me. I think fundamentally it comes down to Excel putting the
spreadsheet ahead of the data.

If the data was clearly separated from the spreadsheet itself then the logic
becomes much easier to reason about and test and the data is no longer
susceptible to the Excel data loss.

The display layer really needs to be logically separate from the data. For
example if I change a column format from text to number and back I should not
lose the leading zeros in the actual data.

Spreadsheet tools are great visual programming environments but they are lousy
databases. The data layer in Excel leaves a lot to be desired but there's
really no reason a spreadsheet has to be so limited.

Basically I'd like to see some kind of hybrid SQL(database) client/Spreadsheet
UI/Pivot Table builder where all three concepts are first class.

------
DonHopkins
The thing that's missing from "Google Docs" is a decent collaborative outliner
called "Google Trees", that does to "NLS" and "Frontier" what "Google Sheets"
did to "VisiCalc" and "Excel".

And I don't mean "Google Wave", I mean a truly collaborative extensible
visually programmable spreadsheet-like outliner with expressions, constraints,
absolute and relative xpath-like addressing, and scripting like Google Sheets,
but with a tree instead of a grid. That eats drinks scripts and shits JSON and
XML or any other structured data.

Of course you should be able to link and embed outlines in spreadsheets, and
spreadsheets in outlines, but "Google Maps" should also be invited to the
party (along with its plus-one, "Google Mind Maps").

It should be like the collaborative outliner Douglass Englebart envisioned and
implemented in his epic demo of NLS:

[https://www.youtube.com/watch?v=yJDv-
zdhzMY&t=8m49s](https://www.youtube.com/watch?v=yJDv-zdhzMY&t=8m49s)

Engelbart also showed how to embed lists and outlines in maps:

[https://www.youtube.com/watch?v=yJDv-
zdhzMY&t=15m39s](https://www.youtube.com/watch?v=yJDv-zdhzMY&t=15m39s)

Dave Winer, the inventor of RSS and founder of UserLand Software, originally
developed a wonderful outliner on the Mac originally called "ThinkTank" and
then "MORE", which later evolved into the "Frontier" programming language, and
ultimately the "Radio Free Userland" desktop blogging and RSS syndication
tool.

[https://en.wikipedia.org/wiki/Dave_Winer](https://en.wikipedia.org/wiki/Dave_Winer)

[https://en.wikipedia.org/wiki/UserLand_Software](https://en.wikipedia.org/wiki/UserLand_Software)

More was great because it had a well designed user interface and feature set
with fluid "fahrvergnügen" that made it really easy to use with the keyboard
as well as the mouse. It could also render your outlines as all kinds of
nicely formatted and stylized charts and presentations. And it had a lot of
powerful features you usually don't see in today's generic outliners.

[https://en.wikipedia.org/wiki/MORE_(application)](https://en.wikipedia.org/wiki/MORE_\(application\))

>MORE is an outline processor application that was created for the Macintosh
in 1986 by software developer Dave Winer and that was not ported to any other
platforms. An earlier outliner, ThinkTank, was developed by Winer, his brother
Peter, and Doug Baron. The outlines could be formatted with different layouts,
colors, and shapes. Outline "nodes" could include pictures and graphics.

>Functions in these outliners included:

>Appending notes, comments, rough drafts of sentences and paragraphs under
some topics

>Assembling various low-level topics and creating a new topic to group them
under

>Deleting duplicate topics

>Demoting a topic to become a subtopic under some other topic

>Disassembling a grouping that does not work, parceling its subtopics out
among various other topics

>Dividing one topic into its component subtopics

>Dragging to rearrange the order of topics

>Making a hierarchical list of topics

>Merging related topics

>Promoting a subtopic to the level of a topic

After the success of MORE, he went on to develop a scripting language whose
syntax (for both code and data) was an outline. Kind of like Lisp with
open/close triangles instead of parens! It had one of the most comprehensive
implementation of Apple Events client and server support of any Mac
application, and was really useful for automating other Mac apps, earlier and
in many ways better than AppleScript.

[https://en.wikipedia.org/wiki/UserLand_Software#Frontier](https://en.wikipedia.org/wiki/UserLand_Software#Frontier)

[http://frontier.userland.com/](http://frontier.userland.com/)

Then XML came along, and he integrated support for XML into the outliner and
programming language, and used Frontier to build "Aretha", "Manila", and
"Radio Userland".

[http://manila.userland.com/](http://manila.userland.com/)

[http://radio.userland.com/](http://radio.userland.com/)

He used Frontier to build a fully programmable blogging and podcasting
platform, with a dynamic HTTP server, a static HTML generator, structured XML
editing, RSS publication and syndication, XML-RPC client and server, OPML
import and export, and much more.

He basically invented and pioneered outliners, RSS, OPML, XML-RPC, blogging
and podcasting along the way.

>UserLand's first product release of April 1989 was UserLand IPC, a developer
tool for interprocess communication that was intended to evolve into a cross-
platform RPC tool. In January 1992 UserLand released version 1.0 of Frontier,
a scripting environment for the Macintosh which included an object database
and a scripting language named UserTalk. At the time of its original release,
Frontier was the only system-level scripting environment for the Macintosh,
but Apple was working on its own scripting language, AppleScript, and started
bundling it with the MacOS 7 system software. As a consequence, most Macintosh
scripting work came to be done in the less powerful, but free, scripting
language provided by Apple.

>UserLand responded to Applescript by re-positioning Frontier as a Web
development environment, distributing the software free of charge with the
"Aretha" release of May 1995. In late 1996, Frontier 4.1 had become "an
integrated development environment that lends itself to the creation and
maintenance of Web sites and management of Web pages sans much busywork," and
by the time Frontier 4.2 was released in January 1997, the software was firmly
established in the realms of website management and CGI scripting, allowing
users to "taste the power of large-scale database publishing with free
software."

[https://en.wikipedia.org/wiki/RSS](https://en.wikipedia.org/wiki/RSS)

[https://en.wikipedia.org/wiki/OPML](https://en.wikipedia.org/wiki/OPML)

[https://en.wikipedia.org/wiki/XML-RPC](https://en.wikipedia.org/wiki/XML-RPC)

------
WeaselNo7
Spreadsheets are phenomenally flexible, but they obviously don't scale well or
behave nicely with source control.

I work at Anaplan, and the most common way that our biggest customers discover
us is when they've been bitten by spreadsheets as they've scaled, and now they
have users emailing spreadsheets around and someone with a full time job
collating them.

We've modelled the product around the flexibility, but rigor and scale on top
of it.

------
chasd00
I knew an admin assistant years ago who was an absolute Excel wizard. I liked
to just sit and watch her work, it was like watching a pro-level gamer.

------
_raoulcousins
Excel is everywhere where I work. it frustrates me to see how it's used and
how much it's used, and it's totally unfair. I could rant about how it's
unmaintainble, error-prone, time consuming, and cause costly mistakes, but I
wonder if the company would be as large as it was without Excel.

I dislike a lot about Excel, but it's so easy to be immediately productive
with it.

------
jedberg
> and transmit not only the results of those calculations, but the actual
> environment itself, to anyone in the world, and expect that if they have a
> computer, they can replicate your results.

I think this is the real secret. It's really the only kind of "model" where
the environment travels with it. Everyone has the same Excel setup. If it
works for you it will work for them.

~~~
weinzierl
> Everyone has the same Excel setup.

I wish this was true. The biggest problem is different language versions that
cause all sorts of problems. Did you know that Excel formulae names are
localized? For example AVERAGE is MITTELWERT if you have a German Excel and
there are some with umlauts as well.

But there are more subtler problems. I'm an engineer and I used to work a lot
with radians and degree values. There is this nice little trick in excel where
you can enter radians and have it display as degrees. It works using the date
format but only if you have 1900 dates enabled. I used to to use that for a
short while until I noticed that all dates in spreadsheets from other people
are off by 30 years.

------
_bxg1
I think one of the main takeaways is: included batteries are underrated. This
is seemingly one of the main appeals of Python.

Another is: an integrated solution that's been designed holistically, instead
of blindly assembled out of modules, does wonders for reliability. See: the
difficulty of setting up a JS build environment vs a Rust build environment.
Also, the success of Atom vs VSCode.

------
growt
Maybe jupyter is some middle ground. Ready to pass around, modify and
replicate, but the full power of a programming language and libraries.

------
KuhlMensch
I saw my housemate finish his degree in engineering (fluid-dynamics) using
ONLY excel. I have always respected spreadsheets since then.

------
pulisse
Difficulty recreating an Excel spreadsheet in a general-purpose programming
language isn't evidence of the superiority of spreadsheets, it's just a
consequence of the object/relational impedance mismatch. I _strongly_ suspect
that reimplementing OP's spreadsheet in MySQL wouldn't have presented any deep
problems.

------
makach
Spreadsheets are great!

But some times it is necessary to extract the logic, processes, and behaviors
from a spreadsheet into an application that can add necessary stability,
traceability and redundancy that an expert concurrent enterprise tool requires
from a legal, compliance and technical point of view.

I once was tasked with the unforgiving job to do exactly that.

------
arxpoetica
The intro to this Svelte (JS framework) conference talk discusses the miracle
of Spreadsheet reactivity:
[https://www.youtube.com/watch?v=AdNJ3fydeao](https://www.youtube.com/watch?v=AdNJ3fydeao)

------
hateful
I can't help but think that people using excel to accomplish their task
instead of writing a program is a lot like Pythagoras doing geometry without
algebra. When you get away from the numbers/code it makes it easier to
visualize things.

------
codeulike
The thing about finance trader types removing the F1 keys from their keyboards
so they don't accidentally press 'help' while using Excel at high speed says a
lot about how handy Excel is (and how slow the Help loads)

~~~
ubermonkey
Well, also about how unremittingly shitty F1 help is.

Googling the topic you need help on is invariably faster, even if all you
really need is, say, the syntax to a given Excel function.

------
sangfroid2
Excel formulas are the most widely-used functional programming language in the
world.

------
GolDDranks
I think spreadsheets are an absolutely great idea. The problem is the current
implementations. I have to deal with Excel files a lot in my work, and it's a
nightmare.

------
iddan
This is why I’ve built React Spreadsheet[1]. To empower users and programmers
to build interactive computational interfaces. [1]: iddan.github.io/react-
spreadsheet

------
stevezsa8
I feel this is a bit like comparing apples to oranges.

If every coder paid £100 for a programming language/compiler, I'm sure it
would also have a great UI and UX.

------
haolez
I’ve always felt that functional programming with lazy evaluation to be
analogous to spreadsheets in programming.

------
dlphn___xyz
excel is perfect for small quick tasks - when it starts to grow into thousands
of rows and a handful of tabs its time to find something else

------
every
Perhaps spreadsheets are end-user COBOL...

------
lucaspottersky
my browser was really delighted to download the 4MB illustrative cover art.

------
scentoni
[https://www.xkcd.com/1667/](https://www.xkcd.com/1667/)

------
irascible
Worked on a simulation game in the 80s.. boss coded the entire world simulator
as an excel spreadsheet.. I then wrote a tool to compile that spreadsheet and
its formulas into 68k assembly language that would run the sim for 1 tick. It
worked Amazingly well. I have never doubted the power of spreadsheets since
then, and whenever a problem in a similar domain comes up, it's one of my go
to tools.

------
dymk
I can bundle my work in an iPython/Jupyter notebook, and if I'm feeling really
frisky, I can package that in a Docker container.

I expect anybody with a computer can now replicate my work, and not pay the
cost of an Microsoft Excel license.

~~~
jedberg
After I set up a Jupyter server and/or docker.

With Excel, chances are I either have it on my computer, it is a single click
download away, or I have another program already on my computer that can open
it.

~~~
dymk
I certainly don't have Excel installed on my computer. It's not even free. And
it's far harder for me to install it compared to free software, like Docker or
Python (for which I can just type `brew install ...`).

If you own a Mac, it's already got Python installed on it.

~~~
jedberg
If you own a Mac then it comes with Numbers which can open Excel spreadsheets.

Also: `brew cask install microsoft-office`

------
shareIdeas
Ah yes, the confident programmer that would DIY everything.

I feel like underestimating Excel is a classic example how an expert can have
holes in their thinking.

With VBA, Excel becomes a ton more useful, adding programming. Excel is merely
a visual database you can share with coworkers and encapsulate in a single
file.

There is a time and place for everything. Excel is very useful for workplace
data sharing and manipulation.

------
simplecomplex
I like spreadsheets because they are reliable and logical, unlike whatever
trash programmers tend to use. Spreadsheets do what they say they do. Outputs
are functions of inputs, like math.

Meanwhile software “engineers” are using programming languages that eschew
logic, reject reason, reject mathematics, reject determinism, and do different
things each time you run them.

Yeah I’ll take a spreadsheet over whatever ball of confusion software
engineers are chasing their tail with.

