← Back to home

Excel and beyond

The good, the bad, and the ugly of Excel.

Every couple of months the fine readers over at Hacker News work themselves up into a real hoot over Microsoft Excel.[0] They alternate between love and hate.

When they love it, I read how democratic Excel is, how anyone can use it, how it’s the most popular programming language and IDE on the planet, and it’s where the real work gets done.

When they hate it, I read that it’s not a real language, there’s no versioning, collaboration, it’s crude, it’s terrible, it creates silos, errors, it’s wrong, and it’s hard to move away from.

I agree with a lot of these, but trying to unlock the single reason of Excel’s success is a fool’s errand; there is no single reason - there are 30 reasons. Excel has a dozen alarming anti-patterns, a thousand features, and is used in so many ways it’s effectively the plastic of computers; used in everything, used everywhere, because it’s cheap and easy.

And because it’s used in so many ways, it’s unlikely that any single tool will replace it. But that doesn’t mean something can’t absorb it, or beat it in another way.

There’s a lot to love about Excel. For most businesses, everyone at the company has a copy of it. It’s easy to hire for. It’s a fairly solid playground for data exploration. It’s not inaccurate to call it a programming language, or IDE with a visualization library. [1] As the graphing component at the end of a solid data pipeline, it’s not half bad.

There’s a lot to dislike about Excel too.

The trouble with listing Excel’s many faults is that they are so diverse and esoteric that each may only affect 5% of users or spreadsheets out there. At the same time they are so numerous, and common that each sheet may be affected by more than one. They’re also so well camouflaged that at best they look like user misuse, at worst they are invisible.

Fundamentally, this is because Excel is used in different ways by different people, so it’s effectively a different tool. A good comparison is C++, which has so many features and variations that most organizations end up using only a subset of those features, limiting portability and readability.[2] They’re technically all writing C++, but in practice they seem like different programming languages. Excel users are using the same product, but in such drastically dissimilar ways that they’re different products.

Each user or organization sees only the problem they’re solving, blinding them to features they don’t need, and highlighting the lack of features they do need. For some, versioning is the most obviously lacking feature. Office 365 and Google Sheets offer snapshots, and visual deltas, but in reality these are poorly thought out and don’t offer much. When Linda from sales wants to see why something isn’t in their quote-machine .xlsx file on O365, they can easily see that Scott touched it. But if Linda wants any degree of cherry-picking of cells across versions, or ability to branch, or merge (you know, like Git might offer), they’re out of luck. This is where several external vendors come into play, offering the features Excel lacks.[3]

If you can’t version an individual sheet, you can’t version a fleet of sheets. This is one of the many problems with templating. Linda from Sales? They use a quote-sheet to price out products when they’re on sales calls. It’s simple enough; just copy the sheet, plug in some numbers, export a region as a new sheet. But this means that they, and every other sales person is a machine in a sheet factory. Full-model .xlsx files - the features that makes spreadsheets so powerful - are now a drawbacks. You have any number of versions floating around out there, and no way to perform migrations across them. You can aggregate them into one if you’re clever enough, and link them together using Excel’s foreign worksheet feature, but you can’t wrangle them back into something manageable.

Errors don’t account for much of the perceived drawbacks of Excel, but they’re out there, and they’re bad. When I write “errors” I mean classic #DIV!, #REF!, or #VALUE! as well as off-by-one ranges, and missing formulas, or straight up copy-and-pasted values rather than formulas (which is alarmingly easy to do, and quite common). They’re all basically the same thing; the sheet didn’t function the way the user thought it should, and maybe they saw it, maybe they didn’t.

Errors are when programming goes wrong, and programming is when math gets complicated, and math gets complicated quickly because people are pretty bad at it.[4] Most Excel sheets are a bunch of numbers, and a couple of formulas adding up those numbers. By one researcher’s account, around 45% of the formulas used in spreadsheets is the SUM formula.[5] So your average user isn’t doing something particularly hard or complex, and they’re still getting it wrong. Sometimes it’s because of formula complexity, and the order of operations. But another part is just missing the right range.

Emery Berger is a professor at University of Massachusetts Amherst is that researcher. He created a tool called CheckCell that looks for errors finding ranges that are basically rectangles, (eg: A1: B10 is 2x10 rectangle) and compares rectangles. If two rectangles line up at one edge, but don’t share the same length or width, odds are the user borked up, and one has the wrong range. This is pretty simple, catches more errors than Excels’ default bug detector, and still isn’t added to Excel.

CheckCell and ExcelLint can catch errors - so there are obviously improvements to be made by Microsoft.[6][7] But the short and the long of it is it’s easy to make mistakes in Excel, especially when the displayed output can mask the underlying miscalculations.

If there are so many errors in spreadsheets, but we’re still using them, is it really a problem? If a sheet throws a #NULL! and no one’s around to see it, is it still bad? Obviously yes. If spreadsheets are doing meaningful work, whether we observe the mistakes or not doesn’t matter, they’re still mistakes.

One of the most concerning aspects of errors is how easily they can propagate and compound in a way that they can’t in other types of code. If a C++ library has a bug, you can find it, fix it, and when other libraries that depend on the buggy code get updated they will no longer have the bug as well. But the copy-and-paste nature of how most formulas get written in Excel prevent that. Copying and pasting a row-wise =SUM(A273:D273) / COUNT(A273:D274) a thousand times for one table-like structure, and then again when the sheet gets duplicated, and again when the .xlsx file itself gets duplicated shows a viral growth curve in the magnitude of severity of the bug. Find it and fix it doesn’t work when there’s no common pointer to where that formula came from. In this sense, mistakes in spreadsheets spread like gene mutations. Subsequent generations of a commonly used sheet may be fixed if the mistake is obvious enough, but that only affects the bloodline going forward.

This duplication is a key feature of Excel in many cases. Being able to say “use this same formula again” through Ctl-C Ctl-V is brilliant, and useful. So much so, it’s used for cells, columns, rows, workbooks, end entire spreadsheets. A conventional pattern at many companies is what I call “The Matrix”. It’s a multi-dimensional matrix-like pattern in which each dimension can be any one of the following: clients (for each client…), interval (daily, monthly, yearly), process type ( accounting, reporting, finance), repetition (do-overs). For example, a company that monitors ad-spend on behalf of companies might implement The Matrix pattern for $clients * $campaign * $quarter * $process * $interval * $numb_of_do_overs. This spans multiple sheets and tables, and spreadsheets themselves. Usually one of the dimensions is the obvious one to use when dividing among spreadsheets. In this case it would be clients. But because Excel makes it difficult to do things that would be easy in a relational database, you end up flattening down many dimensions into one or many tables, with a lot of duplication. The result is a set of spreadsheets that are powerful, and complex.

The Matrix pattern is so powerful (for better or worse) that it’s nearly impossible to get away from. Moving a single dimension out of the spreadsheet is possible, but hard. Moving all dimensions out of the spreadsheets and into more controlled systems? A nearly impossible sell to management, individual contributors, IT, or all three. If you want to improve any of these processes in the spreadsheets, it’s nearly as difficult, and pretty crude; create a new template, tell everyone to use it.

If you happen to work at a company that uses The Matrix, and you’re clever, you may see room for improvement, for consolidation. You combine the various spreadsheets into one. Congratulations, you have created The Beast.

Every company that is dependent on spreadsheets has The Beast somewhere. It’s the biggest .xlsx file you’ve ever seen, and it’s a nightmare. If it was leaked outside the company, the world would stop. If it breaks people wouldn’t get paid. If it produces silent errors you get audited. If you see it you have seen the face of the devil. And if The Beast Tamer (usually a very capable and pragmatic person with a director title) leaves, everyone who has seen The Beast knows it is loose, and begins to run. If you create, tame, or vanquish the beast, you get a promotion, or absent a promotion, a strong notion that perhaps your organization doesn’t deserve you.[8]

Defeating the Beast is the same way you’d defeat smaller Excel files; find a replacement for that pattern of use, get it through purchasing or acquisition or whoever approves that, and then train people. It’s easier said than done, but it’s not un-do-able.

The products that you would purchase range from run-a-sheet services and robotic procedure automation (RPA), to a SaaS product that fits your domain.[9] On the whole, one of the best things that can be done is flow-automation through run-a-sheet services or RPA. It’s best because you can change the flow, and it’s easy to designate someone in the office as the sheet-wrangler; they just plug them together, and everyone else gets to write the sheets with some degree of autonomy.

But the hard part of doing this is that IT often doesn’t want to do it. I read somewhere that most useful tools were brought in through the back door.[10] Excel and Google Sheets were brought in the front door, but with their corresponding suites, so almost everyone has a copy on their laptop from day one. You don’t need anyone’s approval to start tracking returns or calculating burn-down charts or whatever. But if you want another tool you have to ask IT, and they’re often skeptical of anything new and shiny, because they’ll get calls when it stops working. They’re not congenital jerks, they’re just doing the best they can.

Not only is it difficult to replace or automate Excel, it’s difficult to get anything new, including tools that could make Excel safer and easier. This is mainly why best-practices around Excel auditing are really manual. If I had to sum them up in two points it’d be: 1) don’t mess up, 2) have a second pair of eyes to give it a look. Certified accountants are particularly good at this, either by disposition or training. Finance people are pretty good too - mostly because of the financial costs they can incur if they mess up. Everyone else is less than successful when it comes to keeping spreadsheets free of mistakes.

As an analysis tool, Excel is okay. But inevitably someone will want to do something in Excel that gets closer to actual programming; writing something that performs an action. Because of the reactive model of Excel, it’s easy to model a solution to a problem, but hard to take that solution and push it out of the spreadsheet. There are few ways to say “run this calculation with this data, take the result and push it out to another service.”

Which brings us to VBA. To be blunt, few people use VBA, and even fewer people are using it well. VBA is never tested, always brittle, and usually copy, pasted, and modified from someone else’s spreadsheet.

Even worse than VBA is writing some sort of API to read and write spreadsheets. Usually it works like this; someone approaches a very pragmatic developer inside the company, and convinces them to write a wrapper for an Excel file. They do. It’s a click-button thing that pulls in data, writes it to the templated sheet, runs it, and pushes the result out somewhere. Then the developer leaves the company, and no one knows how it works.

VBA, and API work are the two lowest hanging fruits on the tree of automation. In some sense they are forbidden fruits. They will get you cast out of the Garden of Spreadsheets, and out into the cold dark world of software development. You’re on your way to moving off of spreadsheets entirely, for better or for worse.

As easy as it is to see the problems with spreadsheets, they’re even easier to dismiss. An amazing number of companies treat errors in Excel versioning the same way that the American public treated auto accidents before the regulatory requirement of seatbelts; operator error. If people would stop making version control mistakes in their sheets, and would stop getting into accidents, we wouldn’t need version control or seat belts. Until Excel’s Unsafe At Any Speed comes along, it’s hard to see this changing significantly.[11]

It’s just hard to give spreadsheets up. You need to convince individual contributors, management, and IT that there is a better solution, and it’s worth spending money on.

Newer companies can simply avoid it. All new companies are to some degree tech companies, so they’re at least buying, and often times building software. When they build something on top of a spreadsheet, it usually involves the word prototype, so right off the bat they’re not lying to themselves about the quality of their tooling. It’s built to be replaced, and can be thought of as something that’s supposed to break. If it breaks, it means it’s being used enough to be built properly.

This brings me to lo- and no-code tools. In the same way Excel has a thousand different use cases, each a little sliver of the pie, lo- and no-code tools each are little groups of slivers of the pie, slices even. It’s hard to be more specific than that because the market around lo and no-code tools (there has to be a better name) is fragmented, and weird; two dozen start-ups selling little drag-and-drops. I don’t mean that pejoratively, just that they’re selling a gateway to better tools, and it’s yet to be seen whether that gateway works.

It used to boggle my mind that there’s no way to git diff on a spreadsheet, write tests for it, or even see a plain-text line-by-line representation of a spreadsheet. But this is really the state of the industry when it comes to every piece of software ever written; they just don’t come with the same guarantees of error controls that code or common development software comes with. Almost all SaaS products, most video editing products, music editing suites, and GIS tools do not come with the ability to move from correct-state to correct-state through highly-granular version control or correctness checking. It seems like a chicken-egg situation; almost no one knows what it would look like for a SaaS tool that offers sales quota compensation automation software (to pick a random example) to offer version control, so no one really wants it.

Even with all the errors, all the bugs, anti-patterns, and hidden flaws, people want to use Excel. They want it, they like it, they ask for it, they complain if they don’t have it. This is, I believe, the biggest part of Excel’s dominance. And it’s for two reasons: because everyone thinks they’re good at it, and because it’s an extremely transferable and desirable skill in a company. The first one is pretty straight forward, and it’s the Lake Wobegon effect [12] - everyone thinks they’re above average. But the second reason is the doing the heavy lifting: putting Excel on your CV is good, putting GigaTechComCorp Business Dynamics Analytix 18 Pro™ is bad. Everyone wants to hire an Excel Wizard, no one wants to hire a certified expert in whatever analytics suite your former employer purchased.

There’s a feedback loop between professionals that want to use their preferred tools, and the way most businesses think about work. Because individual contributors want to use Excel, businesses use Excel, even if there are observably better tools for the job. But the business - i.e. management - needs a way to control the flow of work, usually by controlling the unit of work as well. So you end up with people whose job is, in spite of their department, position, or rung on the ladder, is to consume some form of data, and produce Excel files. These two forces; professional preference, and unit-of-work, create a cycle from which it is hard to break free. Introducing a new tool changes the unit of work. If I am good(?) at Excel, and now we’re writing reports with New-Tool-X, am I still writing XLSX reports? Uh-oh, what is my unit of work? What’s my job? If management can change the way skilled labor works, and then drop the axe, it’ll do it ten times out of ten. So in a way, Excel is a wall, or a barricade, or a picket line. It’s insisted existence has as much to do with how management and labor think about work as it does with its features.

If there are so many problems, is Excel really that bad? Yes. Yes it is. It’s slow by many standards, there are better tools. The fact that so many SaaS companies have been made peeling off a slice of the Excel pile is testament to this. [13] It’s misuse in the past decades has caused quite a lot of problems, and there’s nothing to indicate that we’ve gotten better at using Excel, or that Excel has become safer to use.

One of the most written about examples of Excel’s difficulty is from about a decade ago. A pair of policy experts write a paper (that was, uh, not peer reviewed!) that used a poorly managed spreadsheet, basically changing the financial course of several major economies.[14][15] Stephen Colbert had a solid segment on it.[16] The spreadsheet involved is still floating around out there, unknowingly (oops!) or knowingly (yikes!). When Excel spurs global austerity measures it seems like a good time to take stock of the situation.

More recently, Excel has made headlines because of its auto-interpretation features for dates. Genomics researchers found errors in each other’s research because Excel interprets the gene “SEP1” as the date September the 1st. In August 2020 (the date, not the gene!) they agreed on a simple solution; to straight up rename the problematic genes.[17]

Another example of Excels danger comes from Enron, on which Berger based some of his research.[18] Why would you use the greatest case of white-collar fraud to study spreadsheets? Well, they’re public, and they followed best practices in the backoffice, where, by all accounts, everything was being done by the book, because most employees of Enron didn’t know about the financial crimes. Basically, the Enron Corpus is like those cross-sections of cargo ships.[19]

Through analysis of the Enron Corpus, Berger was able to find that:

While the infamous SEPT1 story is bad on its own; the auto-format issues with Excel aren’t the worst part. The worst part is that it’s hard to use for number stuff in general. The headline " Scientists rename human genes to stop Microsoft Excel from misreading them as dates" is understandably catchy, but a more honest one would read “Genomics edge case fixable; math program is still a dangerous place to do math, join us while we dance on the edge of the volcano”.

But what’s the solution? What’s the fix? If Excel is so messed up, and so useful, how do we move beyond it? For motivated companies, it often looks like purchasing single-domain SaaS products for the obvious misuses at your company, and maybe purchasing some highly flexible BI tool for the more general cases. If you’ve worked for a company changing database systems, email systems, or cloud providers, you get it. It’s often a big, lift-and-shift operation, or a cut-over-on-Saturday. It’s awful.

Very few of the companies I’ve worked with do this with the intention of getting rid of Excel. Usually it’s something like “tracking referrals needs its own system”, or “we can’t have another reporting error again”. If someone with the right ideas is spearheading it, the closest you can get is “Excel causes accidents”.

The other major solution is to just avoid the problem in the first place. A hilarious number of startups I’ve talked to view it as embarrassing to use a spreadsheet for something too long. Using it to track MAUs for a couple of months is okay, using it to track ALL metrics for a year is something that is generally frowned upon. This is because startups ( and engineer-led startups in particular) are more comfortable prototyping something for a while, and buying a SaaS product when they’re not.

The middle ground is mid-sizes, technically savvy companies building a solid data pipeline, modeling their business with the help of a good data engineering team, and letting the end of the pipeline be whatever tool is fashionable; Tableau, Looker, some sort of BI, or yes, Excel.

In the end, I’m tempted to say that the whole debate around Excel is largely wrong simply because of semantics. When we say Exel, or spreadsheets, there’s no definitive, “right” way to use it. People are doing rigorous, real work in Excel. If you know how do use it, and you’re not misusing it, you’re generally fine. If you’re pressured by the constraints of your business, boss, or industry, it’s harder. But the uses cases are so wide that the talk about what Excel is used for is like talking about what code is used for. Everything. You write code to do everything. To solve problems, do things, automate tasks.

To go back to the code analogy; Excel does resemble code, but it resembles how it was used decades ago. Best practices, or at least safe practices were scarce, tooling was far less advanced than it is now. With the current state that we’re in, it’s like if professional programmers could write code to solve business problems, but not to build tooling to make that programming easier. And, like programming languages that have fallen out of use, business will still depend on Excel, but it might be managed differently. Forty years from now, we might depend on Excel the same way. Depending on GCC and C++ libraries that old isn’t uncommon, and it might not be unheard of to depend on an .xlsx file that contains some business logic for whatever app or thing you’re building.

Or something else might come along. It’ll be easier to collaborate, or version, or it’ll play nice with git, or it’ll emulate the network effects of GitHub. Or it’ll be faster, more flexible, or have better macros. Or it’ll run .xlsx or .ods files in a sandbox with a slick UI to give you more control. Or it’ll have all these things. But it’ll only become the next Excel when people stop using Excel, and that might not happen for a while. There’s no shortage of things to build. There are still a lot of beasts out there to tame.

spreadsheets | excel
2021-11-04